Calculating Interest on accounts - more sophisticated/easy?

Linton

The calculation is straightforward. The problem is how to program it as a simple spreadsheet only entering data when something changes. You have not explained how you would do that do that. For example does your method require a month end re-initilisation?


Are you assuming that the interest is compounded at the end of each calendar month? Is that the normal case or is it based on the day the account was opened? Also AIUI the OP was wanting to handle changes in interest rate at any time.

zagfles

Linton said:

The calculation is straightforward. The problem is how to program it as a simple spreadsheet only entering data when something changes. You have not explained how you would do that do that. For example does your method require a month end re-initilisation?


Are you assuming that the interest is compounded at the end of each calendar month? Is that the normal case or is it based on the day the account was opened? Also AIUI the OP was wanting to handle changes in interest rate at any time.

Interest compounds when it's paid. I've never known anything different, and I've been manually calculating interest on accounts for decades and almost always get it correct to the penny.

It's easy enough to handle interest rate changes. Just split the calculation into two (or more) sections whenever the interest rate changes. Eg in my example if interest changed from 5% to 4% on 1 July, then you need to work out days*balance up to 30 June first, multiply by rate/365 then do the the same for 1 July to 31 Dec.

So instead of 1000*104 + 500*32 + 3500*154+2500*75 = 846500 then multiplying by rate/365

you do up to 30 June first, ie 1000*104 + 500*32 + 3500*45 = 277500 then multiply by rate 0.05 and divide by 365 = 38.01

Then 1 July to 31 Dec: 3500*109 + 2500*75 = 569000 then multiply by rate 0.04/365 = 62.36

And add up the 2 parts, total interest = £100.37

You could leave the divide by 365 till after adding to two parts of course, as it's obviously the same mathematically.

Doing it in a simple spreadsheet is quite easy.

3 columns, date, balance, interest accrued. Interest accrued = date minus previous date (ie number of days) times rate divided by 365. Then add a row every time the balance changes, with the date and new balance. 

Add start/end dates as the date after the interest is paid. So 1/1/23 and 1/1/24 in the example for calculating 2023 interest. And add the interest to the balance when it's paid (interest seems to be treated differently to normal deposits as it only starts earning interest the day after it's paid, so pretending it's added on the day after the payment date seems to get the correct result). So on 1 Jan the formula will need to sum interest accrued since 1 Jan previous year. Will work for monthly too but more fiddly as you need the interest payment date every month (or rather the day after).

If interest rate changes during the term may need another column for rate, and add a row at the rate change date with the same balance but different rate.

There's probably more sophisticated ways of doing it so you can have consistent forumale which don't need manually tweaking on interest payment dates. But to do a simple "how much interest should I earn in 2023 from this account" it's straightforwards.