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 calculating CAGR is:
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
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 for stock price returns.
What is a Good CAGR?
What can be deemed a “good” CAGR will vary by industry and sector. Generally, a higher CAGR indicates better performance, but it should always be compared to industry benchmarks and investment risk. One key variable is the age of a company if looking at its performance:
- For mature companies in established industries a five-year CAGR of 3% to 5% may be seen as a good CAGR by investors
- For companies in the growth-stage you might expect good CAGR to be 10% to 20%
- For early-stage startupsa target CAGR could be over 50%, or even up to 100%, these targets aim to attract venture capital funding
It is important to also look at macro and industry events during the period of time that the CAGR relates to for instance, restaurants, hotels and the airline industry were deeply affected by the COVID pandemic so if a historic CAGR begins during this period it may distort the underlying growth.
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 and IRR may initially seem similar, but they have distinct characteristics. While CAGR measures the average annual growth rate of an investment, the Internal Rate of Return (IRR) is the discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero. IRR accounts for the timing and magnitude of cash flows, making it a more comprehensive measure of investment performance.
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.