The CAGR formula
CAGR = (end value ÷ start value)^(1 ÷ n) − 1
n is the number of periods, not the number of data points. From 2020 to 2026 there are seven yearly values but only six steps of growth, so n = 2026 − 2020 = 6. Calculating n from the years, =A8-A2, avoids the most common CAGR mistake.
The ratio 948,000 ÷ 480,000 = 1.975 says revenue almost doubled. Raising it to the power 1/6 gives the yearly growth factor 1.1201, and subtracting 1 leaves the rate: 12.01% a year.
Three ways to calculate CAGR in Excel
- The ^ operator:
=(B8/B2)^(1/B11)-1 - POWER:
=POWER(B8/B2,1/B11)-1, the same calculation written as a function. - RRI:
=RRI(B11,B2,B8), Excel's built-in CAGR function, with the arguments number of periods, start value and end value.
All three return 12.01%. RRI is the easiest to read, the ^ version works in every spreadsheet program. A quick check proves the result: the start value grown at the CAGR for six years, =B2*(1+B12)^B11, must return the end value of $948,000 exactly.
Worked example: CAGR of an online shop, 2020 to 2026
Revenue is the input (yellow). Column C shows the uneven yearly growth, rows 11 to 18 calculate the CAGR three ways, check it and project it, and rows 21 to 24 annualise a monthly and a 30-month rate.
| A | B | C | |
|---|---|---|---|
| 1 | Year | Revenue ($) | YoY growth |
| 2 | 2,020 | 480,000 | |
| 3 | 2,021 | 600,000 | 25.0% |
| 4 | 2,022 | 540,000 | -10.0% |
| 5 | 2,023 | 702,000 | 30.0% |
| 6 | 2,024 | 737,100 | 5.0% |
| 7 | 2,025 | 884,500 | 20.0% |
| 8 | 2,026 | 948,000 | 7.2% |
| 9 | |||
| 10 | CAGR | Value | |
| 11 | Number of periods | 6 | |
| 12 | CAGR with ^ | 12.01% | |
| 13 | CAGR with RRI | 12.01% | |
| 14 | CAGR with POWER | 12.01% | |
| 15 | Average YoY growth (wrong) | 12.86% | |
| 16 | Check: 2020 grown at CAGR | 948,000 | |
| 17 | Projection year | 2,029 | |
| 18 | Projected revenue | 1,332,269 | |
| 19 | |||
| 20 | Annualising | Value | |
| 21 | Monthly growth | 1.2% | |
| 22 | Annualised growth | 15.39% | |
| 23 | Total return over 30 months | 27.0% | |
| 24 | Annualised return | 10.03% |
- B12
=(B8/B2)^(1/B11)-1CAGR with the ^ operator - B13
=RRI(B11,B2,B8)Built-in CAGR function - B18
=B8*(1+B12)^(B17-A8)Projection at the CAGR - B22
=(1+B21)^12-1Monthly rate annualised - B24
=(1+B23)^(12/30)-130-month return annualised
Fictional shop, illustrative figures. A CAGR describes past growth, it is not a forecast and not investment advice.
Why the average growth rate is wrong
The yearly growth rates in column C average 12.86%, almost a point more than the CAGR. The arithmetic average ignores compounding, and it always overstates the true rate when growth goes up and down. The extreme case shows why: +50% followed by −50% averages 0%, but $100 becomes $150 and then $75, a loss of 25%.
The CAGR is the geometric average of the growth factors. Use it whenever you want one rate that describes growth over several years, and quote the yearly rates separately if the path matters.
Projecting with the CAGR
To project forward, grow the last actual value at the CAGR: =B8*(1+B12)^(B17-A8). Three more years at 12.01% turn $948,000 into $1,332,269 in 2029. Treat this as a what-if, not a forecast: the CAGR describes the past, and the shop's yearly growth was anything but steady.
Annualising monthly and multi-year rates
Annualising follows the same compounding logic. A monthly growth rate of 1.2% is =(1+1.2%)^12-1 = 15.39% a year, not 12 × 1.2% = 14.4%.
For a total return over a period that is not a whole number of years, put the period in years in the exponent: 27% over 30 months is =(1+27%)^(12/30)-1 = 10.03% a year. This is the same CAGR formula with n = 30 ÷ 12 = 2.5 years.
CAGR vs IRR and XIRR
The CAGR uses only the start and the end value. If money was added or withdrawn along the way, for example monthly savings into a portfolio, the CAGR mixes the deposits with the growth. Use the internal rate of return instead, which weighs every cash flow by its timing: see how to calculate IRR in Excel and XIRR vs IRR for dates that are not evenly spaced.
The CAGR also needs a positive start and end value. With a negative or zero start, the ratio is meaningless and Excel returns #NUM! or #DIV/0!. For the change between just two years, the plain percent change formula is enough.
Common mistakes
- Counting data points instead of periods. Seven years of data from 2020 to 2026 are six periods. Using 7 understates the CAGR.
- Averaging the yearly growth rates. The arithmetic average ignores compounding and overstates the rate whenever growth varies: 12.86% instead of 12.01% here.
- Dividing the total growth by the number of years. (end ÷ start − 1) ÷ n is simple growth, not compound growth, and comes out too high.
- Forgetting the −1. Without it, the formula returns the growth factor 1.1201 instead of the rate.
- Using CAGR for an account with deposits or withdrawals. Money added along the way inflates the result. Use IRR or XIRR.
- Multiplying a monthly rate by 12. 1.2% a month compounds to 15.39% a year, not 14.4%.
Frequently asked questions
What is the formula for CAGR in Excel?
Use =(end/start)^(1/years)-1, for example =(B8/B2)^(1/6)-1 for six years of growth. =RRI(6,B2,B8) and =POWER(B8/B2,1/6)-1 give the same result.
Is there a CAGR function in Excel?
Yes, RRI. Its arguments are the number of periods, the start value and the end value: =RRI(6,480000,948000) returns 12.01%.
How do I calculate CAGR for months instead of years?
Express the period in years: with a start and end value 30 months apart, use =(end/start)^(12/30)-1. For a monthly growth rate, annualise it with =(1+rate)^12-1.
What is the difference between CAGR and average annual growth?
Average annual growth is the arithmetic mean of the yearly rates and ignores compounding. CAGR is the steady rate that actually links the start and end values. With uneven growth the average is always higher than the CAGR.