The compound interest is the interest on the principal amount which is added back to the principal to calculate the interest for the next period. The process of adding the interest of the previous period to the principal continues till the maturity. See the screen shot:You can see the in above screen shot that when $ 10,000 are invested at the rate of 5% per year, the first-year interest is $ 500 and this interest is added back to the principal $ 10,000 (it becomes $ 10,000 + $ 500 = $ 15,000) to calculate the interest for the 2nd year which is 5% of $ 15,000 = $ 525. This process is repeated till the maturity period of 10 years. The compound interest formula in Excel is generally used to calculate the future value of the investment with the given time-period and the rate of investment. :The Formula- A = Maturity value at the end of the period
- r = Annual rate of interest (in decimal)
- t = Number of years of investment
- n = Number of compounding periods
The value of n in the different types of compounding:- Yearly compounding; n=1
- Quarterly compounding; n=4
- Monthly compounding; n=12
- Daily compounding; n= 365
## Yearly CompoundingLet us understand with the help of an example:Let’s say I deposit $ 10,000 (one-time investment) for 10 years at the annual rate of 5% with yearly compounding. What should be the total interest and the maturity value of my investment?Solution: To calculate the Maturity value in Excel, use the following formula**Amount = 10,000 * (1 + 0.05/1) ^ (1*10)**
(Please note that ^ means “to the power of” in the excel)So, my investment will become $ 16,288.90 at the end of the 10th year. Many a times, this formula will give you the output with many digits after the decimal. Just use the Round function to limit the digits to 1 or 2 after the decimal. The new formula becomes:**Amount = Round (10,000 * (1 + 0.05/1) ^ (1*10), 1)**
= $ 16,288.90 - $ 10,000 = $ 6,288.90 ## Quarterly CompoundingThe maturity amount varies with the change in the number of compounding period. I can say that the maturity amount increases with the increase in the number of compounding periods. In the above example, if I change the nature of compounding from yearly to quarterly (4 compounding periods in a year), my maturity amount increases to $ 16,436.20 The Excel formula becomes:Amount = Round (10,000 * (1 + 0.05/4) ^ (4*10), 1) = $ 16,436.20 Compound interest accumulated = $ 6,436.20 ## Monthly Compounding The Excel formula becomes:Amount = Round (10,000 * (1 + 0.05/12) ^ (12*10), 1) = $ 16,470.10 Compound interest accumulated = $ 6,470.10 ## Daily Compounding The Excel formula becomes:Amount = Round (10,000 * (1 + 0.05/365) ^ (365*10), 1) = $ 16,486.60 Compound interest accumulated = $ 6,486.60 ## The comparison## ConclusionThe above calculations are based when you do one-time investment in any instrument for the specific period of time and at the constant rate of interest. Next time when you visit the bank and its representative offers you fixed deposit scheme, you simply ask the number of compounding periods in a year. The more are the compounding periods, the better are your returns. I am attaching the Compound interest calculator which you can download and can check yourself.
2 Comments
Charu Singh
19/11/2018 04:59:36 pm
Useful Information.
Reply
## Leave a Reply. |
## Archives## Categories |