Calculating/predicting accrued interest for a tax year

boingy

Does anyone know of an interest calculator/predictor that can take account of changing interest rates and account balances throughout the year? I guess it's more likely to be a spreadsheet-style thing than anything else.

Like many others I'm starting to look at how much interest I am likely to accrue by the end of the tax year. Obviously you can't predict future interest rates but it would be really useful to see a prediction based on the story so far. I've got a half-a$$ed spreadsheet on the go but it's getting pretty messy, and could easily contain errors where my assumptions are wrong.

ChilliBob

I couldn't find one so I sort of rolled my own. For example eith NS&I...

Start balance in April, worked out the rate to a daily one, did thus cumulative until the rate changed, repeated etc, and works when you take cash out etc. 

I think I made a judgment call on most accounts, like did I have them open long enough and with enough balance to make a difference if I took an earlier rate.. If not I just took the most recent rate - thinking thay a future rate rise might make it come out even. 

Hardly scientific but it made me realise quite quickly I needed go do something! 

jaypers

I set up a spreadsheet in Excel.

frugalmacdugal

Hi,

couple of similar threads HERE1 and HERE2.

boingy

Thanks all. One of those threads held a bit of promise but the online version was never posted. I was sure someone, somewhere must have published a workable calculator for it by now.

I've done a bit more work on my spreadsheet and I have quickly realised that once I have covered the big accounts I might as well stop being precise and just estimate the others. For example, my Nationwide Regular Saver has a deposit limit of just £50 per month at 5.25% (about to rise to 5.5% I think). The *average* balance this tax year will be about £500 so I've just taken 5.5% of that as my estimate. It will be within a few quid unless rates go absolutely crazy.

It's been a useful exercise. If rates go up by more than about 0.75% within the next 2-3 months and stay there I'll need to adjust but I think I'll just about squeeze into the happy world of 0% tax. 

The other thing that has come out of it is that two of my six regular savings accounts are not very useful due to the low deposit limits. Earning 1%+ above the best EA saver accounts sounds good but if you can only add £50 per month then it's hardly worth the effort. So I might rejig a few things this week. All good fun!

refluxer

boingy said:

I've done a bit more work on my spreadsheet and I have quickly realised that once I have covered the big accounts I might as well stop being precise and just estimate the others. For example, my Nationwide Regular Saver has a deposit limit of just £50 per month at 5.25% (about to rise to 5.5% I think). The *average* balance this tax year will be about £500 so I've just taken 5.5% of that as my estimate. It will be within a few quid unless rates go absolutely crazy.

A good source for this type of info for a regular saver is the account summary box, as it'll normally show you what interest you would expect to receive if you deposit the maximum every month. Obviously if the rate is fixed, then the amount you receive will be pretty close to the one they state but even if the rate is variable and increases (or decreases) slightly, it won't make much difference to a regular saver account with a low contribution.

I haven't done the sums but I suspect a 0.25% rise midway through a £50/month regular saver (such as in your example) is probably only going to make a few pounds difference to the end result. If the account is still available, then you can always go to the product page and check out the new summary box which will state the expected interest at the new rate (and your end result would presumably be somewhere in-between the two).

Eirambler

Just a question of noting your interest from various accounts each month. If you're a basic rate earner just make sure the total is averaging under £80 a month and you should be fine, if it starts to slip over that figure then it's time to take appropriate action.

auser99

Eirambler said:

Just a question of noting your interest from various accounts each month. If you're a basic rate earner just make sure the total is averaging under £80 a month and you should be fine, if it starts to slip over that figure then it's time to take appropriate action.

What is the "appropriate action" out of interest?
Assuming you have utilised all the usual tax free methods.

boingy

refluxer said:

boingy said:

I've done a bit more work on my spreadsheet and I have quickly realised that once I have covered the big accounts I might as well stop being precise and just estimate the others. For example, my Nationwide Regular Saver has a deposit limit of just £50 per month at 5.25% (about to rise to 5.5% I think). The *average* balance this tax year will be about £500 so I've just taken 5.5% of that as my estimate. It will be within a few quid unless rates go absolutely crazy.

A good source for this type of info for a regular saver is the account summary box, as it'll normally show you what interest you would expect to receive if you deposit the maximum every month. Obviously if the rate is fixed, then the amount you receive will be pretty close to the one they state but even if the rate is variable and increases (or decreases) slightly, it won't make much difference to a regular saver account with a low contribution.

I haven't done the sums but I suspect a 0.25% rise midway through a £50/month regular saver (such as in your example) is probably only going to make a few pounds difference to the end result. If the account is still available, then you can always go to the product page and check out the new summary box which will state the expected interest at the new rate (and your end result would presumably be somewhere in-between the two).

Thanks. Your suggestion only works if you start the reg saver from zero at the start of the tax year. All of mine were already running and had significant balances at the start of April. And, just to be different, Nationwide's interest "illustration" is for £25 per month for 24 months! (It makes more sense when you consider that this particular account runs for a max 24 months and also has a random prize element that requires a minimum monthly deposit of £25.) But the point is, it's not much interest in the grand scheme of things, unless you win the £250 prize, of course.

EthicsGradient

I think a spreadsheet would work in the following way:
Column A: date (start at 6/4/23)
Column B: rate from this date (eg 3.5%)
Column C: added on this date (would be 0 if just a rate change; positive for deposit, negative for withdrawal
Column D accumulated up to this date - formula for cell D3: =(1+B2)^((A3-A2)/365)*E2
Column E new total - formula for cell E3: =C3+D3

copy D3 and E3, and paste into D4 and E4 and all lower cells in those columns. This will replace the "B2" with "C2", "A3" with "A4", "A2" with "A3" and so on.

Format the numbers in columns C, D and E as numbers with 2 decimal places for neatness. In your first row of numbers, put the starting amount as "added", and accumulated to this date as 0 (or the other way round).

This was done on LibreOffice; if Excel takes cells written as a percentage in the same way, it should work.

sample result:

date	rate from date	added on date	acc up to date	total06/04/23	3.50%	3000.00		0.00		3000.0004/05/23	3.50%	500.00		3007.93		3507.9301/06/23	3.80%	0.00		3517.20		3517.2001/07/23	4.10%	0.00		3528.00		3528.00

There might be arguments about when rate changes take effect - on the day announced, or from the next day? But I think that basically works.

Oh - the formatted "code" as a table looked fine when editing the post. Perhaps this?

date	rate from date	added on date	acc up to date	total
06/04/23	3.50%	3000.00	0.00	3000.00
04/05/23	3.50%	500.00	3007.93	3507.93
01/06/23	3.80%	0.00	3517.20	3517.20
01/07/23	4.10%	0.00	3528.00	3528.00

refluxer

boingy said:

Thanks. Your suggestion only works if you start the reg saver from zero at the start of the tax year. All of mine were already running and had significant balances at the start of April. And, just to be different, Nationwide's interest "illustration" is for £25 per month for 24 months! (It makes more sense when you consider that this particular account runs for a max 24 months and also has a random prize element that requires a minimum monthly deposit of £25.) But the point is, it's not much interest in the grand scheme of things, unless you win the £250 prize, of course.

Ah, fair enough. Yes - I'd forgotten that some regular savers are allowed to run beyond 1 year. That's a pain when Nationwide illustrate the likely interest earned like that and not particularly helpful to prospective account holders. Then again - the Start to Save regular savers are intended to encourage people to start saving and the PSA won't be an issue anyway for the majority of people in that situation, I guess.