Calculate CAGR Growth in Excel: Formula and Examples

Formulas & functions · Updated November 18, 2026

To calculate compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning value, raise the result to the power of one divided by the number of years, then subtract 1. With the beginning value in A2, ending value in B2, and elapsed years in C2, enter =(B2/A2)^(1/C2)-1. Format the result as a percentage to read it as an annual growth rate.

CAGR describes the steady yearly rate that would take an initial value to a final value over a specified period, assuming growth compounded at a consistent rate. It is useful for summarizing growth across several years, but it does not show what happened in each individual year. The steps below use Microsoft Excel for Microsoft 365 on Windows; the core formula works in other current Excel editions too.

What You’ll Need

  • A beginning value and an ending value, such as revenue, account balance, units sold, or another measure that can be compared over time.
  • The number of years between those two values. If the dates are not exactly a whole number of years apart, use the date-based option in the steps below.
  • An Excel worksheet. For a valid conventional CAGR, the beginning and ending values should be positive, and the period should be greater than zero.

Step-by-Step

  1. Set up your inputs. Put descriptive labels in row 1 so the worksheet is easy to check later. For example, enter Beginning value in A1, Ending value in B1, Years in C1, and CAGR in D1. Enter 10000 in A2, 16000 in B2, and 5 in C2. These example amounts are illustrative: they represent a value that rises from 10,000 to 16,000 over five years. Do not enter a percent sign in the input cells unless the underlying values themselves are percentages.
  2. Enter the CAGR formula. With the beginning value in A2, ending value in B2, and elapsed years in C2, enter =(B2/A2)^(1/C2)-1 in D2. The division finds the total growth factor, the exponent converts that factor to a yearly compound rate, and subtracting 1 converts the factor to a growth rate. Excel evaluates the example as about 0.09856, or 9.856% before rounding.
  3. Format the result as a percentage. Select D2, then choose Home > Number > Percent Style. The displayed result will usually be 10% at the default zero decimal places. To show more detail, use Increase Decimal on the Home tab until the cell displays 9.86%. The underlying value remains more precise than the display, so other calculations can still use that precision. On Excel for Mac, use Home > Percent Style as well; the ribbon layout may look different. In Excel for the web, select the cell and use the Percent Style button on the Home tab.
  4. Check the answer against the inputs. A 9.86% CAGR compounded for five years should take 10,000 to approximately 16,000. You can verify this with a calculator or a separate worksheet calculation. With the starting value in A2, CAGR in D2, and number of years in C2, the verification formula is =A2*(1+D2)^C2. It returns 16000 when the example inputs are used. This is a check on the calculation, not a second method for defining CAGR.
  5. Use actual dates when the interval is not a whole number of years. Counting calendar years by subtraction can be misleading when the dates fall on different days or the time span includes a leap year. Place the beginning value in A2, ending value in B2, start date in C2, and end date in D2. For values of 10000 and 16000 with dates 1/1/2020 and 1/1/2025, enter =(B2/A2)^(1/YEARFRAC(C2,D2))-1 in E2. YEARFRAC calculates the year fraction between the dates; for these exact five-year dates, the result is about 9.86%. Format E2 as a percentage. Make sure the dates are real Excel dates, not text that only looks like a date.
  6. Apply the calculation to more records if needed. If each row contains a separate beginning value, ending value, and period, enter the formula for the first row and use the fill handle at the lower-right corner of the formula cell to copy it down. For example, with beginning values in A2:A20, ending values in B2:B20, and years in C2:C20, put the formula in D2 using that row’s cells, then fill down. Excel adjusts the row references automatically. Confirm that each row has the right period and that no input cell is blank before relying on the results.
  7. Label the units and the time span. A column heading such as CAGR (annual) is clearer than Growth, because it distinguishes an annualized rate from total growth. Record whether the values are dollars, customers, units, or another measure, and note the start and end dates or year count. This context helps someone reviewing the workbook understand what the percentage means without guessing.

Common Mistakes

Using total growth as if it were CAGR. In the example, the value rises by 60% overall, but its CAGR is about 9.86% per year. Dividing 60% by five gives 12%, which is not the compounded annual rate. CAGR accounts for compounding by taking the appropriate root of the total growth factor.

Reversing the values. The ending value belongs in the numerator and the beginning value in the denominator. Reversing them calculates a rate in the opposite direction and can produce a negative number even when the value grew. Check that the dates and their matching values are in chronological order.

