Excel formula to convert per-annum interest rate to compounding daily and weekly rates

If I borrow $100,000 at an annual interest rate of 10%, then I would have been charged $10,000 at the end of one year.

However, I want the interest to be calculated daily and compound. If I simply take the interest rate divided by 365 (which is around 0.0274%) and apply that each day, I end up with a total of $10,515.58 of interest charged at the end of the period.

What is the Excel formula I can use to apply compounding daily interest and end up with $10,000 charged at the end of 365 days?

Similarly, what is the Excel formula for calculating a compounding weekly interest rate that I can use to apply weekly interest and end up with $10,000 charged at the end of 52 weeks?

2

5 Answers

The compound interest formula is:

I = P(1 + r)^n - P

I is interest
P is principal
r is rate
n is the number of interest periods incurred

Your original equation turned into: 10000 = 100000(1 + .1)^1 - 100000

To find your daily rate after a year where your principle is 100,000 and your interest is 10,000 use

r = ((I + P)/P)^(1/n)-1

((10000 + 100000)/100000)^(1/365)-1 gives you a daily rate of 0.0261158%
Similarly, the weekly rate is 0.1834569%

To find your rate using the annual interest rate (represented by i):

r = (1+i)^(1/n)-1

(1+.1)^(1/365)-1 gives you a daily rate of 0.0261158%
Similarly, the weekly rate is 0.1834569%

The excel equation to calculate your compound interest rate based on the annual rate is:

=POWER((1+A1),(1/B1))-1

Where:
A1 is your annual rate
B1 is the number of interest periods

3

The formula you want is

=NOMINAL(10%,365)

or

=NOMINAL(10%,52)

for daily or weekly interest

Form Excel help: Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.

1

There's no need to use the POWER() formula. One can simply input the equation normally, e.g.

=(1+0.1)^(1/365) equals 1.000261158

0

The compound interest formula is PV*(1+R)^N

PV = current value
R = interest rate
N = periods

So 10,000 after a year at a weekly interest of 5% would be

=A1 * POWER((1 + .05),52)

1

I'm no expert on Excel but I found the exercise interesting.

The formula below is for calculating interest which is compounded daily. I placed the formula in cell A1. In cell B1 I placed the "Present Value". In cell C1 is the annual interest rate expressed as a fraction, ie, in the above example 0.1. In cell D1 I placed the value of "n" which is the number of days the interest is compounded.

So the formula is =B1*((1+(C1/365))^D1)-B1

Of course, you will not end up with $10,000 of interest charged but $10,515.58 as you have quite rightly stated.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy