Intrest calculations in eXCELL

[Guest]

I want to calculate acuring interest in excell from a given calander date, The daily dollar amount is known,

 

[Norman Harker]

Hi!

You need to use the daily effective interest rate.

If you have a base of an APR12 (Nominal compounded monthly) use:

=(1+APR/12)^(12/365)-1

If you have a base of an annual effective use:

=(1+AnnEff)^(1/365)-1

Assuming dates in column A1 down, Flow in column B down, Interest in
column C down and Balance in column D down:

C1: 0
D1:
=B1
C2:
=D1*((1+DailyEff)^(A2-$A$1)-1)
D2:
=D1+B2+C2

Now select C2 and D2
Copy down parallel to the flows in column B

 

[Guest]

Hi!

You need to use the daily effective interest rate.

If you have a base of an APR12 (Nominal compounded monthly) use:

=(1+APR/12)^(12/365)-1

If you have a base of an annual effective use:

=(1+AnnEff)^(1/365)-1

Assuming dates in column A1 down, Flow in column B down, Interest in
column C down and Balance in column D down:

C1: 0
D1:
=B1
C2:
=D1*((1+DailyEff)^(A2-$A$1)-1)
D2:
=D1+B2+C2

Now select C2 and D2
Copy down parallel to the flows in column B

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)


Hello Thank you for the reply. Would it be possible to send me an example of this. My e-mail addy is (e-mail address removed)

 

[Norman Harker]

Hi Rus!

Have done.