CAGR: compound annual growth rate
CAGR is the steady yearly growth rate that would take a starting value to an ending value over a number of years.
=(C2/B2)^(1/D2)-1The sheet: B2 is the starting value, C2 is the ending value and D2 is the number of years between them.
How it works, step by step
- 01C2/B2 is the total growth multiple over the whole period.
- 02^(1/D2) takes the D2-th root, which is the multiple for a single year.
- 03-1 turns that yearly multiple into a growth rate.
- 04Format the cell as a percentage.
The same logic in four tools
=(C2/B2)^(1/D2)-1=(C2/B2)^(1/D2)-1
Equivalent: =RRI(D2,B2,C2)SELECT POWER(end_value / NULLIF(start_value, 0), 1.0 / years) - 1 AS cagr
FROM investments;df["cagr"] = (df["end_value"] / df["start_value"]) ** (1 / df["years"]) - 1Mistakes that break it
- !D2 is the number of periods between values, not the number of data points. Values for 2020 to 2025 give 5 years.
- !It needs positive start and end values. A zero or negative value returns an error.
- !CAGR smooths out the path. Two series with the same CAGR can behave very differently in between.
Common questions
- Is there a built-in function?
- Yes, RRI(years, start, end) in Excel and Google Sheets gives the same result.
- Can I use months?
- Yes, but the result is a monthly rate. Use years for an annual rate.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.