My Excel mortgage spreadsheet

Locoblade · 10 April 2018 at 8:28AM

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.

jlwhite · 10 April 2018 at 8:56AM

Thanks so much Locoblade, I knew someone would be able to explain it!

Bilalzaidi · 10 April 2018 at 10:20AM

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.

DingerUK · 5 May 2018 at 7:11PM

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.

Locoblade · 5 May 2018 at 8:11PM

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.

DingerUK wrote: »

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.

DingerUK · 24 August 2018 at 3:58PM

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.

miss_undastood · 10 February 2019 at 4:40PM

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:

Locoblade · 10 February 2019 at 8:50PM

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.

miss_undastood · 10 February 2019 at 11:01PM

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!

Locoblade · 10 February 2019 at 11:16PM

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 spreadsheet

Comments

Categories

Get our FREE Weekly email full of deals & guides - and it’s spam-free