CAGR Rate Excel Formula: Calculate Annualized Growth

Formulas & functions · Updated November 21, 2026

If B2 contains the beginning value, B3 the ending value, and B4 the number of years, calculate CAGR in Excel with =(B3/B2)^(1/B4)-1. Format the result as a percentage: for example, growth from $10,000 to $16,000 over four years produces a CAGR of about 12.47% per year.

What CAGR Measures

CAGR means compound annual growth rate. It expresses the constant yearly growth rate that would take a starting amount to an ending amount over a stated period, assuming growth compounds. The calculation is useful for summarizing the change in an investment, revenue, sales, website traffic, or another measure across multiple years.

The word “constant” matters. A CAGR is a smoothed rate, not a record of what happened in each individual year. If a balance rises sharply, falls, and then recovers, CAGR compresses that uneven path into one annualized figure. It helps compare start-to-finish growth over equal periods, but does not show volatility or explain the causes of the change.

In effect, CAGR answers this question: what single annual rate, applied repeatedly for the specified number of years, would turn the beginning value into the ending value? The calculation uses the ratio of the ending value to the beginning value, then adjusts that total growth for the number of periods.

Set Up the Excel Calculation

Enter the inputs in separate cells so the formula is easy to audit and update. For the basic example, put the starting value in B2, the ending value in B3, and the elapsed years in B4. With those cells set up, enter =(B3/B2)^(1/B4)-1 in another cell.

For instance, enter 10000 in B2, 16000 in B3, and 4 in B4. The formula returns approximately 0.12468, which represents 12.468% as a rate. Select the result cell and use Home > Number > Percent Style to display it as a percentage. Increase or decrease the displayed decimal places as needed; changing the display does not change the underlying calculation.

The exponent is the reciprocal of the number of years. This adjusts the total growth multiple across all the periods, while subtracting 1 converts the resulting growth factor into a rate. Do not omit the final subtraction: without it, Excel returns the annual growth factor, about 1.12468 in this example, rather than the rate of about 0.12468.

Worked Example with Annual Values

Suppose a business records these year-end values: $10,000 in 2020, $11,000 in 2021, $12,000 in 2022, $14,000 in 2023, and $16,000 in 2024. The first and last observations are five values apart but span four annual periods. If the five values are in B2:F2, use this formula to calculate the CAGR from the first value to the last:

With the 2020–2024 values in B2:F2, enter =(F2/B2)^(1/(COLUMNS(B2:F2)-1))-1. It returns approximately 12.47% per year.

The period count is four, not five, because there are four changes between the five year-end observations. The COLUMNS function counts the observations, and subtracting 1 converts that count into the number of intervals. This pattern is handy when the length of the annual data range may change. Check that every value in the range represents a consecutive year; if a year is missing, counting columns alone will not correctly measure elapsed time.

The example’s actual annual increases are not all 12.47%. They are 10%, about 9.09%, about 16.67%, and about 14.29%. CAGR is the single rate that gives the same overall start-to-finish result when compounded for four years. It is not the arithmetic average of those yearly percentages.

Using Start and End Dates

If the measurement period is not an exact whole number of years, use dates to calculate the duration rather than entering a rounded year count. For example, place the beginning value in B2, the ending value in C2, the start date in B3, and the end date in C3. The following formula uses YEARFRAC with its actual/365 basis to calculate the time between the dates:

With values in B2 and C2 and corresponding dates in B3 and C3, enter =(C2/B2)^(1/YEARFRAC(B3,C3,3))-1. Format the result as a percentage.

YEARFRAC returns a fraction of a year, so the formula can annualize a period that is, for example, 18 months or a little over three years. The basis argument 3 means actual days divided by 365. Different day-count conventions can produce slightly different annualized rates, particularly for periods that include leap days. Use a convention that fits the reporting method you need to follow, and use it consistently when comparing results.

The start date must precede the end date, and the duration must be greater than zero. Make sure Excel recognizes the entries as dates, not text. If the dates are stored as text, YEARFRAC may return an error or an unexpected result; changing the cell display to a date format does not itself convert text into a real date value.

How to Read and Present the Result

A CAGR of 12.47% means that the ending value is equivalent to compounding at roughly 12.47% annually over the chosen period. It does not mean the value actually rose by that percentage in every year. When presenting the figure, identify the beginning and ending dates, the measure being tracked, and the period length so readers can interpret it correctly.

  • Use consistent units: Compare like with like, such as annual revenue to annual revenue, rather than mixing revenue with profit or a monthly figure.
  • Keep the time span visible: A 12% CAGR over two years and the same CAGR over twenty years describe different histories and levels of uncertainty.
  • Retain sensible precision: Two decimal places are often readable, but extra decimals do not make the underlying data more certain.
  • Check the underlying values: Rounding a start or end value before calculating can shift the result, especially when values are close together.