Entering the number of observations instead of the number of intervals. Five annual observations—from the end of 2020 through the end of 2024, for example—span four intervals, not five. Use the elapsed time between the first and last values. If the data covers five complete years, enter 5; if there are five dated annual records but only four years between the endpoints, use the four-year interval or use actual dates.

Using zero or negative starting values. The conventional CAGR formula divides by the beginning value and assumes a positive growth factor. A starting value of zero causes division by zero, and negative starting or ending values do not have a standard real-world CAGR interpretation. Do not try to fix these cases by changing the formula arbitrarily; choose a measure that fits the data, such as absolute change, and explain the limitation.

Rounding the inputs too early. Displaying 9.86% is fine, but replacing the full-precision formula result with a rounded value can slightly change later calculations. Keep the original data and formula precision, and adjust the displayed decimal places for readability.

Tips

Keep the inputs, formula, and result in separate cells rather than typing a long calculation into one cell. This makes the calculation auditable and makes it easier to update a value or period. Adding clear labels and consistent date formats also reduces the chance of mixing up a start value and an ending value.

Use CAGR when you need one comparable annualized summary for a multi-year interval. Pair it with yearly figures or a chart when the path matters. A business could have grown rapidly one year and declined the next while still ending with a positive CAGR; the single rate does not reveal those changes, volatility, or the timing of events.

Compare CAGRs only when the measurement and time spans are meaningfully comparable. For example, revenue growth and customer growth are different measures, and a three-year CAGR should not be presented as though it covered five years. When reporting the result, state the start and end dates, the metric, and the annualized percentage.

If your worksheet uses another currency or unit, the CAGR calculation itself is unchanged as long as both values use the same unit. If values are adjusted or converted between periods, document that treatment separately so readers can understand what the comparison represents.

Troubleshooting

The result shows as a decimal, such as 0.0986. That is the correct underlying rate for 9.86%. Select the result and apply Percent Style, or increase the displayed decimal places if you need a more precise percentage.

Excel displays #DIV/0!. Check that the beginning value is not zero and that the years cell is not zero or blank. If you used dates, check that the start and end dates are valid and not identical. A zero beginning value does not support the conventional CAGR calculation.

The result is negative or unexpectedly large. Confirm that the beginning and ending values are in the correct cells and that the year count is an elapsed period, not a count of records. Check for a misplaced decimal point or a number stored as text. If the ending value is lower than the positive beginning value, a negative CAGR is expected; it represents a compound annual decline.

The formula appears as text instead of calculating. Make sure the entry begins with an equals sign and that the cell is not formatted as Text. Change the format to General or Number, then edit the cell and press Enter to have Excel recalculate it. Also check that Show Formulas is not enabled; on Windows, Ctrl+` toggles formula display. On Mac, the equivalent shortcut is Control+` in many keyboard layouts, though the key position can vary.

The date-based result looks wrong. Select each date cell and check the formula bar to confirm Excel recognizes a date value. If a date was imported as text, re-enter it or convert it to a valid date before using YEARFRAC. Also check that the end date is later than the start date. For a period that includes partial years, YEARFRAC makes the time basis explicit, but the chosen date convention should still match how you intend to describe the period.

Frequently Asked Questions

What is the Excel formula for CAGR? With a beginning value in A2, an ending value in B2, and the number of years in C2, use =(B2/A2)^(1/C2)-1. Format the result as a percentage.

How do I calculate CAGR when I have start and end dates? Put the beginning value in A2, ending value in B2, start date in C2, and end date in D2. Use =(B2/A2)^(1/YEARFRAC(C2,D2))-1. Confirm that C2 and D2 contain valid Excel dates and that the end date is later.

Can CAGR be negative? Yes. If a positive starting value falls to a lower positive ending value over a positive period, the result is a negative annualized rate, indicating compound decline. A zero starting value or negative values are not suitable for the conventional CAGR interpretation.

Is CAGR the same as average annual growth? Not necessarily. CAGR is the constant compound rate connecting the first and last values. The arithmetic average of yearly percentage changes simply averages those individual rates and can differ from CAGR, especially when annual growth varies substantially.

How many decimal places should I show? There is no single required number. Two decimal places, such as 9.86%, are often readable for a worked result, while a report may use fewer. Keep the full precision in the cell and choose display precision based on the context and the accuracy of the original data.

Can I calculate CAGR if the values rise and fall during the period? You can calculate the endpoint CAGR if the beginning and ending values are positive and the period is valid, but it summarizes only those endpoints. It does not account for the intermediate path. Include yearly data or another measure alongside it when the changes between endpoints matter.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.