Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
It's curious that there's no inbuilt function for this –  nicodemus13 Jul 11 at 12:26

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
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 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.

share|improve this answer
    
+1. simpler to use the inbuilt functions. –  brettdj Nov 2 '11 at 12:37
1  
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

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)

share|improve this answer
    
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

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.

share|improve this answer
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.