Take the 2-minute tour ×
Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 11 down vote accepted

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.

Here's a link to a sample Excel spreadsheet (*.xlsx) I created to demonstrate the above.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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