I need to calculate compounded daily interest on an amount owed by a debtor.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Amount 4324.13 @23.99% from 10/22/03 to 8/23/05

We are assuming 23.99% simple interest, calculated and compounded daily
(ooh, that's nasty! )

There may be simpler ways to do this, but this way should get you your
answer:

Beginning in F1 enter the start date (i.e. 10/22/03) and auto fill that
down until you reach 8/23/05 (F672). In G1 enter the beginning value of
the loan ($4324.13). In G2, enter this formula:

=G1+(G1*(0.2399/365)) and copy down to the end of the range (double
click on the drag handle)

For more data, you could see the total amount of interest added to date
by using this in H2:

=G2-G1

and this in H3, copied down:

=G2-G1+H2

Your total will be in G672 (you can continue down with the formulas if
the bugger hasn't paid up yet).

Good Luck!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=398353

Put the dates in cells A1 & A2, oldest date first
Put the Amount in A3 and the Rate in A4

in B1 use this formula

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

That will tell you what the total interest rate is.

In B2 use the formula

=B1*A3

That's the total interest owed



"Calculation limited" wrote:

I need to calculate compounded daily interest on an amount owed by a debtor.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Amount 4324.13 @23.99% from 10/22/03 to 8/23/05

Calculation limited wrote:
I need to calculate compounded daily interest on an amount owed by a debtor.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Amount 4324.13 @23.99% from 10/22/03 to 8/23/05

Did you advertise (or stipulate) that 23.99% is the "APR"?

If so, then I believe that using 23.99%/365 (0.065726%) for
the compounded daily interest rate is incorrect. That would
result in an APR of 27.10%. I believe the correct compounded
daily interest rate is 0.058930%.

(Note: That is a personal opinion, not a legal one. I do
not know what is common practice or regulation among lendors.)

Given an APR of 23.99%, this is how I would compute the
compounded daily interest rate:

RATE(365,,-1,1.2399) [A1]

The number of days (671) can be computed as:

DATE(2005,8,23)-DATE(2003,10,22) [A2]

Do you truly want to compute the "compounded daily interest"
-- the amount of interest each day(!)? Or do you really want
to compute something else -- perhaps the total interest
accumulated between 10/22/03 and 8/23/05, assuming no payments
and a compounded daily interest rate?

The latter ($2096.49) can be computed as follows:

FV(A1,A2,,-4324.13)-4324.13

I hope that helps. As noted, it is not really clear (to me)
what you are trying to compute.

Thanks so much. I am saved.

"Duke Carey" wrote:

Put the dates in cells A1 & A2, oldest date first
Put the Amount in A3 and the Rate in A4

in B1 use this formula

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

That will tell you what the total interest rate is.

In B2 use the formula

=B1*A3

That's the total interest owed



"Calculation limited" wrote:

I need to calculate compounded daily interest on an amount owed by a debtor.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Amount 4324.13 @23.99% from 10/22/03 to 8/23/05

Thanks for your help. I am saved.

" wrote:

Calculation limited wrote:
I need to calculate compounded daily interest on an amount owed by a debtor.
Can someone give me a excel formula. I am not a math please layman terms. I
know the principal of how it works but don't want to spend a day doing it by
calculator. Amount 4324.13 @23.99% from 10/22/03 to 8/23/05

Did you advertise (or stipulate) that 23.99% is the "APR"?

If so, then I believe that using 23.99%/365 (0.065726%) for
the compounded daily interest rate is incorrect. That would
result in an APR of 27.10%. I believe the correct compounded
daily interest rate is 0.058930%.

(Note: That is a personal opinion, not a legal one. I do
not know what is common practice or regulation among lendors.)

Given an APR of 23.99%, this is how I would compute the
compounded daily interest rate:

RATE(365,,-1,1.2399) [A1]

The number of days (671) can be computed as:

DATE(2005,8,23)-DATE(2003,10,22) [A2]

Do you truly want to compute the "compounded daily interest"
-- the amount of interest each day(!)? Or do you really want
to compute something else -- perhaps the total interest
accumulated between 10/22/03 and 8/23/05, assuming no payments
and a compounded daily interest rate?

The latter ($2096.49) can be computed as follows:

FV(A1,A2,,-4324.13)-4324.13

I hope that helps. As noted, it is not really clear (to me)
what you are trying to compute.