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 2
  • spud30
    It's the 2000 version.

    What a shame, I could've happily spent many hours looking at how much I've saved us over the years.

    You're very clever to produce such a clever calculator.
    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:52 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Looks like it will only work with Excel 2002 and newer.....

    http://support.microsoft.com/kb/826923

    When you open an Excel 2002 or an Excel 2003-protected sheet in Excel 2000 or earlier, the Excel 2003 protection functionality reverts back to the level of protection that was available in that version of Excel. Generally, the new protection features in Excel 2002 and in Excel 2003 are exceptions to the default level of protection. In other words, if you use Excel 2000 to open a workbook that was created in Excel 2003, you are not allowed to make changes that are protected in Excel 2003. Protection options (for example, enabling Insert columns on a protected sheet) do not apply to earlier versions
    I'll see if I can work out one thats compatible with 2000 or earlier( ), I suspect some of the equations etc used will screw up in really early versions of Excel anyway, so it might not be possible.
    • fattony
    • By fattony 14th Sep 08, 10:19 PM
    • 164 Posts
    • 22 Thanks
    fattony
    got it ta, what a numpty i am
    • DilbertJones
    • By DilbertJones 15th Sep 08, 2:38 PM
    • 664 Posts
    • 138 Thanks
    DilbertJones
    amazing spreadsheet, many thanks
    • Mrs Chicken
    • By Mrs Chicken 15th Sep 08, 3:19 PM
    • 17 Posts
    • 7 Thanks
    Mrs Chicken
    Blimey this is great, thanks so much for letting us share it
    • lagi
    • By lagi 15th Sep 08, 3:44 PM
    • 584 Posts
    • 139 Thanks
    lagi
    This is an awesome piece of kit, if i have done it right and i continue to overpay and get good mortgage rates over the years then i can see light at the end of the tunnel.

    Puts things into perspective, thanks.
    • bubblesmoney
    • By bubblesmoney 15th Sep 08, 4:10 PM
    • 2,127 Posts
    • 2,412 Thanks
    bubblesmoney
    nice work chris.

    is it possible to choose a daily interest calculation option for the calculator? also is it possible to compare different mortgage providers packages by having an option for factoring in their fees for the mortgage?
    bubblesmoney
    • Locoblade
    • By Locoblade 15th Sep 08, 4:38 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    nice work chris.

    is it possible to choose a daily interest calculation option for the calculator? also is it possible to compare different mortgage providers packages by having an option for factoring in their fees for the mortgage?
    Originally posted by bubblesmoney
    Thanks

    I tried a daily/monthly option which even took into account leap year February extra days to calculate the interest each month, but it made the equations so complicated and I couldn't get it to work properly so I decided to focus on the monthly one to start with.

    Its still embedded in some of the calculations even though its not currently used, so I may re-visit that at some point when my head is a bit less mushed from what Ive done so far to make it work as is

    I could put in an "upfront fees" option which will add itself to the yearly totals so you can do comparisons for how much it will cost over x years, rather than manually having to add up front fees yourself to any figures generated. I was also thinking of putting an additional page in for comparing a new mortgage against the existing one, that would probably be easier than daily interest so I might have a go at that at some point.
    • Locoblade
    • By Locoblade 15th Sep 08, 4:50 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    All

    Just to note, the first proper bug has been found (thankyou lagi).

    If you have a mortgage that has a a split rate, with say 5 years at 5% followed by the remainder at 6%, when you put something like 200 a month in the monthly overpayment box, it won't change the basic payment to the new rate after 5 years, so from year 5 to the end of the loan, it effectively underpays the mortgage slightly and uses some of the overpayment to make up the shortfall, in effect reducing the amount you're actually overpaying.

    I'll have a look at rectifying this tonight, so if anyone is planning on spending time putting in their own mortgage figures from years past, you may want to wait for v1.1
    • pstones578
    • By pstones578 15th Sep 08, 8:11 PM
    • 458 Posts
    • 101 Thanks
    pstones578
    Looks like it will only work with Excel 2002 and newer.....

    http://support.microsoft.com/kb/826923



    I'll see if I can work out one thats compatible with 2000 or earlier( ), I suspect some of the equations etc used will screw up in really early versions of Excel anyway, so it might not be possible.
    Originally posted by Locoblade
    Serves people rite for using 6+ years old version of software!
    --
    Peter Stones
    • pstones578
    • By pstones578 15th Sep 08, 8:14 PM
    • 458 Posts
    • 101 Thanks
    pstones578
    All

    Just to note, the first proper bug has been found (thankyou lagi).

    If you have a mortgage that has a a split rate, with say 5 years at 5% followed by the remainder at 6%, when you put something like 200 a month in the monthly overpayment box, it won't change the basic payment to the new rate after 5 years, so from year 5 to the end of the loan, it effectively underpays the mortgage slightly and uses some of the overpayment to make up the shortfall, in effect reducing the amount you're actually overpaying.

    I'll have a look at rectifying this tonight, so if anyone is planning on spending time putting in their own mortgage figures from years past, you may want to wait for v1.1
    Originally posted by Locoblade
    Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?
    --
    Peter Stones
    • Locoblade
    • By Locoblade 15th Sep 08, 8:48 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?
    Originally posted by pstones578
    Yep, thats correct, if you read the advice note that pops up when you click on that cell you would know what to do

    Basically to make the spreadsheet work, that number needs to be less than the total length of the loan otherwise you get some "divide by zero" errors in certain cells, so if its a 25 year loan with the same rate throughout, just set both interest rates the same and set the intro period to any number smaller than the loan length (1-24 in your case). Because the rate is the same in both, it doesn't matter what that number is.
    • Locoblade
    • By Locoblade 15th Sep 08, 9:39 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    All

    Just to note, the first proper bug has been found (thankyou lagi).

    If you have a mortgage that has a a split rate, with say 5 years at 5% followed by the remainder at 6%, when you put something like £200 a month in the monthly overpayment box, it won't change the basic payment to the new rate after 5 years, so from year 5 to the end of the loan, it effectively underpays the mortgage slightly and uses some of the overpayment to make up the shortfall, in effect reducing the amount you're actually overpaying.

    I'll have a look at rectifying this tonight, so if anyone is planning on spending time putting in their own mortgage figures from years past, you may want to wait for v1.1
    Originally posted by Locoblade
    Having had a look at this, I don't actually think its a bug at all.

    Basically when you select the option for overpayments to not reduce your monthly payment (ie "Keep Same"), you're saying you want to pay the same as your first payment unless you need to pay more because of interest rises etc. When there's no / small overpayments, at the point the interest rate changes from say 5% to 7%, the minimum payment is suddenly higher than the amount you're currently paying so the spreadsheet bumps up your monthly payment to compensate. When you put in large overpayments though, by the time you reach the point when the new higher interest rate kicks in, the minimum payment for that new higher interest rate is still lower than the payment you're already making, so the spreadsheet doesn't bother changing the monthly payment.

    Picture / thousand words and all that....

    With 25 year £90k repayment loan with £300 a month overpayment...

    http://www.locostfireblade.co.uk/Downloads/Spreadsheet1.jpg

    With the same but only £50 a month overpayment

    http://www.locostfireblade.co.uk/Downloads/Spreadsheet2.jpg

    (do others see the embedded pictures or are they just red X?)

    Anyway, if you want to raise the payment further at this point when the spreadsheet doesnt raise it by itself, you can always type it in manually, but doing it this way does tie in with how other overpayment calculators work, so I think I'll leave it as-is.
    Last edited by Locoblade; 15-09-2008 at 9:43 PM.
    • Kulakman
    • By Kulakman 15th Sep 08, 10:03 PM
    • 10 Posts
    • 32 Thanks
    Kulakman
    I am an avid reader of this forum but have never felt compelled to post anything until I downloaded this spreadsheet. It's fantastic and just what I need at the moment as I am about to change my mortgage and am not sure which deal to go for. Thanks!
    • Locoblade
    • By Locoblade 15th Sep 08, 10:10 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    No probs, glad its of use
    • Locoblade
    • By Locoblade 15th Sep 08, 10:35 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    An actual "Bug" found by me, more forgetful rather than a bug because I knew it was there but forgot to fix it.

    In the yearly table, the "Payment" column is correct for the first year as it counts up all the payments for month 1 through to 12, but isn't correct for the remaining years as year two counts the payments from month 2 to month 13 rather than month 13 to month 24 etc etc.

    Version 1.1 is being worked on.
    • Locoblade
    • By Locoblade 16th Sep 08, 11:04 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    Version 1.1 now uploaded, Ive added the upfront fees option as suggested by bubblesmoney, and also sorted out the annual table so it calculates correctly and has information boxes to explain each heading.
    • KTF
    • By KTF 17th Sep 08, 9:02 AM
    • 4,707 Posts
    • 1,929 Thanks
    KTF
    This is really good but would it be possible to put the version number as part of the spreadsheet name so its easy to tell what one it is without opening it up?
    • Locoblade
    • By Locoblade 17th Sep 08, 9:13 AM
    • 754 Posts
    • 572 Thanks
    Locoblade
    I could do it that way I guess, my thinking behind leaving it the same was so the download link stayed the same in the first post, but I guess I can edit that each time.
  • thetigerwhocametotea
    Totally fantastic and exactly what I've been looking for. Was thinking about interest only mortgage and overpaying ecah month against straight repayment mortgage, and trying to work out the difference this would make in years. This does it all for me. Brilliant thanks. Will be using it non stop over the next few months!!!!
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,544Posts Today

6,628Users online

Martin's Twitter