calculate compounded daily interest

[Petro]

I need to calculate compounded daily interest. Can someone give me the excel
formula? I need to calculate it on various amounts and dates i.e. Amount pd
on 01/06/07 - R1000.00 @ 14.50% then I take that total add another payment of
let's say R100.00 made on 01/07/07 @ 13.50%. Interest should therefore
firstly be worked out on R1000.00 @ 14.50 from 01/06/07 - 30/06/07 and then
on the total plus the next payment of R100.00 @ 13.50% from 01/07/07 until
the next payment date. (In other words interest on interest.)

 

[Tyro]

How would you calculate the interest manually? Take your manual calculation
and make an Excel formula. It's that easy.

Tyro

 

[joeu2004]

I need to calculate compounded daily interest.  Can someone give
me the excel formula?  I need to calculate it on various amounts and
dates i.e. Amount pd on 01/06/07 - R1000.00 @ 14.50% then I take
that total add another payment of let's say R100.00 made on
01/07/07 @ 13.50%.  Interest should therefore firstly be worked out
on R1000.00 @ 14.50 from 01/06/07 - 30/06/07 and then
on the total plus the next payment of R100.00 @ 13.50% from
01/07/07 until the next payment date. (In other words interest on
interest.)

I don't believe you can have a single formula that incorporates
variable compounded interest rates, unless you want to write a VBA
function. The following describes how to set up a "schedule".

First, I assume that the stated interest rates are annual rates. So
the daily rate is typically 14.50%/365 and 13.50%/365, for example.
If the interest rates are, instead, APYs, the daily rate can be
computed using RATE().

Second, I assume that interest compounds only when paid to the
account, as you describe, not daily.

For the simple payment schedule that you describe (payment on the
first day of the period), the following would suffice. But see my
comments below about the average daily balance.

Suppose column A is the date, column B are payments, and column C is
the annual interest rate, D is the interest earned, and E is the
balance, starting row 2. Thus:

A2: 01/06/2007
B2: 1000
C2: 14.5%
D2: =if(A3="", "", round((E1+B2)*(A3-A2)*C2/365,2))
E2: =if(A2="", "", E1+B2+D2)

Copy D2 and E2 down. Then fill in the rest of the schedule as time
goes by. For example:

A3: 01/07/2007
B3: 100

A4: 01/08/2007

Note: That structure presumes that payment, if any, is made on the
first of each month, and there are no withdrawals. Normally, US banks
(at least) use the "average daily balance" method for computing
interest, which really means that interest is prorated based on the
balance on individual dates during the month. This handles the case
when deposits and withdrawals are made at various times during the
month. The spreadsheet design would be different.

 

[joeu2004]

Errata....

I need to calculate compounded daily interest. Can someone
give me the excel formula?
[....] Interest should therefore firstly be worked out
on R1000.00 @ 14.50 from 01/06/07 - 30/06/07 and then
on the total plus the next payment of R100.00 @ 13.50% from
01/07/07 until the next payment date.

Since you use "R" before the monetary amounts (rupees? rubles?), I
don't know how much of my (US) experience applies to the financial
institutions in your location. Assuming that it is applicable....

First, I assume that the stated interest rates are annual rates.

Actually, the US Truth In Savings law requires the APY to be reported
on periodic statements. The APY is computed based on daily
compouding, whether or not paid interest is based on daily
compounding. (The US law does not dictate how paid interest should be
computed; only how the interest rate is reported.)

So, for example, if the APY is 14.5%, the daily rate is RATE(365, 0,
-1, 1+14.5%) or about 0.012102%. (Caution: I would not use the
approximate interest rate in computations, lest it introduce too much
numerical error in other calculations. Use RATE() directly or
reference the cell with the RATE() result in formulas.)

(Note: US law permits the use of 366 in leap years instead of 365.
It is in the financial institution's favor to use 366.)

Second, I assume that interest compounds only when paid to the
account, as you describe, not daily.

This is true of the US savings accounts that I looked at. So
following the above example, the amount of interest earned on a
balance (see below) would be B1*(A2-A1)*RATE(365, 0, -1, 1+14.5%),
where B1 is the balance, A1 is the date of that balance (e.g. the end
date for some previous transaction), and A2 is the date of the balance
for the next transaction (deposit, withdrawal or interest paid).

But again, US law does not regulate this. Some accounts might
compound daily.

Normally, US banks
(at least) use the "average daily balance" method for computing
interest, which really means that interest is prorated based on the
balance on individual dates during the month.

US law permits financial institutions to use either the actual daily
balance or the average daily balance. The accounts that I looked at
use the actual daily balance. In either case, interest is computed
based on the daily rate times the applicable balance on each day, be
it the actual or the average balance.

Suppose column A is the date, column B are payments, and column C
is the annual interest rate, D is the interest earned, and E is the
balance, starting row 2.

I would like to offer a slightly different schema and some new rules.
This form makes it easier to extend the paradigm to the case where you
have multiple deposits and/or withdrawals during a period. (But I
will not provide that solution here. I need my own question answered
before I can offer a general solution.)

Column A is the date of a transaction; in your case, either the
deposit at the start of the month, or the interest payment at the end
of the month. Column B are the deposits. Column C are the interest
payments (computed). Column D is the balance. Column E is the APY
corresponding to the interest payment.

Enter the interest payment on a line separate from the deposit. Enter
the APY only on the line with the interest payment.

For example:

A2: 01/06/2007
B2: 1000
C2: =if(E2="", "", round(D1*(A2-A1)*rate(365,0,-1,1+E2),2))
D2: =if(A2="", "", D1+B2+C2)
E2: (empty)

Copy C22 down the columns for as many rows as you want.

A3: 30/06/2007
B3: (empty)
E3: 14.5%

A4: 01/07/2007
B4: 100
E4: (empty)

A5: 31/07/2007
B5: (empty)
E5: 13.5%

HTH.