CAGR formula in Excel: 3 ways to calculate it

Unlike XIRR, CAGR has a direct, closed-form formula: no iteration, no guessing and refining, just a beginning value, an ending value, and a number of years plugged into one equation. That makes it easy to translate into Excel, and there are three common ways to do it. All three agree exactly, as long as you count the years correctly, which is the one step that quietly trips people up.

The manual formula

The most direct route is to type the CAGR formula out exactly as the math defines it: =(Ending Value/Beginning Value)^(1/Years)-1.

The RRI function

Excel also has a built-in function that skips writing the exponent out by hand: =RRI(nper, pv, fv).

nper is the number of years, pv is the beginning value, and fv is the ending value, both entered as positive numbers. For the same example: =RRI(5, 500000, 1000000) returns the identical 14.87%, since RRI is just the CAGR formula built into Excel under a different name.

The RATE function

A third route repurposes RATE, the function Excel normally uses for loan and annuity math: =RATE(nper, 0, -pv, fv).

The mistake that throws all three off

All three formulas are only as good as the years figure fed into them, and the most common error is counting the number of value data points instead of the number of years between them. Five year-end values (say, 2021 through 2025) represent 4 years of growth, not 5: the first value is the starting point, not a year of compounding in its own right. Feeding in 5 instead of 4 understates the true CAGR, since the same total growth gets spread across one extra year that never actually happened.