Daily Interest Monthly Compounding Multiyear

Question

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.

Answer 1

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.

Answer 2

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.