“Compound interest is the eighth wonder of the world. He who understands it, earns it … he who doesn’t … pays it”. – Albert Einstein
What is Compound Interest?
Let me take a simple example to explain it.
Suppose you invest USD 1000 in a bank account that promises to 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, the bank 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:
5 thoughts on “How to Calculate Compound Interest in Excel + FREE Calculator”
good lea
sons just found the page and it excite me alot
All these formula give a result after a period of years. How do I calculate by a period of days; e.g. February = 28 days; March = 31 days, etc?
Thank you very much for your effort..god bless you
It would be great to see an article on this topic in reverse, as an educational tool for borrowers. So many people with credit issues are forced or lured into compound interest contracts that seem simple, and are sold as “monthly payments” but are devastating in the long run, as the interest compounds on the interest… to be PAID by the consumer, while giant companies “EARN” (USURY) the ludicrous interest amounts… making the rich richer and the poor poorer. The financial giants in this country are out of control.
Nice article. What if you are calculating the interest for a series of equal deposits each month but the the compounding is daily? How do you use the PV formula and let it know you are making deposits each month but compounding is each day?