My Excel mortgage spreadsheet

Bean_69_99

thanyou, had already done it but not scrolled across to see the capital up and interest down per month...

thanks

purplebuzz

Thanks a lot, great speadsheet, goes much further than the one I have started to create.

Although I don't want to compare ttwo mortgages, becuase I am porting an existing deal on one rate and then taking new borrowing on another rate, I can put the two seperate sets of figures in the 2 mortgages!!!

Thanks !:beer:

Sponge

I just wanted to say thanks for this spreadsheet. It's really helping me decide what to do with the offers available to me at the moment. Something I wouldn't have had a 'snowball's chance' of doing without it. :money:

Queen-Bee_2

Locoblade wrote: »

Any feedback most welcome

Locoblade - this is a fabulous tool. Thank you so much for making it available to the rest of us. :T I've just started my mortgage free diary on the MFW board, and this has helped me so much! Queen-Bee

vicshippers

Thanks so much, that has just made this process SO much easier ♥

twinklie

Thanks so much for this....it's AMAZING!

sallycanwait

Wanted to add thanks for this spreadsheet - it has kept me amused all morning playing with varying interest rates for the tracker I've just got.

One question that hasn't been asked though (and is probably just me being dumb!), but in the Monthly Table worksheet, the Min Monthly Repayment column doesn't seem to be the same as the Basic Monthly Payment column.

Why is this? I am getting very confused as to why, after the first year, the payments in the Basic column are staying the same, despite a small overpayment entry.

I assume there is a reason for this? We have a maximum amount we can afford and will overpay to this amount each month. Obviously this varies according to interest rates (on a capped tracker) and repayment amount so have been manually entering the figures each month.

We are going to use this spreadsheet in realtime to keep an eye on how we're doing once we start our mortgage (next month if all goes to plan ).

Thanks again, a fantastic spreadsheet that reconfirms my love of Excel!!

Locoblade

Hiya

Minimum monthly is what you need to pay in order to clear the debt in the timeframe specified in "Loan Period", Basic montly payment could be the same, but when you're overpaying, it's dependant on what you tell it to do under "Overpayment/Offset effect on Payments" (No. 18 in Info and Key Figures). If you set this to "Keep Same" then the Basic montly payment will stay the same each month even though you could pay slightly less following an overpayment, ie you're slightly overpaying each month so reducing the term / reducing the Minimum monthly payment for subsequent months. If you set No. 18 to "Reduce Montly" then the Basic monthly payment will mirror the minimum monthly payment, ie you'll pay slightly less basic monthly following each overpayment so you only pay it off at the end of the loan period, not before.

In your case, if you have say £1000 a month you can afford to pay, and your mortgage is say £800 leaving a £200 overpayment, leave the setting 18 as "keep same", and I'd suggest changing setting 10 to manual then the monthly repayment can be manually as £1000 a month into 11 (rather than putting £800 a month plus a seperate £200 overpayment). In month 1 you'll be effectively overpaying £200 but in month 2 you'll actually be overpaying slightly more, which is the £200 plus the difference between the Minimum and Basic payments (say £201 in total). In month 3 you'll be overpaying £200 plus a bit more again. If you set it to "reduce monthly" then you'll only ever overpay £200 per month above the minimum, so won't be paying the full £1000 you can afford

The reason for inputting it as above as one payment is if the interest rate goes up, as long as the £1000 payment covers the new minimum payment, you dont need to change anything else in the spreadsheet apart from the new interest rate. If you input seperate basic and overpayments, then you'd need to change the overpayment otherwise you'd no longer be paying £1000 in total.

BTW, if you click your mouse on the headings of each column in the spreadsheet it explains a bit more detail what each column means, which may also help.

cheers

digispacedesigner

Thanks for this, its taken me ages to find something that actually does the work of computing my offset mortgage. Everything you need is there!

Cheers

Brilliant!

Mrs_Monkey_Boy

Hi - thanks for this, it's a brilliant tool to have!

Just a quick query - we've just moved to a new mortgage deal with a 2 year fixed rate. When I put the figure in, the initial monthly loan repayment figure is different to what I have from the bank.

We have 19 yrs 2 months left to go so I put in 19.17 into the loan period - is this correct?

The difference is only a couple of pounds but I'd like to know what I've been doing wrong!!

Thanks again,

Laura