Sign up to access your free download and get new article notifications, exclusive offers and more.
CAGR Excel Workout
Formula to Calculate CAGR in Excel
March 28, 2025
What is CAGR?
The Compound Annual Growth Rate (CAGR) is the rate of return an investment grows from its beginning balance to its ending balance. It assumes the profits are reinvested at the end of each period over the given time-period. It is a widely used metric to measure what returns are generated by an investment when held over a period of time.
CAGR is useful as it can be used to measure the performance of different investments, such as how a stock has performed versus its peer group or the overall index. For example, we could measure the CAGR of Apple’s stock price over the past 4-years and see if it has outperformed the major US indices or compare it to other tech stocks.
CAGR Formula
The formula for Compound Annual Growth Rate (CAGR) is (Ending Value / Beginning Value) raised to the power of (1 divided by the number of years), minus 1.
Where:
- EV is the ending value
- BV is the beginning value
- n is the number of years
If we were doing this for a 3-year period in Excel we would write the formula:
= (year 3 / year 1) ^ (1/3)-1
How to Calculate CAGR in Excel
These are the steps to calculate the compounded annual growth rate on an investment:
- Divide the value of the investment at the end of the period by the initial value of the investment at the beginning of the period
- Raise the result to an exponent of 1 divided by the number of years
- Subtract 1 from the result
- Multiply the answer by 100 to convert it to a percentage
If we had wanted to calculate the 3-year CAGR of the S&P index from the 1st January 2022, we would need these dates and information
- S&P 500 index value of 3,756.07 on 1st January 2021
- S&P 500 index value of 5,942.47 on 31st December 2024
The calculation would be written as:
The 3-year CAGR is 16.5% which suggests that for each of the 3 years the S&P 500 grew 16.5% annually.
How to Use CAGR to Forecast
The Compound Annual Growth Rate (CAGR) is useful for predicting future values by applying the calculated growth rate to the current value over a specified number of years. This method aids in evaluating the potential growth trajectory of an investment over time.
If a company’s revenue has grown at 8% CAGR for the past 4 years, analysts can use this as an assumption for future revenue growth (assuming market conditions etc. all remain the same). The calculation is similar to historic CAGR and uses this formula:
Future value (FV) for year 5 = $20m x (1+8%) ^ 5 = $29.39m
This can also be shown in the table below if we calculated $20m of revenue growing at a constant 8% YoY p.a. it would equate to £29.39m by the end of year 5.
| Annual growth | Revenue $m | |
| Year 0 | 20 | |
| Year 1 | 8% | 21.6 |
| Year 2 | 8% | 23.3 |
| Year 3 | 8% | 25.2 |
| Year 4 | 8% | 27.2 |
| Year 5 | 8% | 29.4 |
Access the free Financial Edge template to calculate CAGR formula for stock price returns
What Is a Good CAGR? How to Evaluate Growth Effectively
A good CAGR (Compound Annual Growth Rate) varies depending on industry, company size, and market conditions. While a higher CAGR generally indicates strong growth, it must be compared to industry benchmarks and risk factors to determine whether it is sustainable and meaningful.
CAGR Benchmarks: What’s Considered Good?
-
Mature Companies (Established Industries): A 3% to 5% CAGR over five years is typically solid, as these businesses grow steadily.
-
Growth-Stage Companies: A CAGR of 10% to 20% is considered strong, reflecting expansion and increasing market share.
-
Early-Stage Startups: Startups often target CAGRs of 50% or higher, with some aiming for 100%+ growth to attract venture capital.
Why Context Matters in CAGR Analysis
CAGR can be misleading if external factors distort growth. For example, industries like travel, hospitality, and airlines saw massive revenue declines due to the COVID-19 pandemic, impacting CAGR calculations for companies operating during that period.
How to Use CAGR in Investment Decisions
To determine if a CAGR is good:
- Compare it to industry averages and competitor performance.
- Consider the economic climate and market trends.
- Assess whether growth is consistent and sustainable.
Bottom Line: What’s a Good CAGR?
A good CAGR depends on the business stage and industry standards. While high growth is appealing, steady and sustainable growth often matters more than extreme spikes. Always evaluate CAGR in the context of industry benchmarks and broader market conditions for a complete picture of investment potential.
Advantages of Using the CAGR
There are many advantages of CAGR as it offers a straightforward measure of growth over multiple periods. This can work well for historical performance but is also a neat way of expressing what expected growth will be for a forecast period. The benefit is that it will smooth out any bumps in divisional performance (or M&A).
It also enables the comparison of growth rates among various investments or companies over the same period. If we are looking at a series of investments, we can calculate one single CAGR figure to compare performance over a set period.
A third advantage is that CAGR reduces the impact of volatility in periodic returns, giving an accurate view of overall growth. If there is a difficult year (such as 2022 during the Pandemic) a CAGR figure can capture the performance during this year and the following years in one single metric.
Disadvantage of CAGR
CAGR has its limitations, most notably that it does not account for the volatility or fluctuations in returns during the investment period. We cannot see the stand-alone impact of an event such as the pandemic in 2022 if we are calculating CAGR on a 4-year period that spans this year.
It also assumes a steady growth rate, which may not reflect the actual performance – underlying growth may be more volatile than the CAGR growth figure indicates. Analysts must remember that CAGR is a representative figure and not a true return rate. It assumes consistent growth, which may not be realistic.
Finally, CAGR does not account for the risk or variability in returns, which are important factors in investment decisions.
Modifying the CAGR Formula
The CAGR formula can be modified to account for different compounding periods (e.g., monthly or quarterly) by adjusting the exponent accordingly. For example, for monthly compounding, the exponent would be:
CAGR Limitations
As mentioned in the disadvantages, there are a couple of limitations of CAGR, most notably that it assumes consistent growth (or decline), which may not be realistic. It assumes a smoothed return.
It also does not account for the risk or variability in returns, which are important factors in investment decisions. During a 4-year period, a stock may have risen dramatically and then fallen by the period end. This would not have been fully captured with a CAGR and it would not have recorded the risk associated with that stock compared to one that grew slowly at 3% annually over the same period.
Investors would need to use standard deviation to look at the volatility of the performance. This can be added into the CAGR calculation to produce a risk-adjusted CAGR using this formula:
CAGR vs. IRR
CAGR (Compound Annual Growth Rate) and IRR (Internal Rate of Return) are both important financial metrics, but they serve different purposes. CAGR measures the average annual growth rate of an investment over time, assuming a steady rate of return. It’s useful for comparing the historical performance of different investments.
IRR is the discount rate that makes the net present value (NPV) of all cash flows equal to zero. Unlike CAGR, IRR factors in the timing and size of each cash flow, making it a more comprehensive metric for evaluating projects with irregular cash flows, such as real estate or private equity investments. Use CAGR for simple comparisons of investment growth and IRR for analyzing cash flow-based investments.
Conclusion
Compound annual growth rate is a much-used metric in finance to measure the performance of an investment. It provides a smoothed rate of return over a period for investments enabling comparisons of returns and performance. Further calculations may be required to factor in the risk and volatility of an investment, but it is a good initial indication of how investments have performed or are expected to perform in the future.