Calculating/predicting accrued interest for a tax year
boingy
Forumite Posts: 654
Forumite
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 spreadsheetstyle 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 halfa$$ed spreadsheet on the go but it's getting pretty messy, and could easily contain errors where my assumptions are wrong.
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 halfa$$ed spreadsheet on the go but it's getting pretty messy, and could easily contain errors where my assumptions are wrong.
0
Comments

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!1 
I set up a spreadsheet in Excel.1


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 23 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!0 
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.
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 inbetween the two).1 
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.0

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.
Assuming you have utilised all the usual tax free methods.0 
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.
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 inbetween the two).0 
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)^((A3A2)/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 4 
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.0
Categories
 All Categories
 339K Banking & Borrowing
 248.7K Reduce Debt & Boost Income
 447.6K Spending & Discounts
 230.8K Work, Benefits & Business
 601.1K Mortgages, Homes & Bills
 171.1K Life & Family
 244.1K Travel & Transport
 1.5M Hobbies & Leisure
 15.9K Discuss & Feedback
 15.1K Coronavirus Support Boards