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 23
    • jlwhite
    • By jlwhite 10th Apr 18, 7:36 AM
    • 109 Posts
    • 33 Thanks
    jlwhite
    Thanks for the spreadsheet, I know this forum is old but was wondering if someone could answer a question for me.

    Mortgage

    Inputs Highlight Cells for more Info.
    1 Mortgage amount 185,999.00
    2 Mortgage Type Repayment
    3 Introductory/Initial Interest Rate 1.59%
    4 Period of Introductory Rate (years) 2.0
    5 Interest Rate after Intro Period 4.74%
    6 Loan period (years) 24.0
    7 Starting Year of Loan 2017
    8 Starting Month of Loan May
    10 Payment Calculated / Manually added? Calculated
    11 Manual Input Initial Monthly Payment N/A
    12 Upfront Fees
    13 Fees added to the loan
    14 Early Repayment Charge Rate (%) 0.03%
    15 Early Repayment Charge Period (years) 2.0
    16 Closure / Completion Fee
    Overpayments (optional)
    17 Monthly Overpayment Amount 200.00
    18 Overpayment/Offset effect on Payments Keep Same
    Offset Mortgage (optional)
    19 Average Offset Current Account Balance
    20 Initial Offset Savings Balance
    21 Monthly Payment into Offset Savings
    Compare Offset/overpayment against savings or ISA
    22 Savings account Gross rate for comparison
    23 Select tax band for savings ISA/No Tax

    I was trying to work out how much I would save if I overpayed by 200 per month (reducing term) and it says I would save 31,676.77 in interest but if I just saved that money I worked out that over 24 years I would have 57,600 saved. I can't get my head round this. Can anyone help explain please?
    • Locoblade
    • By Locoblade 10th Apr 18, 8:28 AM
    • 754 Posts
    • 572 Thanks
    Locoblade
    I'm still here

    Its because you're paying off extra capital as well as saving interest and it's not taking the former into account when looking at that figure. If you look at the monthly table and find the month your mortgage gets paid off when including the overpayment (October in the 18th year), then remove the 200/month overpayment. Now go back to that 18th year in the monthly table you'll see you'd still owe about 62k at that point because there's another 6 years to go on the loan. That 62k owed is significantly more than what you'd have sitting in your savings account having saved 200/month over that 18 year point which works out to about 44k before any interest earned. In effect over 18 years you're therefore saving yourself about 18k overall by overpaying the mortgage compared to putting the money separately into a savings pot paying zero interest.
    Last edited by Locoblade; 10-04-2018 at 8:33 AM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • jlwhite
    • By jlwhite 10th Apr 18, 8:56 AM
    • 109 Posts
    • 33 Thanks
    jlwhite
    Thanks so much Locoblade, I knew someone would be able to explain it!
    • Bilalzaidi
    • By Bilalzaidi 10th Apr 18, 10:20 AM
    • 1 Posts
    • 0 Thanks
    Bilalzaidi
    This spreadsheet helps you analyse and understand mortgages. It calculates and compares various scenarios to show the effect of different variables on the mortgage calculator payments and cost of borrowing.
    Last edited by Bilalzaidi; 10-04-2018 at 10:22 AM.
    • DingerUK
    • By DingerUK 5th May 18, 7:11 PM
    • 10 Posts
    • 4 Thanks
    DingerUK
    Hi Locoblade I finally have a use for your spreadsheet! I have just applied for my first mortgage It's great so far but i'd like to make a suggestion for a future version if I may? Could you add a sheet to the end to show how current monthly payments would be affected by an interest rate change, say of 0.5, 1, 2, 3, 5 and 10% for sad people like me? It would be nice to have the info all in one place.
    • Locoblade
    • By Locoblade 5th May 18, 8:11 PM
    • 754 Posts
    • 572 Thanks
    Locoblade
    I doubt there'll be any significant upgrades in the foreseeable future I'm afraid, but what your want to do is one of the reasons the second mortgage comparison tab is there, so you can add alternative mortgages or the same mortgage with a different interest rate and then compare it on the middle tab.

    Hi Locoblade I finally have a use for your spreadsheet! I have just applied for my first mortgage It's great so far but i'd like to make a suggestion for a future version if I may? Could you add a sheet to the end to show how current monthly payments would be affected by an interest rate change, say of 0.5, 1, 2, 3, 5 and 10% for sad people like me? It would be nice to have the info all in one place.
    Originally posted by DingerUK
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • DingerUK
    • By DingerUK 24th Aug 18, 3:58 PM
    • 10 Posts
    • 4 Thanks
    DingerUK
    Hi Locoblade

    I've now completed on the mortgage and have the details for my first payment next month. I've updated the spreadsheet but it's not playing nice. I've tried to email you a copy of what i've done but it bounced back to me.
    Last edited by DingerUK; 24-08-2018 at 4:00 PM.
    • CASCAS1
    • By CASCAS1 31st Aug 18, 2:29 PM
    • 1 Posts
    • 0 Thanks
    CASCAS1
    Password
    For those many that have asked, the password to unprotect is:

    AAABABBABABu
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