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, 7:10 PM
    • 756Posts
    • 572Thanks
    Locoblade
    My Excel mortgage spreadsheet (now Mac Compatible)
    • #1
    • 14th Sep 08, 7:10 PM
    My Excel mortgage spreadsheet (now Mac Compatible) 14th Sep 08 at 7: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 8:29 PM. Reason: Upload of v1.10
Page 23
    • jlwhite
    • By jlwhite 10th Apr 18, 6:36 AM
    • 116 Posts
    • 35 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, 7:28 AM
    • 756 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 7:33 AM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • jlwhite
    • By jlwhite 10th Apr 18, 7:56 AM
    • 116 Posts
    • 35 Thanks
    jlwhite
    Thanks so much Locoblade, I knew someone would be able to explain it!
    • Bilalzaidi
    • By Bilalzaidi 10th Apr 18, 9: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 9:22 AM.
    • DingerUK
    • By DingerUK 5th May 18, 6:11 PM
    • 12 Posts
    • 8 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, 7:11 PM
    • 756 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, 2:58 PM
    • 12 Posts
    • 8 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 3:00 PM.
    • miss undastood
    • By miss undastood 10th Feb 19, 2:40 PM
    • 157 Posts
    • 396 Thanks
    miss undastood
    Hi

    I have added in my overpayments in the months I have made them, but Iím struggling to get the banks calculation of what I should pay to match yours

    £908 is your payment and itís closer to £970 from the bank

    The balance seems to be a bit off too - itís £188k on my app and £192k on the spreadsheet

    Do you know why the figures are all so different?

    Iím convinced I should be paying less than I am...!

    Appreciate this is all really old now so not sure if you would be able to help or not
    Last edited by miss undastood; 10-02-2019 at 3:20 PM. Reason: iOS emoji didnít work
    • Locoblade
    • By Locoblade 10th Feb 19, 6:50 PM
    • 756 Posts
    • 572 Thanks
    Locoblade
    Obviously it will rarely if ever match up exactly as each mortgage interest is calculated slightly differently, but I wouldnt have thought it would be that far out.



    What setting have you got the spreadsheet set to with regards to how it deals with overpayments (the "Keep Same" or "Reduce Monthly" option)? If you've got it set to reduce monthly then it will retain mortgage term and reduce the monthly payment to pay it off over that term so that may explain a lowe number. If set to Keep Same then it will carry on paying the same original monthly payment so using the overpayments to reduce the time it takes to pay off the loan.


    As for the differing loan amounts left, again if you've had the "Reduce Monthly" setting set for a while since some overpayments then the amount left will be more than if you'd continued to pay off at the old amount.
    Last edited by Locoblade; 10-02-2019 at 6:52 PM.
    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
    • miss undastood
    • By miss undastood 10th Feb 19, 9:01 PM
    • 157 Posts
    • 396 Thanks
    miss undastood
    Hi, thanks for your reply. I have it set as ďkeep the sameĒ as that is how my mortgage is set up.

    I overpay in large chunks which are then classed as part redemptions and my monthly payment is recalculated whilst my term remains the same. It is probably to do with the daily interest calculation why the balances arenít balancing correctly?

    At the moment I am taking the lower monthly payment but still overpaying large chunks annually. What I will do soon is decide I want to pay £x per month (say £1000) and I will set up a standing order to work out the difference between what Iím contracted to pay and £1000

    I guess I just hoped the numbers would tally closer as I wanted to avoid a spreadsheet that was 13,000 rows long!
    • Locoblade
    • By Locoblade 10th Feb 19, 9:16 PM
    • 756 Posts
    • 572 Thanks
    Locoblade
    Hi, thanks for your reply. I have it set as ďkeep the sameĒ as that is how my mortgage is set up.

    I overpay in large chunks which are then classed as part redemptions and my monthly payment is recalculated whilst my term remains the same. It is probably to do with the daily interest calculation why the balances arenít balancing correctly?
    Originally posted by miss undastood

    What you've described there is the "Reduce Monthly" setting though, keeping the term the same but the monthly payment gets recalculated?


    How long has the mortgage been running and how many overpayments have you done? Without seeing your real figures and spreadsheet its hard to say why it might have drifted that much. Happy to have a look if you want to contact me via the email in the spreadsheet but obviously understand if you'd rather not share that.
    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

100Posts Today

1,258Users online

Martin's Twitter
  • Mini MSE is on half term next week, so I'm excited to be taking the week off to be daddy. As normal I'm signing of? https://t.co/G3366shWh1

  • I once blurted out on @gmb "Theresa May hasn't been given a poisoned chalice - she's been given a poisoned chalice? https://t.co/onfRbY3XVg

  • It'd be fascinating to know how history will judge Theresa May's premiership. Currently, it is hard to see it as a? https://t.co/eH77G0O9LA

  • Follow Martin