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, 7:10 PM
    • 760Posts
    • 572Thanks
    Locoblade
    My Excel mortgage spreadsheet (now Mac Compatible)
    • #1
    • 14th Sep 08, 7:10 PM
    My Excel mortgage spreadsheet (now Mac Compatible) 14th Sep 08 at 7: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 8:29 PM. Reason: Upload of v1.10
Page 21
  • dominicallkins
    Seriously great work - fantastic spreadsheet.

    Is there any chance that you could share the password with me over PM (I'd like to add a couple of calcs so that I can do some goal seeks for the comparisons). No worries if you'd rather not - would just save me some time.

    Thanks
  • UncleSimit

    Is there any chance that you could share the password with me over PM (I'd like to add a couple of calcs so that I can do some goal seeks for the comparisons). No worries if you'd rather not - would just save me some time.

    Thanks
    Originally posted by dominicallkins
    Me too, if possible.

    Cheers.
    • sKiTz-0
    • By sKiTz-0 18th Oct 14, 9:31 AM
    • 905 Posts
    • 671 Thanks
    sKiTz-0
    I could do with the password for this also - I've just moved to a new laptop and gone from Office 2003 (where this sheet worked perfectly) to LibreOffice. Which seems to have protected every single cell on the sheet. I am unable to edit anything.
    This is WAY more fun than monopoly.
  • yogurtspy
    Great tool, but I don't suppose there is an ODS version of the spreadsheet? I'm using Libre and all the editable cells are protected when using the xlsx version.
    • MoneyDummy
    • By MoneyDummy 20th Nov 14, 7:13 PM
    • 15 Posts
    • 1 Thanks
    MoneyDummy
    Protected cells
    Hi, just found this great spreadsheet but I cannot enter any figures in the yellow columns as suggested as they are password protected.
    I am using Excel 2000.
    Any help would be great as want to know the difference between lump sums & monthly overpayments.
    Thanks
  • Passcovery
    There's a risk of being gauntleted but all the same. It is stated in the article "Password protection in Excel. How to break it?" that in fact the document is not ptotected by a password - collision passwords set for Excel sheets can be found immediately by any password recovery program.
    • sKiTz-0
    • By sKiTz-0 16th Dec 14, 5:15 PM
    • 905 Posts
    • 671 Thanks
    sKiTz-0
    Has anybody managed to get this to work in Libre Office or anything similar? I don't want to pay for MS Office for the sake of one spreadsheet.

    Cheers.

    sKiTz
    This is WAY more fun than monopoly.
    • Mr Black
    • By Mr Black 19th Apr 15, 9:34 AM
    • 18 Posts
    • 17 Thanks
    Mr Black
    The sheet is great, but the cells are protected in Open Office. What's the password? It's useless without it for me.
    • Locoblade
    • By Locoblade 19th Apr 15, 11:24 AM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Jeez guys, read the front page of the spreadsheet, it's there to help!

    The original spreadsheet WILL NOT WORK with anything other than relatively recent versions of Windows based Excel, and when I say "relatively" I'm taking anything from the last decade! .
    If you want to run or on Open Office, Libre Office or on a Mac, try downloading the "compatibility" version. It should work but I can't guarantee it, certainly you couldn't write it natively in something like Open Office so it just depends on how well it can convert/understand all the Excel functions.
    Last edited by Locoblade; 19-04-2015 at 11:30 AM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Mr Black
    • By Mr Black 19th Apr 15, 11:31 AM
    • 18 Posts
    • 17 Thanks
    Mr Black
    It works just fine in OpenOffice its just that it asks for the password to unprotect the cells when I want to add adhoc payments in the yellow sections.
    • Locoblade
    • By Locoblade 19th Apr 15, 11:37 AM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Yes because you haven't got the compatibility version. There's no cell locking in the compatibility version because Open Office etc doesn't understand them properly, it just locks the entire sheet rather than individual cells
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Mr Black
    • By Mr Black 19th Apr 15, 11:52 AM
    • 18 Posts
    • 17 Thanks
    Mr Black
    I see! ha thanks for helping me out, quite clearly I'm a bit of a biff.
    • Locoblade
    • By Locoblade 19th Apr 15, 11:59 AM
    • 760 Posts
    • 572 Thanks
    Locoblade
    No worries, I could just publish the compatibility version really but it's got extra hidden sheets to compensate so it's quite a bit bigger file size and not quite as pretty.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Mr Black
    • By Mr Black 19th Apr 15, 12:03 PM
    • 18 Posts
    • 17 Thanks
    Mr Black
    No worries, I could just publish the compatibility version really but it's got extra hidden sheets to compensate so it's quite a bit bigger file size and not quite as pretty.
    Originally posted by Locoblade
    Would you mind emailing me that please?
    • Locoblade
    • By Locoblade 19th Apr 15, 3:37 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    It's on the website, right next to the regular download!
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • always29
    • By always29 1st May 15, 2:33 PM
    • 475 Posts
    • 2,388 Thanks
    always29
    Locoblade, thank you for this spreadsheet, it is truly awesome. I've just downloaded and working out how to pay my mortgage off by the time husband and I are 50. I think we might have to sell a kidney each or something but it's great to play with the figures!
    • ellie27
    • By ellie27 1st May 15, 8:30 PM
    • 1,067 Posts
    • 719 Thanks
    ellie27
    Thanks for the spreadsheet! It is great.

    I would be looking to make a lump sum overpayment every year or so, is there anywhere I can input that to see how it changes things? I see there is a monthly overpayment but it was going to be a lumpsum each year perhaps.

    Thanks!
    • Locoblade
    • By Locoblade 1st May 15, 9:03 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Hi

    Thanks, yep just go into the "Mortgage 1 Monthly Payment" page and put the lump sum in the "Monthly Overpayment" column on the month you want it to be paid, then scroll down and repeat for subsequent years leaving all other months as zero
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • ellie27
    • By ellie27 1st May 15, 9:24 PM
    • 1,067 Posts
    • 719 Thanks
    ellie27
    Wow, its amazing how much a lumpsum payment every now and again can make in the amount of interest you will have paid by the end of the mortgage! Thanks!
    • carlosveiga
    • By carlosveiga 23rd Jul 15, 10:52 PM
    • 4 Posts
    • 1 Thanks
    carlosveiga
    Kudos for such a great tool!

    I have a question - is there a recommended way to update the sheet with the "actuals" from the mortgage payments that the bank takes every month, in case the values aren't always correct 100%?
    Thanks for your help!
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

3,998Posts Today

4,844Users online

Martin's Twitter