My Excel mortgage spreadsheet

14243454748

Comments

  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 10 April 2018 at 8:33AM
    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=1157173
  • jlwhite
    jlwhite Posts: 121 Forumite
    Thanks so much Locoblade, I knew someone would be able to explain it!
  • Bilalzaidi
    Bilalzaidi Posts: 1 Newbie
    edited 10 April 2018 at 10:22AM
    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
    DingerUK Posts: 64 Forumite
    Name Dropper First Anniversary First Post
    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
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    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.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • DingerUK
    DingerUK Posts: 64 Forumite
    Name Dropper First Anniversary First Post
    edited 24 August 2018 at 4:00PM
    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
    miss_undastood Posts: 236 Forumite
    Name Dropper First Anniversary Combo Breaker First Post
    edited 10 February 2019 at 5:20PM
    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 MFW-22 #9 MFIT-T6 #35
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 10 February 2019 at 8:52PM
    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=1157173
  • 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 MFW-22 #9 MFIT-T6 #35
  • 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=1157173
Meet your Ambassadors

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