Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@.

Search
  • FIRST POST
    • Locoblade
    • By Locoblade 14th Sep 08, 8:10 PM
    • 754Posts
    • 572Thanks
    Locoblade
    My Excel mortgage spreadsheet (now Mac Compatible)
    • #1
    • 14th Sep 08, 8:10 PM
    My Excel mortgage spreadsheet (now Mac Compatible) 14th Sep 08 at 8:10 PM
    Hi All


    Over the past couple of weeks Ive given a few people on here some figures to compare mortgages with including the capital left after certain timeframes and the savings of offsetting certain sums. This was done using a heavily modified version of the Microsoft Amortization Schedule spreadsheet that Ive re-written to allow several things that most of the existing online/spreadsheet calculators didn't seem to be able to do, such as:
    • Interest only / capital repayment mortgages with option to part pay
    • Offset mortgages, including average offset current account balance
    • Calculation when offset pot could pay off the mortgage remainder.
    • Monthly and one off overpayments / offset payments
    • Change of rates after introductory period expires
    • Ad-hoc rate changes for variable/tracker mortgages]
    • Additional borrowing at any time
    • A few pretty graphs
    • Edit: v1.02 allows you to compare two different mortgage products to see which is cheapest for you.
    • Edit: v1.03 adds in early redemption charges and exit fees.
    • Edit: v1.04 Comparison for savings account/ISA against offset/overpayments on Mortgage 1
      Comparison of two mortgages with differing fees added to loan, showing break even month
      Seperate fees added to loan box.
    • Edit: v1.05 Break even month now takes into consideration the upfront fees and also completion fee to give a more accurate figure.
    • Edit: v1.06 Fixes the additional borrowing feature, it now works!
    • Edit: v1.07 Fixes the total cost / total capital remaining calculation in the Comparison sheet.
    • Edit: v1.08 Fixes Cumulative Interest calculation that was previously miscalculating the first year. Also uploaded a modified version for Mac and older Excel.
    • Edit: v1.09 Fixes the same bug as 1.8 tried unsuccessfully to fix in certain circumstances, hopefully second time lucky! I've also started naming the files without the version number so that for future releases, those following direct links to my web space (rather than clicking the links here) will also get the latest version by default. Ive also added an entirely optional donate button as a few appreciative users asked/suggested this, any donations will go towards my web hosting which I've recently had to expand to accomodate the average 800-1000 downloads per month!
    • Edit: v1.10 Changed the formatting to allow end users to change column widths and hide columns if required.
    I have where possible compared the spreadsheet against other online calculators so I hope its all correct. Please only download the Mac version if the main one in the first link doesn't work with your version of Excel.

    Both downloads can be accessed here:

    http://spreadsheet.locostfireblade.co.uk

    I've put the links on a simple web page now as I intend on eventually expanding it to include stuff like an FAQ to answer some of the more common questions that get asked on here and via e-mail.

    Any feedback most welcome
    Last edited by Locoblade; 07-05-2010 at 9:29 PM. Reason: Upload of v1.10
