View Full Version : Working out mortgage interest with an offset account?
Does anyone have a spreadsheet or some pointers to creating one to calculate how much of my mortgage I am paying off, what the interest is etc.
I have an offset account and daily interest and fixed monthly payments based on the interest rate (currently 5.25%) and want to know how much I am effectively overpaying my mortgage (the offset means my monthly payments go towards capital more than normal as well as the offset amount offsetting the capital).
I know the bank will send me a yearly statement, but was hoping to get some ideas before that. The spreadsheet would obviously have to work with changing interest rates as I am on a BofE tracker.
Assuming the monthly payments were set so that it would be a normal repayment mortgage without the offset, the effective overpayment is equal to the interest saved, so each month you effectively overpay by about 0.4375% of your average offset for that month (ie, your current and/or saving account balance(s)).
Yes it is a normal repayment mortgage.
Its just a savings account unfortunately, but that seems good to me. How did you come up with the 0.4375% ?
EDIT: I just tried some basics via the BBC site calculator and came up with different numbers. If I put in my initial mortgage-offset I get more than 0.4375% off the offset as overpayment (e.g. the payment it says is due compared to my actual payment, the difference is more than 0.4375% of offset)
Shamaniac
07-09-2008, 14:32
It's not possible that easily. Firstly, what presicely are you offsetting?
Do you offset a savings account which remains untouched over the mortgage period, or do you take money out or fill up in regular/irregular intervals?
Do you also offset one or more current accounts, the balances of which change every day?
In which case, the actual mortgage savings through offsetting can only be calculated ex post, on a day-to-day basis. (That's what your bank does in the monthly statement).
In case you offset a permanent amount in savings, it's possible to work out a good approximation to what you're going to save over the mortgage period, and also the date when the mortgage will be paid off.
Its an offset savings account, but I cam pay into and take out of at will. Its just savings only and not current account with this mortgage.
The approximation and estimated date of pay off is what I am after :)
Shamaniac
07-09-2008, 14:37
How did you come up with the 0.4375% ?
Yeah, I wonder? Doesn't that depend on one's specific remaining mortgage period, mortgage value, offset amount and interest rate?
The approximation and estimated date of pay off is what I am after
So savings offset only, no current accounts. Your final savings then depend on what you do with your savings account, what you take out and pay in over time. Unforeseen expenses, unforeseen windfall... maths can't help here, you need a crystal ball.
But let's assume your savings remain stable and you can go like this:
1) Put your mortgage value, monthly repayment and interest rate into any online mortgage calculator and get the total repayment value.
2) Now do the same again, but put in your mortgage value minus your savings. Your monthly repayment should be unchanged. You should arrive at a considerably lower total repayment value.
3) The difference between 1) non-offset total repayment value and 2) offset repayment is a good estimate of what you are going to save through offsetting.
Its just a savings account unfortunately, but that seems good to me. How did you come up with the 0.4375% ?Just 5.25/12 . For reasons best known to themselves, the quoted interest rate for mortgages is just 12x the monthly rate, no compounding.
Obviously as Shamaniac says to work it out exactly requires calculating day by day, as the bank itself does. Taking the average balance over the month is a good enough approximation though, especially for savings accounts which won't really be changing day to day like current accounts do.
[edit]Shamaniac: If your just interested in the effective amount overpaid, then all that matters is the interest saved, so all that matters is the interest rate.
Well that still seems way off to me compared to running the numbers on the BBC web site for fixed amounts. Also my offset will go up (hopefully only up!) every month as I pay more in so need to calculate that (ideally based on the day I pay it in that month)
Shamaniac
07-09-2008, 14:50
Makes perfect sense, Mr Nice.
Kryten, for the case that
a) you don't take money out of your savings,
b) put a regular amount into your savings account at regular intervals (e.g. monthly),
c) your interest rate remains stable,
it is possible to draw up an Excel sheet that tells you your savings month for month, with the accumulated savings over the whole mortgage on the bottom. It would also work out when you break even.
But it ain't simple.
Kryten, which bbc calculators that? The only one I found is http://www.bbc.co.uk/homes/property/mortgagecalculator.shtml
.
Which is actually a bit broken. For the repayment total, it assumes the given interest rate is the compound rate over the year (what the apr would be if it didn't also have to take into account fees and all those years after the deal ends etc.).
But for interest only, it assumes that the given rate is the flat sum of the monthly rate (which is what quoted mortgage rates are by all providers). Probably because that makes the maths easier for interest only calculations.
It does seem that its not so simple, I would have hoped it would have been :(
cjanderson
07-09-2008, 15:16
you could knock a spreadsheet that has each month
then you have across the rows
start of month
less regaulr payment
less LUMP SUM OVERPAYMENT (this would mostly be zero)
then + interest on current outstanding balance
to get to end of month
and then start of next month is end of current month
Then you stick the interest rate in (assume same for all time or could do different rates at different times)/
your initial offset is month 1's lump sum.
I've done quite a few of these sort of spreadsheets, fairly simple really.
(and just found one i did before if you want it)
Yeah, mail it to my forums address and I'll see if I can adapt it to what I want. I'm really crap at Excel so hate doing these things.
It does seem that its not so simple, I would have hoped it would have been :(
It's not simple - we've been trying to do the same.
We had a NR mortgage we were overpaying on (doubling the payments). We switched to a better offset repayment mortgage. OH has been trying to work out if we are better overpaying or offsetting with the money we have 'spare' each month.
It's proving quite complicated. On the old mortgage with the level of overpayment, we would have had it paid off in 11 years (we had a 30 year mortgage). With the new 25 year one offset we worked out something like 16 years BUT we're having work done to the house at the moment, so would not be overpaying anyway. We changed to an offset rather than an overpayment because we were stopping and starting the overpayments all the time and it was a pain.
But overpayment seems to be the way to go.
OH says when you work it out, let him know :lol:
Well based on the spreadsheet Catherine just sent it looks like I can adapt that for variable interest rates and maybe even daily interest. But simple sums puts mortgage repayment at 41 months from start so looks good to me :) (probably will not happen though!)
cjanderson
07-09-2008, 16:18
OH has been trying to work out if we are better overpaying or offsetting with the money we have 'spare' each month.
how are the 2 any different :thinking:
Yeah, offsetting is always better if you can as you can get the money back, from a financial point of view there is no difference
cjanderson
07-09-2008, 16:29
ah right, if the offset amount is held in a seperate pot you mean? yes, thats more flexible.
Yeah, typically the offset is kept in a savings account and/or current account (savings only in my case).
Yes, offset account linked to the mortgage (offset mortgage).
We worked out that there was a financial difference, at least for ours anyway. But as ours is a tracker, we probably need to look at it again as the rate of the mortgage has changed.
OH said to post that he used the IF calculator. Don't ask me - I'm just a girl :lol:
cjanderson
07-09-2008, 17:02
what, different interest rates on the offset to the main mortgage? never heard of that.
Tracker doesn't make any difference, thats just how the interest rate moves over time (rather than fixed or just SVR).
Usually money in an offset account is calculated daily against the outstanding balance, as are overpayments into a mortgage. There is absolutely no difference in how much you save on your mortgage (or should not be) even if its a tracker/fixed or SVR.
Shamaniac
07-09-2008, 17:08
I've also just cooked up a sheet that spits out your offset benefit on a monthly basis, with the following assumptions:
a) the interest rate doesn't change
b) your offset savings stay untouched
c) you put a stable monthly amount into your savings, or none (value = 0)
Just enter outstanding mortgage, interest rate, offset savings, monthly rate and monthly savings amount. The sheet does the rest. You're out when your savings break even with your outstanding mortgage.
It wasn't easy, and it's probably not as nice and elegant as CJ's. Let me know if you want it anyway.
Well you are welcome to mail it to me and I can compare it to the one Catherine sent and I have edited. I'll see if the numbers match up!
Shamaniac
07-09-2008, 17:29
YGM
I bloody hope the numbers match up, but I have a bag ready to pull over my head in case they don't.
We may have been plugging the details in incorrectly then. Or the calculator could have been wrong. But there was definately a difference between overpaying and leaving that money in the offset :shrug:
cjanderson
07-09-2008, 17:35
mine's only done on a basic Annual interest /12 sort of way (rather than APR ^1/12) but in terms of paying it all off, its only going to make a few £s difference anyway.
whatever happens, interest rate is the factor.
if your mortgage rate is higher than your savings rate, pay off mortgage, if other way round, put it into savings. So i stick it in a cash isa first as higher rate than the mortgage at present. and if mortgage rate and isa rate the same, makes no odds which way you do it, you end up in the same position at the end (either all mortgage paid off or cash isa balance balances mortgage o/s)
If both the same, best to stick to a cash isa rather than overpay as those tax free savings are lost if the limit not used each year.
I do Cash ISA first too (3600/year) and then offset account leaving some in normal instant access savings account for day to day spending and for balancing my work expenses vs credit card payments.
The way I look at the offset is its better than any savings account due to 40% income tax on savings interest and at 5.25% mortgage interest I'd have to earn nearly 9%, and when the rate goes up to 5.75% at the end of the year it would be closer to 10%, so no savings account pays that on the amount I want to save!
My ISA is my "rainy day" account if I lose my job to pay my mortgage, but also haivng the offset makes repaying mortgage from that savings account easy too (or stretches my ISA money more).
Shamaniac
07-09-2008, 17:43
Kryten, YGM. Converted to xls.
Got it and replied. Thanks :)
I'm confused :D
So cj are you saying that there is no difference in overpayments then offsetting?
The offset calculator here http://www.if.com/ seems to say different?
I'd rather continue to use the offset account as it's easy to get money out if we need to :)
cjanderson
07-09-2008, 18:28
well if you overpay 5000 in month 1 or offset 5000 in month 1, it makes no difference from what i can tell.
suppose they assume with overpayments you would do small amounts each month rather than a big pump sum. but if the amounts are the same, it makes no difference.
Ok, that's sorted that matter out, we will continue to use the offset which is good as it's a lot more flexible then overpaying.
I sort of understand the reasoning/logic behind it :D
Shamaniac
07-09-2008, 18:52
Cheers Kryten, all received.
What is definitely better in CJ's sheet is how you can adjust interest rates when they change. :thumbs: :notworthy
It takes an extra column, but there's no way around that, I suppose.
I added that as needed to do that per month :D
cjanderson
07-09-2008, 19:02
yep, you can have multiple rates, as most mortgages do change rate over time. Though then again you'd probably remortgage then.
Still wouldn't recommend offsets, they cost far more than normal trackers where you just overypay against the mortgage.
And the benefits of having your salary in there are very tiny (an average of say 1k offsetting for 25 years) and the risks of getting into a pickle are high (ie no displine to ACTUALLY pay off your mortgage each month by a set amount)
My rate is BofE +0.25% for 6 months then +0.75% for life with just a 99GBP application fee and only 1 year tie in. Did not see anything else close to that at the time offset or not though, and most non-offset limited me to 10% overpayment per year.
Any chance you could send us the spreadsheet, would be interesting to see.
Cheers.
We managed to get a good offset with Hinkley before the rot set into the market - Bof E +.2% for the life of the mortgate offset (no tie in).
YGM to your forums registered address
yep, you can have multiple rates, as most mortgages do change rate over time. Though then again you'd probably remortgage then.
Still wouldn't recommend offsets, they cost far more than normal trackers where you just overypay against the mortgage.
And the benefits of having your salary in there are very tiny (an average of say 1k offsetting for 25 years) and the risks of getting into a pickle are high (ie no displine to ACTUALLY pay off your mortgage each month by a set amount)
We pay a set amount to the mortgage - so we pay enough to pay it off in 25 years. We just put our spare cash into the offset account. We have current accounts elsewhere.
So even if the offset remained empty, we'd still be paying off the mortgage in 25 years.
That is exactly the same setup as mine (West Brom).
cjanderson
07-09-2008, 20:02
yep, they are fine if they are just linked savings (though keep your isa's elswewhere), its the all in one/current account type ones that are just far too complex for most people that i dislike.
My ISA is at Abbey still, would not keep them at the same palce unless rates are really good, and always fill ISA first as the interest rate is much better and kept seperate.
vBulletin® v3.8.5, Copyright ©2000-2012, Jelsoft Enterprises Ltd.