Compound Interest is an important concept in banking and finance. This tutorial will help you understand this concept, show you how to compute it, and how to use Google Sheets to quickly find the compound interest annually, monthly, and daily.
Table of Contents
What is Compound Interest?
In simple words, Compound interest is the interest accrued on both the principal amount as well as interest accumulated over past periods.
It is different from simple interest where interest is accrued only on the principal amount.
To understand how Compound interest works let’s take a basic example.
Say you make an investment of $1000 to a business venture that returns an interest of 6% on your investment, compounded annually (assuming you make no more investments after that).
In the first year, you earn an interest of 6% of $1000 (=$60). So you now have 1000+60=$1060 at the end of the first year.
In the second year, you earn interest on this compounded amount (not just your initial investment). This means you now get an interest of 6% of $1060 (=$63.6). You now have 1060+63.6=$1123.6 at the end of the second year.
In the third year, you again earn an interest of 6% of $1123.6 (=$67.416). You now have 1123.6 +67.416=$1191.016 at the end of the third year.
In three years, your investment compounded from $1000 to $1123.6. In 10 years, this amount will compound to $1790.85.
When using compound interest, your money grows at a much higher rate than simple interest, bringing you a higher return on investment. Moreover, the longer you keep your money, the more you benefit, since your interest keeps growing with time.
Basic Formula to Compute Compound Interest in Google Sheets
The calculations shown in the previous section were just to give you a basic understanding of the concept of compound interest. Let’s now see a more generalized formula to compute compound interest.
Consider a principal amount P with an interest rate of R. At the end of the first year, the total will amount to P+(P*R), or P(1+R) if simplified.
At the end of the second year, this total amount will increase to P(1+R)+P(1+R)*R, which is simply P(1+R)2.
Similarly, at the end of the third year, the total amount will become P(1+R)2+P(1+R)2*R, which is just P(1+R)3.
So, if N is the number of years, the formula for compound interest can be generalized to:
Compound Interest=P(1+R)N
Now it’s important to understand that interest is not always compounded annually. It can also be compounded semi-annually, monthly, weekly, or even daily. So, it makes sense to further generalize the above formula so that it works for all kinds of compounding time periods.
Let’s say t is the compounding time period (in a year). If interest is compounded annually, then t = 1, since you need to compound the interest just once a year. If the interest is compounded semi-annually (or twice in a year), then t = 2. Similarly, if it is compounded monthly, t = 12.
So if we want to further generalize our compound interest formula and express it in terms of t, then the formula becomes:
Compound Interest = P(1+R/t)(n*t)
How to Calculate Compound Interest in Google Sheets (with example)
Let us now apply the above formula to compute the Compound interest in Google Sheets. There are two ways to get this done:
- Using the compound interest formula
- Using the FV Function
Compound Interest Calculator
I created a compound interest calculator to help you quickly find these results. With it, you can modify compounding frequency and more.
You can make a copy of it here.
Note that I included options for you to change the principal, interest rate, and number of years to compound. I also included a row that allows you to account for monthly compounding.
To account for an annual interest rate, just leave the number of sessions at 1. If you want monthly compounding, just change the interest rate accordingly and input 12 for the number of compounding periods per year.
Once you input your info, my compound interest calculator does the rest. It shows the final amount based on your total number of years invested. That makes it a valuable way to evaluate high-yield savings accounts or any other savings vehicle with fixed-rate returns.
Now let’s look at how to apply each of these methods to compute the compound Interest in different cases.
Adding a Visualization
I’ve updated my compound interest spreadsheet to include a visualization of your timeline.
Get my updated compound interest spreadsheet here.
Adding Annual Contributions
If you want to calculate compound interest with additional payments, you can use the following formula.
I used this formula to calculate the year one ending balance in cell B14:
=$B$5*(1+$B$6/$A15)^($B$7*$A15) + $B$9*((1+$B$6/$A15)^($B$7*$A15)-1)*($B$7/$B$6)
And this is the formula I used to calculate year 2 (and the following years) in cell B15:
= B15 * (1 + $B$6 / $B$7) + $B$9 * ((1 + $B$6 / $B$7) - 1) * ($B$7 / $B$6)
Here’s what my updated spreadsheet template looks like, now with the new option to add extra payments in cell B9.
Get my “extra-payments” compound interest spreadsheet here.
How to Calculate Compound Interest Compounded Annually
Let us consider the same case where you have an initial investment of $1000, and want to find the future value (or compound interest) when a 6% interest is compounded on it annually.
Related:Â Google Sheet Tournament Bracket Template
We can tabulate the initial parameters in a Google Sheets compound interest template as follows:
Using the Google Sheets Compound Interest Formula
The first way is to apply the same generic Google Sheets Compound Interest formula (discussed in the previous section) in the form of a Google Sheets formula as follows:
=B1*(1+B2/B3)^(B4*B3)
When you enter the above formula into cell B6 of the compound interest spreadsheet, here’s the result you should get:
If you want to find the resultant amount in 10 years, you simply need to replace the value in cell B4 with 10, as shown below:
Your resultant amount would then be:
Using the FV Function
The second method to compute the compound interest uses the FV function. The FV function is used to compute the future value of an investment at a fixed interest rate. In fact, the abbreviation FV is short for ‘Future Value’.
The syntax for the function is as follows:
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
Here,
- rate is the rate of interest during the payment period.
- number_of_periods is the number of periods for which you want to calculate the future value.
- payment_amount is the additional payment made during each period. If there’s no additional payment being made then this value can be 0.
- present_value is the principal amount. This value is optional. If omitted, it is assumed to be 0. Specify this value as a negative number (if you are trying to compute the future value on deposits made, like to a bank account)
- end_or_beginning is an integer value that specifies when the payment is due. If you want to compute the final amount at the end of the period, then specify with a parameter 0. If you want to compute the final amount at the beginning of the period, then specify with a parameter 1. By default, this value is assumed to be 0.
Let’s use the FV formula to calculate the compound interest in the same problem:
=FV(B2,B3*B4, 0, B1)
The result we get is as follows:
Notice that we specified the Principal amount value (cell B1) as a negative number. This is because we are depositing money (or paying out) to the bank / organization.
How to Calculate Compound Interest Compounded Monthly
Let us look at another case where the interest of 6% is compounded monthly, instead of once a year. In this case, our parameters will be as follows:
Let us see how this change is implemented in both the methods discussed
Using the Google Sheets Compound Interest Formula
In this case, the only thing that changed is the number of periods in a year. Since interest is compounded monthly, it means the number of periods in a year, t is basically 12.
Let’s insert this value in our original compound interest formula:
At the end of 10 years, the total amount becomes $1,1819.40 when a 6% interest is compounded on a monthly basis.
Using the FV Function
If you prefer using the FV function for the same problem, then you will have to tweak the formula a little. Since interest is compounded 12 times in a year, your interest rate per month becomes rate/12, while the number_of_periods is the number of periods in a year times the number of years.
In our example, we need to specify the parameters of the FV function as follows:
- rate=B2/B3
- number_of_periods=B3*B4
- payment_amount=0
- present_value=B1
- end_or_beginning=0 Â (the default value)
When we plug these parameters into the FV function, here’s the formula we get:
=FV(B2/B3, B3*B4, 0,B1)
The result we get is as follows:
As we can see, both methods return the same result.
How to Calculate Compound Interest Compounded Daily
Finally, let us look at one more case where the interest of 6% is compounded daily. In this case, our parameters will be as follows:
Using the Compound Interest Formula
Since interest is compounded daily, it means the number of periods in a year, t is 365.
Let’s insert this value in our original compound interest formula:
At the end of 10 years, the total amount becomes $1,822.03 when a 6% interest is compounded on a daily basis.
Using the FV Function
Since interest is compounded 365 times in a year, your interest rate per day becomes rate/365, while the number_of_periods is 365 times the number of years.
We simply need to replace the value in cell B3 (compounding periods per year) with 365 and use the same FV formula:
=FV(B2/B3, B3*B4, 0,B1)
This gives us the future value for the given investment at the end of 10 years as follows:
Again, both methods returned the same result.
Note that when we use the FV function, we get the returned result as a positive number, since we specified the present_value (or Principal amount) as a negative number.
FAQs
How do I calculate compound interest in Google Sheets?
There are two ways to calculate compound interest in Google Sheets:
- Using the basic compound interest formula
- Using the FV function
Both these methods have been discussed in detail in this tutorial.
What is the formula for calculating compound interest?
The general formula for calculating compound interest is as follows:
Compound Interest = P(1+R/t)(n*t)
Here,
- P is the Principal amount
- R is the rate of interest
- t is the number of compounding periods in a year
- n is the number of years
How do you calculate interest compounded monthly?
To calculate interest compounded monthly, you need to divide the interest rate by 12 and multiply the number of years by 12, since the interest is compounded 12 times in a year.
Thus, the value, t in the compound interest formula needs to be specified as 12.
What is compound interest with an example?
Compound interest is the interest applied on both the principal amount as well as interest accumulated over previous periods.
For example, if you save $100 in a bank account for which the bank pays you 5% interest annually, you get an interest of $5 in the first year ($100 * 5%). In the second year, you get an interest of $5.25 ($105 * 5%). In the third year, your interest compounds to $5.51 ($110.25 * 5%). In this way, each year your interest keeps compounding, since you’re earning interest not just on the principal amount, but also on the interest that you’re accumulating each year.
What is better compounded monthly or annually?
If you’re investing money (for example saving money in a bank), then interest compounded monthly is slightly more beneficial for you. If you’re borrowing money (for example on credit), then interest compounded annually is more advantageous for you.
Conclusion
In this tutorial we discussed the concept of compound interest, what it means, how it works, and two ways to compute it in Google Sheets. The first method uses the Google Sheets compound interest formula, while the second method uses the Google Sheets FV function. We hope you found this tutorial helpful.
Here are some more finance-related articles that you can read:
