My Excel mortgage spreadsheet
Comments
-
pstones578 wrote: »Is it right that if I have a 25 year mortgage I cannot enter 25 years into the initial rate field?
Yep, thats correct, if you read the advice note that pops up when you click on that cell you would know what to do
Basically to make the spreadsheet work, that number needs to be less than the total length of the loan otherwise you get some "divide by zero" errors in certain cells, so if its a 25 year loan with the same rate throughout, just set both interest rates the same and set the intro period to any number smaller than the loan length (1-24 in your case). Because the rate is the same in both, it doesn't matter what that number is.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 -
All
Just to note, the first proper bug has been found (thankyou lagi).
If you have a mortgage that has a a split rate, with say 5 years at 5% followed by the remainder at 6%, when you put something like £200 a month in the monthly overpayment box, it won't change the basic payment to the new rate after 5 years, so from year 5 to the end of the loan, it effectively underpays the mortgage slightly and uses some of the overpayment to make up the shortfall, in effect reducing the amount you're actually overpaying.
I'll have a look at rectifying this tonight, so if anyone is planning on spending time putting in their own mortgage figures from years past, you may want to wait for v1.1
Having had a look at this, I don't actually think its a bug at all. :j
Basically when you select the option for overpayments to not reduce your monthly payment (ie "Keep Same"), you're saying you want to pay the same as your first payment unless you need to pay more because of interest rises etc. When there's no / small overpayments, at the point the interest rate changes from say 5% to 7%, the minimum payment is suddenly higher than the amount you're currently paying so the spreadsheet bumps up your monthly payment to compensate. When you put in large overpayments though, by the time you reach the point when the new higher interest rate kicks in, the minimum payment for that new higher interest rate is still lower than the payment you're already making, so the spreadsheet doesn't bother changing the monthly payment.
Picture / thousand words and all that....
With 25 year £90k repayment loan with £300 a month overpayment...
http://www.locostfireblade.co.uk/Downloads/Spreadsheet1.jpg
With the same but only £50 a month overpayment
http://www.locostfireblade.co.uk/Downloads/Spreadsheet2.jpg
(do others see the embedded pictures or are they just red X?)
Anyway, if you want to raise the payment further at this point when the spreadsheet doesnt raise it by itself, you can always type it in manually, but doing it this way does tie in with how other overpayment calculators work, so I think I'll leave it as-is.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 -
I am an avid reader of this forum but have never felt compelled to post anything until I downloaded this spreadsheet. It's fantastic and just what I need at the moment as I am about to change my mortgage and am not sure which deal to go for. Thanks!0
-
An actual "Bug" found by me, more forgetful rather than a bug because I knew it was there but forgot to fix it.
In the yearly table, the "Payment" column is correct for the first year as it counts up all the payments for month 1 through to 12, but isn't correct for the remaining years as year two counts the payments from month 2 to month 13 rather than month 13 to month 24 etc etc.
Version 1.1 is being worked on.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 -
Version 1.1 now uploaded, Ive added the upfront fees option as suggested by bubblesmoney, and also sorted out the annual table so it calculates correctly and has information boxes to explain each heading.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730
-
This is really good but would it be possible to put the version number as part of the spreadsheet name so its easy to tell what one it is without opening it up?0
-
I could do it that way I guess, my thinking behind leaving it the same was so the download link stayed the same in the first post, but I guess I can edit that each time.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730
-
Totally fantastic and exactly what I've been looking for. Was thinking about interest only mortgage and overpaying ecah month against straight repayment mortgage, and trying to work out the difference this would make in years. This does it all for me. Brilliant thanks. Will be using it non stop over the next few months!!!!0
-
Version 1.2 now uploaded, Ive basically doubled up the spreadsheet so you can now put in details for 2 mortgages and compare them on a single comparison sheet.
http://www.locostfireblade.co.uk/Downloads/Mortgage%20Schedule%20Calculator%20v1.2.xls
Ive done this fairly quickly tonight therefore its not had a lot of testing, so please excuse any bugs on the Mortgage 2 sheets, let me know if you find any.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730
Categories
- All Categories
- 343.2K Banking & Borrowing
- 250.1K Reduce Debt & Boost Income
- 449.7K Spending & Discounts
- 235.3K Work, Benefits & Business
- 608K Mortgages, Homes & Bills
- 173.1K Life & Family
- 247.9K Travel & Transport
- 1.5M Hobbies & Leisure
- 15.9K Discuss & Feedback
- 15.1K Coronavirus Support Boards