How to calculate Compound Interest in Excel?

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.

This 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
• 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:

• Yearly compounding; n=1
• Quarterly compounding; n=4
• Monthly compounding; n=12
• Daily compounding; n=365

Yearly Compounding

Let 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.
​

Yearly Compounding

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) 
• Total interest accumulated = Amount – Principal
• $ 16,288.90 - $ 10,000 = $ 6,288.90

​

Quarterly Compounding

The 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.

Quarterly Compounding

The Excel formula becomes:

• Amount = Round (10,000 * (1 + 0.05/4) ^ (4*10), 1)
  

• Output= $ 16,436.20
  

Compound interest accumulated = $ 6,436.20

Monthly Compounding
​

Monthly Compounding

The Excel formula becomes:

• Amount = Round (10,000 * (1 + 0.05/12) ^ (12*10), 1)
• Output= $ 16,470.10

Compound interest accumulated = $ 6,470.10
​

Daily Compounding

Daily Compounding

The Excel formula becomes:

• Amount = Round (10,000 * (1 + 0.05/365) ^ (365*10), 1)
• Output= $ 16,486.60

Compound interest accumulated = $ 6,486.60
​

The Comparison

The comparison of different compounding periods

Conclusion

The 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.​

compound_interest_template.xlsx
File Size:	28 kb
File Type:	xlsx

Download File

---