How to Calculate CAGR in Excel for 3 Years

How-to basics · Updated November 18, 2026

To calculate a three-year compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning value, raise the result to the power of one-third, then subtract 1. With the beginning value in B2 and the ending value in B5 exactly three years later, enter =(B5/B2)^(1/3)-1 in another cell and format it as a percentage. You will need the two values and a clear understanding of the time between them; CAGR describes a smoothed annual rate, not the actual path taken each year.

What You’ll Need

  • A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The calculation also works in Excel for Mac and Excel for the web.
  • A positive beginning value and a positive ending value, such as revenue, sales, or an account balance. Use values measured on the same basis and in the same units.
  • The number of years between the beginning and ending values. For a full three-year period, use 3, even if your table contains four year-end observations.
  • Optional annual observations for the intervening years. They help you check the data, but the basic CAGR calculation uses only the first and last values.

Step-by-Step

  1. Set up your values and dates. In a blank worksheet, enter the years in column A and the corresponding values in column B. For example, enter 2021 and 10000 in A2 and B2, then 2022 and 11000 in A3 and B3, 2023 and 12100 in A4 and B4, and 2024 and 13310 in A5 and B5. This represents three elapsed years: 2021 to 2022, 2022 to 2023, and 2023 to 2024. There are four observations, but only three annual periods. If your data has actual dates rather than year labels, check that the start and end dates really span three years.

  2. Check the beginning and ending values. Confirm that B2 is the value at the start of the period and B5 is the value at the end, not the other way around. Make sure both values are numeric, positive, and comparable. For example, if the first number is monthly revenue but the last is annual revenue, the result will not be meaningful until you put them on a consistent basis. If values include currency symbols, Excel may still store them as numbers when you apply currency formatting; symbols typed into a cell as text can prevent calculation.

  3. Choose a result cell and enter the CAGR formula. With the beginning value in B2, the ending value in B5, and exactly three years between them, click B6 and enter =(B5/B2)^(1/3)-1. Press Enter. Excel calculates the total growth factor, converts it to an equivalent annual compounded rate, and subtracts 1 to express that rate as growth rather than as a multiplier. In the example, 13310 divided by 10000 is 1.331; the cube root is 1.1; subtracting 1 gives 0.1, or 10% per year. This is the expected result.

  4. Format the result as a percentage. Select B6, then choose Home > Number > Percent Style. Excel displays 0.1 as 10%. To show more detail, use the Increase Decimal button in the Number group on the Home tab. A result displayed as 10.00% is the same rate as 10%; the extra digits are just a display choice. You can also format a selected cell with Ctrl+1 on Windows and select Percentage in the Format Cells dialog. On Mac, use Command+1 to open Format Cells.

  5. Verify the period count. Count the elapsed intervals between the starting and ending observations, rather than counting the rows. For observations at the ends of 2021, 2022, 2023, and 2024, the elapsed time is three years. If your observations instead cover 2021 through 2023, the elapsed time is two years, so using one-third would understate the annualized rate. If your dates are irregular, first determine the elapsed period in years or use an appropriate date-based calculation; do not assume that three rows automatically mean three years.

  6. Compare the result with the data. The example increases by 10% each year: 10000 becomes 11000, then 12100, then 13310. Those annual values confirm a 10% CAGR. Real data can rise and fall and still have the same CAGR, so the rate does not prove that the value grew by the same percentage in every individual year. Compare it with the annual figures before using it to summarize a trend.

  7. Label the output. Add a label such as Three-year CAGR and include the start year, end year, and units nearby. A clear label could read Revenue CAGR, 2021–2024, 10.0%. If someone else will use the workbook, identify whether values are in dollars, thousands, units sold, or another measure. That context helps prevent a correct percentage from being mistaken for a different period or metric.

Common Mistakes

Counting observations instead of elapsed years: Four annual observations from 2021 through 2024 contain three periods. The exponent must reflect the number of periods, not the number of rows or data points. This is one of the most frequent reasons a CAGR result differs from a calculator or another spreadsheet.

Using the wrong order: The ending value belongs in the numerator and the beginning value in the denominator. Reversing them calculates the rate of change in the opposite direction. Check the cell references in the formula bar if the result seems unexpectedly negative or large.

Forgetting to subtract 1: The power calculation alone returns a growth factor. In the example, that factor is 1.1, meaning the ending value is equivalent to 1.1 times the start each year. Subtracting 1 converts it to a growth rate of 0.1, which displays as 10%.

