Ampler adds a ready-made CAGR function to Excel — select the value range and the number of periods, and Ampler builds the compound annual growth rate formula for you, instead of typing the exponent formula by hand.
Quick answer: Select the cell for the result, click Insert CAGR formula on the Ampler ribbon, and enter the value range and the number of periods. Ampler constructs the compound annual growth rate formula in the cell.
How to use it
Select the range and periods, and Ampler builds the CAGR formula.
- Select the cell where the CAGR result should go.
- Click Insert CAGR formula on the Ampler ribbon.
- Enter the parameters — the value range and the number of periods.
CAGR is one of Ampler’s custom math functions (alongside others such as COUNTDISTINCT); building it from a range and period count reduces the chance of formulaic errors.
Keyboard shortcut
Insert CAGR formula has no default shortcut. To assign one, open the Settings dropdown on the Ampler ribbon, choose Shortcuts, click Set shortcut on the function, and press your key combination.
Why use it
Written by hand, a CAGR is an easy formula to get wrong — an off-by-one on the number of periods or a misplaced parenthesis quietly changes the result. Ampler’s function takes the value range and the period count and constructs the calculation for you, so the growth rate is right and the formula is consistent everywhere it’s used.
How consultants use it
CAGR is one of the most-used figures in a consulting analysis — it’s how growth is summarised in market sizing, benchmarking, and valuation work at firms like McKinsey, BCG, and Bain — so it has to be both correct and consistent across a model. Three conventions apply:
- Growth reported as CAGR: a multi-year trend is compressed into a single comparable rate rather than a string of year-on-year changes.
- Auditable, error-free formulas: the calculation must be transparent and reviewable, because a wrong growth rate can flip a recommendation.
- Consistent period conventions: the number of periods is counted the same way throughout, so rates stay comparable across the model.
Ampler’s CAGR function enforces that consistency and removes the manual error.
Tips & best practices
- Point the function at the exact start and end values — the period count must match the span between them.
- Wrap the result in IFERROR when the inputs may be blank or zero.
- Use it wherever a growth rate feeds a model — market sizing, projections, or a DCF.
Availability
Included in Ampler for Excel and the Ampler Suite, on Windows desktop Excel.
Frequently asked questions
How do you calculate CAGR in Excel?
Use Ampler’s Insert CAGR formula function: select the result cell, click Insert CAGR formula on the Ampler ribbon, and enter the value range and the number of periods. Ampler builds the compound annual growth rate formula for you.
What is CAGR?
CAGR — compound annual growth rate — is the constant year-on-year rate at which a value would have grown from its start to its end over a number of periods. It summarises a multi-year trend as a single comparable rate.
How do you avoid errors in a CAGR formula?
By using Ampler’s built-in CAGR function instead of typing the exponent formula by hand. You select the range and the period count, and Ampler constructs the formula, reducing the chance of a formulaic error.
Is the CAGR function built into Excel?
No. CAGR is a custom function added by the Ampler add-in, alongside other custom math functions such as COUNTDISTINCT.