This tutorial will teach you how to calculate the compound annual growth rate, or CAGR, in Excel. You’ll also learn about some of the limitations.
Different investments go up or down in value by different amounts over different time periods. Investors need a method of comparing one investment against another, especially if the returns have been volatile, or if investments and withdrawals are at irregular dates.
CAGR is the annual return of an investment assuming it has grown at the same rate every year. It’s a common concept; for example, the one, three and five-year returns on mutual fund fact sheets are CAGR values.
One method of calculating CAGR is given by this equation.
There are three parameters in this equation.
- start value of the investment
- end value of the investment
- number of years between the start and end value
You can also manipulate this formula to give, for example, the number of years required to grow an initial investment from a start value to an end value, given the growth rate.
This Excel spreadsheet uses the formulas above to calculate CAGR.
The spreadsheet also rearranges the formula so you can calculate the final amount (given the initial amount, CAGR, and number of years) and the number of years (given the initial and final amount, and CAGR).
You can also calculate the Compound Annual Growth Rate using Excel’s XIRR function – check out the screengrab below for an example. XIRR takes three arguments.
- The first is a range of cash flows into or out of the investment. Invested amounts are positive, but withdrawals are negative.
- The second is a range of dates corresponding to the investments or withdrawals,
- The third is a guess value for the CAGR (XIRR uses Newton-Raphson iteration, so it needs a guess value tostart the iteration).
XIRR is flexible, and can also given you the CAGR given investments and withdrawals at irregular dates. As an example, examine the screengrab of the Excel spreadsheet.
Limitations of CAGR
CAGR has several limitations that investors need to be aware of.
- CAGR hides volatility by assuming that investments grow at a constant rate. Volatility is an important factor in managing investment risk and can’t be ignored.
- It’s based on historical data, and can’t be relied on as the only method of predicting future value.
- CAGR can be manipulated by picking the time period over which it is measured. An unscrupulous fund manager can, for example, choose a start date with an unusually low investment value.
This Excel spreadsheet contains the examples demonstrated in this article. You’ll need to enable the Analysis Toolpack to use the XIRR function.
Download Excel Spreadsheet to Calculate Compound Annual Growth Rate
I have a historical time series of annual returns. How do I calculate the CAGR using the annual return data?
Hello Samir, I am not a maths exponent. I would like to find the amount of interest rate to be applied in a mortgage calculation situation. I can calculate a mortgage monthly payment (on paper with pencil). There are four components in the formula; Amount $ ; Term months; Annual Interest rate ; Payment due monthly: I am told if you know any three of these then you can find the fourth. This is my question : How do I find the Annual interest Rate % for example of : $200,00 loan at 25 years (300 months) and I can afford to pay monthly payments of $1,800 What will be the Annual interest rate% for that loan. How do I calculate this on paper or a non scientific calculator. (^ available) Use of Monthly Term periods is ok. Can it be done in Excel? How would it be typed in.
Hope you can help, Thanks, Leo
I am using the net profit margins (%) for 4 period (5 years) to calculate the CAGR but I am getting a negative result. All the annual percentages are positive so why am I getting a negative result for CAGR? However, when I use Excel’s Constant growth Rate function I get a positive result? What seems to be the problem here? Thank you
Are you sure that using XIRR for calculating CAGR is correct? In IRR (on which XIRR is based) there is an assumption that profits are reinvested at the IRR. As a result in most cases (X)IRR would be significantly bigger than CAGR (In your example where you compare CAGR equation to XIRR function there is only one cashflow – that is the reason why the amount is similar). Don’t you think that MIRR with 0% reinvestment rate would more appropriate?
BTW – As CAGR I mean it’s explanation from Investopedia (which I think you used) but I think its current section about using XIRR to calculate CAGR is incorrect.