Calculating/predicting accrued interest for a tax year

BikingBud

Try this:


Formulas as follows:

• Inputs - Relatively straightforward for planning but enable differing rates and savings amounts.
• Date - Column E just copy and drag as required until end date.
• Period - Column F -simple count up from 0 
• Beginning G5 = D4, G6 = I5 as no interest, G7 = I7+£2000 (monthly saving) or G8=I7+$D$8 (monthly expected saving) - so you can plan on an amount £1050 and if you actually save less or more replace $D$8 with actual amount in required row.
• Interest - Column H - =$D$5/$D$7 - If D7 = 12 this will give you monthly interest or if D7 is 365 daily interest but you will need to increase the period of investment.
• End = beginning sum +interest for period eg =G7*(1+H7), this compounds up the interest.

As you go through and reconcile savings and interest as added you can replace formulas with actuals and it should tally with your accounts and enable interest tracking. Or for a lump sum something like this and you can see when you may have a tax liability:

boingy

EthicsGradient said:

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:

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

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

Brilliant! Thank you. In the time it took you to add the formatted version I was already in Calc making sense of the unformatted version. My attempted solution was very close to that but I got bogged down in trying to include the interest rate changes - it never occurred to me to add zero funds with the new interest rate. So you'll be amused to hear that my solution to that had <cough> 365 rows in it. Not subtle but the two methods agree to within 10 pence. It's like trying to calculate the exact numbers on your pay slip every month....

EthicsGradient

And, if you want a quick calculation of the interest (which was the purpose), another column to the right, with formula
=E3-SUM(C$2:C3)
which is just the total, minus the initial amount and all the deposits and withdrawals.

boingy

Already done it. 

boingy

refluxer said:

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. 

You are exactly right. I jumped into this one because it was waving a rate that started with a 5 at a time when not much else was and I didn't give much thought to the relatively small sums of money involved. They have two prize draws each year. The chance of winning £250 is between 1 in 34 and 1 in 67 and the next draw is August so I'll wait for that and then probably move the money elsewhere, leaving the usual £100 there in case they distribute an even Fairer Share next year  

Eirambler

auser99 said:

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.

In my case it's 1) max out the ISA for the year (already done), 2) move some across to my wife's regular and ISA accounts (still some wiggle room there, but not for much longer the way interest rates are increasing and 3) the last resort, Premium Bonds (still plenty of spare capacity to add funds there if required).

auser99

Eirambler said:

auser99 said:

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.

In my case it's 1) max out the ISA for the year (already done), 2) move some across to my wife's regular and ISA accounts (still some wiggle room there, but not for much longer the way interest rates are increasing and 3) the last resort, Premium Bonds (still plenty of spare capacity to add funds there if required).

Fair enough, all sounds good work.
Was just checking you didn't mean something like move funds to a non interest account to avoid the extra interest that would create tax!
A few on here seem more intent on not paying any tax, then focusing on what gives the higher net sum.

dosh37

boingy said:

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.

I'm curious to know why you think you need such information.

Once you have maxed out your annual ISA limit, and you cross the savings tax threshold, there is little you can do about paying savings tax, so I can't see much point in trying to predict interest figures in advance unless you are a couple and can open accounts under different names or you invest elsewhere such as the stock market.

I maintain an Excel spreadsheet, but it's sole purpose is to keep track of the interest from many different savings accounts for filling in the HMRC Self Assessment form in April. I don't bother trying to predict interest income in advance.

The £85K FSCS limit and frequent rate interest changes mean I have several savings accounts. I open and close accounts many times when chasing the best interest rates.

Many online savings organisations do not provide Interest and Tax Statements (especially for closed accounts) so the spreadsheet is about the only way I have to keep track.

EthicsGradient

dosh37 said:

boingy said:

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.

I'm curious to know why you think you need such information.

Once you have maxed out your annual ISA limit, and you cross the savings tax threshold, there is little you can do about paying savings tax, so I can't see much point in trying to predict interest figures in advance unless you are a couple and can open accounts under different names or you invest elsewhere such as the stock market.

I maintain an Excel spreadsheet, but it's sole purpose is to keep track of the interest from many different savings accounts for filling in the HMRC Self Assessment form in April. I don't bother trying to predict interest income in advance.

The £85K FSCS limit and frequent rate interest changes mean I have several savings accounts. I open and close accounts many times when chasing the best interest rates.

Many online savings organisations do not provide Interest and Tax Statements (especially for closed accounts) so the spreadsheet is about the only way I have to keep track.

When you're within the PSA, you can get better interest in a normal account than in an ISA. But if you're going to exceed the PSA in a tax year, it could be worth putting some in an ISA. So a tool to predict the end-of-year outcome can be useful.

dales1

And if you're close to the higher-rate threshold, you might bust it with £1 of unforeseen extra interest income. And thereby cause a cliff-edge loss of £500 PSA and £100 extra tax.

So it's good to forecast your interest (and possibly dividend) income.