How to Calculate CAGR in Excel

CAGR, the compound annual growth rate, smooths a multi period change into a single annualized rate. It answers what constant yearly growth would take a starting value to an ending value over a given number of years. In Excel the core formula is one line, but the number of years is where most people slip, so getting the count right matters as much as the formula.

The formula

The standard expression is CAGR = (End / Begin) ^ (1 / Years) - 1. You take the total growth ratio, raise it to the reciprocal of the number of years to annualize it, then subtract one to convert from a multiple into a rate.

The key input is years, which is the number of periods of growth, not the count of data points. Growth from year 1 to year 5 spans four years, not five.

Worked example in Excel

Put the beginning value in B1, the ending value in B2, and the number of years in B3. Then enter the formula in a result cell.

1. Enter =(B2/B1)^(1/B3)-1 in B4 and format it as a percentage.
2. Set B3 to the number of periods between the dates, which is the later year minus the earlier year.
3. Cross check with the built in =RRI(B3,B1,B2), which returns the same rate from the same three inputs.

=(200/100)^(1/5)-1 returns about 14.87%, and =RRI(5,100,200) returns the same.

RRI and POWER alternatives

Excel offers tidier ways to write the same calculation. RRI(periods, present, future) is purpose built for this and is the clearest option in modern Excel.

You can also use =POWER(B2/B1, 1/B3)-1, which is identical to the caret operator but easier to read for some. All three return exactly the same rate from the same inputs, so choose the one your team finds most readable.

• =(B2/B1)^(1/B3)-1 is the explicit formula.
• =RRI(B3,B1,B2) is the dedicated function.
• =POWER(B2/B1,1/B3)-1 reads more clearly to some.

The years trap and when CAGR misleads

The most common error is the off by one years count. If your data runs from 2019 to 2024, that is five years of growth, not six annual figures and not four. Counting the cells instead of the gaps inflates or deflates the rate. When the year count is typed directly into the formula as a constant, the mistake is invisible, so keep years in its own cell.

CAGR also hides volatility. It reports the smooth equivalent rate and says nothing about the path, so a series that crashed then recovered can show the same CAGR as one that grew steadily. It is also undefined or meaningless when the beginning value is zero or negative. Use it to summarize, never to imply the journey was smooth.