SheetSense

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.

fx
=(C2/B2)^(1/D2)-1

The 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

  1. 01C2/B2 is the total growth multiple over the whole period.
  2. 02^(1/D2) takes the D2-th root, which is the multiple for a single year.
  3. 03-1 turns that yearly multiple into a growth rate.
  4. 04Format the cell as a percentage.

The same logic in four tools

Excel
=(C2/B2)^(1/D2)-1
Google Sheets
=(C2/B2)^(1/D2)-1
Equivalent: =RRI(D2,B2,C2)
SQL
SELECT POWER(end_value / NULLIF(start_value, 0), 1.0 / years) - 1 AS cagr
FROM investments;
Python (pandas)
df["cagr"] = (df["end_value"] / df["start_value"]) ** (1 / df["years"]) - 1

Mistakes 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.