Excel table to compute interest on savings account that is compounded daily but paid monthly?

Question

I have an excel sheet that represents a real world savings account which interest is compounded daily but paid out monthly. It looks like:

Example Table

For clarity, I will call each row in the "Interest Calculating Table" a transaction. The basic information (date, amount, source) for each transactions is dynamically added to the sheet in a sorted way. As you can see the sheet is responsible for keeping track of the starting and ending balance in the account after each transaction.

The sheet also has to act as an interest bearing savings account in which interest is accrued daily but paid monthly. To do this, for each transaction, it calculates the daily compounded interest that was accumulated between the previous transaction and current transaction. It adds this value to any previously calculated interests to create a monthly rolling sum of daily interest payments (the accrued interest column). Upon seeing a transaction that contains the source of "Interest Paid", the rolling count of gained interest is dumped into the the savings account balance and thus the requirement for "interest calculated daily and paid monthly" is realized.

The formula for the interest column to calculate the compounded daily interest calculations in between the previous transactions and the current transaction is: Previous Ending Balance * (1 + APR/365) ^ ((365 * Days in between previous transaction and current transaction) / 365))) - Previous Ending Balance.

Using that base formula, a set of if then statements in the interest cell checks if the interest that was gained between the last transaction and the current transaction starts a new rolling interest count (interest starts accruing from 0 at the beginning of the month) or appends to the existing rolling interest count.

For completeness sake, I have included the formula below as well as a description of the cases.

Example Formula

1.) If the current transaction is an "interest paid transaction" and the previous transaction is an "interest paid transaction". In this case, the interest that was gained between the last transaction and the current transaction is apart of a new rolling interest count.

2.) If the current transaction is an "interest paid transaction" and the previous transaction is not an "interest paid transaction". In this case, the interest that was gained between the last transaction and the current transaction is apart of a the current rolling interest count.

3.) If the current transaction is not an "interest paid transaction" and the previous transaction is an "interest paid transaction". In this case, the interest that was gained between the last transaction and the current transaction is apart of a new rolling interest count.

4.) If the current transaction is not an "interest paid transaction" and the previous transaction is not an "interest paid transaction". In this case, the interest that was gained between the last transaction and the current transaction is apart of a the current rolling interest count.

My issue is that I am encountering an offset issue in my interest calculations from what the bank is reporting.

• On 2/28/2019, the bank reported an interest paid of $2.62 where I got $2.26.

• On 3/29/2019, the bank reported an interest paid of $13.38 where I got $12.30.

• On 4/30/2019, the bank reported an interest paid of $16.55 where I got $17.37.

I do know that there will be a small variation due to banking rounding errors, however they should not be as far off as I'm getting. I'm confident my daily interest accrual equation is wrong, but I'm not sure which one to use.

Note: Interest rate and APY is shown in the table.

Answer 1

I can't tell what mistakes you've made in your sheet, but I re-created each month in Excel and match the bank's results. I simplified by just making a list of dates in the month, and calculating interest daily whether or not there were transactions.

One thing to note is that interest is for the calendar month, so interest paid on 3/29 (friday) includes interest for 3/30 and 3/31, they are pre-paying those days. That explains why April is the only month your calculation is over actual, because you're including 2 extra days worth of interest.

Perhaps in February the multiple transactions on some days are throwing things off, I just combined them before calculating interest to simplify.

One thing I would definitely suggest is to move any redundant sections of code to a separate field that you can then reference, for example (1 + $C$4 / 365)) could be moved to $D$4 to help clean things up.

Answer 2

"Compounded daily but paid out monthly" means the same as "compounded monthly" for money that has been in the account for the entire month. It just means that new deposits draw interest the first month in proportion to how many days they've been there, and similarly for withdrawals during the month.

Your computations are of what the balance would have been after a month if the interest had been paid daily (and the balance kept in fractional cents to make this possible) -- but's that not what "compounded daily" means.

In other words, even though interest is computed every day, interest earned early in the month does not begin to draw interest of its own until it is actually paid, which is only monthly.

As far as long-term savings go, this first-month pro-rating has negligible effect, and you can treat the account as simply "compounded monthly".