My Excel mortgage spreadsheet

Locoblade

Hi

Unfortunately that would need a complete rewrite of all the equations to allow it to split up the monthly payment etc so unlikely to ever get done I'm afraid.

*Jellie*

Hi Locoblades.
This spreadsheet looks amazing- many thanks for all your work in it. I am on an offset mortgage and although I can use the spreadsheet to work out scenarios I am struggling to work out how I can input my actual offest figures each month to get an accurate picture. I'm sure it is me being out of practice with spreadsheets but the only ways I can think to do it the cells are password protected that I need to change.
Any ideas?

*Jellie*

Don't worry- as I explained the problem here the answer popped into my head!!
Thanks so much for an amazing spreadsheet!!

Yorkie1

Hi Locoblade

Thanks for your spreadsheet. :j

Not sure if I'm doing something wrong with it, but it doesn't appear to have reduced my repayment amounts when I drop down to the SVR % from my fixed rate % at the end of the fixed period. It keeps deducting the same 'basic monthly repayment' amounts in the tables (although the 'minimum monthly repayment' amount is correct), so the mortgage shows as finished 2 y 4 m earlier in box K11 than it should (no overpayments / offset).

Can you assist, please?

Edit: it worked fine when I put in alternative figures where the follow-on rate was higher than the intro fixed rate - perhaps that's got something to do with it?

Locoblade

Hi

It sounds like you have the monthly payment option set to "Keep the same", which means it will keep your same initial monthly payment even if the interest rate drops. You need to change that to Reduce so the monthly will always follow the minimum you need to pay.

Cheers

Yorkie1

A-ha! Thanks very much!! I wasn't adding any additional overpayments so hadn't appreciated that that applied even where the SVR was simply lower.

runestone

Hi,
The tax bands are now 20% and 40% (cell F29). The 10% tax band only applies in very specific scenarios where income is only savings. Also, just to check- are you using the latest tax bands- it would make a difference to the offset calculations.

Cheers

Locoblade

runestone wrote: »

Hi,
The tax bands are now 20% and 40% (cell F29). The 10% tax band only applies in very specific scenarios where income is only savings. Also, just to check- are you using the latest tax bands- it would make a difference to the offset calculations.

Cheers

Hi

Unless I'm missing the intention of your first sentence, 20% and 40% are still options in that drop-down so feel free to ignore the 10% option if it's not relevant

The spreadsheet doesn't use any tax bands, it assumes all your savings will be taxed at the rate your salary is taxed at so it just uses what you set in the drop-down. If you're savings interest income takes you through a tax boundary or your entire income is from savings interest meaning you're paying tax on those earnings at more than one rate then you need something a bit cleverer to calculate it all I'm afraid.

Calzor_Suzay

What's the best way to keep track of an offset interest only mortgage?

I can't edit the "monthly capital paid off column but this seems to be the most appropriate column, all I really want to do is enter how much I pay into the mortgage account each month and I guess what my offset balance is, the interest charge is just taken from my Current account.

Calzor_Suzay

Ok I think I got it, Didn't relaise it was in 'repayment' mode, switched it to 'interest ony'. Now I think in the 'Monthly Overpayment' column I enter what I put into my mortgage account and the 'Monthly offset saving' column is where my offset savings are although I have + & - the amount from my initial input.

Is this correct?