My Excel mortgage spreadsheet

Locoblade

Hi

See Option 18 on the Info and Key Figures page, there's a drop-down to select either Keep Same or Reduce Monthly when an overpayment is made, if you chose the latter the term will remain the same but the monthly will be reduced.

cheers

JonB1081

Hi there,

I've just started using this amazing spreadsheet. We're just starting our mortgage and i want to keep an eye on it as we repay.

The only thing i couldn't work out is that the calculation doesn't see to lower our repayment (Total monthly payment) after the initial fixed rate. (as currently our initial rate is higher so we have a higher initial repayment) It seems to keep the repayment at the initial level.

I've put in the variable interest rate and the length of the fixed term but i can't seem to get it to work.

To put it into numbers:

Fixed Rate Period our monthly payments will be 840 - this is for the first 2 years.

Variable Rate they will be 660 - for the remainder of the mortgage.

However the spreadsheet keeps the payments at 840 for the whole mortgage and cuts the term of the mortgage. Is there anyway to put the lower payments in?

Locoblade

Hi

Set option 18 to "Reduce Monthly" rather than "Keep Same", that affects reductions in interest rate as well, otherwise the spreadsheet assumes you want to continue paying at the higher amount.

Cheers

JonB1081

Locoblade wrote: »

Hi

Set option 18 to "Reduce Monthly" rather than "Keep Same", that affects reductions in interest rate as well, otherwise the spreadsheet assumes you want to continue paying at the higher amount.

Cheers

Worked perfectly thanks!

BristolBob

Excellent resource, thank you.
I had the same issue as the previous poster and this leads onto a second issue.
I have a 5 year fixed on interest rate X and then SVR Y. So, the solution above works to effect a change in interest rate. However, if I want to make OVERPAYMENTS during the initial 5 year fixed (and DON'T want to reduce monthly payment - i.e. want it to bring down the term), it doesn't seem possible.

Locoblade

Sorry missed this one, not entirely clear on what youre trying to do, i think a combination of keeping the same payment after an overpayment, and reducing it when the SVR kicks in? If so, you want to make ad hoc overpayments to reduce the term, just set the above mentioned setting to keep same, then it will carry on making the same basic payment each month and reduce the term. If you then want it to reduce initially when the SVR kicks in, you'll have to manually change the first monthly payment back down again, asking it from the minimum payment figures in the spreadsheet.

BristolBob

Locoblade wrote: »

Sorry missed this one, not entirely clear on what youre trying to do, i think a combination of keeping the same payment after an overpayment, and reducing it when the SVR kicks in? If so, you want to make ad hoc overpayments to reduce the term, just set the above mentioned setting to keep same, then it will carry on making the same basic payment each month and reduce the term. If you then want it to reduce initially when the SVR kicks in, you'll have to manually change the first monthly payment back down again, asking it from the minimum payment figures in the spreadsheet.

Thanks for the response. This was exactly what I was trying to do. I have "wasted" many hours playing with your spreadsheet, to the extent that my wife groans whenever she sees me opening it.
Has given me LOTS and LOTS to think about. Thank you so much! Greatly appreciated.

Cariad71

Hi, thanks for the spreadsheet.
I'm confused! I have a 172k interest only mortgage with 20 years to go. I intend to OP £760 per month. The spreadsheet says this will take 20 years. But the overpayment calculator says 14?? Please unconfuse me

Thanks in advance!:)

Locoblade

Hiya

Without knowing all your interest figures etc it's hard to understand what's going on, could you note down everything you're putting into the inputs boxes so I can try it myself, specifically inputs 1-5, 10 and 17-18

cheers

getmore4less

Do you have a dump/import feature to make transfer of data easy?

a tabulated format of name value pairs is probably easiest that way order does not matter