Calculation of Compounded Interest on a debt

[David Brereton]

HELP please. I have looked through all possible formulae but cannot find one
to suit.
I am trying to find an easy / short way to calculate interest on a debt ,
but compounded on a daily basis.
The info I will have will be :-
1. Amount of debt
2. Start date of debt
3. Date to which interest is to be calculated to
4. Interest rate ( fixed )

How can I create a formulae to calculate this in one swoop ?

 

[Gary''s Student]

With the start date in A1 and the end date in A2 and the amount in A3 and the
APR in A4. In another cell (say Z100):

=((1+A4/365)^(A2-A1)-1)*A3

is the total due, so the total interest is:

Z100-A3

 

[Gary''s Student]

Sory... the formula in Z100 is the total interest

 

[joeu2004]

I am trying to find an easy / short way to calculate interest on a debt ,
but compounded on a daily basis.
The info I will have will be :-
1. Amount of debt
2. Start date of debt
3. Date to which interest is to be calculated to
4. Interest rate ( fixed )

If those values are in A1, A2, A3 and A4 respectively, then:

=fv(A4/365, A3-A2, 0, -A1) - A1

Note: This asumes that A4 is entered in fractional form (less than 1)
or in percentage form. For example, 0.0123 or 1.23%, not 1.23.

 

[David Brereton]

Thank you very much !!

With the start date in A1 and the end date in A2 and the amount in A3 and the
APR in A4. In another cell (say Z100):

=((1+A4/365)^(A2-A1)-1)*A3

is the total due, so the total interest is:

Z100-A3

 

[David Brereton]

Thank you.
If I made it more complicated by adding another factor ...

The daily interest amount would vary from another worksheet that details a
date in column A and the rate in column B.
Is it possible to get the formula to see the interest rate applicable for
each day , before it calculates and compounds the interest.
Hope you get that ?

 

[joeu2004]

If I made it more complicated by adding another factor ...
The daily interest amount would vary from another worksheet
that details a date in column A and the rate in column B.
Is it possible to get the formula to see the interest rate applicable
for each day, before it calculates and compounds the interest.

I would create an amortization schedule in that case. IMHO, it is not
practical to write a single formula that incorporates the variable
rate. I am not sure it is even feasible to do, at least not without
the use of a UDF (VBA function).

The amortization schedule can be designed to find the applicable
interest rate based on the payment date. Ostensibly, you might use
VLOOKUP; but beware of real-world complications, for example if the
interest changes on the payment date.


----- original posting -----