1

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.

  • Why divide the datedif by 365 then multiply it by 365? – Hart CO May 3 at 23:06
  • That is the standard compound interest formula of A = P(1 + R/n) ^ (nt). Specifically since the APR is a yearly rate and we are doing a daily compounding, divide and then multiply is rendundant and it could be simplified. – BegalRegal2 May 4 at 11:32
  • Looking at it again this portion ^ (365 * DATEDIF(B9,B10,"D") / 365) seems to be the ^(nt) portion, but is equivalent to just ^ DATEDIF(B9,B10,"D"). Wouldn't cause any problems but I just wondered if there was a reason for it, like cleaning up a quirk in Excel or something. – Hart CO May 4 at 13:48
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.

enter image description here

  • Your solution is the basic form and probably what I should have started out with and then branch out to encompass the non regular daily transactions so I'll accept your answer. Thank you! – BegalRegal2 May 4 at 11:38
-1

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

  • If this were the flaw, I'd expect that actual interest payments would be lower than calculated, but actual are higher than calculated. – Hart CO May 3 at 22:07
  • @HartCO: Hmm, you're right, at least for two of the three examples. I looked most at the last with 16.55 < 17.37. I wonder if there's some kind of 360-vs-365 effect going on too. – Henning Makholm May 3 at 22:09
  • Another possibility is that the interest that is paid on the last business day of the month actually includes interest that will accrue until the 1st of the next month. – Henning Makholm May 3 at 22:18
  • 1
    Can you give a definition of what you think "compounded" means and a cite? – Acccumulation May 3 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.