# Daily interest calculation combined with monthly compounding: Why do banks do this, and how-to in Excel?

I often see banks advertise (for accounts with fixed yearly interest, let's say 1.5%) – "interested is calculated daily, and compounded monthly".

I have 2 questions:

1. Why are they calculating it daily?? If the compounding period is monthly, what is the point of these daily calculations??! They could just calculate it once at the end of the month, right?

2. Can somebody provide me with an Excel formula to calculate future investment value for this type of scenario, where the interest calculation and compound periods differ? Perhaps playing with numbers in Excel will help me understand better.

Thank you.

First, calculating interest on your bank account daily makes the most sense because your balance in a bank account typically fluctuates throughout the month: that is, you make deposits, and you make withdrawals.

If the bank calculated interest only at the end of the month, say, based on your balance at that point in time, then it might not be fair to either you or the bank. Depending on whether your end-of-month balance was higher than average, or lower than average, either you or the bank would come out ahead. So, by calculating interest daily the bank is, in effect, arriving at an amount of interest on some form of average balance, which is more fair to both of you.

However, even though interest may be calculated daily, it is typically only credited to your account once per month. Imagine the mess it would make on your statement if it were credited daily!

Regarding calculating interest in Excel, have a look at the EFFECT() function. See also How to calculate compound interest for an intra-year period in Excel. For instance, if the nominal annual interest rate were 5% and you wanted to know what the effective annual interest rate is with monthly compounding, you would write `=EFFECT(0.05,12)`, which would yield `0.051161898`, or ~5.116%.

A longer form in lieu of the Excel `EFFECT()` function is what you'll find explained at Wikipedia - Credit card interest - Calculation of interest rates, i.e. the `EAR = (1 + APR/n)^n -1` formula. Or, in Excel, `=POWER(1+0.05/12,12)-1` to match the example above. Also yields `0.051161898`.

However, each of the methods above to compute the effective annual interest rate is only appropriate if you want to know the future value some years hence but without any inflows or outflows. Once you have a situation where you are making deposits or withdrawals, you'll want to create a spreadsheet that calculates the daily interest and adds it to the ongoing balance on a monthly frequency.

To arrive at the actual amount of interest you would need to accrue for a single day, you would divide the original interest rate by 360 or 365. (Bank rules on this may vary – I'm not exactly sure.) So, the daily interest on a balance of, say, \$1000 would be `=1000*0.05/365`, yielding `0.13698630` or 14 cents if rounded up to the nearest penny. Of course, you need to know the rounding rules. Perhaps rounding is done on each day's resulting interest (before summing), or on the sum of the month's resulting interest. Plus, bankers can round different than you might expect. Again, I'm not exactly sure on this.

In constructing a spreadsheet to calculate interest this way, you should not be adding the daily interest to the ongoing balance directly, but rather accrue the interest in a separate spot off to the side somewhere until the end of the month. At which point, sum up all of the daily interest earned and add it to the ongoing balance. Consider: If you were to credit the ongoing balance each day with that day's interest, then you would, in effect, be performing daily compounding instead. By adding the interest to the ongoing balance only once per month, the compounding is in effect monthly, even though the interest is calculated on the daily balance.

• This is very useful indeed! I wonder though, if there are going to be irregular deposits and withdrawals being made throughout the year, is there a single formula which we can apply by the end of each month, to work out the interest for that month, without having to list out all the days like you did in the spreadsheet? – user7711 Dec 4 '12 at 15:39
• @Koo I don't think there could be a single, simple formula that could deal with irregular deposits and withdrawals. The point of daily compounding is to calculate the interest according to how much was in the account on any given day, i.e. calculate correct interest despite there being irregular deposits and withdrawals. Any simpler formula would need to make assumptions about the frequency and/or amount of the deposits and withdrawals (but they're irregular!) or else the average daily balance, and thus be incorrect in the general case. – Chris W. Rea Dec 4 '12 at 16:07

When its mentioned interest is calculated daily, it means that the balances of every day are considered. The actual calculation in most of the cases is at month-end [or the compounding happens].

Certain Banks, that run accurals may calculate daily for other accounts, however it is not a norm for doing this on savings account.

Here is the formulat (interest is calculated daily and compounded monthly )

## I= P(1+r/12)^n * (1+(r/360*d))-P

I: amount of interest P: principal r: annual interest rate n: number of months d: number of days

example: \$1,500 deposited on April 1, fully withdrawn on June 15. the applicable interest rate is 6%. Earned interest is calculated as follows:

\$1,500(1+.06/12)^2 * (1+(0.06/360*15))-\$1,500 = \$18.83

The formula you are looking for is :

Final amount = Principal * e ^(r*t)

Where

e- base of natural logarithms

r- annual rate of interest

t-time in years

So if your bank is paying out an annual rate of interest of 1%, compounded inifinitely over a period of one year, you could expect to have e^0.01 = 1.01005 times your original principal in your bank account at the end of the year.