5

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
  • It's curious that there's no inbuilt function for this Jul 11, 2014 at 12:26
  • @nicodemus13 There is: NOMINAL(). See chris neilsen's answer.
    – Phil Frost
    Jan 21, 2016 at 12:59

5 Answers 5

8

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 "A1*POWER((1+B1),C1)-A1" gives the total value owed resulting after borrowing $A1 at B1% per period for C1 periods. It doesn't calculate the interest rate. The one you've got above to "find your daily rate" looks better, but it's not in Excel format yet. Nov 2, 2011 at 0:48
  • Converting your formula "r = ((I + P)/P)^(1/n)-1" to Excel format, and removing the principle (which isn't needed in the calculation), I get "=POWER((1+B1),(1/C1))-1" where B1 is the pa interest rate and C1 is the number of times per year to compound the interest. I get the same answer as you: 0.0261158% for compounding daily to be 10%pa. If you can reword your answer to provide this more clearly, I'll accept it! Thanks Nov 2, 2011 at 0:59
  • Sorry about that. Originally, I thought you were asking how to calculate compound interest and then edited the answer when I realized you wanted to get the equivalent interest rate. Of course, I forgot to rewrite the excel formula.
    – Chris Ting
    Nov 2, 2011 at 1:30
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
  • 3
    That's not what I'm after though, as "NOMINAL(10%,365)" gives the result 0.0953226 which doesn't have much meaning to me at all. However, if I divide that by 365, I get the answer I want (0.0261158%) so the formula "=NOMINAL(0.1,365)/365" works. Personally, I don't see this as being any more elegant than "=POWER((1+0.1),(1/365))-1" because neither are particularly intuitive, but it might be a little easier to remember. Nov 2, 2011 at 21:06
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
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
  • Thanks, but that's the reverse of what I'm after! I'm trying to obtain a daily and weekly rate starting from a per-annum rate. Nov 2, 2011 at 0:41
0

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
  • 1
    Thanks @BobNorris, but this isn't what the question is asking for, so I can't give it an upvote. Jun 29, 2012 at 22:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .