Main site > MoneySavingExpert.com Forums > Essential Money > Mortgages & Endowments > My Excel mortgage spreadsheet (Page 1)

IMPORTANT! This is MoneySavingExpert's open forum - anyone can post

Please exercise caution & report any spam, illegal, offensive, racist, libellous post to forumteam@moneysavingexpert.com

  • Be nice to all MoneySavers
  • All the best tips go in the MoneySavingExpert weekly email

    Plus all the new guides, deals & loopholes

  • No spam/referral links
or Login with Facebook
My Excel mortgage spreadsheet
Reply
Views: 192,196
Thread Tools Search this Thread Display Modes
# 1
Locoblade
Old 14-09-2008, 8:10 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default My Excel mortgage spreadsheet (now Mac Compatible)

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
Locoblade is offline
Reply With Quote Report Post
The Following 198 Users Say Thank You to Locoblade For This Useful Post: Show me >>
# 2
lapat
Old 14-09-2008, 8:21 PM
Serious MoneySaving Fan
 
Join Date: Nov 2004
Posts: 795
Default

cant look at as it jammed my laptop up
need to have a lightbulb moment
lapat is offline
Reply With Quote Report Post
# 3
Gorgeous George
Old 14-09-2008, 8:22 PM
Fantastically Fervent MoneySaving Super Fan
 
Join Date: Mar 2006
Location: ??485285
Posts: 7,787
Default



My old desktop downloaded it just fine.

Good work.


GG
There are 10 types of people in this world. Those who understand binary and those that don't.
Gorgeous George is offline
Reply With Quote Report Post
The Following User Says Thank You to Gorgeous George For This Useful Post: Show me >>
# 4
Locoblade
Old 14-09-2008, 8:28 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Quote:
Originally Posted by lapat View Post
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?
Locoblade is offline
Reply With Quote Report Post
# 5
lilmissmup
Old 14-09-2008, 8:31 PM
Fantastically Fervent MoneySaving Super Fan
 
Join Date: Apr 2007
Location: Bedford
Posts: 6,633
Default

Thanks a lot i won't have a mortgage for a year at least but this looks amazing
Debt 0% until March 2016 4500 -27/06/14
Trying to lower our mortgage whilst having a baby
Current mortgage balance 107,382 June 2014
1313.75 made in repayments August 2014


lilmissmup is offline
Reply With Quote Report Post
# 6
KTF
Old 14-09-2008, 8:35 PM
Fantastically Fervent MoneySaving Super Fan
 
Join Date: Sep 2004
Posts: 4,145
Default

Very good. Another one for my collection
KTF is offline
Reply With Quote Report Post
# 7
lapat
Old 14-09-2008, 8:36 PM
Serious MoneySaving Fan
 
Join Date: Nov 2004
Posts: 795
Default

ill try again
i had sky anytime playing at the same time that may have caused the problem
need to have a lightbulb moment
lapat is offline
Reply With Quote Report Post
# 8
sujman
Old 14-09-2008, 8:36 PM
MoneySaving Stalwart
 
Join Date: Jan 2006
Posts: 493
Default

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.
sujman is offline
Reply With Quote Report Post
# 9
lapat
Old 14-09-2008, 8:38 PM
Serious MoneySaving Fan
 
Join Date: Nov 2004
Posts: 795
Default

sorted thanks for that
need to have a lightbulb moment
lapat is offline
Reply With Quote Report Post
# 10
pstones578
Old 14-09-2008, 8:49 PM
MoneySaving Stalwart
 
Join Date: Jan 2005
Location: North West
Posts: 441
Default

Quote:
Originally Posted by Locoblade View Post
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
Ive finally (I think!) got it to a "finished" condition so I thought I'd post a link to it on here so that other people can use it too. It would also be good to get some others to test it to see if there are any scenarios where it gives false information, although I have where possible compared it against other online calculators so I hope its all correct.

Here's the link to download it: http://www.locostfireblade.co.uk/Dow...Calculator.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 Stones
pstones578 is offline
Reply With Quote Report Post
The Following User Says Thank You to pstones578 For This Useful Post: Show me >>
# 11
Locoblade
Old 14-09-2008, 8:54 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Quote:
Originally Posted by pstones578 View Post
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....
Locoblade is offline
Reply With Quote Report Post
The Following User Says Thank You to Locoblade For This Useful Post: Show me >>
# 12
Locoblade
Old 14-09-2008, 9:02 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Quote:
Originally Posted by Gorgeous George View Post
I'm sure you will be able to open it with Open Office or GoogleDocs if you don't have Excel.

