Calculating Interest on accounts  more sophisticated/easy?
I'm trying to generate two figures  current interest earnt, and predicted interest at the end of the tax year. In the case of many accounts this is pretty easy  monthly interest, see what you've had so far, work out the dates of payments and see if you have two or three left this tax year, add on etc.
Some accounts, such as yearly easy access, seems a bit more convoluted  e.g YBS or NS&I.. my current plan:
Spreadsheet with the following columns:
Date, Amount, Interest
Date is a run of 365 days, amount is the starting amount at that tax year, with interest incremented along based on "Interest rate %/ 365  to get a daily rate".. So, with no transactions and no change in interest rate it'd be the same as just doing the rate * balance..
But, when you add cash, withdraw cash, or the rate changes its' more fuss  obviously.
Has anyone come up with a more sophisticated way to do this than basically building up a manual yearly time series thus far type approach in Excel?
Also  if anyone knew the YBS interest rate changes  well, where I could find them over time, that'd be grand  I can only find the most recent change and previous, not a timeseries!
I have a few accounts I'd need to do this with, and having done, nearly, YBS and NS&I it's rather time consuming!
Cheers!
Comments

The total % increase rate over n days is ((1+x%/100)^(n/365)1)*100 assuming your interest is compounded.
So when an event occurs yo
(( (1+x1/100)^(d1/365))*100
You start with V0 in your account at day D0 with interest rate X0. So when an event occurs at D1 you can calculate the value of your account at D1 using interest rate X0.
So
interest rate, date, amount withdrawn, initial value
1: X0, D0, 0, V0
2: X1, D1, W1, V1=V0*((1+X0%/100)^((D1D0)/365)W1
3: X2,D2, W2, V2=V1*((1+X1%/100)^((D2D1)/365)W2
etc etc
All you need to do now is to work out how to calculate the time periods in days.
0 
You can shorten the spreadsheet a bit by using a new line for each change  whether that's a deposit, withdrawal, or change in interest rate. You then have another column for "days elapsed", which is just the difference in 2 dates, and then you work out the interest for each period of that number of days (whether that should be "gross rate / 365 * number of days", or "(1+ AER rate) ^ (number of days / 365)  1" is an argument). So you'd have lines like
Date days deposit rate interest balance comment
1/1/2024 0 £1000.00 0.05 £0.00 £1000.00 (starting balance of £1000)
21/1/2024 20 £200 0.05 £2.74 £1202.74 (deposit of £200)
25/1/2024 4 £0.00 0.045 £0.82 £1203.56 (rate decrease from 5% to 4.5%)0 
Linton said:The total % increase rate over n days is ((1+x%/100)^(n/365)1)*100 assuming your interest is compounded.
So when an event occurs yo
(( (1+x1/100)^(d1/365))*100
You start with V0 in your account at day D0 with interest rate X0. So when an event occurs at D1 you can calculate the value of your account at D1 using interest rate X0.
So
interest rate, date, amount withdrawn, initial value
1: X0, D0, 0, V0
2: X1, D1, W1, V1=V0*((1+X0%/100)^((D1D0)/365)W1
3: X2,D2, W2, V2=V1*((1+X1%/100)^((D2D1)/365)W2
etc etc
All you need to do now is to work out how to calculate the time periods in days.0 
EthicsGradient said:You can shorten the spreadsheet a bit by using a new line for each change  whether that's a deposit, withdrawal, or change in interest rate. You then have another column for "days elapsed", which is just the difference in 2 dates, and then you work out the interest for each period of that number of days (whether that should be "gross rate / 365 * number of days", or "(1+ AER rate) ^ (number of days / 365)  1" is an argument). So you'd have lines like
Date days deposit rate interest balance comment
1/1/2024 0 £1000.00 0.05 £0.00 £1000.00 (starting balance of £1000)
21/1/2024 20 £200 0.05 £2.74 £1202.74 (deposit of £200)
25/1/2024 4 £0.00 0.045 £0.82 £1203.56 (rate decrease from 5% to 4.5%)
Funny how it's got me thinking in a SQL DB way in my head thinking if I'd be able to solve it more elegantly that way  but those days are a long time gone!0 
As above, it's not necessary to list individual days if they all accrue identical daily interest, so group those subtotals together. Add a new date entry whenever there is a change in activity that affects the daily accrual amount, ending with =TODAY() for the most up to date accrual figure in the previous row.
A B C D E F G H 1 Date In Out Balance Rate Days Interest Accrued 2 01/02/2023 9000  9000 5.00% 42 51.7808 51.7808 3 15/03/2023  500 8500 5.00% 95 110.6164 162.3972 4 18/06/2023 6000  14500 5.00% 94 186.7123 349.1096 5 20/09/2023  1000 13500 5.00% 96 177.5342 526.6438 6 25/12/2023 5000  18500 4.00% 28 56.7671 583.4110 7 22/01/2024 A B C D E F G H 1 Date In Out Balance Rate Days Interest Accrued 2 01/02/2023 9000  9000 5.00% 42 51.7808 51.7808 3 15/03/2023  500 8500 5.00% =A4A3 =D3*E3*F3/365 =H2+G3 4 18/06/2023 6000  14500 5.00% 94 186.7123 349.1096 5 20/09/2023  1000 13500 5.00% 96 177.5342 526.6438 6 25/12/2023 5000  18500 4.00% 28 56.7671 583.4110 7 =TODAY()
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).3 
AmityNeon said:
As above, it's not necessary to list individual days if they all accrue identical daily interest, so group those subtotals together. Add a new date entry whenever there is a change in activity that affects the daily accrual amount, ending with =TODAY() for the most up to date accrual figure in the previous row.
A B C D E F G H 1 Date In Out Balance Rate Days Interest Accrued 2 01/02/2023 9000  9000 5.00% 42 51.7808 51.7808 3 15/03/2023  500 8500 5.00% 95 110.6164 162.3972 4 18/06/2023 6000  14500 5.00% 94 186.7123 349.1096 5 20/09/2023  1000 13500 5.00% 96 177.5342 526.6438 6 25/12/2023 5000  18500 4.00% 28 56.7671 583.4110 7 22/01/2024 A B C D E F G H 1 Date In Out Balance Rate Days Interest Accrued 2 01/02/2023 9000  9000 5.00% 42 51.7808 51.7808 3 15/03/2023  500 8500 5.00% =A4A3 =D3*E3*F3/365 =H2+G3 4 18/06/2023 6000  14500 5.00% 94 186.7123 349.1096 5 20/09/2023  1000 13500 5.00% 96 177.5342 526.6438 6 25/12/2023 5000  18500 4.00% 28 56.7671 583.4110 7 =TODAY()
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).0 
ColdIron said:
@AmityNeon how do you get the nicely formatted tables?
At some point during the recent revamp I seem to recall a 'table' option under the button with the 'quote' option but it didn't survive unless I dreamed it
I used the Code option (between Spoiler and Quote) as the formatting is more concise and visually distinct compared to a table.
For tables, the easy option would be to copy/paste directly from spreadsheet software and the forum editor will automatically generate the formatted table for you. The messy option is editing the HTML directly.
2 
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).
3 
Linton said:The total % increase rate over n days is ((1+x%/100)^(n/365)1)*100 assuming your interest is compounded.
So when an event occurs yo
(( (1+x1/100)^(d1/365))*100
You start with V0 in your account at day D0 with interest rate X0. So when an event occurs at D1 you can calculate the value of your account at D1 using interest rate X0.
So
interest rate, date, amount withdrawn, initial value
1: X0, D0, 0, V0
2: X1, D1, W1, V1=V0*((1+X0%/100)^((D1D0)/365)W1
3: X2,D2, W2, V2=V1*((1+X1%/100)^((D2D1)/365)W2
etc etc
All you need to do now is to work out how to calculate the time periods in days.I've never known any account which compounds interest daily. Every account I've ever had either compounds monthly or annually. So above is far too complicated.For a normal account which pays interest annually, it's far simpler, just use balance*(days/365)*(interest rate/100) for every period where the balance and interest rate stays the same. Or use 366 instead of 365 if the year over which the interest is paid includes 29 Feb. It's a bit harder for monthly interest as mentioned by masonic above.If you're wanting to work out the interest over the tax year for tax purposes, there's no point working out accrued interest as HMRC will be using interest actually paid.
1
Categories
 All Categories
 341.7K Banking & Borrowing
 249.7K Reduce Debt & Boost Income
 449.1K Spending & Discounts
 233.8K Work, Benefits & Business
 606K Mortgages, Homes & Bills
 172.4K Life & Family
 246.7K Travel & Transport
 1.5M Hobbies & Leisure
 15.8K Discuss & Feedback
 15.1K Coronavirus Support Boards