FormulaA = P(1+r/n)nt
A: Final Amount one will receive after completion of the entire period ( Amount invested + Compound interest ).
P: Principal amount invested.
r: Annual Interest rate.
n: Number of compounding periods per year. If compounded yearly, then n=1. If compounded monthly, n=12.
t= Number of years. If invested for 6 months, then t=0.5
Sample Usage=B1*(1+B2/B3)^(B3*B4)
B1 is the principal, B2 the annual interest rate, B3 the number of compounding periods, and B4 is the number of years.
What is Compound Interest in Google Sheets?
Compound interest is the interest you receive on interest. It sounds a bit complicated at the first glance, so let’s dive deep. Assume you have deposited an amount of $10,000 in Bank of America at 7% for 5 years. So after 1 year, you will receive an interest of $700 (10,000*0.07*1). Similarly, after completion of the second year, you will receive an interest of $700 and so on for 5 years. This concept is called simple interest, where the interest is calculated on the principal amount (here $10,000).
However, in Compound Interest, the interest earned after every year gets added to the principal, thereby increasing the principal amount. So in the same example, after 1 year, you will receive $700 as interest. Now, the new principal amount will be $10,000 + $700 = $10,700. So, after completion of the second year, you will receive an interest of 749 (10,700*0.07*1). Now, the new principal is 10,700+749=$11,449. So for the third year, you will receive an interest of $801.43 (11,449*0.07*1) and so on. The difference is highlighted in the figure below:
Why Calculate Compound Interest in Google Sheets?
Compound interest causes your wealth to grow faster. It makes a sum of money grow at a faster rate than simple interest because you will earn returns on the money you invest, as well as on returns at the end of every compounding period. Therefore, financial institutions advise compounding money rather than extracting the interest every year. Compound interest quickens the process of wealth creation.
How to Calculate Compound Interest in Google Sheets?
Let’s say you want to find the amount you will receive after 7 years, if you invest $20,000 at 6.5%, compounded annually.
An easy way to do this is to enter the input parameters in cells and apply the mathematical formula displayed above.
Enter the parameters as shown below:
=B1*(1+B2/B3)^(B3*B4)
You will see that the amount you will receive after 7 years is $31,079.73 if compounded annually. If you want to find the compound interest earned, subtract the principal invested from the final amount, 31,079.73- 20,000 = $11,079.73.
Now, if you want to see the interests earned in every period, multiply the interest rate with the new principal for every year, as shown below:
Compound Interest Formula with Monthly Compounding
Let’s say you want to find the amount you will receive after 7 years, if you invest $20,000 at 6.5%, compounded monthly. Here, since we are compounding monthly, you have to change the parameter “Compounding periods per year (n)” from 1 to 12, as there will be 12 periods in a year.
An easy way to do this is to enter the input parameters in cells and apply the mathematical formula displayed above.
Enter the parameters as shown below:
=B1*(1+B2/B3)^(B3*B4)
You will see that the amount you will receive after 7 years is $31484.78502 if compounded annually. If you want to find the compound interest earned, subtract the principal invested from the final amount, 31484.78502- 20,000 = $11,484.78502.
You can see that if you increase the compounding periods per year, you will receive higher compounded interest and therefore higher returns.
Conclusion
You can calculate the compound interest in Google Sheets by using the mathematical formula. Higher the number of compounding periods per year, the higher the compound interest.
If you want to know more about how to calculate compound interest in Google Sheets, you can click here.
See Also
Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.
How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.
Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.
How to View Edit History of Cells in Google Sheets | Easy 2 Minute Guide: Learn how to view the edit history of cells in Google Sheets. Learn to spot mistakes or past changes.