For a reusable worksheet, you can lock the input references so copying the formula does not move them. With the beginning value in B2, ending value in B3, and years in B4, enter =($B$3/$B$2)^(1/$B$4)-1. The dollar signs keep those cell references fixed when the formula is filled elsewhere. This is useful when applying the same inputs to several output cells, though a single CAGR calculation usually does not require absolute references.

Common Errors and Limits

The standard CAGR calculation needs a positive beginning value, a positive ending value, and a positive period length. A zero beginning value makes the division undefined. Negative values generally do not produce a meaningful growth rate for ordinary measures such as revenue or account balances, and fractional exponents of negative ratios can produce Excel errors. If a metric can be zero or negative, consider whether a percentage growth rate is meaningful at all; an absolute change or another measure may communicate the change better.

  • Wrong period count: Count the intervals between observations, not the number of observations. Six annual data points span five years when they cover consecutive year-ends.
  • Inconsistent timing: Do not treat monthly, quarterly, and annual observations as though each interval were one year. Use the elapsed time or convert the period count appropriately.
  • Misread percentage: Excel stores 0.12468 as a decimal rate. Apply percentage formatting rather than multiplying the formula by 100 and then formatting it as a percentage, which would inflate the displayed answer.
  • Non-consecutive observations: If one or more years are missing, the number of gaps may not equal the number of years elapsed. Use the real dates or the true elapsed period.
  • Cash added or removed: Deposits and withdrawals can make a simple comparison of beginning and ending balances misleading because the change is not solely due to growth.

CAGR also says nothing about the path between the endpoints. A series that steadily gains and one that experiences a major decline before recovering can share the same CAGR. For investment performance, that distinction can matter: the annualized rate is a summary, not a measure of risk, drawdown, or the timing of cash flows.

CAGR Compared with Other Measures

Total growth reports the overall change from the beginning to the end, while CAGR expresses that change as a compounded annual rate. In the example, the ending value is 60% above the starting value overall, but 60% is not the annual rate. The CAGR of about 12.47% translates the four-year total change into an annualized figure.

The arithmetic average of annual percentage changes is another different measure: it adds the yearly rates and divides by the number of years. That average does not generally compound to the observed ending value. CAGR is a geometric, endpoint-based measure, so it captures the compounded relationship between those endpoints but leaves out the individual year-by-year sequence.

When there are contributions, withdrawals, or dated cash flows, an internal-rate-of-return measure may be more relevant than simple CAGR. For example, if cash amounts are in A2:A6, corresponding dates are in B2:B6, the initial investment is negative, and later proceeds or contributions have their correct signs, enter =XIRR(A2:A6,B2:B6) to calculate an annualized rate for irregularly spaced cash flows. XIRR requires cash flows that support a solution and can return an error if the values or dates do not allow it to find one. This calculation answers a different question from CAGR, so choose based on what the figures represent.

Frequently Asked Questions

What is the simplest CAGR formula in Excel?
With the beginning value in B2, ending value in B3, and years in B4, enter =(B3/B2)^(1/B4)-1. Format the result as a percentage to read it as an annual rate.

How do I calculate CAGR for five years?
Use five as the period count if the start and end values are exactly five years apart. With beginning value in B2, ending value in B3, and the five-year duration in B4, enter =(B3/B2)^(1/B4)-1. The elapsed time is what matters, not how many rows of data happen to be shown.

Why is my CAGR result showing as a decimal?
Excel stores rates as decimals. A result of 0.12468 is about 12.468%; select the result cell and choose Home > Number > Percent Style. Do not multiply the formula by 100 if you are also using percentage formatting.

Can CAGR be negative?
Yes. If the ending value is positive but lower than the positive beginning value, the standard formula returns a negative annualized rate. A negative result indicates decline over the period; it is not automatically an error.

Is CAGR the same as average annual return?
Not necessarily. CAGR is the constant compounded rate between two endpoints. The arithmetic average of yearly returns averages the individual rates, and it can differ from CAGR when returns vary. Neither figure alone shows the full sequence of gains and losses.

Choosing the Right Calculation

For two positive values measured over a known, consistent period, the basic CAGR formula is usually the clearest choice. If the endpoints fall on uneven dates, calculate the duration from dates and state the day-count convention. If the data has yearly observations, count the intervals carefully. If cash flows occur along the way, use a cash-flow method such as XIRR when that matches the question. In every case, keep the source values, dates, and assumptions visible beside the result so the annualized figure can be checked rather than treated as a standalone forecast.

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.