Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '14 at 12:26
    
@nicodemus13 There is: NOMINAL(). See chris neilsen's answer. – Phil Frost Jan 21 at 12:59
up vote 5 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

You must log in to answer this question.

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