“Compound interest is the eighth wonder of the world. He who understandsit,earns it … he who doesn’t … pays it”. – Albert Einstein
This Tutorial Covers:
What is Compound Interest?
Let me take a simple example to explain it.
Suppose you invest USD 1000 in a bank account that promisesto give you 10% return at the end of the year.
So at the end of year 1, you get USD 1100 (1000+100).
Now since you didn’t have any immediate use of the money, you let it stay in the account. And the bank did its part and added 10% at the end of the year.
Since now you had USD 1100 in the account, thebank pays you 10% interest on 1100 (which includes the USD 1000 you invested at the beginning and the USD 100 interest you earned at the end of the first year). So you end up with USD 1210.
The benefit of compounding is that even your interest would earn interest.
What is the difference between Simple Interest and Compound Interest?
Simple Interest simply calculates the interest amount based on the initial investment, total number of years, and the rate of interest, For example, if you invest USD 1000 for 20 years at 10% rate, you will get USD 3000 a the end of 20 years (that is USD 100o of your initial investment and 2000 of the simple interest).
Compound Interest, on the other hand, calculates interest on the interest amount as well. So if you invest USD 1000 for 20 years at 10% rate, the first year your investment grows to USD 1100. In the second year, your investment grows to USD 1210 (this happens as in the second year, you earn interest on 1100 and not 1000). At the end of 20 years, compound interest will make your investment grow to USD 6727.5.
As you can note, the investment with compound interest grew twice as compared with the one with simple interest.
‘Simple interest is calculated on the principal, or original, amount of a loan. Compound interest is calculated on the principal amount and also on the accumulated interest of previous periods, and can thus be regarded as “interest on interest.’ (Source: Investopedia).
Calculating Compound Interest in Excel
Let’s see how investment grows year-on-year when calculating compound interest is Excel.
Suppose you invest USD 1000 at a 10% interest rate.
By the end of Year 1, your investment grows to USD 1100.
Now in the second year, the interest is paid on USD 1100. So the investment grows to 1210.
At the end of five years, the investment grows to 1610.51.
The formula for compound interest at the end of five years is: =B1 * 1.1 * 1.1 * 1.1 * 1.1 * 1.1
Or =B1*(1.1)^5
So here is the formula for calculating the value of your investment when compound interest in used:
Future Value of Investment = P*(1+ R/N)^(T*N)
- P – This is the principal amount or the initial investment.
- R – the annual interest rate. Note that the rate needs to be in percentage in Excel. For example, when the compound interest is 10%, use 10% or .1, or 10/100 as R.
- T – the number of years.
- N – Number of time interest is compounded in a year. In the case where the interest is compounded annually, N is taken as 1. In the case of quarterly compounding, N is 4. In the case of monthly compounding, N is 12.
Now let’s have a look at different examples of calculating compound interest in Excel.
Yearly Compounding
In the case of yearly compounding, compound interest can be calculated using the below formula:
Compound Interest = P *R^T
The future value of the investment can be calculated using the following formula:
Future Value of Investment = P*(1+R)^T
Note that you need to specify the rate as 10% or 0.1.
Quarterly Compounding
In the case of quarterly compounding, compound interest can be calculated using the below formula:
Compound Interest = P *(R/4)^(T*4)
The future value of the investment can be calculated using the following formula:
Future Value of Investment = P*(1+R/4)^(T*4)
Monthly Compounding
In the case of quarterly compounding, compound interest can be calculated using the below formula:
Compound Interest = P *(R/12)^(T*12)
The future value of the investment can be calculated using the following formula:
Future Value of Investment = P*(1+R/12)^(T*12)
Note that the as the number of period increase, the value of your future investment grows. In the examples shown above, the value in monthly compounding is highest.
Similarly, you can calculate the investment value with weekly compounding (use Ns 52) or daily compounding (use N as 365).
Using Excel FV Function to Calculate Compound Interest
Apart from the formulas shown above, you can also use the FV function to calculate compound interest in Excel.
FV is a financial function in Excel that is used to calculate the future values of the investments.
Here is the formula that will give you the future value of the investments:
=FV(R/N,R*N,,-P)
- R – the annual rate of interest.
- N – Number of time interest is compounded in a year. In the case where the interest is compounded annually, N is taken as 1. In the case of quarterly compounding, N is 4. In the case of monthly compounding, N is 12.
- P – the initial investment. Note that this is used with a negative sign as this is an outflow.
Compound Interest Calculator Template
Here is a simple compound interest calculator template you can use to calculate the value of investments.
From the drop-down, select the number of times the interest is to be compounded. The result will automatically update in cell E2.
Click here to download the compound interest calculator template.
You May Also Find the Following Excel Tutorials Useful:
- Calculating Weighted Average in Excel.
- Age Calculation Template.
- Calculating Standard Deviation in Excel.
- Calculating CAGR in Excel.
- Using PMT Function in Excel.
- Calculating Moving Average in Excel
- How to Calculate IRR in Excel
- Calculating NPV in Excel