Page 5
    • Kavanne
    • By Kavanne 5th Oct 08, 6:50 PM
    • 5,014 Posts
    • 2,551 Thanks
    Kavanne
    Thanks to your new sheet I can see how even £200 a month overpayments on my mortgage would shave off 2 yrs!! MF in 16 yrs here I come lol!!
    • Locoblade
    • By Locoblade 5th Oct 08, 7:21 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    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.

    thanks
    excited.
    Originally posted by excited
    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: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • excited
    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.

    thanks

    excited
  • mankell
    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
    • By Locoblade 15th Oct 08, 4:49 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    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: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • leveret
    • By leveret 15th Oct 08, 11:11 PM
    • 4 Posts
    • 1 Thanks
    leveret
    The spreadsheet will only work with Excel 2002 or newer though, and may not work with Works or OpenOffice
    Originally posted by Locoblade
    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 www.openoffice.org 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
    • By Locoblade 16th Oct 08, 10:06 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    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.

    http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator%20v1.5.xls
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • hpuse
    • By hpuse 17th Oct 08, 5:32 PM
    • 1,064 Posts
    • 144 Thanks
    hpuse
    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!
  • Wayno
    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
    • By nv200 26th Oct 08, 4:22 PM
    • 49 Posts
    • 41 Thanks
    nv200
    This spreadsheet is fantastic thanks for all the hard work!
  • PayAttention
    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?
  • Albee
    Hi,

    Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

    In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a 85,5000 mortgage and 9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid 770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be 91,252 but according to NR I owe them 93,209:confused:

    Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.
  • gazareth
    Great spreadsheet! Amazing to see just how effective even small overpayments are.
    • Locoblade
    • By Locoblade 31st Oct 08, 9:56 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    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!
    Originally posted by Wayno
    Hi

    Unfortunately, as I found out a week or so ago, that bit doesn't seem to work at the moment, and having been studying for an exam recently I haven't had time to look into why and fix it.

    I'll make sure I do that before I release the next version though, for now what you could do is put in a one-off additional payment as a negative number (e.g. -20,000) for the month you want that additional borrowing, as I think that should do prety much the same thing in terms of repayment calculations etc.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 31st Oct 08, 10:13 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    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?
    Originally posted by PayAttention

    Hi

    Could you maybe PM me the exact figures you added in so I can have a look please, as when I use the default settings as downloaded (100k loan over 25 years etc), and put in a 300 monthly overpayment, or a 300 monthly offset, the amount of interest is virtually the same with either. There will be a slight error/variation in there compared to overpayments because of the limitations on how it can all be calculated. When offsetting for example, the spreadsheet looks at the offset total and the remaining capital, and if the former is larger than the latter, it sets the interest rate to zero and starts drawing the monthly payment out of the offset. Obviously if the offset is 1 under at the end of a month, it will go through another full monthly cycle of putting money into the offset before it starts the above process. If it was 1 over, then it will obviously kick in straight away so will pay things off a month earlier which will affect the figures you see. I can't think why it would be as much as 7% out though.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 31st Oct 08, 10:31 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Hi,

    Fantastic spreadsheet! It'll make my search for a decent remortgae in the next couple of months a lot easier! Cheers!

    In an effort to get a proper handle on my financial circumstances following an accident in July that's left me unable to work I used your spreadsheet to calculate how much I owe NR as I took out a 85,5000 mortgage and 9,500 as an unsecured loan in late Oct 2005 at a fixed 5.99% for 3 years the onto SVR (7.48%). This year is the first time I have been able to make over payments and have paid 770 so far (not alot of money to some - but alot to me!). Anyway, using the spread sheet my present debt to NR should be 91,252 but according to NR I owe them 93,209:confused:

    Is there a problem with the spread sheet or have NR got their figures wrong and I need to question it? Any advice would be much appreciated.
    Originally posted by Albee
    Hi

    Are you sure they aren't giving you a figure including any early redemption fees, or fees that were added at the start of the mortgage? Also are you sure the unsecured loan is also at the same rate as the loan?

    I can't guarantee the spreadsheet is correct obviously, but I've compared it as much as possible with other mortgage calculators online etc, so I believe it should be correct, at least for the fairly simple calculations like yours.
    (edited to add) - The spreadsheet does only do monthly compound interest calculations though, so if your lender uses daily interest etc it will be slightly different, although only by a few pounds per month max.

    Can you send me the remainder of your details that you put in, how long the loan is for, when you made overpayments and how much etc?
    Last edited by Locoblade; 31-10-2008 at 11:42 PM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Jett
    wow, very impressive
  • Billy_Bob
    Been thinking about attempting to write a spreadsheet like this for ages. Glad I found this and saved the time.
  • benjdr
    Just a bump and a thank you. This thing rocks.
    • Locoblade
    • By Locoblade 12th Nov 08, 2:24 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    No probs Ben, the only bug I know of which Ive yet to get around to fixing is mentioned above, namely adding something into the additional borrowing column doesnt work, but the workaround if you wish to look at additional borrowing is to either add the additional borrowing into the overpayments column as a negative number, or use the second loan spreadsheet to calculate it seperately.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

1,885Posts Today

6,676Users online

Martin's Twitter