My Excel mortgage spreadsheet

sieuph · 26 May 2016 at 2:41PM

Thanks for this - Brilliant to be able to compare mortgages with different rates and fees.

ccbrowning · 14 July 2016 at 10:48AM

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.

Locoblade · 14 July 2016 at 10:24PM

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

dustiebin · 26 July 2016 at 10:17AM

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

Dustiebin

Locoblade · 26 July 2016 at 8:22PM

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.

cheers

tightasabulls · 20 November 2016 at 11:10PM

I was wondering if you could share the password by any chance?

Thankyou again for sharing this work.

twarde · 7 May 2017 at 9:52PM

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

penst0ne · 29 May 2017 at 1:38PM

Thanks, just what I wanted.

Locoblade · 25 July 2017 at 3:50PM

twarde wrote: »

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.

jlwhite · 10 April 2018 at 7:36AM

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?

My Excel mortgage spreadsheet

Comments

Categories

Get our FREE Weekly email full of deals & guides - and it’s spam-free