GG
Its an errant locked cell that was spotted, it wasnt that it couldnt be opened

New one now uploaded....
Locoblade is offline
Reply With Quote Report Post
The Following User Says Thank You to Locoblade For This Useful Post: Show me >>
# 13
benbenandme
Old 14-09-2008, 9:05 PM
Fantastically Fervent MoneySaving Super Fan
 
Join Date: Jan 2006
Posts: 9,097
Default

I saved it but it won't let me open it, it says its password protected or encrypted?? :confused: I'm also not at all techie so it could be me being a muppet
Mortgage Balance 24,701
MFD = Dec 2034 June 2019
September Savings: 68
benbenandme is offline
Reply With Quote Report Post
# 14
Locoblade
Old 14-09-2008, 9:08 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Certain cells / pages are locked so they can't be edited, but you should be able to open it no problems. What version of Excel are you using?
Locoblade is offline
Reply With Quote Report Post
# 15
benbenandme
Old 14-09-2008, 9:11 PM
Fantastically Fervent MoneySaving Super Fan
 
Join Date: Jan 2006
Posts: 9,097
Default

open office or microsoft works
Mortgage Balance 24,701
MFD = Dec 2034 June 2019
September Savings: 68
benbenandme is offline
Reply With Quote Report Post
The Following User Says Thank You to benbenandme For This Useful Post: Show me >>
# 16
Locoblade
Old 14-09-2008, 9:20 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Ah that may explain it then, Ive only tested it with Excel 2003 and 2007.

Anyone know whether both of those products dislike locked cells?
Locoblade is offline
Reply With Quote Report Post
# 17
fattony
Old 14-09-2008, 9:25 PM
MoneySaving Convert
 
Join Date: Sep 2008
Posts: 151
Default

thanks, it looks good but would be great if I could personalise it, i.e my mortgage is 132k, is there any way I could change the 100k to 132k?
fattony is offline
Reply With Quote Report Post
# 18
Locoblade
Old 14-09-2008, 9:31 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Quote:
Originally Posted by fattony View Post
thanks, it looks good but would be great if I could personalise it, i.e my mortgage is 132k, is there any way I could change the 100k to 132k?
Yes, just type over all the cells on the input page putting in your own rates, loan amounts etc etc, the £100k and all the other inputs already typed in are only there as an example!
Locoblade is offline
Reply With Quote Report Post
The Following User Says Thank You to Locoblade For This Useful Post: Show me >>
# 19
spud30
Old 14-09-2008, 9:38 PM
Deliciously Dedicated Diehard MoneySaving Devotee
 
Join Date: Mar 2006
Posts: 16,550
Default

Loco, I am using an old version of Excel and cant change any of the values on the yellow columns in the Detailed Monthly Table. Am I doing something wrong? All say password protected.
Is it better to aim for the stars and hit a tree or aim for a tree and land in its branches

Loves being a Wonderbra friend
spud30 is offline
Reply With Quote Report Post
# 20
Locoblade
Old 14-09-2008, 9:46 PM
MoneySaving Stalwart
 
Join Date: Apr 2008
Posts: 712
Default

Hmm. There are locks on the cells that do all the calculations so they dont get over-written but the yellow columns are all editable. I can only think that older versions can't support partially locked pages so treat the whole page as locked

What version of Excel is it?
Locoblade is offline
Reply With Quote Report Post
The Following User Says Thank You to Locoblade For This Useful Post: Show me >>
Reply

Bookmarks
 
 




Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

 Forum Jump  

Contact Us - MoneySavingExpert.com - Archive - Privacy Statement - Top

Powered by vBulletin® Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

All times are GMT +1. The time now is 10:19 PM.

 Forum Jump  

Free MoneySaving Email

Top deals: Week of 27 August 2014

Get all this & more in MoneySavingExpert's weekly email full of guides, vouchers and Deals

GET THIS FREE WEEKLY EMAIL Full of deals, guides & it's spam free

Latest News & Blogs

Martin's Twitter Feed

profile

Cheap Travel Money

Find the best online rate for holiday cash with MSE's TravelMoneyMax.

Find the best online rate for your holiday cash with MoneySavingExpert's TravelMoneyMax.

TuneChecker Top Albums

  • ED SHEERANX (DELUXE EDITION)
  • VARIOUS ARTISTSNOW THAT'S WHAT I CALL MUSIC! 88
  • SAM SMITHIN THE LONELY HOUR (DELUXE EDITION)

MSE's Twitter Feed

profile
Always remember anyone can post on the MSE forums, so it can be very different from our opinion.
We use Skimlinks and other affiliated links in some of our boards, for some of our users.