Calculating Interest on accounts - more sophisticated/easy?

ChilliBob

Hey guys,

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!

Linton

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)^((D1-D0)/365)-W1
3:  X2,D2, W2, V2=V1*((1+X1%/100)^((D2-D1)/365)-W2

etc etc

All you need to do now is to work out how to calculate the time periods in days.

EthicsGradient

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%)

ChilliBob

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)^((D1-D0)/365)-W1
3:  X2,D2, W2, V2=V1*((1+X1%/100)^((D2-D1)/365)-W2

etc etc

All you need to do now is to work out how to calculate the time periods in days.

Thank you Linton. That (mostly!) makes sense, I'll have to have a play and see how I get on with it. I think it does make me realise that monthly interest is probably preferred for me in the future. However, I did deliberately move to some fixes and some easy access paying yearly paying *after* this tax year to try and smooth out my interest payments - will see how well that works in time - bit of guess work involved!

ChilliBob

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%)

Thanks, that approach also looks like it would work - on the whole this isn't (thankfully!) tonnes of transactions of each type - probably a max of say 15 - would certainly make for a tidier XLSX!

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!

AmityNeon

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%  =A4-A3  =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).

ColdIron

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

ChilliBob

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%  =A4-A3  =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).

Thanks for going to the trouble of doing the mockup for me! Much appreciated, and helps to visualise the ideas above very much

AmityNeon

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.

masonic

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

Yes, it's highly unusual for interest to be paid on interest that hasn't been credited yet, and also very rare for interest to be credited daily. It is true for the vast majority of accounts that annual interest is paid and compounded annually and monthly interest is paid and compounded monthly. Since months do not have regular numbers of days, the only accurate way to deal with monthly compounding is to take the gross rate divided by 365 as the base daily rate and include credited interest in the balance from the appropriate payment date. The only use of powers in the sums is getting from AER to a gross rate for monthly interest if you don't know it - but it should always be easy to find, being the basis for interest payments.

zagfles

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)^((D1-D0)/365)-W1
3:  X2,D2, W2, V2=V1*((1+X1%/100)^((D2-D1)/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.