CAGR formula in Excel
The CAGR formula in Excel is `=(End/Start)^(1/Years)-1`. CAGR means compound annual growth rate: the constant annual rate that would turn a starting value into an ending value over a given number of years.
Key takeaways
- Count elapsed years, not data points. Six year-end values cover five years.
- Format the result as a percentage. A result of
0.3195means about 31.95%. - CAGR measures compounded growth between two endpoints. It does not show the actual growth in each year.
- Excel's
RRIfunction gives the same result:=RRI(Years,Start,End).
How to calculate CAGR in Excel
Replace End, Start and Years with numbers or cell references. The calculation has three steps:
- Divide the ending value by the starting value.
- Raise that ratio to the power of
1/Years. - Subtract 1.
Select the result cell and apply Excel's Percentage format. Do not also multiply the formula by 100, or the displayed percentage will be too large.
Use CAGR for positive starting and ending values. If a business moves from a loss to a profit, report the dollar change instead of trying to express that turnaround as CAGR.
Growing revenue from $500,000 to $2 million
A company records the following annual revenue in Excel. These are full-year figures measured on the same basis, five years apart.
| Excel row | Column A: Year | Column B: Revenue |
|---|---|---|
| 2 | 2020 | $500,000 |
| 3 | 2021 | $800,000 |
| 4 | 2022 | $650,000 |
| 5 | 2023 | $1,200,000 |
| 6 | 2024 | $1,500,000 |
| 7 | 2025 | $2,000,000 |
Enter:
``excel =(B7/B2)^(1/5)-1 ``
The arithmetic is:
``text ($2,000,000 / $500,000)^(1/5) - 1 = 4^0.2 - 1 ≈ 0.319508 ≈ 31.95% ``
Revenue grew fourfold, equivalent to about 31.95% compounded annually. That does not mean revenue rose 31.95% every year. It fell from $800,000 to $650,000 in 2022.
To calculate the year count from column A rather than hard-code it, use:
``excel =(B7/B2)^(1/(A7-A2))-1 ``
This works because column A contains numeric years, not Excel date values. If your cells contain actual dates, subtracting them returns days rather than years.
What Excel's documentation says
Microsoft's documentation for the RRI function describes three inputs: the number of periods, present value and future value. For the same example:
``excel =RRI(5,B2,B7) ``
This also returns about 31.95%. With years as the periods, the result is an annual rate. With months as the periods, it is a monthly rate.
For investment returns, distinguish growth in account value from performance. Deposits and withdrawals change the balance without being investment gains or losses. Excel's XIRR function accounts for dated cash flows; a balance-only CAGR does not.
CAGR versus average annual growth
CAGR is not the arithmetic average of annual growth rates.
| CAGR | Average annual growth rate | |
|---|---|---|
| Calculation | Compounded rate between the first and last values | Arithmetic mean of individual yearly percentage changes |
| Data needed | Starting value, ending value and elapsed years | Each year's growth rate |
| Best use | Describing growth over the whole period | Describing the average of observed annual changes |
| Main limitation | Hides the path between endpoints | Does not reproduce the ending value through compounding |
Dividing total percentage growth by the number of years is not CAGR either. In the example, total growth is 300%. Dividing that by five gives 60%, which is not the compounded annual rate.
Using CAGR in a startup pitch
Label the metric "Revenue CAGR, 2020-2025: 31.95%", not simply "Growth: 31.95%". An investor needs to know the metric and period.
Include recent growth alongside historical CAGR. A strong endpoint calculation can conceal a weak latest year, and a high rate from a small starting base says little about the company's current scale.
Questions people ask
What is the formula for CAGR?
CAGR is (Ending value / Beginning value)^(1 / Number of years) - 1. In Excel, use ^ for the exponent and format the result as a percentage.
What is the formula for calculating CAGR for 3 years in Excel?
For the table above, 2020 to 2023 is three years. Use =(B5/B2)^(1/3)-1. Revenue rises from $500,000 to $1.2 million, so the calculation is 2.4^(1/3)-1, or about 33.89%.
How do I calculate the 5-year CAGR?
Divide the ending value by the value five years earlier, take the fifth root and subtract 1. The example uses =(B7/B2)^(1/5)-1. With yearly values in cells, six values span five years of growth; only the first and last enter the formula.
How do I calculate CAGR manually?
Divide the ending value by the starting value. Take the root corresponding to the number of years, then subtract 1. Multiply by 100 to express the answer as a percentage. For five years, take the fifth root.
