To calculate 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 one. If the beginning value is in B2, the ending value is in B3, and the number of years is in B4, enter =(B3/B2)^(1/B4)-1 in an empty cell and format the result as a percentage.
CAGR means compound annual growth rate. It expresses the constant yearly rate that would turn a starting value into an ending value over a specified period. The calculation is useful for comparing growth across different time spans, but it is an average rate—not a record of what happened in each year.
What CAGR Measures
CAGR answers a specific question: what annual rate, compounded each year, would connect these two values over this many years? It condenses a multi-year change into one comparable percentage. For example, a business can use it to summarize revenue growth, while an investor may use it to describe the change in an asset’s value over a period.
The word “compound” matters. Each year’s growth is applied to the value reached in the previous year, rather than being calculated only on the original amount. CAGR is therefore different from dividing the total percentage change by the number of years. If a value rises from 100 to 150 over three years, the total increase is 50%, but the compound annual rate is less than 50% divided by three because the annual increases build on one another.
CAGR is a descriptive measure, not a forecast or a promise of future performance. It also does not show volatility. Two investments, companies, or projects can have the same CAGR even if one grew steadily and the other experienced large gains and losses along the way.
The CAGR Formula in Excel
The mathematical formula is (ending value ÷ beginning value) raised to the power of (1 ÷ number of years), minus 1. Excel uses the caret character ^ for exponentiation. There is no dedicated CAGR function in standard Excel; the calculation is built from ordinary arithmetic.
With the beginning value in B2, the ending value in B3, and the number of years in B4, enter this formula in another cell:
=(B3/B2)^(1/B4)-1
Excel evaluates the ratio first, raises it to the fractional power, and subtracts 1. The result is a decimal: for example, 0.08 represents 8%. Apply percentage formatting to make the result easier to read. The equivalent function-based version uses POWER, though the caret formula is shorter.
With the beginning value in B2, ending value in B3, and years in B4, the equivalent formula is:
=POWER(B3/B2,1/B4)-1
Set Up the Calculation Step by Step
A small, clearly labeled table helps prevent the most common mistake: using the wrong number of years. Put the starting and ending values in separate cells, and record the period length independently. For a four-year period with annual observations at the start and end, the elapsed time is four years, not the number of observations minus one by default in every possible data setup. Check the dates or reporting periods that define the interval.
- Enter the beginning value in one cell, such as B2, and label it clearly.
- Enter the ending value in B3. Make sure it measures the same thing as the beginning value, using consistent units and definitions.
- Enter the elapsed period in years in B4. Use a positive number, such as 4 for four years.
- In a separate result cell, enter
=(B3/B2)^(1/B4)-1. - Select the result cell and choose Home > Number > Percent Style. Increase the displayed decimal places if the comparison needs more precision.
The formula works the same way in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. If a workbook uses a regional setting that expects semicolons instead of commas, that does not affect this formula because it contains no function arguments separated by commas.
Worked Example: From 10,000 to 16,000
Suppose a value increases from 10,000 to 16,000 over exactly four years. Put 10,000 in B2, 16,000 in B3, and 4 in B4. With those cells laid out as described, enter:
=(B3/B2)^(1/B4)-1
The calculation is (16,000 ÷ 10,000) raised to 1/4, minus 1. The ratio is 1.6; its fourth root is approximately 1.1247. Subtracting 1 gives approximately 0.1247, or 12.47% when displayed as a percentage. That means a constant annual compound rate of about 12.47% would turn 10,000 into 16,000 in four years.
This does not mean the value actually grew by 12.47% in each of those years. It could have risen by different amounts, stayed flat for a year, or even fallen temporarily. CAGR uses only the start, end, and duration. If you want to inspect the actual year-to-year pattern, calculate each year’s change separately or chart the annual values.
Using Dates Instead of a Typed Year Count
When the period does not span a whole number of years, calculating the duration from dates can be more consistent than entering a rounded year count. Excel’s YEARFRAC function returns a year fraction between two dates. Its basis argument controls the day-count convention; basis 1 uses the actual number of days and an actual/actual convention.
With the beginning date in A2, ending date in A3, beginning value in B2, and ending value in B3, use:
=(B3/B2)^(1/YEARFRAC(A2,A3,1))-1
Enter real Excel dates in A2 and A3, not text that only looks like a date. The formula then annualizes the change over the fractional period returned by YEARFRAC. Different day-count conventions can produce slightly different annualized results, especially for periods involving leap years. For routine reporting, use the convention required by your organization or analysis and apply it consistently.
For clean anniversaries exactly four years apart, a typed duration of 4 is often transparent and sufficient. For irregular dates, using YEARFRAC makes the date logic visible in the workbook and avoids silently treating, for example, 18 months as exactly two years.
Format and Interpret the Result
Excel stores a percentage as a decimal fraction. A result of 0.1247 is 12.47%; applying Percent Style changes how it is displayed, not the underlying calculation. Avoid multiplying the formula by 100 and then applying percentage formatting, because that would display a value one hundred times too large. If a report requires a numeric percentage rather than percentage formatting, document that choice clearly.
Choose the number of decimal places based on the purpose. One or two decimal places are usually easier to compare than a long string of digits, but retain more precision in the underlying cell. Rounding the formula itself can slightly alter later calculations. Use the original result for further calculations and round only for presentation when possible.
When comparing CAGRs, make sure the values are measured on the same basis and use comparable periods. Comparing nominal revenue growth with inflation-adjusted growth, or a price-only investment return with a total-return series that includes distributions, can be misleading. The calculation is only as meaningful as the inputs and definitions behind it.
Limits and Assumptions to Keep in Mind
CAGR assumes a smooth, equivalent compounding rate between the endpoints. Actual results may be uneven, and the metric does not reveal the sequence of gains and losses. This matters particularly when comparing volatile series: ending at the same value does not mean the path or risk was the same.
The standard calculation also assumes a positive beginning value and a positive ending value. If the beginning value is zero, the ratio involves division by zero and CAGR is undefined. If the beginning or ending value is negative, the conventional CAGR interpretation generally does not apply; Excel may return a numeric error for a fractional exponent of a negative ratio. For a business moving from losses to profits, a margin or another measure may describe the change more appropriately than CAGR.
CAGR also ignores intermediate cash flows. If money is added to or withdrawn from an account during the period, comparing only its first and last balances does not isolate the performance of the underlying investment. A time-weighted return or a cash-flow-sensitive measure such as IRR may better fit that question. These measures answer different questions, so choose based on what the values represent.
Finally, CAGR is a calculation, not financial advice. A result based on historical values does not establish what will happen next. Check the source data and the assumptions relevant to the particular analysis.
Common Excel Errors and How to Fix Them
- Wrong period length: Count elapsed time between the start and end points, not just the number of rows. If the first and last annual observations are five years apart, entering four as the duration will overstate the calculated rate.
- Reversed values: The ending value belongs in the numerator and the beginning value in the denominator. Reversing them changes the answer and can turn growth into a decline.
- Zero or negative inputs: Check for blanks, zero starting values, and negative values. These can make the conventional calculation undefined or produce an error.
- Inconsistent units: Do not compare a beginning value in thousands with an ending value in full units. Ensure both values use the same currency, scale, and measurement basis.
- Percentage confusion: Enter 12% as 12% or 0.12, not as 12 if the cell is meant to store a percentage. In the CAGR formula, the answer is already a decimal proportion; apply percentage formatting rather than multiplying by 100.
- Misread result: A positive CAGR describes the annualized endpoint change, not a guaranteed annual increase. Review the underlying observations if consistency or volatility matters.
When to Use CAGR Instead of Other Growth Measures
Use CAGR when the goal is to summarize the compound change between two positive endpoints across a known period. It is especially handy for comparing different-length periods because it converts both into an annualized rate. It can summarize revenue, customer counts, production, or an investment value, provided the measure is defined consistently.
Use year-over-year growth when you need to see changes in specific years. That measure reveals acceleration, deceleration, and declines that CAGR smooths away. Use an arithmetic average of annual returns only when that is the statistic you intend to report; it is not generally interchangeable with a compound annual rate. For a series with contributions or withdrawals, consider whether a cash-flow-aware return measure is more appropriate.
These methods are not competitors with one universally correct answer. They answer different questions: CAGR describes the equivalent endpoint rate, yearly growth describes individual intervals, and cash-flow-based measures incorporate the timing and size of transactions.
Frequently Asked Questions
Does Excel have a built-in CAGR function?
No. Excel does not provide a standard function named CAGR. Use the arithmetic formula with exponentiation, or use POWER for the same calculation. Both approaches work in Microsoft 365 and current desktop and web editions of Excel.
How do I calculate CAGR over three years?
Divide the ending value by the beginning value, raise that ratio to the power of 1/3, and subtract 1. In a worksheet, use the general formula with the year-count cell set to 3. Confirm that the dates really are three years apart.
Can CAGR be negative?
Yes. If both endpoint values are positive and the ending value is lower than the beginning value, the result is negative, indicating an annualized decline. A negative CAGR does not mean the value fell by the same percentage in each year.
Why is my CAGR formula returning an error?
Check that the beginning value is not zero, the period is positive, and the ratio used with a fractional exponent is valid. Also check for text in cells that should contain numbers or dates. Negative values are usually not suitable for conventional CAGR.
Should I use CAGR or average annual growth?
Use CAGR to express the compounded rate implied by the start and end values. Use average annual growth when you need an average of separately calculated yearly changes, while recognizing that an arithmetic average and a compound rate can differ. State which measure you used.
Related
- Accounting Year-End Checklist Template for Excel Users
- 3-Way Forecast Excel Template: 3 Models to Compare
- Excel Borders: How to Add, Change, and Remove Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter by Year in Excel: A Clear, Practical GuideTo filter a date column to one calendar year in Excel, select your data, choose Data > Filter,…
- Best Way to Interpolate in Excel: Formulas for 5 CasesFor most workbooks, the best way to interpolate in Excel is piecewise linear interpolation: use the two data…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
- Excel Text Filter Options: A Practical GuideExcel’s text filters let you show rows whose text matches a condition: choose options such as Equals, Begins…
- How to Find Variance in Excel: Formulas and ExamplesTo find variance in Excel, enter =VAR.S(B2:B6) for a sample or =VAR.P(B2:B6) for an entire population, replacing the…
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.