My Excel mortgage spreadsheet

14243444547

Comments

  • alexxela
    alexxela Posts: 16 Forumite
    First Post First Anniversary
    Can someone please upload a fresh version of a mortgage free spreadsheet? Thank you
  • Locoblade
    Locoblade Posts: 795 Forumite
    Name Dropper First Post First Anniversary
    edited 30 August 2020 at 11:01AM
    What do you mean a fresh version? The one in the link on page 1 is the latest version :)
    alexxela said:
    Can someone please upload a fresh version of a mortgage free spreadsheet? Thank you


    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Baileys_Babe
    Baileys_Babe Posts: 5,575 Forumite
    Name Dropper First Anniversary First Post
    Locoblade said:
    What do you mean a fresh version? The one in the link on page 1 is the latest version :)
    alexxela said:
    Can someone please upload a fresh version of a mortgage free spreadsheet? Thank you


    Thank you @Locoblade for all the work you have done to produce this spreadsheet and supporting those using it.
    Fashion on a ration 2024 66/66 coupons remaining
    80 coupons rolled over 0/80 coupons spent - using for secondhand purchases

    One
     income, home educating family 
  • VoucherMan
    VoucherMan Posts: 2,771 Forumite
    Name Dropper First Post First Anniversary
    I've said for a log time that this is one of the best pieces of software/spreadsheet that I've had the pleasure of using. Not just to encourage the ops, but fun to play with as well.
    Not used much in the last year or three, but up until then it really made a difference, and I always meant to make a small contribution, so thanks Locoblade, today I've made that final payment, and your sheet probably played quite a part in it.
  • I've said for a log time that this is one of the best pieces of software/spreadsheet that I've had the pleasure of using. Not just to encourage the ops, but fun to play with as well.
    Not used much in the last year or three, but up until then it really made a difference, and I always meant to make a small contribution, so thanks Locoblade, today I've made that final payment, and your sheet probably played quite a part in it.
    Many thanks for the kind words and donation! It's always great to hear when it's helped people on their mortgage repayment journey, congratulations on making that final payment :)

    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • Locoblade said:
    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 :)
    I just found your sheet on my quest to find a very specific overpayment calculator since I am a beginner at excel and mortgages. I think yours is absolutely fantastic. I wonder if you can help...

    I'm after a calculator that allows input of dated one-off overpayments which effect the interest from that day, my mortgage interest is calculated daily and seeing how on target I am with regular over payments and their frequency. Any tips on how to get it to work please or know of one already in existence?

    Thanks!

  • Rakushun said:
    I just found your sheet on my quest to find a very specific overpayment calculator since I am a beginner at excel and mortgages. I think yours is absolutely fantastic. I wonder if you can help...

    I'm after a calculator that allows input of dated one-off overpayments which effect the interest from that day, my mortgage interest is calculated daily and seeing how on target I am with regular over payments and their frequency. Any tips on how to get it to work please or know of one already in existence?

    Thanks!

    You'll struggle getting excel to calculate daily interest as it doesn't have a built in function to do it, and if you try and do it manually over the average mortgage length it becomes unweildy due to the number of lines and calculations that need doing. I tried it whilst developing this one and it was a real struggle especially when you get things like leap years etc you need to deal with.

    Even if you could get something working you'd still unlikely be 100% accurate with what your mortgage company calculate as they generally have additional elements to the calculation such as the monthly payment not applying straight away etc, so best use the spreadshseet as a fairly accurate prediction tool than a to-the-penny accurate tracker.

    My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
  • getmore4less
    getmore4less Posts: 46,882 Forumite
    Name Dropper First Anniversary First Post I've helped Parliament
    The standard built in functions don't work properly for most UK mortgages because of the way payments and interest are actually applied to the real the balance.

    The common problem is BH/weekends which move the dates away from the amortization functions dates which use equal intervals

    Then you have a less common one like mine where the interest is added on the 1st of the month(monthly compounding) and the payments are the 16th and they can move(due to BH/WE) independently.

    I think some lenders do might do daily compounding where each days interest gets added, that also complicates the interest calculation as you can't use rate/365

    That as you say for most practical purposes the regular amortization functions with monthly period does the job.

  • Snapelover
    Snapelover Posts: 435 Forumite
    First Post First Anniversary Combo Breaker
    Hi Locoblade, I am trying to use your fantastic spreadsheet but have a three problems that I just cannot sort out. 
    Firstly, the initial payment for our mortgage was different to the rest of the payments and also on a different day of the month.  Secondly, the monthly amount calculated from the details given is actually different to the monthly amount we are paying.
    Thirdly, we are making overpayments of the same amount each month but no overpayments were made with the initial payment.
    I hope you can help me.
  • getmore4less
    getmore4less Posts: 46,882 Forumite
    Name Dropper First Anniversary First Post I've helped Parliament
    Probably the easy solution is to skip the first months and start with the balance payment and term one months in.

    How far out are you numbers to what the spreadsheet gets

    actual real numbers will help.
Meet your Ambassadors

Categories

  • All Categories
  • 343.1K Banking & Borrowing
  • 250.1K Reduce Debt & Boost Income
  • 449.7K Spending & Discounts
  • 235.2K Work, Benefits & Business
  • 607.9K Mortgages, Homes & Bills
  • 173K Life & Family
  • 247.8K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards