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?
Answer
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
No comments:
Post a Comment