How to Calculate CAGR in Excel: Formula & Examples

If you have ever stared at an Excel spreadsheet and wondered how much your investments have grown on an annual basis, you are not alone. With just two numbers and a basic Excel formula, you can figure out the true annual pace of your returns.

CAGR Formula: (Ending Value / Beginning Value)^(1 / Number of Years) – 1 · Typical Long-Term CAGR (S&P 500): ~7–10% · Example 5-Year CAGR: $10,000 to $16,105 over 5 years = 10% CAGR

How do I calculate the 5-year CAGR?

CAGR, or Compound Annual Growth Rate, is a financial metric that measures the mean annual growth rate of an investment over a specified period longer than one year. It represents a smoothed rate of return, as if the investment had grown at a steady, consistent rate each year, rather than experiencing the actual year-by-year volatility of the market Investopedia.

“CAGR is the rate of return that would be required for an investment to grow from its beginning balance to its ending balance assuming the profits were reinvested at the end of each year.” — Investopedia

The formula is deceptively simple: (Ending Value / Beginning Value)^(1 / Years) – 1. It requires only two data points and works for any time period Excel Easy.

For example, if you invest $10,000 and it grows to $16,105 over 5 years, the CAGR is 10% — meaning your investment grew at an average of 10% per year, compounded annually Vertex42.

How do I calculate a 5-year CAGR in Excel?

Now, let’s get into the practical steps of calculating CAGR in Excel, a skill useful for both personal finance tracking and professional financial analysis.

1. Enter Your Data: In column A, list your periods (Year 0, Year 1, etc.). In column B, list the investment values.
2. Apply the Formula: In a blank cell, type = (Ending_Value / Beginning_Value)^(1 / Years) - 1. For example, if your beginning value is in B2 and ending value is in B7, with 5 years of growth, the formula is =(B7/B2)^(1/5)-1.
3. Format as Percentage: To display the result as a percentage, select the cell and click the percent style button in the Number group on the Home tab. Microsoft Support

Excel also offers alternative methods. The RATE function can be used with the pattern =RATE(Years,,-PV, FV) to achieve the same result Vertex42. Think-cell documents a custom Excel formula TCCAGR(FIRST_CELL:LAST_CELL) for ranges containing growth data think-cell.

“For cash flows that occur at irregular intervals, the XIRR function is more appropriate as it accounts for the exact timing of cash flows.” — Microsoft Support

Remember, for XIRR, which is used for irregular cash flows, the calculation is more complex but follows a similar logic Microsoft Support.

What does 12% CAGR mean?

A 12% CAGR means an investment’s value grows by 12% per year, compounded annually. Over 5 years, $10,000 at 12% CAGR would become $17,623. This is higher than typical S&P 500 returns, indicating above-average growth performance.

Is a CAGR of 7% considered good?

A 7% CAGR is moderate and generally considered good when compared to risk-free rates like Treasury bonds. The S&P 500 historically averages 7–10% nominal CAGR, so 7% aligns with long-term equity returns. However, context matters: time period, inflation, and risk tolerance all affect whether 7% meets your financial goals.

Is CAGR better than ROI?

Understanding the difference between CAGR and ROI (Return on Investment) is essential for accurate financial interpretation.

For instance, a 50% ROI over 5 years translates to a CAGR of approximately 8.45% per year Investopedia. Conversely, a 10% CAGR over 5 years would result in a 61% total ROI.

Excel Functions for CAGR: RRI, XIRR, and More

Excel provides several functions that can compute CAGR, each suitable for different scenarios.

The RRI function is the most direct: =RRI(Nper, Pv, FV), where Nper is the number of periods, Pv is the present value, and Fv is the future value Microsoft Support. For cash flows that occur at irregular intervals, the XIRR function is more appropriate as it accounts for the exact timing of cash flows Microsoft Support.

Some Excel tutorials recommend formatting the CAGR result as a percentage and warn about common errors, such as returning a #NUM error if the present value is not entered as a negative number in certain cash-flow setups ExcelDemy.

The pattern here: Excel’s built-in tools let you compute CAGR without manual formulas, ensuring accuracy and efficiency.

Worked Example of CAGR Calculation

Let’s walk through a concrete example to solidify your understanding.

• Beginning Value = $10,000
• Ending Value = $16,105
• Number of Years = 5
• CAGR = ($16,105 / $10,000)^(1/5) – 1 = 0.10 or 10%

This calculation shows that the investment grew at a steady 10% annual rate, regardless of the actual ups and downs in the yearly values.

Summary

Mastering CAGR in Excel is a foundational financial skill that empowers you to analyze investments with clarity. By understanding the formula, leveraging Excel’s functions, and distinguishing it from ROI, you arm yourself with a tool that reveals the true, time-adjusted performance of your assets—a critical step toward informed financial decisions.

Frequently Asked Questions