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 3
    • Locoblade
    • By Locoblade 17th Sep 08, 11:22 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Version 1.2 now uploaded, Ive basically doubled up the spreadsheet so you can now put in details for 2 mortgages and compare them on a single comparison sheet.

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

    Ive done this fairly quickly tonight therefore its not had a lot of testing, so please excuse any bugs on the Mortgage 2 sheets, let me know if you find any.
  • ThriftyBeanie
    I haven't logged on for ages ..... general malaise after having sorted out my own mortgage. I have from time to time scanned posts to see if anything grabbed my attention, but not much has piqued my interest.

    Until this.

    What a cracking workbook!! Nice piece of work.

    I think I will use this to set my 5 and 10 year plans, my own spreadsheets are now superseded!

    As someone else said, thank you for sharing it.
    • Optimisticpair
    • By Optimisticpair 18th Sep 08, 6:59 PM
    • 630 Posts
    • 1,854 Thanks
    Optimisticpair
    Thanks Locoblade - It's brilliant bit of work, I am not very high-tec but think I have worked out what to put in where.
    No longer half of Optimisticpair


  • Ian Griffiths Halifax
    Locoblade, that's a great spreadsheet! Well done. I'd say it was better than most on the internet.

    Can I suggest though, it would be handy if there was a facility to input a date in the future to start overpaying. Say the person using the spreadsheet knows that they have a loan ending in 2 years time and they can start overpaying at that point. Possibly a 'Stop overpaying at X date' box as well, so that they could just overpay for a certain length of time.

    As an example, I often advise clients to overpay on their mortgage when a Car Loan ends, then stop overpaying when they need to change their car and get another loan. That way their outgoings stay the same and they don't get used to the luxury of extra money to spend each month and then go through the pain of having to find money when the car conks out.

    Also, a facility to input a Lump Sum at a date in the future. They may have an Endowment maturing at a certain date or a Pension Lump Sum.

    Other than that it's very good, though I would have to take your word for its' accuracy ............... My appologies if any of these functions are there already, as I've only had chance to have a quick look over it.
    Last edited by Ian Griffiths Halifax; 18-09-2008 at 9:04 PM.
    I am a Mortgage Consultant and don't like to be told what I can and can't put in a signature so long as it's legal and truthful.
    • Locoblade
    • By Locoblade 19th Sep 08, 4:41 AM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Hi Ian

    Those features aren't configurable from the key info page Im afraid, but you can easily add them in by going to the monthly sheet and manually adding them in. For example if you wanted to do £300 a month repayments from the start of year 2 for 12 months, you'd go to the monthly page, scroll down adding £300 manually into the "overpayment" cell corresponding to month 24, then copy that down 12 cells to make the overpayment for the year.

    Likewise if you want to put in a single lump overpayment you can put the lump sum figure in the "overpayment" box for the month you want to put the lump sum in.

    I could put it in a selection box on the front page but that would possibly limit you to one period of overpayments etc, whereas if you add them manually you can put as many ad-hoc periods in as you wish.

    Thanks all for the compliments BTW
    Last edited by Locoblade; 19-09-2008 at 6:25 AM.
  • kjharve
    Nice looking spreadsheet mate. Is it worth adding an APR to help comparisons?

    If you think that would be worthwhile I can knock something up for you to build in.
  • oki
    This is a fantastic tool!! Thankyou
  • jayoh
    Wow I'm impressed, this is really useful. I needed to make a decision about whether to stay on my current deal or remortgage and incur many costs. This has helped me work out that it's better to stay on what I'm on for a while as the savings I would make for reducing my mortgage would be less than the cost of actually getting the mortgage in the first place

    I am in awe!
  • 2for1
    Hi Locoblade, I'm new to the forum but really impressed by your spreadsheet and will be so useful to many. I have a part repayment/part interest mortgage, is there a way I could use the spreadsheet for that type of mortgage? Many thanks!
    • bubblesmoney
    • By bubblesmoney 19th Sep 08, 8:13 PM
    • 2,127 Posts
    • 2,412 Thanks
    bubblesmoney
    could someone nominate locoblades post for the 'post of the month award' if there is such a thing. i dont know how to nominate posts. the link to this mortgage calculator probably should go out in the weekly email from mse
    bubblesmoney
    • Jnr_81
    • By Jnr_81 19th Sep 08, 10:53 PM
    • 27 Posts
    • 2 Thanks
    Jnr_81
    Excellent tool, thanks very much! I'm new to the forums but already I'm getting great hints, tips and tools to put me on my money saving way.

    One slight problem though, when I go to manually type in an overpayment figure, it tells me the cells are locked/protected, can anyone help. I've tried on the most recent version of excel, and a previous version and both give the same message.
  • Ian Griffiths Halifax
    You won't see Locoblade again for a while. He's getting his pitch ready for Dragons Den. It will be on the shelves of PC World and Staples by Christmas
    I am a Mortgage Consultant and don't like to be told what I can and can't put in a signature so long as it's legal and truthful.
    • Locoblade
    • By Locoblade 20th Sep 08, 3:45 AM
    • 754 Posts
    • 572 Thanks
    Locoblade
    You won't see Locoblade again for a while. He's getting his pitch ready for Dragons Den. It will be on the shelves of PC World and Staples by Christmas
    Originally posted by Ian Griffiths Halifax
    LOL, sadly not, currently having a bit of an IT "issue" at work which meant a 24 hours shift yesterday, overnight tonight and probably more of the same for the rest of the weekend, hence the odd posting times lately. :rolleyes:

    Jnr_81, when you say the latest version of Excel, which one is it, and is it full Excel (part of Office), not OpenOffice or Works etc, because only Excel 2002 onwards will work? Also, I assume you're editing the Monthly Overpayment column and not the "Total Monthly Payment" column, because only the yellow highlighted columns are editable (in any version)?

    Thanks again for all the positive comments all, gives motivation to improve it further when I get time, maybe v1.3 will include my paypal address so you can all donate half the savings you make by using this
    Last edited by Locoblade; 20-09-2008 at 4:51 AM.
    • Locoblade
    • By Locoblade 20th Sep 08, 4:37 AM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Nice looking spreadsheet mate. Is it worth adding an APR to help comparisons?

    If you think that would be worthwhile I can knock something up for you to build in.
    Originally posted by kjharve
    I'll have a look, do many believe that APR is a good indicator of loan value when 90% of loans have an introductory rate after which people invariably jump ship to another loan rather than taking it through to term (when the APR would be relevent)?

    Its on KFI sheets you get from all the lenders I guess, so maybe its worth putting in.
    Last edited by Locoblade; 20-09-2008 at 5:13 AM.
  • Ian Griffiths Halifax
    I'll have a look, do many believe that APR is a good indicator of loan value when 90% of loans have an introductory rate after which people invariably jump ship to another loan rather than taking it through to term (when the APR would be relevent)?

    Its on KFI sheets you get from all the lenders I guess, so maybe its worth putting in.
    Originally posted by Locoblade
    In my opinion, APR rates add unnecessary confusion to Mortgage Key Facts Illustrations as most people will change their mortgage at some point, even if it's not immediately after their initial deal ends.
    I am a Mortgage Consultant and don't like to be told what I can and can't put in a signature so long as it's legal and truthful.
  • Bismarck
    great stuff...well done...thanks! Something to play with over the weekend.....how sad am I?
    For what I've done...I start again...And whatever pain may come ...Today this ends... I'm forgiving what I've done -AF since June 2007
    • Locoblade
    • By Locoblade 20th Sep 08, 5:03 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    In my opinion, APR rates add unnecessary confusion to Mortgage Key Facts Illustrations as most people will change their mortgage at some point, even if it's not immediately after their initial deal ends.
    Originally posted by Ian Griffiths Halifax
    Exactly how I feel about it yup, hence not bothering putting it in the spreadsheet originally.
    • Locoblade
    • By Locoblade 20th Sep 08, 5:04 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    great stuff...well done...thanks! Something to play with over the weekend.....how sad am I?
    Originally posted by Bismarck
    Probably not as sad as me spending many hours over the last few weeks writing it
  • Chris2685
    That's an amazing tool. Nice work
    • nesssie1702
    • By nesssie1702 21st Sep 08, 2:00 PM
    • 1,342 Posts
    • 4,384 Thanks
    nesssie1702
    Excellent! I'm due to remortgage next year and this will come in really useful.
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,623Posts Today

6,840Users online

Martin's Twitter