What CAGR Is and Why You'd Calculate It in Excel

CAGR stands for Compound Annual Growth Rate. It tells you the average rate at which something grew each year over a period of years. If you invested $1,000 in 2015 and it was worth $1,500 in 2020, CAGR shows you the steady yearly growth rate that would get you from $1,000 to $1,500 in that five-year span.

Excel doesn't have a built-in CAGR function, but the formula is straightforward once you know the pieces. You need three numbers: the starting value, the ending value, and the number of years between them. The formula itself is straightforward algebra—Excel just does the math faster and more reliably than a calculator.

People use CAGR to compare investments, track business revenue growth, measure population changes, or understand how fast a savings account grew. It smooths out the bumps in year-to-year changes and shows you the overall trend.

Key Takeaways

  • The CAGR formula in Excel is =(Ending Value/Beginning Value)^(1/Number of Years)-1, and you enter it as =((E2/B2)^(1/C2))-1 if your values are in cells B2, E2, and C2.
  • You need exactly three pieces of data: the starting amount, the final amount, and how many years passed between them.
  • The result appears as a decimal—multiply by 100 to see it as a percentage, or format the cell as a percentage in Excel.
  • CAGR assumes steady growth each year; it ignores the actual ups and downs that happened in between.

Setting Up Your Data in Excel

Start by opening a blank spreadsheet and entering your three values in separate cells. Put the beginning value in one cell (for example, B2), the ending value in another (E2), and the number of years in a third (C2). Label each column so you don't lose track—"Starting Amount," "Years," "Ending Amount" work fine.

Make sure your beginning and ending values are actual numbers, not text. If you copied them from somewhere else, Excel sometimes stores them as text by mistake, and the formula will return an error. The number of years should be a whole number or a decimal if you're measuring partial years—for instance, 5.5 for five and a half years.

If your data spans from January 2015 to December 2020, that's six calendar years but five full years of growth (2015 to 2016, 2016 to 2017, and so on). Count carefully—off by one year changes your result noticeably.

Entering the CAGR Formula

Click on an empty cell where you want the result to appear. Type the formula exactly as it is: =((E2/B2)^(1/C2))-1, but replace E2, B2, and C2 with the actual cell references where your data sits.

The formula works like this: divide the ending value by the beginning value (E2/B2), raise that result to the power of one divided by the number of years (^(1/C2)), then subtract 1. The parentheses matter—they tell Excel the order to do the math. Without them, you get the wrong answer.

Press Enter. Excel calculates the result and shows it as a decimal. If your CAGR is 0.0847, that means 8.47% annual growth. If it shows a negative number like -0.0234, that's a 2.34% annual decline.

Converting the Result to a Percentage

The decimal format is correct, but percentages are easier to read. You have two options. The quickest is to select the cell with your result, then click the percentage button (%) in the toolbar. Excel multiplies the decimal by 100 and adds the % symbol.

If the percentage button shows too many decimal places (like 8.47123%), right-click the cell, choose "Format Cells," click the "Number" tab, select "Percentage," and set the decimal places to 2. That gives you 8.47%.

The other option is to multiply the formula result by 100 inside the formula itself: =((E2/B2)^(1/C2))-1)*100. This gives you the percentage as a number rather than a formatted percentage, but both approaches show the same growth rate.

A Real Example: Calculating Investment Growth

Say you invested $5,000 on January 1, 2018, and it grew to $7,500 by January 1, 2023. That's five years of growth. Put 5000 in cell B2, 7500 in cell E2, and 5 in cell C2. Enter the formula =((E2/B2)^(1/C2))-1 in cell F2.

Excel calculates (7500/5000) = 1.5, then raises 1.5 to the power of (1/5) = 0.2, which gives 1.0845. Subtract 1 and you get 0.0845, or 8.45% CAGR. Your investment grew an average of 8.45% per year over those five years.

If you format the result as a percentage, it displays as 8.45% instead of 0.0845. Both numbers are the same—just different ways of showing it. The percentage format is usually clearer when you're comparing multiple investments or showing results to someone else.

Common Mistakes and How to Avoid Them

The most common error is counting the years wrong. If you have data from 2018 to 2023, that looks like five years, but check whether you mean five calendar years or five years of actual growth. If you started on June 1, 2018, and ended on June 1, 2023, it's exactly five years. If you started January 1, 2018, and ended December 31, 2022, it's only five calendar years but closer to 4.92 years of growth.

Another mistake is forgetting the parentheses around (1/C2). If you type =((E2/B2)^1/C2)-1 instead, Excel calculates (E2/B2)^1 first, then divides by C2, which gives the wrong answer. The caret (^) symbol means "raise to the power of," and the parentheses control which operation happens first.

If your result shows #DIV/0! or #VALUE!, check that your cells contain numbers, not text. Click each cell and look at the formula bar at the top—if the value is left-aligned, it's stored as text. Re-enter it or use the VALUE function to convert it.

When CAGR Doesn't Tell the Whole Story

CAGR assumes steady growth every single year, but real investments bounce around. If your account grew 20% one year and fell 5% the next, CAGR gives you the average, but it hides the volatility. A stock that swings wildly but ends up at the same place as a steady bond fund will show the same CAGR, even though the risk is very different.

CAGR also doesn't account for money you added or withdrew along the way. If you invested $5,000 in year one and another $10,000 in year three, a straightforward CAGR calculation ignores that second deposit. For investments where you add money regularly, a more complex calculation called the Internal Rate of Return (IRR) is more accurate, but that's beyond a basic CAGR formula.

Use CAGR as a starting point to understand growth over time, but pair it with other information—like how much the value fluctuated year to year, or what the actual returns were in each individual year—to get the full picture.

Frequently Asked Questions

Can I use CAGR for data that spans less than one year?

Yes, but the result is less meaningful. If you measure growth over six months, enter 0.5 as the number of years. The formula still works mathematically, but CAGR is designed for longer periods. Over very short timeframes, a single big change can distort the annual rate.

What if my beginning value is negative or zero?

CAGR doesn't work well with negative starting values. If you started with a debt of -$1,000 and ended with a profit of $1,000, the math breaks down because you're dividing by a negative number. For situations like that, describe the change in absolute dollars rather than as a growth rate.

How do I calculate CAGR for multiple investments at once?

Set up each investment in its own row with beginning value, ending value, and years. Enter the CAGR formula in a new column for the first investment, then copy the formula down to the other rows. Excel automatically adjusts the cell references (B2 becomes B3, B4, and so on), so you get the CAGR for each investment without retyping.

Does the order of my data matter—beginning value first or ending value first?

Yes. The formula divides ending value by beginning value, so swapping them gives you the reciprocal, which is wrong. Always put the earlier amount in the numerator (top of the division) and the later amount in the denominator (bottom). If you're unsure, label your columns clearly so you don't mix them up.