4

I downloaded the Excel spreadsheet mentioned in the accepted answer to this question: Daily interest calculation combined with monthly compounding: Why do banks do this, and how-to in Excel?

It was helpful. However, I noticed that each month in the spreadsheet only has 30 days, instead of alternating 30, 31, 30, 31 days etc.

Is there a way to do this? Otherwise the spreadsheet is pretty useless as it the dates are all off.

Thanks for your help.

|improve this question
  • 2
    That spreadsheet is completely accurate for a loan/account with a 30/360 daycount convention, which is very common in banking. – D Stanley Jan 19 '18 at 14:12
2

Here is a short explanation and example of daily interest and monthly compounding that will hopefully be helpful to you.

Suppose a bank's interest rate is 5% nominal compounded monthly. The measure "monthly compounding" is the key to calculating the effective annual rate, from which can be calculated the daily rate.

See https://en.wikipedia.org/wiki/Effective_interest_rate#Calculation

nominal rate   = i = 0.05
effective rate = r = (1 + i/12)^12 - 1 = 0.0511619
daily rate     = d = (1 + r)^(1/365) - 1 = 0.000136711

Then suppose three deposits are made

                     days from 12-Aug-14
12-Aug-14    1000           0
18-Mar-15    2000         218
10-Jun-15    2500         302

What is the balance x on 22-Oct-16?

22-Oct-16 is 802 days after 12-Aug-14.

Using the number of days and the daily rate, the balance is found by solving this equation.

1000/(1 + d)^0 + 2000/(1 + d)^218 + 2500/(1 + d)^302 - x/(1 + d)^802 = 0

∴ 5340.17 = x/(1 + d)^802

∴ x = 5958.94

Reciprocally, Excel's XIRR function will calculate the effective annual rate from the cashflows. The nominal monthly compounded rate can then be calculated from the effective annual rate.

enter image description here

|improve this answer
1

It was helpful. However, I noticed that each month in the spreadsheet only has 30 days, instead of alternating 30, 31, 30, 31 days etc.
Is there a way to do this?

Add one more one more column to the spread sheet. Instead of day column, fill this with dates you need. i.e.1st jan, 2nd Jan etc. In another column, find the number of "days in the month". This can be done by =DAY(EOMONTH(date column,0). In the Month's accrued interest, instead of fixed "30"; look this up against the new "days in the month" column.

|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .