CAGR Formula in Excel: Compound Annual Growth Rate
· 6 min read
CAGR, the compound annual growth rate, is the steady yearly growth that would take you from a starting value to an ending value. Divide the end by the start, raise it to the power of 1 over the number of years, and subtract 1:
=(end_value/start_value)^(1/years)-1The number of years is the number of periods between the first and last value, not the number of values. Five years of data, 2021 to 2025, is four periods. Getting that wrong is the most common CAGR mistake, so this guide shows it with real numbers. The CAGR formula Excel users write by hand gives the same answer as the built-in RRI function, which we cover below.
The example table
Five years of revenue for a supplies company. Column C has each year's growth, and column F has every formula from this guide, starting with the CAGR in F2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Year | Revenue | Growth | Formula | Result | |
| 2 | 2021 | 480,000 | CAGR, 4 periods | 9.50% | ||
| 3 | 2022 | 552,000 | 15.00% | CAGR, periods from the years | 9.50% | |
| 4 | 2023 | 530,000 | -3.99% | Total growth, not yearly | 43.75% | |
| 5 | 2024 | 612,000 | 15.47% | AI's formula: 5 periods | 7.53% | |
| 6 | 2025 | 690,000 | 12.75% | AAGR: average of C3:C6 | 9.81% | |
| 7 | Check: 2021 grown by CAGR 4 times | 690,000 | ||||
| 8 | 2026 at the same CAGR | 755,529 | ||||
| 9 | 2027 at the same CAGR | 827,281 | ||||
| 10 | 2021 grown by AAGR 4 times | 697,870 |
Worked example
From 2021 to 2025 there are four years of growth (2021 to 2022, 2022 to 2023, and so on).
=(B6/B2)^(1/4)-1Result: 0.09497, or 9.50% when formatted as a percentage.
Step by step:
B6/B2is 690,000 / 480,000 = 1.4375. Revenue grew 43.75% in total.^(1/4)takes the fourth root: about 1.09497.-1turns the growth factor into a rate: 0.09497.
To check it, grow the start by that rate four times: 480,000 × 1.09497^4 comes back to 690,000 (F7 in the sheet).
Let Excel count the periods
Typing 4 works, but it breaks when you add a year. Calculate it from the years instead:
=(B6/B2)^(1/(A6-A2))-1A6-A2 is 2025 − 2021 = 4. If your periods are rows rather than year numbers, ROWS(B2:B6)-1 gives the same 4.
The RRI function
Excel has a built-in function that returns the same rate:
=RRI(4, B2, B6)Result: 9.50%. The arguments are the number of periods, the start value and the end value. RRI is easier to read, while the power formula makes the maths visible. Use whichever your team will recognise.
Growth rate formula in Excel: simple growth vs CAGR
The simple growth rate between two values is the percent change:
=(B6-B2)/B2Result: 43.75%. That is the total growth over four years, not a yearly rate. Year-on-year growth uses the same formula on neighbouring rows, such as =(B3-B2)/B2 for 2022, which gives 15.00%. Our percent change formula guide covers this in detail, including month over month change and what to do with zero starting values.
CAGR answers a different question: "what single yearly rate explains the whole period?"
Average annual growth rate vs CAGR
The average annual growth rate (AAGR) is the simple average of each year's growth. Column C of the sheet has the yearly change, starting in C3 with =(B3-B2)/B2 and filled down: 15.00%, −3.99%, 15.47% and 12.75%. Then average it:
=AVERAGE(C3:C6)Result: 9.81%, higher than the CAGR of 9.50%. AVERAGE gives every year's rate the same weight, as our average formula guide explains, and it ignores the fact that each year grows on a different base. The two only match when growth is the same every year. AAGR is always at least as high as CAGR, and the gap grows when years swing up and down, like the dip in 2023.
Which should you report? CAGR, when you want one rate that links the start and end values. If you grow 480,000 by 9.81% for four years you get about 697,870, not the real 690,000. AAGR is fine as a description of "a typical year", as long as you label it clearly.
Compound growth: projecting forward
CAGR also works in reverse. To project revenue if the same rate continues, raise one plus the rate to the number of years ahead. With the CAGR in F2, as in the sheet:
=B6*(1+$F$2)^1
=B6*(1+$F$2)^2Results: about 755,529 for 2026 and 827,281 for 2027. This is compound growth: each year grows on top of the previous year's growth, not on the original value.
Monthly growth rates
The same formula works for any period length. If revenue was 41,200 in January and 52,800 in December, there are 11 monthly periods between them:
=(52800/41200)^(1/11)-1Result: about 2.28% per month. Again, it is 11 periods, not 12: count the steps between months, not the months.
Common mistakes
Counting values instead of periods. This is the mistake to look for when an AI assistant writes a CAGR. Ask for "the 5-year CAGR" on five years of data, and you may get =(B6/B2)^(1/5)-1. It returns 7.53% instead of 9.50% (compare F5 with F2 in the sheet). The formula looks right, and 7.53% looks like a believable number. Check the exponent: it should be the number of gaps between the first and last value, which for 2021 to 2025 is 4.
Dividing by the wrong value. Growth always divides by the starting value. =(B6-B2)/B6 divides by the end and understates growth. The same applies inside CAGR: end divided by start, not the other way round.
Averaging yearly growth and calling it CAGR. The 9.81% above is an AAGR. If a report says "CAGR", check that it uses only the first and last values.
Negative or zero starting values. CAGR needs a positive start. A start of 0 gives #DIV/0!, and a negative ratio raised to a fractional power gives #NUM!. For profit that changes sign, describe the change in words or in amounts instead.
Forgetting to format as a percentage. 0.09497 and 9.50% are the same value. Format the cell as Percentage with two decimals, or round it as shown in round to 2 decimal places.
FAQ
What is the formula for CAGR in Excel?
=(end/start)^(1/periods)-1, for example =(B6/B2)^(1/4)-1. You can also use =RRI(periods, start, end).
How many years do I use for CAGR?
The number of periods between the first and last value. Data from 2021 to 2025 is 4 periods, not 5.
What is the difference between CAGR and average annual growth rate?
CAGR is the steady rate that links the first and last value. The average annual growth rate is the simple average of each year's change, and it is higher than CAGR whenever growth varies from year to year.
How do I calculate a simple growth rate in Excel?
Use =(new-old)/old and format it as a percentage. That gives the total change between two values, not a yearly rate.
Can I calculate CAGR for months instead of years?
Yes. Use the number of months between the first and last value as the periods. The result is a monthly compound growth rate.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “The AI's growth numbers” →