Compound Average Growth RateDec 3rd, 2012 | By Andrew Matuszak | Category: Featured Articles
One of the most widely used references for measuring growth, the Compound Average Growth Rate (CAGR) is frequently misunderstood and often miscalculated. In this article, we will provide a straight-forward explanation of how to properly calculate CAGR using both Excel and a financial calculator as well as how to interpret the results.
CAGR (often pronounced 'keger') is a very useful tool for describing the growth (or attrition) of a value over a number of periods. It is often used to describe the magnitude of revenue growth for a company, as in the example below.
"Apple's revenue for FYE 2012 was $156,508 million, representing a CAGR of 53.94% since 2009."
The compounded average growth rate is simple to calculate, whether using a financial calculator or Excel. Since CAGR is often using when analyzing performance in the context of investments using a spreadsheet, we will begin by illustrating how to correctly calculate CAGR using Excel.
Excel has a built-in formula that can be used to simply the calculation, rate(). Although it is not necessary, using this formula is often faster and is certainly less prone to error.
The rate() formula takes the following six arguments:
nper – the number of periods
pmt – the payment per period
pv – present value
fv – future value
type – whether the interest is calculated at the beginning or end (default) of the period
guess – a user input that can be used if the iterative calculation does not converge on a rate
Typically, there is no reason to specify either the type or the guess. The default type (interest is calculated at the end of the period) is correct for calculating CAGR and the guess is unnecessary since the rate can be calculated in less than 20 iterations. Leave these two arguments blank.
Let's examine a simple example using Apple's recent revenue.
The corresponding Excel formula would look like:
Of course, the values specified in the arguments above could be replaced with cell references. However, be careful to include the negative sign in the PV argument.
The result is a CAGR of 53.94%. We'll examine how to interpret the results later in this article.
Calculating CAGR using a financial calculator is very similar to using Excel. We will illustrate using the HP 12c financial calculator. There are six buttons used in the calculation.
n – the number of periods
i – the calculated CAGR
PV – the present value
PMT – the payment per period
FV – the future value
CHS – change sign (used to input negative pv)
Begin by clearing the registry of any previous calculations:
- Press f (the orange button) and then REG
- Press 3 and then the n button
- Press 42905, then CHS (to change the value to negative), and then PV
- Press 0, then the PMT button
- Press 156508 and then the FV button
- Press the i button for the resulting CAGR
The result will be 53.94.
Perhaps the most common mistake when calculating CAGR is improperly specifying the number of periods (nper or n). It is important to remember that it is the end date less the beginning date. So if analyzing the revenue of a company from 2009 to the end of 2012, one would use 3 as the nper (2012 less 2009). Often, we see people using one too many in the nper argument–assuming since there are four years (2009, 2010, 2011, and 2012). However, you must remember that the 2009 value is measured at the end of 2009 and so it must be excluded (for example, from 10/1/2009->9/30/2010, 10/1/2010-9/30/2011, and 10/1/2011->9/30/2012).
The second most common mistake is forgetting that either the pv or the fv argument must be negative. For example, in the case of Apple the 2009 revenue was $42,905 million and 2012 revenue was $156,508 million. When inputting these values into Excel, we recommend (for consistency's sake) always assigning the pv argument as negative.
Interpreting the Results
The CAGR can be used to measure performance over periods of time, such as in the example above. It helps to describe a long term trend and has the effect of smoothing out period fluctuations. Above, we calculated that the CAGR for Apple's revenue between 2009 and 2012 was approximately 54%. We can use this result to help project future performance or compare different periods or different companies.
If we were building a financial model for Apple, we may consider using 54% as the growth rate if we believed that the company's recent historical performance is likely to continue in the future. Using the CAGR instead of just the most recent year's performance can help to avoid distortions that could exist in shorter time frames (for example, 2011 to 2012 revenue grew at only 45%). However, it is important to note that you must still consider whether the CAGR represents a trend that is likely to continue or whether there is a better suited metric (such as a declining growth rate, etc.). CAGR represents just one more tool that the analyst should consider.
One useful view to remember is that CAGR is the geometric mean of the returns. Please visit here for an in-depth discussion on how to calculate geometric means and a comparison with other means.
The table below illustrates how both the geometric mean and the CAGR for Apple's revenue are the same. (Remember that when calculating the geometric mean of numbers you must add 1 to the values.)