With the starting value in A2, ending value in B2, and number of years in C2, calculate CAGR in Excel with =(B2/A2)^(1/C2)-1. Format the result as a percentage; for example, growth from $10,000 to $15,000 over five years is about 8.45% per year.
What CAGR Measures
CAGR stands for compound annual growth rate. It describes the constant yearly growth rate that would take a beginning value to an ending value over a specified period, assuming growth compounds. It is a useful way to summarize growth across several years with one annualized figure.
The word “constant” matters: CAGR is a mathematical average, not a record of what happened in each individual year. If an investment rose sharply, fell, and later recovered, its CAGR smooths those changes into one rate. It can help compare the overall growth of values measured across the same period, but it does not show volatility, the timing of gains and losses, or the path between the two endpoints.
CAGR is often used for historical comparisons of revenue, sales, account balances, or other quantities that start and end above zero. It is a description of past change, not a forecast or a guarantee that the same rate will continue.
The Basic Excel Formula
The general calculation is the ending value divided by the beginning value, raised to the power of one divided by the number of years, minus one. In the basic worksheet layout—beginning value in A2, ending value in B2, and elapsed years in C2—enter this formula in D2:
=(B2/A2)^(1/C2)-1
Excel’s caret character, ^, means “raise to a power.” The division inside the parentheses gives the total growth multiple. Raising that multiple to the reciprocal of the number of years converts the total change into an equivalent annual rate; subtracting one changes the annual growth multiple into a rate.
For instance, if the ending value is 1.5 times the beginning value after five years, the calculation finds the yearly rate that, compounded five times, produces that 1.5 multiple. The formula uses ordinary worksheet cell references, so you can fill it down for multiple rows when each row has its own beginning value, ending value, and duration.
Build a CAGR Calculator Step by Step
In Excel for Microsoft 365 on Windows, create a small table with labels in row 1 and values in row 2. A practical layout is:
- A1: Beginning value; A2: 10000
- B1: Ending value; B2: 15000
- C1: Years; C2: 5
- D1: CAGR; D2: the formula
With those values in A2:C2, enter =(B2/A2)^(1/C2)-1 in D2. The unformatted result is approximately 0.08447177. To display it as a familiar rate, select D2 and choose Home > Number > Percent Style. Use the increase or decrease decimal buttons in the Number group to show the precision you need. At two decimal places, the displayed result is 8.45%.
The formula itself does not change the underlying value when you apply percentage formatting; formatting only changes how Excel displays it. A result displayed as 8.45% is stored as approximately 0.08447. If you see 0.08 instead, the calculation may be correct but the cell may still use General or Number formatting.
To reuse the calculator for several items, put each item on a separate row and copy the formula down. For example, if the first record is in row 2 and the next record is in row 3, relative references adjust to the new row automatically. Keep the column headings clear and include the unit or currency in the label so values are not compared accidentally when they represent different things.
Worked Example and Arithmetic Check
Suppose a business’s revenue increases from $10,000 to $15,000 in five years, with no need to account for interim additions or withdrawals. The total multiple is 1.5. The fifth root of 1.5 is approximately 1.08447177, so the annual growth rate is approximately 0.08447177, or 8.45%. This is the rate that compounds to the stated ending value, subject to rounding.
To check the result, raise one plus the displayed rate to the number of years and multiply by the starting value. With beginning value in A2, ending value in B2, years in C2, and calculated CAGR in D2, enter this check in E2:
=A2*(1+D2)^C2
For the example, the check returns approximately $15,000. Small differences in the last few cents can occur if the CAGR cell is rounded for display, but Excel still uses the unrounded value in its calculation. Do not round the CAGR inside the main formula unless a particular reporting rule requires it.
Using Dates Instead of a Year Count
If the start and end dates are not exactly a whole number of years apart, using a rounded year count can affect the answer. For a date-based calculation, place the beginning value in A2, the ending value in B2, the start date in C2, and the end date in D2. With valid Excel dates in C2 and D2, use:
=(B2/A2)^(1/YEARFRAC(C2,D2,1))-1
YEARFRAC returns the fraction of a year between the dates; its third argument, 1, specifies the Actual/Actual day-count basis. This approach uses the dates rather than requiring you to decide whether a period such as 18 months should be entered as 1.5 years. Check that Excel recognizes both entries as dates, not text. A date displayed in an unexpected format can still be a real date, but a date stored as text may not calculate correctly.
Date conventions can matter when comparing results across reports or financial products. Different conventions may define a year fraction differently, especially around leap years. For a general historical growth comparison, the date-based calculation is often practical; for a contract, regulated report, or financial product, use the convention specified in the relevant documentation.
When CAGR Is Useful—and When It Misleads
CAGR is useful when you need to compare start-to-end growth over a known span and want a single annualized figure. For example, it can summarize the change in yearly revenue over a multi-year period or compare two historical measures across the same dates. Comparisons are more meaningful when the starting and ending definitions, time span, and measurement units match.
It can mislead if readers interpret the rate as what occurred every year. Consider a value that grows from 100 to 121 over two years. Its CAGR is 10%, whether it went up 10% in each year or followed a very different route and arrived at the same ending value. The endpoint calculation cannot reveal the intermediate results.
CAGR also assumes the measured value can be meaningfully treated as a beginning and ending amount with no intervening cash flows. If an account receives deposits or withdrawals, simply comparing its first and last balance can attribute those cash flows to investment performance. For a series of dated contributions and withdrawals, a cash-flow return measure such as XIRR may be more appropriate. With cash-flow amounts in A2:A7 and their corresponding Excel dates in B2:B7, where the initial outflow is negative and later inflows are positive, use:
=XIRR(A2:A7,B2:B7)
XIRR accounts for the amount and timing of those dated cash flows, so it answers a different question from CAGR. Choose a measure that fits the data and explain the method when reporting the result. This article is about spreadsheet calculations, not financial, tax, investment, or legal advice; check actual terms with a lender or a qualified professional where they apply.
Inputs, Errors, and Common Fixes
The standard CAGR formula requires a positive beginning value, a positive ending value, and a positive elapsed period. Check these common issues if Excel returns an error or a surprising result:
- Zero starting value: The formula divides by the beginning value, so a zero beginning value causes division by zero. A standard CAGR is not defined for that case.
- Zero or negative ending value: A zero ending value can represent a total loss, but negative values generally do not produce a meaningful standard CAGR. Fractional powers of negative ratios can also return an error in Excel.
- Zero or negative duration: The number of years must be greater than zero. Confirm that start and end dates are in the correct order if you calculate duration from dates.
- Text in a numeric cell: Values containing pasted currency symbols, spaces, or other text may not behave as numbers. Use numeric values and apply currency formatting separately.
- Wrong cell references: Confirm that the numerator is the ending value and the denominator is the beginning value. Reversing them changes the meaning and can produce an unexpected rate.
- Percent entered as a whole number: Enter a rate as a decimal when using it elsewhere in a calculation: 8.45% is stored as about 0.0845, not 8.45.
If the beginning value is negative, such as a business measure that starts below zero and later becomes positive, the standard CAGR interpretation may not be valid. Do not force a percentage result simply because a spreadsheet can produce a number; explain the underlying measure or choose a different comparison method.
Formula Versus Excel’s RATE Function
The direct formula makes the relationship between the endpoints and elapsed time easy to inspect. Excel’s RATE function can also calculate a periodic rate when there are regular periods and no payments between the starting and ending amounts. For the same layout—beginning value in A2, ending value in B2, and number of periods in C2—enter:
=RATE(C2,0,-A2,B2)
In this setup, the zero is the payment per period, the starting amount is entered as a negative present value, and the ending amount is the future value. RATE returns approximately 8.45% for the example values. The negative sign follows Excel’s cash-flow sign convention: money paid out and money received use opposite signs. If the periods are years, the result is an annual rate; if the periods are months, it is a monthly rate and should not be described as annual CAGR without converting it appropriately.
For a simple beginning-and-ending comparison, the direct formula is usually easier to audit. RATE is more flexible when a problem includes periodic payments, but its arguments and sign convention can be less obvious. Neither function makes a cash-flow comparison valid if the inputs do not match the assumptions of the calculation.
Frequently Asked Questions
How do I calculate CAGR in Excel?
Put the beginning value, ending value, and number of years in separate cells. With those values in A2, B2, and C2, respectively, enter =(B2/A2)^(1/C2)-1 and format the result as a percentage.
How do I calculate CAGR when I know the dates?
Use the date-based formula with the start and end dates in Excel date cells. YEARFRAC can calculate the elapsed year fraction, avoiding a manually rounded year count. Confirm that your reporting convention supports the selected day-count basis.
Why is my CAGR showing as a decimal?
Excel stores percentages as decimal values, so a result near 0.0845 represents about 8.45%. Select the result cell and use Home > Number > Percent Style to display it as a percentage.
Can CAGR be negative?
Yes. If both endpoint values are positive and the ending value is lower than the beginning value, the calculated rate is negative. It represents an equivalent annual decline, not a yearly record of the actual changes.
Is CAGR the same as average annual return?
Not necessarily. CAGR is the annualized rate implied by the beginning and ending values. An arithmetic average of yearly returns is calculated differently and may not describe the compounded change over the whole period.
Can I use CAGR if I added money during the period?
A basic CAGR calculation does not account for the size or timing of deposits and withdrawals. For dated cash flows, consider a cash-flow measure such as XIRR, and make sure the selected measure fits the question you are trying to answer.
Related
- Best Way to Forecast in Excel: 5 Practical Options
- Best Free Excel Budget Templates for Monthly Planning
- Basic Project Timeline Template in Excel: A Simple Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Using VLOOKUP to Compare Two Sheets in Excel Step by StepTo compare records on two Excel sheets, use VLOOKUP with an exact match and test whether it finds…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- Excel Table Formulas: Do They Fill Down Automatically?Yes. In Microsoft Excel for Microsoft 365 on Windows, a formula entered in a table column normally fills…
- How to Copy a Formula Horizontally in ExcelTo copy a formula horizontally in Excel, select the cell that contains it and drag the small fill…
- Flip Names in Excel in the Same Cell With a FormulaTo flip a name such as John Smith to Smith John in Excel for Microsoft 365, enter a…
- Excel: How to Substitute Multiple Text ValuesIn Excel, substitute multiple text strings by nesting SUBSTITUTE functions, one for each change. If A2 contains “The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.