My Excel mortgage spreadsheet
Comments

I'm still here
Its because you're paying off extra capital as well as saving interest and it's not taking the former into account when looking at that figure. If you look at the monthly table and find the month your mortgage gets paid off when including the overpayment (October in the 18th year), then remove the £200/month overpayment. Now go back to that 18th year in the monthly table you'll see you'd still owe about £62k at that point because there's another 6 years to go on the loan. That £62k owed is significantly more than what you'd have sitting in your savings account having saved £200/month over that 18 year point which works out to about £44k before any interest earned. In effect over 18 years you're therefore saving yourself about £18k overall by overpaying the mortgage compared to putting the money separately into a savings pot paying zero interest.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Thanks so much Locoblade, I knew someone would be able to explain it!0

This spreadsheet helps you analyse and understand mortgages. It calculates and compares various scenarios to show the effect of different variables on the mortgage calculator payments and cost of borrowing.0

Hi Locoblade :wave: I finally have a use for your spreadsheet! I have just applied for my first mortgage :eek: :T It's great so far but i'd like to make a suggestion for a future version if I may? Could you add a sheet to the end to show how current monthly payments would be affected by an interest rate change, say of 0.5, 1, 2, 3, 5 and 10% for sad people like me? It would be nice to have the info all in one place.0

I doubt there'll be any significant upgrades in the foreseeable future I'm afraid, but what your want to do is one of the reasons the second mortgage comparison tab is there, so you can add alternative mortgages or the same mortgage with a different interest rate and then compare it on the middle tab.Hi Locoblade :wave: I finally have a use for your spreadsheet! I have just applied for my first mortgage :eek: :T It's great so far but i'd like to make a suggestion for a future version if I may? Could you add a sheet to the end to show how current monthly payments would be affected by an interest rate change, say of 0.5, 1, 2, 3, 5 and 10% for sad people like me? It would be nice to have the info all in one place.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730

Hi Locoblade :wave:
I've now completed on the mortgage and have the details for my first payment next month. I've updated the spreadsheet but it's not playing nice. I've tried to email you a copy of what i've done but it bounced back to me.0 
Hi
I have added in my overpayments in the months I have made them, but I’m struggling to get the banks calculation of what I should pay to match yours
£908 is your payment and it’s closer to £970 from the bank
The balance seems to be a bit off too  it’s £188k on my app and £192k on the spreadsheet
Do you know why the figures are all so different?
I’m convinced I should be paying less than I am...!
Appreciate this is all really old now so not sure if you would be able to help or not :idea:Mortgages Oct 2020: £308,283 Jul 2021 £286,600 October 2022 £253,456 MFW22 #9 MFITT6 #350 
Obviously it will rarely if ever match up exactly as each mortgage interest is calculated slightly differently, but I wouldnt have thought it would be that far out.
What setting have you got the spreadsheet set to with regards to how it deals with overpayments (the "Keep Same" or "Reduce Monthly" option)? If you've got it set to reduce monthly then it will retain mortgage term and reduce the monthly payment to pay it off over that term so that may explain a lowe number. If set to Keep Same then it will carry on paying the same original monthly payment so using the overpayments to reduce the time it takes to pay off the loan.
As for the differing loan amounts left, again if you've had the "Reduce Monthly" setting set for a while since some overpayments then the amount left will be more than if you'd continued to pay off at the old amount.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Hi, thanks for your reply. I have it set as “keep the same” as that is how my mortgage is set up.
I overpay in large chunks which are then classed as part redemptions and my monthly payment is recalculated whilst my term remains the same. It is probably to do with the daily interest calculation why the balances aren’t balancing correctly?
At the moment I am taking the lower monthly payment but still overpaying large chunks annually. What I will do soon is decide I want to pay £x per month (say £1000) and I will set up a standing order to work out the difference between what I’m contracted to pay and £1000
I guess I just hoped the numbers would tally closer as I wanted to avoid a spreadsheet that was 13,000 rows long!Mortgages Oct 2020: £308,283 Jul 2021 £286,600 October 2022 £253,456 MFW22 #9 MFITT6 #350 
miss_undastood wrote: »Hi, thanks for your reply. I have it set as “keep the same” as that is how my mortgage is set up.
I overpay in large chunks which are then classed as part redemptions and my monthly payment is recalculated whilst my term remains the same. It is probably to do with the daily interest calculation why the balances aren’t balancing correctly?
What you've described there is the "Reduce Monthly" setting though, keeping the term the same but the monthly payment gets recalculated?
How long has the mortgage been running and how many overpayments have you done? Without seeing your real figures and spreadsheet its hard to say why it might have drifted that much. Happy to have a look if you want to contact me via the email in the spreadsheet but obviously understand if you'd rather not share that.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730
Categories
 All Categories
 343.1K Banking & Borrowing
 250.1K Reduce Debt & Boost Income
 449.7K Spending & Discounts
 235.2K Work, Benefits & Business
 607.9K Mortgages, Homes & Bills
 173K Life & Family
 247.8K Travel & Transport
 1.5M Hobbies & Leisure
 15.9K Discuss & Feedback
 15.1K Coronavirus Support Boards