My Excel mortgage spreadsheet
Comments

Hi,
Is there a way to see how two mortgages compare if your over payment is a percentage of the balance?
For example, I would like to compare two mortgages from HSBC; one has a 5 year fixed term with no fee and other one is 2 year fixed with a fee. So far, I can input this easily. HSBC allows me to do over payment. They check the balance at the beginning of the year and in that year I can overpay up to 10% of the balance without incurring a fee. I would like to include the maximum over payment allowed in the calculation and see which deal is better. Overpayment allowed would be different every year since it will be a percentage of the balance.
Thank you.0 
Hi
Yep you can do that. In the Montly Table tab  Monthly Overpayment column, add an equation to work out 10% of the current balance for the month you want to do the overpayment.
Here's an example of a 10% overpayment done on the 12th month of the mortgage, done by adding the equation "=K23*10%" to the 12th overpayment cell where K23 is the corresponding cell where the current balance is (the box highlighted blue in the second column)
Once you have the correct month populated, you can then copy that equation down every 12 rows so it recalculates once each year.
My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Hi
Yep you can do that. In the Montly Table tab  Monthly Overpayment column, add an equation to work out 10% of the current balance for the month you want to do the overpayment....
Thank you for your quick reply locoblade. The solution you suggestion is good except that overpayment will be made monthly (total allowed for that year divided by 12). If I add it all once per year, I do not think it will calculate the interest properly as monthly payment is fixed but interest is based on balance. I will need to add monthly payments for every month; different for that year. It is not a big deal but thought I would run it by you to make sure I am doing it right.
Thanks.0 
Thank you for your quick reply locoblade. The solution you suggestion is good except that overpayment will be made monthly (total allowed for that year divided by 12). If I add it all once per year, I do not think it will calculate the interest properly as monthly payment is fixed but interest is based on balance. I will need to add monthly payments for every month; different for that year. It is not a big deal but thought I would run it by you to make sure I am doing it right.
Thanks.
You can just adjust the cells to make the extra payment monthly at 1/12 of the initial calculation
Depending on lender you may need other adjustments as some lenders don't allow the max overpayment and keep the payment the same.0 
getmore4less wrote: »You can just adjust the cells to make the extra payment monthly at 1/12 of the initial calculation
Depending on lender you may need other adjustments as some lenders don't allow the max overpayment and keep the payment the same.
Thank you for your reply. I will need to figure out what I need to do here. I think I am a bit exception to the rule here. I have a regular over payment that goes every month with normal payment. I calculate at the beginning of the year how much over payment that would be. Then I take the remaining over payment allowed and pay the in the first 6 months so that by the end of the year all over payment allowed is made. I guess every bank allows different over payments. I will do the calculations manually and see which one works out better.
Thank you.0 
pay the money as soon as you can that is always better.
If you can hit the limit early in the ERC free year then consider a 0% purchase credit card to free up cash flow in the early months till you hit the limit then pay off the card with the money you would have used to pay the mortgage.0 
Change the "=K23*10%" equation to "=(K$23*10%)/6" and then copy it down to the next 5 months below the first equation. If you want to repay it over 4 months change the divider from 6 to 4 and only copy down another 3 cells etc.
Because you're now using the $ sign to lock the equation to row 23 even when dragged down a few rows, it won't now copy/paste down to future years so you'll need to manually change the row number for each year you want to calculate. There's probably a way to write the equation so it will change correctly as you copy it down but can't think of a way off the top of my head.
Edit: Actually if you make the first cell "=(K23*10%)/6" (without the dollar sign) and then the 5 cells below it just type in =P23 (the cell where the equation is in), then you can copy and paste those cells down to subsequent years and it will automatically change.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Hi, Can I just say firstly that this is a masterpiece! Really clever on the solutions you came up with on how to produce certain outcomes
Just a question on some formulas I noticed on the 'Mortgage 1 Monthly Table' Sheet. Particularly on Column D (End of Year). There's a whole bunch of them in between the year numbers and it doesn't seem to mean anything?
For example, If I go to 'Mortgage 1 Info and Key Figures' and enter 2020 for the year and Feb as the month, then go to the 'Mortgage 1 Monthly Table' Sheet  I can see an error on Cell D53. The formula there seems to be referencing a validation to return month + 1 if True?
=IF('Mortgage 1 Variables'!C49=1,F43+1,"")
And there's a whole lot more along Column D that you don't see all as the validation is returning "" if FALSE.
Wondering if you could enlighten for my own learning?
Thanks1 
Hi
Thanks for the compliments! Most of it was written over 10 years ago now so to be perfectly honest I'm a little rusty on some of the underlying workings myself now but I think it's some left over code from when I was trying to make the year column stop counting up when the mortgage was paid off but I ended up not bothering and setting them statically for a maximum of 50 years so should have deleted those formulas as they're not doing anything useful now! Good spot :rotfl:My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Bloomin' excellent this  I think the main mortgage calculators should link here and the point needs to be made that more affordable monthly payments or total cost over the initial terms are not the only considerations when picking a mortgage.
This is one of the only tools I could find in a week of googling to compare those costs to the capital and overall cost of the mortgage. I think MSE should include this information in the general mortgage guides.2
Categories
 All Categories
 342.9K Banking & Borrowing
 250K Reduce Debt & Boost Income
 449.6K Spending & Discounts
 235K Work, Benefits & Business
 607.7K Mortgages, Homes & Bills
 172.9K Life & Family
 247.7K Travel & Transport
 1.5M Hobbies & Leisure
 15.9K Discuss & Feedback
 15.1K Coronavirus Support Boards