=(B2/A2)^(1/C2)-1 returns the compound annual growth rate (CAGR) from the start value in A2 to the end value in B2 over C2 years. It is the one steady yearly rate that would turn the start value into the end value. =RRI(C2,A2,B2) gives the same result.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | End | Years | CAGR | RRI |
| 2 | $50,000 | $80,000 | 4 | 12.47% | 12.47% |
Revenue grew from 50,000 to 80,000 in 4 years, a CAGR of 12.47%. Format the result as a percentage (Home > Percent Style, or Ctrl+Shift+%, Control+Shift+% on a Mac), or it shows as a decimal such as 0.1247. Change C2 to 2 and the same growth in half the time is 26.49% a year.
The CAGR formula, step by step
CAGR = (end / start) ^ (1 / years) - 1
end/startis the total growth factor: 80,000 / 50,000 = 1.6, so revenue is 1.6 times what it was.^(1/years)takes the 4th root of that factor, the yearly factor that multiplied by itself 4 times gives 1.6. Raising to the power 1/4 is the 4th root.-1turns the factor into a rate: 1.1247 becomes 12.47%.
The parentheses matter. =B2/A2^(1/C2)-1 raises only A2 to the power, because ^ is calculated before /, and gives a meaningless result.
RRI (Excel 2013 and later, and Google Sheets) takes the same three numbers in a different order: =RRI(nper, pv, fv), that is years, start, end.
CAGR from a table of yearly values
With one row per year, take the first and last value and count the periods from the years themselves. Six rows from 2021 to 2026 are 5 periods of growth, and a common mistake is to use 6.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Users | Growth | Measure | Rate | |
| 2 | 2021 | 12,000 | CAGR | 16.72% | ||
| 3 | 2022 | 18,000 | 50.0% | Average growth | 18.86% | |
| 4 | 2023 | 15,300 | -15.0% | |||
| 5 | 2024 | 19,900 | 30.1% | |||
| 6 | 2025 | 24,500 | 23.1% | |||
| 7 | 2026 | 26,000 | 6.1% |
The users grew at a CAGR of 16.72%, but the average of the five yearly growth rates is 18.86%. The average is pulled up by the 50% jump in 2022 and does not fully count the fall in 2023. The CAGR is the honest single number: =B2*(1+F2)^5 lands exactly on 26,000. Change B3 to 8000: the average drops sharply, while the CAGR does not move at all, because it depends only on the first and last value. For one year's change on its own, see percent change.
Project a future value with CAGR
Turned around, a growth rate tells you where a value will be: =start*(1+rate)^years. Here 10,000 grows at 8% a year:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year | Value | Rate | |
| 2 | 0 | $10,000 | 8% | |
| 3 | 1 | $10,800 | ||
| 4 | 2 | $11,664 | ||
| 5 | 3 | $12,597 | ||
| 6 | 4 | $13,605 | ||
| 7 | 5 | $14,693 |
After 5 years at 8%, 10,000 becomes 14,693. Put that end value back into the CAGR formula with 5 years and you get 8% again. The same compounding drives savings plans and loans on the PMT page.
CAGR between two dates
When the period is not a whole number of years, use the exact length in years as the exponent. YEARFRAC(start,end,1) returns it from two dates; the 1 counts actual days, while the default counts 30-day months.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Bought | Sold | Paid | Sold for | Years | CAGR |
| 2 | 2022-03-15 | 2026-09-30 | $8,000 | $11,500 | 4.55 | 8.31% |
From March 2022 to September 2026 is about 4.55 years, a CAGR of 8.31%. With money going in and out along the way (monthly deposits, partial sales), CAGR no longer applies: use XIRR from the NPV and IRR page.
Try it: growth of a city's population
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Years | Pop 2016 | Pop 2026 | CAGR |
| 2 | 10 | 412,000 | 538,000 |
Your turn: In D2, calculate the compound annual growth rate from the population in B2 to the population in C2, over the number of years in A2.
Hint: (end/start)^(1/years)-1. Keep the parentheses around the division and around the exponent.
CAGR vs average growth vs IRR
| You have | Use | Formula |
|---|---|---|
| A start value, an end value and a number of years | CAGR | =(end/start)^(1/years)-1 or =RRI(years,start,end) |
| The change from one year to the next | Percent change | =new/old-1 |
| A list of yearly rates you want to summarise | Geometric mean | =GEOMEAN(1+C3:C7)-1, which equals the CAGR |
| Cash going in and out over time | IRR or XIRR | =IRR(values), =XIRR(values,dates) |
Avoid reporting the plain AVERAGE of yearly growth rates as "average annual growth" for anything that compounds, such as revenue, prices or investments. After +50% and then -50% the average is 0%, but the value has fallen to 75% of where it started.
Frequently Asked Questions
What is the CAGR formula in Excel?
=(end/start)^(1/years)-1, for example =(B2/A2)^(1/C2)-1. Format the result as a percentage. =RRI(C2,A2,B2) gives the same result in Excel 2013 and later.
How many years do I use for CAGR?
The number of periods between the first and last value, not the number of values. From 2021 to 2026 is 5 years, even though the table has 6 rows.
What is the difference between CAGR and average annual growth?
Average growth is the plain average of each year's percent change. CAGR is the single steady rate that turns the start value into the end value. After a fall and a recovery the average overstates growth: +50% then -50% averages 0% but leaves you at 75% of the start, a CAGR of about -13.4%.
How do I calculate CAGR between two dates in Excel?
Use the exact length in years as the exponent: =(B2/A2)^(1/YEARFRAC(C2,D2,1))-1, where C2 and D2 are the start and end dates. The 1 counts actual days.
Can you calculate CAGR with a negative number?
Not meaningfully. A start value of 0 gives #DIV/0!, and when the start and end have different signs the formula returns #NUM! or a number that means nothing. Report the change in absolute terms instead.
