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
    • 727Posts
    • 566Thanks
    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 1
  • lapat
    • #2
    • 14th Sep 08, 8:21 PM
    • #2
    • 14th Sep 08, 8:21 PM
    cant look at as it jammed my laptop up
    need to have a lightbulb moment
    • Gorgeous George
    • By Gorgeous George 14th Sep 08, 8:22 PM
    • 7,787 Posts
    • 8,466 Thanks
    Gorgeous George
    • #3
    • 14th Sep 08, 8:22 PM
    • #3
    • 14th Sep 08, 8:22 PM


    My old desktop downloaded it just fine.

    Good work.


    GG
    There are 10 types of people in this world. Those who understand binary and those that don't.
    • Locoblade
    • By Locoblade 14th Sep 08, 8:28 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    • #4
    • 14th Sep 08, 8:28 PM
    • #4
    • 14th Sep 08, 8:28 PM
    cant look at as it jammed my laptop up
    Originally posted by lapat
    Not sure why that would be, do you have Excel installed, and did you right click - save as rather than try and open the link in IE?
    • lilmissmup
    • By lilmissmup 14th Sep 08, 8:31 PM
    • 6,782 Posts
    • 8,674 Thanks
    lilmissmup
    • #5
    • 14th Sep 08, 8:31 PM
    • #5
    • 14th Sep 08, 8:31 PM
    Thanks a lot i won't have a mortgage for a year at least but this looks amazing
    Debt 0% until March 2016 £2402 - Savings £2330 13/01/2016
    Now a SAHM trying to earn some spare pennies each month

    Money earned since 25/04/2015 - £213.13


    • KTF
    • By KTF 14th Sep 08, 8:35 PM
    • 4,358 Posts
    • 1,755 Thanks
    KTF
    • #6
    • 14th Sep 08, 8:35 PM
    • #6
    • 14th Sep 08, 8:35 PM
    Very good. Another one for my collection
  • lapat
    • #7
    • 14th Sep 08, 8:36 PM
    • #7
    • 14th Sep 08, 8:36 PM
    ill try again
    i had sky anytime playing at the same time that may have caused the problem
    need to have a lightbulb moment
    • sujman
    • By sujman 14th Sep 08, 8:36 PM
    • 510 Posts
    • 264 Thanks
    sujman
    • #8
    • 14th Sep 08, 8:36 PM
    • #8
    • 14th Sep 08, 8:36 PM
    thanks for this.

    I have been pondering about whether offset mortgages are worth it and this may help me to double check calculations given by other websites.
  • lapat
    • #9
    • 14th Sep 08, 8:38 PM
    • #9
    • 14th Sep 08, 8:38 PM
    sorted thanks for that
    need to have a lightbulb moment
    • pstones578
    • By pstones578 14th Sep 08, 8:49 PM
    • 451 Posts
    • 95 Thanks
    pstones578
    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
    Ive finally (I think!) got it to a "finished" condition so I thought I'd post a link to it on here so that other people can use it too. It would also be good to get some others to test it to see if there are any scenarios where it gives false information, although I have where possible compared it against other online calculators so I hope its all correct.

    Here's the link to download it: http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator.xls

    It was written in Excel 2007 but has been saved so it's compatible with earlier versions, and there's no macros in it.

    Any feedback most welcome
    Originally posted by Locoblade
    There seems to be an issue with the Detailed Monthly Table sheet. I tried to add a payment in the row 10 rows down and it would not let me saying the cell was locked?

    Good spreadsheet though!
    --
    Peter Stones
    • Locoblade
    • By Locoblade 14th Sep 08, 8:54 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    There seems to be an issue with the Detailed Monthly Table sheet. I tried to add a payment in the row 10 rows down and it would not let me saying the cell was locked?

    Good spreadsheet though!
    Originally posted by pstones578
    Cheers, dont know how that happened, I checked all the top cells but that one got through the net

    Give me 5 mins and I'll upload a new one....
    • Locoblade
    • By Locoblade 14th Sep 08, 9:02 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    I'm sure you will be able to open it with Open Office or GoogleDocs if you don't have Excel.

    GG
    Originally posted by Gorgeous George
    Its an errant locked cell that was spotted, it wasnt that it couldnt be opened

    New one now uploaded....
    • benbenandme
    • By benbenandme 14th Sep 08, 9:05 PM
    • 9,295 Posts
    • 23,076 Thanks
    benbenandme
    I saved it but it won't let me open it, it says its password protected or encrypted?? :confused: I'm also not at all techie so it could be me being a muppet
    Save £6k in 2016: £1348 / £6000 #116
    • Locoblade
    • By Locoblade 14th Sep 08, 9:08 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    Certain cells / pages are locked so they can't be edited, but you should be able to open it no problems. What version of Excel are you using?
    • benbenandme
    • By benbenandme 14th Sep 08, 9:11 PM
    • 9,295 Posts
    • 23,076 Thanks
    benbenandme
    open office or microsoft works
    Save £6k in 2016: £1348 / £6000 #116
    • Locoblade
    • By Locoblade 14th Sep 08, 9:20 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    Ah that may explain it then, Ive only tested it with Excel 2003 and 2007.

    Anyone know whether both of those products dislike locked cells?
    • fattony
    • By fattony 14th Sep 08, 9:25 PM
    • 154 Posts
    • 21 Thanks
    fattony
    thanks, it looks good but would be great if I could personalise it, i.e my mortgage is 132k, is there any way I could change the 100k to 132k?
    • Locoblade
    • By Locoblade 14th Sep 08, 9:31 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    thanks, it looks good but would be great if I could personalise it, i.e my mortgage is 132k, is there any way I could change the 100k to 132k?
    Originally posted by fattony
    Yes, just type over all the cells on the input page putting in your own rates, loan amounts etc etc, the £100k and all the other inputs already typed in are only there as an example!
  • spud30
    Loco, I am using an old version of Excel and cant change any of the values on the yellow columns in the Detailed Monthly Table. Am I doing something wrong? All say password protected.
    Is it better to aim for the stars and hit a tree or aim for a tree and land in its branches

    Loves being a Wonderbra friend
    • Locoblade
    • By Locoblade 14th Sep 08, 9:46 PM
    • 727 Posts
    • 566 Thanks
    Locoblade
    Hmm. There are locks on the cells that do all the calculations so they dont get over-written but the yellow columns are all editable. I can only think that older versions can't support partially locked pages so treat the whole page as locked

    What version of Excel is it?
Welcome to our new Forum!

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

Forum Team Contact us

Live Stats

3,626Posts Today

7,904Users online

Martin's Twitter
  • On @bbc5live now there is a story that horses can read human emotions. Personally I'm a neigh sayer.

  • RT @CouncilReporter: How London's skyline will evolve if the 250 high rise towers planned or underway in the capital go ahead as planned ht?

  • Age UK boss is right to say on BBC news "you cant compare 2yr fix to a 1yr fix" yet for a decent time it was expensive compared to 2yr fixes

  • Follow Martin