CAGR Return in Excel: Calculate Investment Growth

How-to basics · Updated November 18, 2026

To calculate CAGR in Excel, divide the ending value by the beginning value, raise the result to the power of one divided by the elapsed years, then subtract 1. For a date-based calculation, put the start and end dates beside the corresponding values and use Excel’s YEARFRAC function to measure the time between them. Format the result as a percentage to read it as an annualized growth rate.

What You’ll Need

  • A beginning value and an ending value that are both greater than zero. These might be account balances, sales figures, or another measure that grew over time.
  • The dates that match those values, or a reliable count of the number of years between them.
  • Microsoft Excel for Microsoft 365 on Windows. The formulas also work in current Excel for Mac and Excel for the web; ribbon labels or layout may vary slightly.
  • A clear understanding of what the values include. For an investment, decide whether the figures include reinvested distributions, contributions, fees, or other adjustments before calculating.

Step-by-Step

  1. Set up the data. Enter the starting date in A2 and the beginning value in B2. Enter the ending date in A3 and the ending value in B3. For a worked example, use January 1, 2020 and 10000 in row 2, then January 1, 2024 and 14641 in row 3. Enter the dates as real Excel dates, not text that merely looks like a date. You can check by changing the cell format: a real date will display differently when you apply another date format.
  2. Check the time interval and values. Confirm that the earlier date and its value are on the first row, and the later date and its value are on the next row. Both values must be positive for the standard CAGR calculation. In the example, four years elapse from January 1, 2020 to January 1, 2024. The end value is 1.4641 times the beginning value, so the annualized result should be a positive rate.
  3. Enter the date-based CAGR formula. With the starting date in A2, ending date in A3, beginning value in B2, and ending value in B3, enter this formula in C2: =(B3/B2)^(1/YEARFRAC(A2,A3,1))-1. The final argument, 1, tells YEARFRAC to use its actual/actual day-count basis. For the example dates and values, the result is 0.10, equivalent to 10% per year. The calculation is (14641/10000) raised to the power of one quarter, minus 1; the fourth root of 1.4641 is 1.1.
  4. Format the result as a percentage. Select C2, then choose Home > Number > Percent Style on the Windows ribbon. The cell will display 10% rather than 0.1. Increase or decrease the number of decimal places with the decimal-place controls in the Number group if you need a more precise display, such as 10.00%. Formatting changes how the result appears; it does not change the underlying calculation.
  5. Use a year count when periods are already known. If your data covers an exact whole number of years, you can use a separate years cell instead of dates. With the beginning value in B2, ending value in B3, and number of years in C2, put this formula in D2: =(B3/B2)^(1/C2)-1. If B2 is 10000, B3 is 14641, and C2 is 4, the result is again 10%. This version is useful for annual reports or examples where the interval is explicitly four years; do not enter the number of observations in place of elapsed years unless those numbers truly match.
  6. Check the direction and scale. A positive CAGR means the ending value is higher than the beginning value; a negative CAGR means it is lower. If the value has doubled over a span of several years, the CAGR should be positive but usually less than 100%. Compare the formula result with the start and end values before relying on it. A result such as 1000% may indicate that Excel treated a percentage input as a whole number, or that the dates or units are wrong.
  7. Label the result and save the workbook. Add a label such as “CAGR” or “Annualized growth” near the result, and note the period being measured. If the workbook will be shared, retain the original values and dates so another person can trace the calculation. For financial comparisons, also note whether the values include income, deposits, withdrawals, costs, or other adjustments; a percentage alone does not explain what was measured.

CAGR is a compound annualized rate: it answers what steady yearly growth rate would turn the beginning value into the ending value over the selected period. It does not claim that the value actually rose by the same percentage every year. This distinction matters when results were uneven, such as a rise followed by a fall. For an illustrative calculation only, not investment advice, check actual product terms and consult a qualified professional when you need advice about a real investment.

