Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate CAGR (compound annual growth rate) in Excel and Google Sheets
Excel & Google Sheets
=POWER(B6/B2, 1/(COUNTA(B2:B6)-1))-1✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Year | Value |
| 2018 | 1000 |
| 2019 | 1200 |
| 2020 | 1440 |
| 2021 | 1728 |
| 2022 | 2074 |
=POWER(B6/B2, 1/(COUNTA(B2:B6)-1))-1→0.2000578662
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your initial value in cell B2 and your final value in cell B6.
- 2List the years or periods in cells A2 through A6, starting from the initial year in A2.
- 3In cell B7, enter the formula to calculate CAGR: =POWER(B6/B2, 1/(COUNTA(B2:B6)-1))-1.
- 4Press Enter to see the CAGR as a decimal. Format the cell as a percentage for better readability.
Tips
- Ensure that your data range includes all relevant years and values for accurate CAGR calculation.
- If your data spans different locales, be aware that Excel and Google Sheets might use different date formats, which could affect calculations.
- Remember to format the result as a percentage to make it more interpretable.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What does CAGR stand for?
CAGR stands for Compound Annual Growth Rate, which is a useful measure for evaluating the performance of an investment over time.
Can I use this formula for any type of data?
While the CAGR formula is typically used for financial data, it can be applied to any set of values where you want to measure the compounded growth rate over time.
Why do I get a #DIV/0! error?
This error usually occurs if there are no values in the range or if the range contains only one value. Ensure your range includes at least two values.