CAGR formula in Excel

Business basics4min read

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.3195 means about 31.95%.
  • CAGR measures compounded growth between two endpoints. It does not show the actual growth in each year.
  • Excel's RRI function 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:

  1. Divide the ending value by the starting value.
  2. Raise that ratio to the power of 1/Years.
  3. 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 rowColumn A: YearColumn B: Revenue
22020$500,000
32021$800,000
42022$650,000
52023$1,200,000
62024$1,500,000
72025$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.

CAGRAverage annual growth rate
CalculationCompounded rate between the first and last valuesArithmetic mean of individual yearly percentage changes
Data neededStarting value, ending value and elapsed yearsEach year's growth rate
Best useDescribing growth over the whole periodDescribing the average of observed annual changes
Main limitationHides the path between endpointsDoes 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.

Related terms

Sources

  1. Microsoft Support, "RRI function".
  2. Microsoft Support, "XIRR function".