Common Mistakes

  • Using the wrong number of years. Counting calendar labels can misstate the elapsed time. A value measured on December 31, 2020 and another on January 1, 2024 are not exactly four years apart. Use the actual dates with YEARFRAC, or use a year count that matches the period being compared.
  • Mixing up percentage values and decimals. Excel stores 10% as 0.10. If a source says 10% and you type 10 into the value cells, you have entered ten times the intended amount. Make sure the start and end values use the same units and scale.
  • Putting dates beside the wrong values. If the start date is paired with the ending balance, the time and value inputs no longer describe the same period. Keep each date and its measurement together on the same row, then confirm that the dates are in chronological order.
  • Assuming CAGR describes the path. CAGR uses only the beginning value, ending value, and period. It hides interim ups and downs. Two sequences can have the same CAGR even if one grew steadily and the other suffered a large decline before recovering.
  • Applying the formula to cash flows as if they were growth. Regular additions or withdrawals change an account balance independently of investment performance. A basic CAGR between two balances does not isolate the return on money invested at different dates. Consider a cash-flow-aware measure such as XIRR when the question involves dated deposits and withdrawals.

Tips

  • Keep original values in separate cells and put the formula in another cell. This makes it easier to audit the calculation and update it when new data arrives.
  • Use the same definition of value at both endpoints. For example, comparing revenue at the beginning with profit at the end will not produce a meaningful growth rate.
  • Use enough decimal places when comparing close results. Two values that both display as 10% might differ when shown as 9.96% and 10.04%.
  • State the measurement period alongside the rate, such as “CAGR, January 2020–January 2024.” This prevents an annualized rate from being mistaken for the total change over the full period.
  • For a simple exact-year calculation, the year-count version is easy to review. For dates that do not line up with whole years, the date-based version makes the chosen time span explicit.
  • Remember that Excel stores a percentage as a decimal. You can use the unformatted decimal in further calculations, even when the cell displays a percent sign.

Troubleshooting

The formula returns #DIV/0!. Check that the beginning value is not zero and that the two dates are not identical. The formula divides by the beginning value, and YEARFRAC returns no usable period when the dates do not span time. Enter the correct positive starting value and a later end date.

The formula returns #NUM! or an unexpected error. Check that the dates are valid Excel date values, that the ending date is later than the starting date, and that both values are positive. Text dates can look correct while Excel treats them as text; re-enter them using a date format or convert them to real dates. Also check for blank cells or misplaced references.

The answer looks far too large or too small. Confirm that you used 1 as the exponent numerator in the years-based formula, and that the years cell contains elapsed years rather than months or a count of rows. Check for inconsistent units, such as entering 10000 for the start and 14.641 for the end when the latter should be 14641. If the result displays as 0.1, apply percentage formatting rather than multiplying the formula by 100.

The rate is negative even though the result seems to have grown. Verify that the start and end values are referenced in the right order and that the values belong to their displayed dates. A reversed ratio changes the outcome. If the ending value is genuinely below the beginning value, a negative CAGR is expected.

YEARFRAC gives a result that differs from a published figure. The source may use a different year-count convention, rounded dates, or a period definition that is not based on exact dates. Confirm the source’s methodology. If it specifies an exact number of years, use that number in the year-count formula instead of silently changing the date basis.

Frequently Asked Questions

Is CAGR the same as average annual return?

No. CAGR is the single compounded annual rate linking the starting and ending values. An arithmetic average adds annual percentage changes and divides by the number of years, so it can differ, particularly when returns vary substantially. CAGR is useful for comparing endpoints, but it does not show the year-by-year experience.

Can I calculate CAGR when the beginning value is zero or negative?

Not with the standard CAGR formula. It requires a positive beginning value and a positive ending value because it uses a ratio and a fractional power. For a business measure that starts at zero or is negative, report the absolute change or choose a metric suited to that measure rather than forcing a CAGR calculation.

Can CAGR account for deposits, withdrawals, or dividends?

The basic formula does not separate external cash flows from growth. If money was added or removed during the period, the change in the balance is not necessarily the investment return. For dividends, decide whether the endpoint values represent total return with distributions reinvested. For dated deposits and withdrawals, a cash-flow method such as XIRR may better match the question.

Should I use dates or enter the number of years?

Use dates when the start and end points have specific calendar dates, especially when the interval is not an exact number of years. Use a year count when the period is explicitly known, such as four completed annual periods. Whichever method you choose, use the same period definition as the data you are comparing.

Why does Excel show 0.1 instead of 10%?

The calculation returns a decimal, and 0.1 is the decimal equivalent of 10%. Select the result cell and apply Percent Style from the Home tab. Do not change the formula simply to make the display look like a percentage; number formatting is the appropriate fix.

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.