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 4
  • _dave_
    Hi Locoblade,

    First of all, excellent spreadsheet - well done!

    Now, I've been trying to look at the "cost savings" from overpayments, and I noticed your "Interest Saved by Overpayment/Offset" cell. But that seems to just add up the differences over the term, without allowing for the "time-value" of money. Is there any way to get the cost savings in "todays money" from your spreadsheet, much like the following calculator does? :-

    http://calc-calc-calc.net/get/calc/Mortgage-Overpayment/?l=100000&i=6&t=25&m=744.30&d=4

    That shows the difference in total payments as 193,290 - 166,252 = 27,038 (compared to 27,035.73 in your spreadsheet), but also the difference in "Effective Overall Cost (Present Value of all payments)" as 122,471 - 117,498 = 4,973 (when allowing for inflation at 4%).

    Can your spreadsheet produce similar figures?

    Thanks!
    • Locoblade
    • By Locoblade 24th Sep 08, 10:15 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Hi Dave

    Im probably missing the point but can you explain exactly what the present worth figure tells you as the average mortgage payer? I can see why you might want figures to compare the amount of interest saved on overpayments against the amount you'd earn by putting that money into savings, is that effectively what you mean?

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Locoblade
    • By Locoblade 24th Sep 08, 10:20 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Version 1.3 now uploaded

    http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator%20v1.3.xls

    There's no huge changes in this one, Ive just added early repayment charges (ERC) and exit fees into the equation so you can see the complete cost when considering remortgaging again.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • _dave_
    Im probably missing the point but can you explain exactly what the present worth figure tells you as the average mortgage payer?
    Originally posted by Locoblade
    I'm just trying to get a handle on how much the mortgage costs overall, in "today's money". What I mean is, if prices and wages generally increase each year, then £100 in 20 years time will be "worth" less than £100 now (I guess it'll be a smaller proportion of my wages then, and would buy less than £100 will today). If inflation is high enough, then £100 in 20 years time could easily be worth less than £50 today.

    For instance, £600 each month for the next 15 years (£108,000 in total), might buy the same number of loaves of bread (or pints of milk) as £400 each month for the next 25 years (£120,000 in total). In that case, I'd tend to think of a mortgage at £600 a month for 15 years as the same cost as a mortgage for 25 years at £400 a month, even though the second one "looks" like it costs £12,000 more! (The payments on both would buy the same amount of bread or milk...)

    Does that make sense?


    I can see why you might want figures to compare the amount of interest saved on overpayments against the amount you'd earn by putting that money into savings, is that effectively what you mean?
    Originally posted by Locoblade
    I think that's one of the two suggestions on the online calculator that I mentioned (their description '(B)'), but I was more thinking about allowing for inflation (their description '(A)').
    Last edited by _dave_; 25-09-2008 at 7:38 PM.
    • Locoblade
    • By Locoblade 25th Sep 08, 9:17 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Hi Dave

    Yup it makes sense now, I think it would need a fair bit of calculation to get it working though. If I could be convinced that it would be of interest to others as well as you I'll put it on the list to look at, but Im not entirely sure many would have a need for that information or know how to interpret it?

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • bubblesmoney
    • By bubblesmoney 25th Sep 08, 11:31 PM
    • 2,127 Posts
    • 2,412 Thanks
    bubblesmoney
    finally figured out how to nominate this post for the 'post of the month award' and have nominated this thread for the award. see link i do hope anyone who found the calculator saved them money and time would spare a few seconds to nominate his post for the 'post of the month award for september'
    bubblesmoney
  • _dave_
    ... If I could be convinced that it would be of interest to others as well as you I'll put it on the list to look at, but Im not entirely sure many would have a need for that information or know how to interpret it?
    Originally posted by Locoblade
    Thanks Locoblade.

    I'd have thought that most people would understand figures in "todays" money easier than in "future" money -- for instance, if someone said your pension in 40 years time could be 50,000 per year (!yippee! - sounds great!), but that was equivalent to 10,000 in todays money, which do you think most people would find easier to interpret?

    When you add up the cost savings from overpayments by just summing all payments, then the total is a mixture of "todays" money and "future" money. I really find that quite hard to interpret, myself!
    • 1978
    • By 1978 26th Sep 08, 9:25 AM
    • 72 Posts
    • 25 Thanks
    1978
    this will come in handy when i change next year also.

    many thanks.
    • Locoblade
    • By Locoblade 27th Sep 08, 4:12 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Dave, I'll give it a go and see how it goes, I can see the logic / benefit of knowing that info, just needs to be presented in a way that doesn't confuse more than it enlightens
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Kavanne
    • By Kavanne 27th Sep 08, 4:20 PM
    • 5,014 Posts
    • 2,551 Thanks
    Kavanne
    Locoblade, really amazing, thank you thank you!!
    • Locoblade
    • By Locoblade 27th Sep 08, 4:28 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Thanks BTW to BUBBLESmoney for nominating me for post of the month, and to the others who have also put comments on that thread, much appreciated
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • sleepless saver
    • By sleepless saver 28th Sep 08, 3:53 PM
    • 2,688 Posts
    • 2,416 Thanks
    sleepless saver
    Great spreadsheet, many thanks. I can use it on OpenOffice 1.3 with no apparent problems so far. I had to remove the protection on the monthly sheet to be able to change figures but that is all.
    • uzubairu
    • By uzubairu 28th Sep 08, 5:15 PM
    • 1,172 Posts
    • 3,135 Thanks
    uzubairu
    Great tool and easy to use.

    Thanks Locoblade!
    • Locoblade
    • By Locoblade 3rd Oct 08, 10:54 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Version 1.4
    Version 1.4 just uploaded, this might be the last for a bit as I think there's a danger it could become too complicated to use in its current guise if I add much more.

    This one adds the following:
    • 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
    • Seperated out fees added to loan
    Ive also realised that the fairly conservative colour scheme I chose in Excel 2007 gets converted into a rather painful colour scheme in Excel 2003, so although Ive not changed the colours, I have allowed editing of the formats so you can pick your own if you find it really offensive
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • tightasaduckas
    Locoblade

    Any chance you could provide a current link to version 1.4

    cheers
  • herbiesjp
    The link is on the first post
    I am a Mortgage Adviser

    You should note that this site doesn't check my status as a Mortgage Adviser, so you need to take my word for it. This signature is here as I follow MSE's Mortgage Adviser Code of Conduct. Any posts on here are for information and discussion purposes only and shouldn't be seen as financial advice.
    • Locoblade
    • By Locoblade 4th Oct 08, 1:12 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Locoblade

    Any chance you could provide a current link to version 1.4

    cheers
    Originally posted by tightasaduckas
    Sorry, yep I updated the first post but meant to put it in the new post too

    http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator%20v1.4.xls
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • BARGAINHUNTER!
    Wow! What an amazing spreadsheet and totally addictive! I have just spent the last half hour playing around with it! Thank you so much for developing it and sharing it with us!
    MFW 2011 challenge - Aim: Overpay 414.26 a month/5,000 a year. Overpayment Total to date: 414.26Mortgage start 28/9/07 46,217.00 Mortgage balance as of 25/05/11 24,490.58
    Interest saved as of 25/05/11: 2,849.84 Projected term reduction as of 25/05/11: 9 years 11 months
    • beniamino
    • By beniamino 5th Oct 08, 11:22 AM
    • 54 Posts
    • 9 Thanks
    beniamino
    This looks really useful. Would you mind releasing a version with all cells unlocked for editing? The locking doesn't seem to work properly on Mac Office 2008, so it's not possible to edit the yellow columns. Thanks!
  • excited
    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.
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,739Posts Today

6,497Users online

Martin's Twitter