Calculating Interest on accounts - more sophisticated/easy?

ChilliBob

masonic said:

AmityNeon said:

I personally would avoid raising to the power of (n / 365) as it results in approximations, and deviates further from accurate interest calculations the more transaction activity that occurs (outside of interest payments).

Yes, it's highly unusual for interest to be paid on interest that hasn't been credited yet, and also very rare for interest to be credited daily. It is true for the vast majority of accounts that annual interest is paid and compounded annually and monthly interest is paid and compounded monthly. Since months do not have regular numbers of days, the only accurate way to deal with monthly compounding is to take the gross rate divided by 365 as the base daily rate and include credited interest in the balance from the appropriate payment date. The only use of powers in the sums is getting from AER to a gross rate for monthly interest if you don't know it - but it should always be easy to find, being the basis for interest payments.

I have just done a little test, and I'm wondering if my logic is wrong anyway.. 

Take a 5% rate account with 50k in it, and no withdrawals - you'd expect this to net £2,500 in a given year, for example.

If you use my first method, calculating the daily rate, and rolling it up, you get to a total of £2,570.58 - essentially adding on the interest and paying 'interest on interest that hasn't been credited' I guess - which is inflating the total more than it should, I think!

As regards another poster asking the rational for these calculations - essentially I am trying to estimate:
1. Predicted income
2. Guaranteed income

Essentially I can't do anything about 2. - either it's already been paid, or it's a fixed rate with a maturity date - I know this is going to happen.

However, 1. is where I can make changes - if 1. means it'd be higher than I am targetting, then (as I have already done, as I approximated this exercise about 3-4 months ago), I can switch things up - either:
a. Buying other non income generating things -  e.g. funds with low/no dividends
b. Taking up fixed rates - essentially moving the income to future tax years
c. Buy gilts - not paying tax on the gain, only the (small) coupon

So with two months left, ish, I'm trying to work this out to decide if I can let things roll, or need to take further actions essentially. 

I probably won't re-do the analysis I have already done, that seems a bit wonky - as it seems to overstate - which is better than understate, but for future bits (for this year), and other years I can be a bit better.

Linton

To do a 100% accurate calculation you need to know exactly how and when interest is credited and compounded. That could be messy to calculate in a spreadsheet if you can withdraw money at any time and interest rates can change at any time.  And perhaps the accrual rules could change as well.

So ISTM that assuming daily compounding is the easiest way to get a pretty accurate answer without having to do a day by day calculation.

dales1

£2,570.58 is 366 days' interest at 5% compounded daily.

So you've an extra day in the calculation, and you're compounding (ie rolling-up) daily whereas the bank will compound only when the interest is awarded (eg annually).

See AmityNeon's post, where the accrued interest is kept separate from the balance earning the interest.

ChilliBob

Yeah, just by adding the interest each day and not compounding it I get the same result as the simple calculation. 

As Linton says though to be 100% accurate, or closer to that at least, you'd need to read, and probably question the T&Cs for each account. I don't need it to be perfect - just close enough, and an over approximation will be better than an under.

I should read about NS&I carefully though - as like I'm sure many others - when they had the best rate in town I combined several accounts into the Direct Saver, so it had a sizable balance compared to the other accounts I'm looking into. 

boingy

Just to add that I went through a similar exercise a few months ago, with one eye on whether I'd exceed my "allowance" for tax this year (6k in my case). I have a bunch of savings accounts, some normal, some fixed and some regular. I made a multi tab spreadsheet and did a few versions of the calculations before I realised what I should have realised at the start - the vast majority of my interest earnings come from just a couple of those accounts so I can just guestimate the others. Some of my regular savers have a monthly contribution of £50 so are never going to contribute anything significant to the final total. In that £50 example I just stuck £20 as the estimate -  deliberately a little on the high side to suit my purpose.

So the spreadsheet was fun to do but I stopped once I'd got a good enough result from it. What I'm really saying is: don't worry about calculating your interest to the last penny, especially as HMRC just go on what is reported to them by your banks and building societies, which is usually what they have paid out rather than what you have actually earned in the year.

ChilliBob

Yeah, I know what you mean, I did start a bit forensic yesterday - checking Starling, which has something like £150 in it and has a rate of something like 0.5! - Essentially I can save the bother on a small handful of current accounts and just tack on £50-100 to the balance if necessary.

Also, as I need to consider the dividends I'll get - which is far from an exact science, I need to factor in some kind of contingency  

zagfles

Linton said:

To do a 100% accurate calculation you need to know exactly how and when interest is credited and compounded. That could be messy to calculate in a spreadsheet if you can withdraw money at any time and interest rates can change at any time.  And perhaps the accrual rules could change as well.

So ISTM that assuming daily compounding is the easiest way to get a pretty accurate answer without having to do a day by day calculation.

Which accounts compound daily? I've never known any account which does that.

It's not that messy with an account where you can deposit and withdraw at random, and the rate can change. It's just a bit tedious. Just use the formula I mentioned above for every period where the balance and interest rate stays the same, and add them all up. I've done it loads of times with various accounts, and interest I've been paid has almost always matched my calculations to within a penny (where it hasn't has usually been because I made a mistake). 

For monthly interest, use the same except use the gross rate NOT the AER and account for how many days the interest is paid over (so you get more in a 31 day month than 30, or if interest is only paid on working days the period can vary even more). Then account for the monthly interest deposit as a normal deposit. But approximating using the annual method and AER is likely to get pretty close if you just want an estimate.

ChilliBob

Cheers yeah, so for the monthly ones, at this point I'm seeing when they pay to see if I need to tack on two or three payments, then picking a long month and multiplying by 2 or 3 - will be close enough!

I'm nearly done one way or another - looks like it'll be squeaky bum time, so I should probably buy some more TN28, or open another fix perhaps. 

clowning

Wow, those formulas are mind blowing to me

zagfles

clowning said:

Wow, those formulas are mind blowing to me

Don't worry about the forumla with the powers, that's only for daily compounding accounts which probably don't even exist (or are very rare if they do).

For a normal account you can shortcut the formula I mentioned by leaving the division for days in the year and the multiplication by the rate till the end. That'll work if the rate doesn't change.

So all you need to do is multiply the balance by how many days that balance stayed the same, and add them all up.

For instance say you have an annual interest account paying 5% interest on 31 Dec.

At the start of the year 1 Jan you have £1000 in the account.

Then on 15 Apr you withdraw £500, so balance £500

Then on 17 May you add £3000, so balance £3500

Then on 18 Oct, you withdraw £1000, balance £2500

What interest is paid on 31 Dec?  (assume not a leap year)

Just multiply each balance by the number of days that balance stays the same, and add them. (usually balance at the end of the day counts for that day, so count £1000 for 1 Jan to 14 Apr inc, then £500 for 15 Apr to 16 May inc etc)

So 1000*104 + 500*32 + 3500*154+2500*75 = 846500

Then just multiply by the rate and divide by days in the year 846500*0.05/365 = £115.96

Easy to do in a spreadsheet as it can work out the number of days in each period easily. Bit error prone if you do it manually. But it should match the actual interest paid to the penny, if it doesn't you've probably made a mistake.

Works for monthly interest account too, just done it for my Nationwide Flex direct account for Dec.

1 Dec paid in £1000, balance £1000

14 Dec balance £700

29 Dec balance £350

So 1000*13 + 700*15 + 350*3 = 24550

Now multiply by the rate (gross rate NOT AER) and divide by days in the YEAR.

So 24550*0.0489/365 = £3.29

Exactly what I was paid.