calculate interest

[Paul B]

Is this the right formula to calculate interest per day?
Starting amount in B3, Interest rate in C2 and number of days in D2
=B3*(1+(C2/365.25))^D2

and this for months, with months in D2
=B3*(1+(C2/12))^D2

Thanks

 

[Dave Patton]

You could try Excel's Nominal and Effect or

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

= (1 + C2) ^ (1 / 12) - 1

These functions may require installation of Analysis ToolPak;
see HELP for either of the function.

=NOMINAL(C1,12)/12

=EFFECT(E4*12,12)

 

[Ekim]

Using your cell references:

B3: $50,000.00
C2: 8%
D2: 56 (days)

1. Interest after 56 days with daily compounding:

=B3*((1+(C2/12))^12)^(D2/365)-B3
= $615.42

2. Or slightly different formula:

=B3*(((1+(C2/12))^12)^(D2/365)-1)

= $615.42 (formatted to 2 decimal places)

Cross-check:

You can cross-check the interest result by using the standard compound interest formula:

=PV*(1+r)^n - PV

where:
PV = initial investment
r = Interest rate
n = period

But, the above depends on how you define "daily rate".

1. APR(12) or Annual Nominal compounded monthly:

=((1+(C2/12))^(12/365)-1)
= 0.00021847 (daily rate)

Using that daily rate in the compound interest formula:
=B3*(1+0.00021847)^$D$2-$B$3
=$615.42 (same as above)

2. APR(365) or Annual Nominal compounded daily

= C2/365
= 0.00021918 (daily rate)

Using that daily rate in the compound interest formula:
=B3*(1+0.00021918)^$D$2-$B$3
= $617.41

3. Annual effective rate:

=(1+0.08)^(1/365)-1
= 0.00021087

or :
use Excel's Nominal function:
=NOMINAL(C2,365)/365

or:
=EXP((LN(1+C2)/365))-1

Using that daily rate in the compound interest formula:
=B3*(1+0.00021087)^$D$2-$B$3
= $593.89
(Banks love this one because the interest payable is less)

HTH

Mike

 

[Paul B]

Mike and Dave, Thanks

 

[RalphA]

Using your cell contents, namely, "starting amount in B3, interest rate in C2 and number of days in D2", you are using:
=B3*(1+(C2/365.25))^D2

The above formula assumes you are using the yearly rate of interest expressed in percent.

The formula I would use is:

=B3*(1+C2/365)^D2

because the financial institutions are very exacting in all their calculations, and 365 is the number of days in the year to use, at least on non-leap-year years.

I use, instead of D2, the actual dates. For instance, if rhe starting period is in E1 and the ending period is in E2, I use, instead of your D2, (E2-E1). That is, I let Excel do the calculation of days. And, for an every day, automatic calculation for up-to-the-present-day calculation, I use (NOW()-E1). Makes things a lot easier!

I haven't checked out this next statement, but, I would think the banks and such would use 366 for the number of days for a leap year.

You could actually let Excel supply the right number of days by using, instead of 365 or 366, (E4-E3), where E4 contains the date with the last day of the year, and E3 the date of the first day of the year, say, E3 would have 01/01/2003, and E4 would have 12/31/2003.

 

[Paul B]

Ralph, thanks I like the part about "automatic calculation for up-to-the-present-day calculation"