My Excel mortgage spreadsheet
Comments

Thanks for this  Brilliant to be able to compare mortgages with different rates and fees.June 2020  Flack DVD Box Set
May 2020  Tarmac Pen
April 2020  Midweek Break to Blackpool0 
The SVR bit doesn't seem to work? When I look at the monthly table, the amount never changes after the fixed term period ends and stays the original low, fixed amount. Using Excel 2016... If I manually edit the interest rate starting at the month the SVR kicks in, it sort of works for that worksheet, though.
Seems amazing for the other bits, though.0 
Hi
It does work but one of the options set in a certain way can affect the end result. Input 18 on the front page (Overpayment/Offset effect...) has an effect when the interest rate change, if it's set to "Keep Same" it will keep the same monthly payment even when the interest rate changes if the SVR is lower than the introductory rate. Below is how you should set it so it always follows the minimum amount to be paid.
cheers
My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
Hello Locoblade,
Great SS. It has been invaluable for me comparing my reportage offers. So many thanks.
Now I am not sure if I am doing something wrong (most probably) but I am putting in a predicted lump sum overpayment into the sheet and it is not reducing the overall term of the mortgage. When I put in a monthly overpayment via the info and key figures tab it does reduce the overall term.
I am putting in my lump sum over payment in the Mortgage X Monthly Table part way through my 5 year fixed example in column L ( Monthly Overpayment)  when I put it here nothing changes on the term.
If I put in a regular payment into Mortgage X Info and Key Figures Cell F21 the term reduces.
I would add screen shots but unsure how that works on here.....
Am I doing something wrong or is there an error ?
Many thanks
Dustiebin0 
Hiya
Its the same setting as mentioned above to the previous question, this needs to be changed to "Keep Same" so subsequent monthly payments after the big overpayment arent reduced to retain the term. The reason it reduces the term regardless of that setting when a "Monthly Overpayment" amount is set just above it is because that box adds an overpayment every single month, so reducing the monthly each month whilst upping the monthly with a regular overpayment each month would cancel each other out and be pointless really.
cheersMy Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
I was wondering if you could share the password by any chance?
Thankyou again for sharing this work.0 
Locoblade,
I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?
Or is there a better solution for this type of mortgage?
Thanks0 
Thanks, just what I wanted.0

Locoblade,
I was looking at your spreadsheet to calculate the roll up interest on a non paying equity (or is called lifetime) mortgage. So using interest only option but I would like to be able to change the interest rate as its linked to BOE base rate. Would the calculations take account of more than two interest rate changes over the life of the loan?
Or is there a better solution for this type of mortgage?
Thanks
Hi
Sorry just catching up here as the forum seems to have stopped notifying me of new messages. You can change the interest rate every month if you like by editing it in the appropriate column in the Monthly tab, so should do what you need.My Excel Mortgage Calculator Spreadsheet: http://forums.moneysavingexpert.com/showthread.html?t=11571730 
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?0
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