On Jan 10, 1:29*am, Petro wrote:
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.