# 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?

-
It's curious that there's no inbuilt function for this –  nicodemus13 Jul 11 at 12:26

## 4 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

-
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. –  Highly Irregular Nov 2 '11 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 –  Highly Irregular Nov 2 '11 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 '11 at 1:30

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)

-
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. –  Highly Irregular Nov 2 '11 at 0:41

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. simpler to use the inbuilt functions. –  brettdj Nov 2 '11 at 12:37
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. –  Highly Irregular Nov 2 '11 at 21:06

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.

-
Thanks @BobNorris, but this isn't what the question is asking for, so I can't give it an upvote. –  Highly Irregular Jun 29 '12 at 22:20