My Excel mortgage spreadsheet

Locoblade

Hiya

I dont have the spreadsheet in front of me here at work to confirm, but it sounds like you're almost doing it right. .

From memory, firstly set the mortgage type to interest only, then allow the spreadsheet to automatically calculate your monthly payments each month, and finally set the payment to "Reduce Monthly" rather than "Keep Same" (this is the bit you may have missed). If you leave this as "Keep Same" or manually put figures into the Monthly Payment column that don't exactly equate to the interest owed that month, this will always stat paying off capital if you're offsetting because each month your minimum payment (ie the amount of interest you owe) will have dropped slightly, i.e. in month two the figure will be higher than the minimum required to service the interest payment, so paying off a bit of capital is the only thing the excess cash can do.

The variable amount of money you're putting into the offset each month needs to go into the monthly offset savings column, this will then reduce interest owed for the following month (and so reduce the calculated monthly payment) but the capital will stay the same because the spreadsheet recalaculates and reduces the monthly payment to suit.

Basically to sum up, change the settings to above then put all your variable payments in the offset column, leaving the monthly payment column as calculated.

cheers

kerridp

Locoblade wrote: »

From memory, firstly set the mortgage type to interest only, then allow the spreadsheet to automatically calculate your monthly payments each month, and finally set the payment to "Reduce Monthly" rather than "Keep Same" (this is the bit you may have missed). If you leave this as "Keep Same" or manually put figures into the Monthly Payment column that don't exactly equate to the interest owed that month, this will always stat paying off capital if you're offsetting because each month your minimum payment (ie the amount of interest you owe) will have dropped slightly, i.e. in month two the figure will be higher than the minimum required to service the interest payment, so paying off a bit of capital is the only thing the excess cash can do.

The variable amount of money you're putting into the offset each month needs to go into the monthly offset savings column, this will then reduce interest owed for the following month (and so reduce the calculated monthly payment) but the capital will stay the same because the spreadsheet recalaculates and reduces the monthly payment to suit.

Basically to sum up, change the settings to above then put all your variable payments in the offset column, leaving the monthly payment column as calculated.

Thanks very much for your reply. The problem I have is that the offset benefit is calculated daily from balances in a number of accounts (including a current account with obviously a frequently changing balance). Therefore there aren't 'offset payments' to speak of - just a benefit of having the offset facility. I know the value of this benefit on a monthly basis - it's the difference between the cost of paying interest on the full mortgage amount minus the actual amount calculated from the daily balance within all my accounts. I don't think I can reverse engineer this benefit to get to a value to put into the spreadsheet - unless I'm being thick (always a likely possibility!).

My offset mortgage is with First Direct but I believe it's a similar approach with One Account, IF etc.

I think I might be trying to do something the spreadsheet isn't designed to do so not to worry if this is the case.

Thanks again.

Locoblade

I think so unfortunately, the spreadsheet can only do monthly calculations because daily calculations are a nightmare, and as I primarily wrote it to predict future mortgage activity rather than act as a month by month log, accuracy to that level isnt really needed. The best you can do with varying current account offsets that calculate daily interest is average out the current account offset balance over a month and put that figure into the spreadsheet.

cheers

byb3

Wow, just last week I found the basic one from Microsoft on the net and thought that was great, but this has just blown me out of the water. Great work Locoblade!

clairehi

I never thought I could be so enthusiastic about a spreadsheet, but this has blown me away. for the first time I can actually do a full "life time" costing on different mortgage options to get a direct comparison between them. its been incredibly helpful, only a crystal ball can help me any further now!

I only wish I had the nous to have written it myself - well done Locoblade!

Palm_Centro

LOL, sadly not, currently having a bit of an IT

kazd

Hi

Love the spreadsheet but am unable to make any amendments in the yellow coloumns.

What I want to compare is having say a £100k offset mortgage and making an overpayment of approx £5k versus leaving the £5k in the offset savings account. What the reduction in term be the same.

Anon

Excellent thank you Locoblade - I have spent the past month working out calculations on whether to take a hit on an early redemption charge or carry on with my 5 year fix and comparing different scenarios. Almost convinced that this is a good decision despite the expense and the going from fixed to variable, therefore no guarantee for future rates.

Even taking the ERC into consideration (and a couple of assumptions - that the low interest rate will only last for 2 years followed by the rate I am currently paying - 5.63%), this demonstrates that my move to offsetting appears to make financial sense.

An interesting development for this in future may be to have the option to change the overall remaining term of mortgage part way through (this may be done to increase monthly payments to get around restrictions on overpayments by some mortgage companies and has been mentioned in a number of threads recently).

Anon

Mr_E_Man_2

Thanks for this. I'm going to have some fun messing about with it and seeing what I can save.

Locoblade

kazd wrote: »

Hi

Love the spreadsheet but am unable to make any amendments in the yellow coloumns.

Does anybody actually read the instructions or comments in the first post?