If you want to calculate the final value of an investment with compound interest after a period of time, Google Sheets makes this a very easy calculation to make.
There are a couple of different ways that you can create a compound interest formula in your spreadsheet.
In this tutorial, I will show you how to calculate compound interest in Google Sheets.
Table of Contents
Compound Interest Formula
The basic formula for calculating compound interest is:
A = P(1 + r/n)nt
A: Final Amount
P: Initial Principal
r: Annual Interest Rate
n: Number of compounding periods each year
t: Number of years
Using the Compound Interest Formula in Google Sheets
Now that we have covered the mathematical formula for calculating the value of a compound interest investment over a period of time, let’s take a look at how we should set up this formula in our spreadsheet.
First, you need to have your variables. In this example, I will be calculating the final value of a $2000 investment, with a 4% interest rate that compounds annually over a period of 5 years.
Here is what this looks like in my spreadsheet:
Once you have the variables entered into your spreadsheet, you can enter the formula:
The exact formula used in this example is =B1*(1+B2/B3)^(B3*B4)
This is the same as our compound interest formula: Initial Principal (B1) * (1+ Annual Interest rate(B2)/Compounding Periods Each Year(B3) ^ (Compounding Periods Each Year(B3)*Number of Periods(B4)
You can use the same formula whether you are calculating for annual, monthly, or daily compounding.
The only thing that would change with the above formula when calculating for monthly or daily compounding is the “Compound Periods Each Year”.
For monthly compounding, this number would change to 12, and for daily compounding, it would be 365.
Using the FV Function to Calculate Compound Interest
If you don’t want to set up the math formula yourself to get the compound interest, you can use the FV function in Google Sheets.
The FV function calculates the future value of an annuity investment based on a constant interest rate. The function includes a payment amount, but for our purposes, we will keep that parameter at zero since we only want to know the final future value.
Here is the syntax of the FV function:
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
- rate – the interest rate.
- number_of_periods – the number of payments to be made
- payment_amount – the amount per period to be paid
- present_value – the current value of the annuity. This is an optional argument that will be set to 0 by default
- end_or_beginning – This argument controls whether payments are due at the end (0) or beginning (1) of each period. This is an optional argument that will be set to 0 by default
Again for our purposes, we are only interested in calculating the final amount of the investment after compound interest for a set number of periods, so the payment_amount parameter we will be setting to zero when we use the function.
Here is how to set up this function using our previous variables of a $2000 investment, with a 4% interest rate that compounds annually over a period of 5 years:
The exact formula I have used here is =–FV(B2,B4,0,B1). I have placed a minus symbol (-) before the FV function because the function will return a negative number and I want the number to be positive, this is optional.
Closing Thoughts
Compound interest might seem complex at first, but using a spreadsheet program like Google Sheets makes calculating it incredibly easy.
You have a couple of options for calculating it depending on your comfort level with math: calculating with the formula or using the FV function to calculate it.
Both options work well, so it’s up to you which one you choose!
More Google Sheets Tutorials:
How to Use the NPV Function
How to Create a Loan Amortization Schedule
How to Use the FV Function