Calculating/predicting accrued interest for a tax year
Comments

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:Mortgage: £200,000 (Sep 2021) Initial MF date: Sep 2031
Int Rate: 1.19% fixed until Nov 2026 (8.5% follow on rate?)
Cap+Int Repaid: £65100 (32%) £80,704 (40%) £82468 (40.48%)£89507 (43%) £91267 (44.7%) £98,309 (48.02%)
Target MF date: Nov 2026 Current MF date: Dec 2029, Nov 2029, Apr 2029, May 2029
Target Int Saving: £25,561 Current Int Saved: £12,350, £13,421, £16,991, £17,989, £18,699, £20,495
Overpayments suspended and surplus cash currently being diverted to high interest savings.2 
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)^((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:<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
0 
And, if you want a quick calculation of the interest (which was the purpose), another column to the right, with formula
=E3SUM(C$2:C3)
which is just the total, minus the initial amount and all the deposits and withdrawals.1 
Already done it.0

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.0

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.
Assuming you have utilised all the usual tax free methods.0 
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.
Assuming you have utilised all the usual tax free methods.
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.1 
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 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.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.
1 
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 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.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.0 
And if you're close to the higherrate threshold, you might bust it with £1 of unforeseen extra interest income. And thereby cause a cliffedge loss of £500 PSA and £100 extra tax.So it's good to forecast your interest (and possibly dividend) income.1
Categories
 All Categories
 340.1K Banking & Borrowing
 249.1K Reduce Debt & Boost Income
 448.3K Spending & Discounts
 232K Work, Benefits & Business
 603.2K Mortgages, Homes & Bills
 171.6K Life & Family
 245.2K Travel & Transport
 1.5M Hobbies & Leisure
 15.8K Discuss & Feedback
 15.1K Coronavirus Support Boards