Spreadsheets and finance go hand in hand. In finance, compound interest is one of the most powerful formulas. If you are investing in the stock market then you know what this is. While you have used it in online calculators, today you will write the compound interest formula in google sheets.
In this ultimate guide, we will first explore the compound interest formula in google sheets. Then you will learn how to calculate future values with yearly, monthly, and daily compounding.
Bonus: At the end of this tutorial, you will get a copy of the compound interest calculation sheet for personal use.
What is compound interest?
When the interest is calculated on both the principal amount and previous period interest, it is known as compound interest.
Compound interest formula in google sheets
Compound interest formula in google sheets is:
Compound interest = P(1 + r/n)nt – P
Where,
P = Principal amount (or initial investment)
r = rate of interest
t = number of years
n = how many times compounding happens in a year
Now let’s see how to use this formula to calculate compound interest in google sheets.
Table of contents
- Compound interest formula
- FV (Future value) calculation
- CAGR calculations
- Compounding with principal increasing every year
Compound interest formula in google sheets – Types of compounding
Annual compounding:
Suppose you bought $10000 of a company stock 5 years back. The investment compounds annually at a rate of 4%. Below we have the sheet filled with this data.
To calculate compound interest, you need to write the compound interest formula in google sheets which is: P(1 + r/n)nt – P
Here is how you will write it in google sheets:
=(B1*(1+B3/B4)^(B4*B2))-B1
As you see above the compound interest is 2166.529.
This is how you will calculate compound interest in google sheets.
Once you know the compound interest, then you can calculate the final amount.
Final amount = Initial amount (Principal) + Compound interest
So in this example, the final amount after compounding will be as below:
10000 + 2166.529 = 12166.529
Monthly compounding
Take the same example as above. Instead of annual compounding, let’s say the investment compounds monthly.
Since there are 12 months in a year, the number of compounding periods per year will be 12.
Meaning, n=12
Now you can calculate the compound interest using the same formula. Just input the new value for “Times compounding/year” and the result will automatically update.
Daily compounding
Now the compounding happens daily. We have 365 days in a year. So the compounding periods/year will now be 365.
Meaning, n=365
Go ahead and input this value in your sheet and get the new result for compound interest. Here is the screenshot of the result:
Quarterly compounding
Let’s say the investment is compounded quarterly.
So n will be equal to 4 as there are 4 quarters in a year.
After putting the value of n as 4 in our sheet, the final result will update like this:
Half-yearly compounding
In the case of half-yearly compounding, n will be 2.
The final result will be as below:
How does compound interest grow?
Compound interest grows in an accelerating growth with time because it includes the interest from the previous periods. The interest from the previous period adds on to the initial amount and this goes on every time compounding happens.
Compared to simple interest, in the case of compound interest, you get higher returns on your investments. If you have invested $100000 at a simple annual interest rate of 5% over 10 years, after 10 years the final amount will be $150000. This includes interest earnings of $50000.
If the same investment compounds annually, then you will get approximately $162889 after 10 years.
Compound interest schedules
Interest can be compounded in any frequency schedule from annually to daily. In the case of saving accounts in a bank, the common compounding frequency is daily. While interest can be compounded daily but many banks credit it on a monthly or quarterly basis.
Compounding frequency has a positive impact on the final amount. For example in the above example of a $100000 investment, if it compounds daily, then the final amount will be $164866 after 10 years.
Effect of time on compound interest
As Albert Einstein once said, compound interest is the eighth wonder of the world. This is because compound interest grows exponentially with time.
Let’s say you have the same $100,000 as an initial investment. The rate of interest is 5% and compounding happens annually.
In the below graph you can see how the investment grows with time.
As time grows, your investment grows exponentially.
So the time has a large impact on compounding.
Important considerations
Compound interest is closely tied with a few other calculations.
These are:
Future value calculation in google sheets
Future value is all about finding the future value of your investment when you have a periodic investment with a fixed interest rate.
Google sheet has a function to calculate the future value which is:
=FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
Rule of 72
The rule of 72 essentially calculates the time by which your investment will double at a given interest rate. It is used for annual compounding only.
Compound annual growth rate (CAGR) calculation in google sheets
CAGR is a popular term used in financial calculations. It gives you a single growth rate over some time.
Let’s say your investment has grown from $100000 to $150000 over 3 years. The common question people ask is what’s the CAGR? Because that’s how you will project the future growth.
Formula for CAGR is:
CAGR = (future value / present value)1/periods – 1
Compounding with principal increasing every year
Lets consider below data:
Starting annual contribution = $1000
Annual increase in contribution each year = 10%
Annual Compound growth rate = 5%
The ask is to calculate the total value after 20 years.
So here is the table showing the calculation:
And here is a chart showing the growth over years.
The google spreadsheet link is available at the bottom to copy – Copy Now
Pros and Cons of compounding
Compounding interest is a miracle as per Einstein’s words. But sometimes compound interest works against you.
Specifically, if you have taken a loan or say you have not paid the bill on your credit card. The compounding effect will work against you when your bank calculates the final due. A credit card with an interest that compounds monthly can add up a significant amount to your debt.
The pros are when you invest your money as you saw from the compound interest calculations we did.
FAQ
How to do compound interest in google sheets?
To calculate compound interest in google sheets, you can type the compound interest formula which is P(1 + r/n)nt – P
How to calculate daily compound interest in google sheets?
To calculate daily compounding interest problems, you can set n=365 which means setting how many times compounding happens in a year to 365 days.
What is the future value calculation in google sheets?
Future value calculation in google sheets tells you the future value of a present value. You can use the inbuilt future value formula in google sheets to calculate this.
Wrapping up
In this tutorial, we learned about compound interest formula in google sheets. We saw examples of how compounding happens annually, monthly, daily, and so on.
Compounding interest calculation comes in handy when working with financial data. I suggest you make a copy of this google sheets spreadsheet and use this in your future calculations.
Copy compound interest formula
Here is the link of the google sheet. Create a copy of this sheet and then use it for personal use. Link
Appendix
[1] Future value formula – Link
Further Reading
New to google sheets ? Start here
More related to Formulas:
Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets
