My Excel mortgage spreadsheet
Options
Locoblade
Posts: 795 Forumite
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 rewritten to allow several things that most of the existing online/spreadsheet calculators didn't seem to be able to do, such as:
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 email.
Any feedback most welcome
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 rewritten 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
 Adhoc 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 8001000 downloads per month!
 Edit: v1.10 Changed the formatting to allow end users to change column widths and hide columns if required.
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 email.
Any feedback most welcome
My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=1157173
1
Comments

cant look at as it jammed my laptop upneed to have a lightbulb moment0

:cool:
My old desktop downloaded it just fine.
Good work.
GGThere are 10 types of people in this world. Those who understand binary and those that don't.0 
cant look at as it jammed my laptop up
Not sure why that would be, do you have Excel installed, and did you right click  save as rather than try and open the link in IE?My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Thanks a lot i won't have a mortgage for a year at least but this looks amazingNow a SAHM trying to earn some spare pennies each month0

Very good. Another one for my collection0

ill try again
i had sky anytime playing at the same time that may have caused the problemneed to have a lightbulb moment0 
thanks for this.
I have been pondering about whether offset mortgages are worth it and this may help me to double check calculations given by other websites.0 
sorted thanks for thatneed to have a lightbulb moment0

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 rewritten 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
 Adhoc rate changes for variable/tracker mortgages]
 Additional borrowing at any time
 A few pretty graphs
Here's the link to download it: http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator.xls
It was written in Excel 2007 but has been saved so it's compatible with earlier versions, and there's no macros in it.
Any feedback most welcome
There seems to be an issue with the Detailed Monthly Table sheet. I tried to add a payment in the row 10 rows down and it would not let me saying the cell was locked?
Good spreadsheet though!
Peter Stones0 
pstones578 wrote: »There seems to be an issue with the Detailed Monthly Table sheet. I tried to add a payment in the row 10 rows down and it would not let me saying the cell was locked?
Good spreadsheet though!
Cheers, dont know how that happened, I checked all the top cells but that one got through the net
Give me 5 mins and I'll upload a new one....My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730
Categories
 All Categories
 343.8K Banking & Borrowing
 250.3K Reduce Debt & Boost Income
 450K Spending & Discounts
 235.9K Work, Benefits & Business
 609K Mortgages, Homes & Bills
 173.4K Life & Family
 248.5K Travel & Transport
 1.5M Hobbies & Leisure
 15.9K Discuss & Feedback
 15.1K Coronavirus Support Boards