My Excel mortgage spreadsheet



  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    excited wrote: »
    Locoblade, Thanks for making the calculator available, Can I ask say i want to make a one off payent do i jut lower the mortgage amount.


    If its an overpayment when you first take out the mortgage then you can do that, but to keep it as clear as possible, you're probably better off putting in the full mortgage amount, then putting the one-off overpayment into month 1 of the Monthly table, in the overpayment column.

    If its a one-off overpayment in the middle of your mortgage term (ie not the month when you're remortgaging), then you need to do as above, find the correct month in the Monthly table and put it into the overpayment column, as obviously it will only reduce the capital/interest from the month you pay it in, not from the start.
    My Excel Mortgage Calculator Spreadsheet:
  • excited
    excited Posts: 21 Forumite
    Thanks I found it, I started reading from the wrong end of this thread. If i had only looked at the beginning and read it through i would have found the answer. Sorry for wasting your time.

    Bloody good spreadsheet.


  • great spreadsheet. Been looking for something like this for ages. I've got an offset mortgage which will only offset up to 80% of the mortgage total with savings. Any chance of an amendment that would include that function. Thanks again.
  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Cheers, the problem I have is if I create options like that for every little mortgage specific nuance, the spreadsheet would become (even) more complicated than it is now, both for people to use and for me to write, so it wouldnt be something I'd want to incorporate into the standard sheet.

    I'll have a look later though to see whether I could easily incorporate that into a one-off for you, but I think it might be rather complicated so no promises.
    My Excel Mortgage Calculator Spreadsheet:
  • Locoblade wrote: »
    The spreadsheet will only work with Excel 2002 or newer though, and may not work with Works or OpenOffice

    The spreadsheet didn't completely work with OOo version 2 (had various cross-sheet reference errors), but on a limited check it seems to work well with OOo v3. OOo3 downloads seem to be possible again now, after crashed under the load when v3 was first released earlier this week !

    Many thanks for a useful piece of work which goes a long way to clarify the differences in cost of the various mortgage offers available.
  • Locoblade
    Locoblade Posts: 795
    Name Dropper First Post First Anniversary
    Version 1.5 just uploaded, there's not much change in this, the only difference is that it takes into consideration upfront fees and completion fees when calculating the break even month if compared to another loan.
    My Excel Mortgage Calculator Spreadsheet:
  • hpuse
    hpuse Posts: 1,117
    Name Dropper First Post First Anniversary Combo Breaker
    Nice one ! I liked the offset over payment display feature(fixed and variable drop down) and the yearly/monthly offset saving column display .
    It definitely helps someone to plan ahead especially in what appears to be tough times. Awesome spreadsheet. Kudos to you Locoblade dude!
  • Locoblade I've been searching the web for a spreadsheet like yours for a while now and yours is by far the best i have personally come across.
    While navigating around the sheet, learning how it works. I tried using the addtional borrowing column, each time i place a figure in th cell it doesn't alter the cumulative overpayment or end balance. (this was after having paid in more over payments than wishing to draw on addtional borrowing). Was just wondering if you can let me know how this part works?

    Great spreadsheet!
  • nv200
    nv200 Posts: 52 Forumite
    This spreadsheet is fantastic thanks for all the hard work!
    Mortgage when started: £127,500
    Current mortgage Jan2016: £127500
    Mortgage free day: Dec 2022
    OverPaid so far; £0
  • Locoblade,

    Great spreadsheet, many thanks. If I compare the effect of adding a monthly overpayment vs the effect of monthly offset savings, as expected the reduction in the time to pay off the loan is greater using overpayment. However, the total interest is marginally higher (about 7%). Interest rate was fixed throughout and I excluded all fees, to keep the comparison simple. Intuitively I would not not expected this. Any thoughts on this?
Meet your Ambassadors


  • All Categories
  • 341.7K Banking & Borrowing
  • 249.7K Reduce Debt & Boost Income
  • 449.1K Spending & Discounts
  • 233.8K Work, Benefits & Business
  • 606K Mortgages, Homes & Bills
  • 172.5K Life & Family
  • 246.7K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.8K Discuss & Feedback
  • 15.1K Coronavirus Support Boards