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
    • 760Posts
    • 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 22
    • Locoblade
    • By Locoblade 24th Jul 15, 7:29 AM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Hi

    You could adjust it in the monthly detailed section, not directly in the start/end balance but say it was 5 over your actual, you could put -5 in the monthly overpayment or 5 in the additional borrowing to bring the end balance back in line.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • the_penfool
    Hi
    I have a YBS fixed offset that uses the offset account to reduce the length of the mortgage rather than the montlhy payments - is there any way to show this in the spreadsheet?
    Thanks
    • Locoblade
    • By Locoblade 26th Aug 15, 12:22 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Yep there's a dropdown option to reduce or keep payments the same on overpayment, just set it to Keep Same.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • Giggs_11
    • By Giggs_11 26th Aug 15, 9:30 PM
    • 45 Posts
    • 6 Thanks
    Giggs_11
    This is such a fantastic spreadsheet!! How long did this take to create may I ask haha?
    • Locoblade
    • By Locoblade 26th Aug 15, 10:28 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Ermm far too long!

    TBH I have no idea, it started off in a relatively simple state for my own requirements in 2008 and when I posted it here and people started using it, bugs were found and new features were suggested so it developed over around 3 years to roughly the state it's still in now.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • always29
    • By always29 13th Nov 15, 12:03 PM
    • 475 Posts
    • 2,388 Thanks
    always29
    Locoblade, this is a fabulous (donation worthy!) sheet, thank you so much It didn't tie in perfectly with my HSBC tracker statement but it was out by less than a fiver on 118k so I can cope with that, probably dates or something as we started it mid month.
    • Locoblade
    • By Locoblade 13th Nov 15, 12:23 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Many thanks, there'll always be slight difference to real life mortgage figures as mortgage lenders each use different methods of calculating interest (daily interest for example, or sometimes a few weeks in arrears etc). The inbuilt "amortization" calculation in Excel is fairly basic in comparison as it only calculates interest on a monthly basis, but as you've seen it gives a pretty good idea of where the mortgage is going and how it will behave with overpayments etc which was the original intention.

    cheers
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • snakee
    • By snakee 13th Nov 15, 12:57 PM
    • 11 Posts
    • 2 Thanks
    snakee
    amazing spreadsheet !! great work
    • mrsp1987
    • By mrsp1987 5th Feb 16, 7:28 AM
    • 796 Posts
    • 3,921 Thanks
    mrsp1987
    Looking forward to finishing work and getting my nerd on with this spreadsheet! Thanks very much
    • mrsp1987
    • By mrsp1987 12th Feb 16, 5:20 PM
    • 796 Posts
    • 3,921 Thanks
    mrsp1987
    Hi there. Not quite sure what I'm doing wrong but I've set up the spreadsheet to start from when I remortgaged last year. In December I made a one-off lump sum payment of 10K. In the spreadsheet I've selected to keep the monthly payments the same after an over-payment. However, it's still showing a reduced minimum monthly payment on the monthly table tab.
    Basically, how do I get the spreadsheet to reflect the fact that this OP led to a reduced term not reduced monthly payments for the same term?
    • Nellybauer
    • By Nellybauer 18th May 16, 11:34 PM
    • 3 Posts
    • 0 Thanks
    Nellybauer
    Just wanted to say great spreadsheet.

    I was trying to compare re-mortgages from the same provider that offer slightly different interest rates depending if you take a product with a fee or not, if taking the fee, adding it to the loan. They can only tell me the saving over the full term of the mortgage but with this tool I can see the difference over just the initial term, after which I'd be looking at a new deal anyway.

    Top work!
    • sieuph
    • By sieuph 26th May 16, 2:41 PM
    • 17 Posts
    • 611 Thanks
    sieuph
    Thanks for this - Brilliant to be able to compare mortgages with different rates and fees.
    • ccbrowning
    • By ccbrowning 14th Jul 16, 10:48 AM
    • 225 Posts
    • 245 Thanks
    ccbrowning
    The SVR bit doesn't seem to work? When I look at the monthly table, the amount never changes after the fixed term period ends and stays the original low, fixed amount. Using Excel 2016... If I manually edit the interest rate starting at the month the SVR kicks in, it sort of works for that worksheet, though.

    Seems amazing for the other bits, though.
    • Locoblade
    • By Locoblade 14th Jul 16, 10:24 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Hi

    It does work but one of the options set in a certain way can affect the end result. Input 18 on the front page (Overpayment/Offset effect...) has an effect when the interest rate change, if it's set to "Keep Same" it will keep the same monthly payment even when the interest rate changes if the SVR is lower than the introductory rate. Below is how you should set it so it always follows the minimum amount to be paid.

    cheers




    Last edited by Locoblade; 14-07-2016 at 10:26 PM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • dustiebin
    • By dustiebin 26th Jul 16, 10:17 AM
    • 1 Posts
    • 1 Thanks
    dustiebin
    Adhoc overpayments not reducing term of Mortage.
    Hello Locoblade,


    Great SS. It has been invaluable for me comparing my reportage offers. So many thanks.


    Now I am not sure if I am doing something wrong (most probably) but I am putting in a predicted lump sum overpayment into the sheet and it is not reducing the overall term of the mortgage. When I put in a monthly overpayment via the info and key figures tab it does reduce the overall term.


    I am putting in my lump sum over payment in the Mortgage X Monthly Table part way through my 5 year fixed example in column L ( Monthly Overpayment) - when I put it here nothing changes on the term.
    If I put in a regular payment into Mortgage X Info and Key Figures Cell F21 the term reduces.


    I would add screen shots but unsure how that works on here.....


    Am I doing something wrong or is there an error ?


    Many thanks

    Dustiebin
    • Locoblade
    • By Locoblade 26th Jul 16, 8:22 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Hiya

    Its the same setting as mentioned above to the previous question, this needs to be changed to "Keep Same" so subsequent monthly payments after the big overpayment arent reduced to retain the term. The reason it reduces the term regardless of that setting when a "Monthly Overpayment" amount is set just above it is because that box adds an overpayment every single month, so reducing the monthly each month whilst upping the monthly with a regular overpayment each month would cancel each other out and be pointless really.



    cheers
    Last edited by Locoblade; 26-07-2016 at 8:44 PM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • tightasabulls
    • By tightasabulls 20th Nov 16, 10:10 PM
    • 28 Posts
    • 8 Thanks
    tightasabulls
    Thankyou locoblade
    I was wondering if you could share the password by any chance?

    Thankyou again for sharing this work.
    • twarde
    • By twarde 7th May 17, 9:52 PM
    • 23 Posts
    • 2 Thanks
    twarde
    Equity Release loan Calculation
    Locoblade,
    I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?

    Or is there a better solution for this type of mortgage?

    Thanks
    • penst0ne
    • By penst0ne 29th May 17, 1:38 PM
    • 7 Posts
    • 1 Thanks
    penst0ne
    Thanks, just what I wanted.
    • Locoblade
    • By Locoblade 25th Jul 17, 3:50 PM
    • 760 Posts
    • 572 Thanks
    Locoblade
    Locoblade,
    I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?

    Or is there a better solution for this type of mortgage?

    Thanks
    Originally posted by twarde
    Hi

    Sorry just catching up here as the forum seems to have stopped notifying me of new messages. You can change the interest rate every month if you like by editing it in the appropriate column in the Monthly tab, so should do what you need.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
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,085Posts Today

7,916Users online

Martin's Twitter