MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure

How to calculate Compound Interest in Excel?

24/5/2019

0 Comments

 
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:
Compound Interest

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:
Compound Interest 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.
​

Maturity Value
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
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
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
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

Different Compounding periods
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

0 Comments



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    May 2019
    April 2019
    March 2019
    February 2019


    Categories

    All
    Grow Your Business


    RSS Feed

Copyright Mend My Ladder @ 2021
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure