We can use the following compound interest formula to find the ending value of some investment after a certain amount of time:
A = P(1 + r/n)nt
where:
- A:Â Final Amount
- P:Â Initial Principal
- r:Â Annual Interest Rate
- n:Â Number of compounding periods per year
- t:Â Number of years
The following examples show how to use this formula in Google Sheets to calculate the ending value of investments in different scenarios.
Example 1: Compound Interest Formula with Annual Compounding
Suppose we invest $5,000 into an investment that compounds at 6% annually.
The following screenshot shows how to use the compound interest formula in Google Sheets to calculate the ending value of this investment after 10 years:
This investment will be worth $8,954.24 after 10 years.
The following screenshot shows how to calculate the ending investment after each year during the 10-year period.
Note that Column F shows the formula we used in each corresponding cell in Column E:
Example 2: Compound Interest Formula with Monthly Compounding
Suppose we invest $1,000 into an investment that compounds at 6% annually and is compounded on a monthly basis (12 times per year).
The following screenshot shows how to use the compound interest formula in Google Sheets to calculate the ending value of this investment after 5 years:
This investment will be worth $1,348.85 after 5 years.
Example 3: Compound Interest Formula with Daily Compounding
Suppose we invest $5,000 into an investment that compounds at 8% annually and is compounded on a daily basis (365 times per year).
The following screenshot shows how to use the compound interest formula in Google Sheets to calculate the ending value of this investment after 15 years:
This investment will be worth $16,598.39 after 15 years.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Sum Across Multiple Sheets in Google Sheets
How to Combine Columns in Google Sheets
How to Compare Two Columns in Google Sheets