Using a zero or negative starting value: CAGR relies on a meaningful positive ratio of ending value to beginning value. A beginning value of zero requires division by zero, and a negative value can lead to an invalid result or a rate that does not have the usual growth interpretation. Do not force a number by changing the source data; choose a measure suited to the situation or explain the change separately.

Mixing units or relying on rounded inputs: A value in thousands cannot be compared directly with a value in dollars unless both are converted to the same scale. Likewise, calculate from the most precise values available and format the output for display afterward. Rounding the inputs first can slightly change the result.

Confusing CAGR with an investment outcome: CAGR is a historical annualized summary. It smooths changes between two endpoints and does not show volatility, fees, taxes, or what may happen in the future. Treat it as a descriptive calculation, not a promise or forecast.

Tips

Keep the start value, end value, and period count easy to identify. For a one-off calculation, direct references such as B2 and B5 are simple. In a workbook that will be reused, add a separate cell for the number of years and reference it in the formula, so updating the period does not require editing the formula itself. Label that cell clearly and verify it whenever the date range changes.

Preserve the underlying values and apply number formats instead of typing commas, currency signs, or percent signs into numeric entries. Excel can then calculate normally. A percentage with one or two decimal places is usually readable, but the best display precision depends on the size and purpose of the data. Avoid implying that extra decimal places make the estimate more certain.

Keep annual values alongside the CAGR when possible. A chart or small table can reveal a sharp dip, a one-time spike, or a flat period that a single annualized figure hides. If the data is about money, this guide is a mathematical explanation, not financial, tax, or investment advice. Check the underlying records and any relevant real-world terms with a qualified professional where appropriate.

For exactly three years, the exponent is one-third even if the period includes a leap year, provided the dates represent the same calendar date three years apart. For dates that do not span an exact number of years, decide on a consistent year-count convention before annualizing. Financial reporting and analysis can use different conventions, so document the choice instead of silently treating a partial year as a full year.

Troubleshooting

Excel shows #DIV/0!: The beginning-value cell is probably zero or blank. Confirm that the correct cell contains a positive numeric starting value. If the underlying starting value truly is zero, a conventional CAGR cannot be calculated from that starting point; report the change using another clearly described measure rather than substituting an arbitrary number.

Excel shows #NUM! or a strange result: Check for negative values, a negative ratio raised to a fractional power, or incorrect cell references. Confirm the end value is positive and the beginning value is positive. Also check whether the formula is pointing at a heading or a blank cell instead of a number.

The answer displays as 0.1 instead of 10%: The calculation may be correct; the cell is simply formatted as a decimal. Select it and use Home > Number > Percent Style. If you see 1000% instead of 10%, inspect the formula for an extra multiplication by 100. When using percentage formatting, leave the formula result as a decimal fraction.

The result differs from an online calculator: Compare the exact start and end values, the elapsed time, and the number of periods used. Check whether the other calculation uses three full years or a day-based year fraction, and whether either result was rounded. Also make sure a value entered as text has not been omitted or misread. For a nonstandard date range, agreeing on the time convention often resolves the discrepancy.

The formula appears as text instead of calculating: Make sure it begins with an equals sign and the cell is not formatted as Text. Change the cell format to General, then re-enter the formula. If Excel uses a leading apostrophe before the equals sign, remove it. In Excel for the web or Mac, the formula itself is the same; ribbon placement can vary slightly, but cell formatting controls are still available.

Frequently Asked Questions

Why are there four yearly values in a three-year CAGR?
Annual observations at the start and end of a three-year span include both endpoints. From 2021 to 2024 there are four year labels, but the elapsed intervals are 2021–2022, 2022–2023, and 2023–2024: three years.

Can I calculate CAGR using only the first and last values?
Yes. The standard CAGR calculation needs the beginning value, ending value, and elapsed time. Intermediate values are not part of the formula, although reviewing them is useful because CAGR hides the path between the endpoints.

Is CAGR the same as the average annual growth rate?
No. CAGR is the constant compounded rate that connects the start and end values over the chosen period. The arithmetic average of each year’s growth rates can be different, especially when yearly changes vary substantially.

What if the period is not exactly three years?
Use the actual elapsed time under a clearly selected convention rather than automatically using 3. For example, a period of two years and six months is not three full years. Be consistent when comparing rates, because different time conventions can produce different annualized results.

Does a 10% CAGR mean the value grew by 10% every year?
No. It means the start and end values are equivalent to a steady 10% annual compound increase. The actual annual changes may have been higher, lower, or negative in some years.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.