In this tutorial you will learn what is CAGR and how to calculate it in Microsoft Excel.
CAGR is the acronym of Compound Annual Growth Rate. It is the geometric progression rate of an investment or business. It calculates the annualized growth rate of an investment or business over specified period. Since it is annualized growth rate, it can be compared with other investments to measure the performance and take decision.
Standard Formula to Calculate CAGR
Below is the standard formula to calculate the Compound Annual Growth Rate.
CAGR: Compound Annual Growth Rate
Final Value: Ending Value of an Investment or Business
Starting Value: Beginning Value of an investment or business.
N: Time Period (Years)
Method of Calculating CAGR in Excel
If you have the excel version of 2013 and later, you can calculate CAGR by dedicated excel function that is RRI function. If you are using the excel version 2010 and earlier, you have to write the formula expression manually. In both methods, result of calculation should be converted into percentage formatting.
Example 1
Let’s take a look at below example, where Mr. A and Mr. B has the investment in land. A had purchased land at the cost of 1500000 before three years. Now the price of land is 2275000. On the other hand B had purchased land at the cost of 1500000 before 7 years and now the price of land is 3200000. In this case how do you make a comparison between performance of these two investments? If you calculate direct or absolute growth, A has grown by 52%, calculated by ((2275000-1500000)/1500000)*100 and B has grown by 113%, calculated by ((3200000-1500000)/1500000)*100. If we overlook these two growth rates, we may say 113% is greater than 52%. So, investment B is doing better than A. But, this is totally wrong way of comparing. We cannot compare these two investments by absolute growth, because it is silent about the time frame or period of these investments. To make a comparison, we must calculate the annualized growth, which gives by the CAGR.
In the example above, CAGR of Investment A is calculated 15% and B is 11%. Since both of these are annualized growth rate which takes the time period in account, we can now make a comparison. Investment A is giving higher rate of return. Hence, Mr. A’s investment is performing better than Mr. B.
Calculating CAGR by RRI Function
If excel has RRI function available, use the RRI function to calculate CAGR.
In the above example, CAGR is calculated by RRI function. RRI function has 3 arguments. These are;
nper: number of period in years
pv: starting value of investment
fv: ending value of investment
Example 2
Let’s look at another example of calculating CAGR. Here we have sales revenue data of ABC company and XYZ company for past couple of years. XYZ company has the data of 8 years period and ABC has 5 years period. By calculating CAGR, we can compare the performance of these two businesses in terms of annualized growth. ABC company’s sales revenue has greater CAGR. So, we can conclude that the ABC company is performing better than XYZ company.
Download the practice workbook from below link.
