One very common task that we are faced with when analyzing historical data is the calculation of compound annual growth rate (CAGR). While possible to calculate by hand, it’s a somewhat tedious equation so fortunately for us Microsoft Excel has built in functions to do the calculations for us. This quick tutorial will show you how, using historical EPS data for Caterpillar (CAT).
There are several different functions within excel that deal with compound growth, but the one we’ll use today is the RATE function. We’ll also need some data to work with. In another recent article we looked at how to estimate future EPS growth rates, and used historical data from Caterpillar (CAT). For consistency, let’s use that same data again here. Let’s attempt to calculate the CAGR for CAT’s EPS over the past seven years, given the following data pulled from msn money:
The column we’re interested in is the EPS data. For a primer on just what EPS is exactly, check out one of my previous posts on the subject. The excel RATE function takes a number of parameters and provides the CAGR as the result. The functions looks like so:
=RATE(nper, pmt, pv, [fv], [type], [guess]
- nper – Number of periods. In our case this is the number of years that we wish to calculate the CAGR for (7 years).
- pmt – Payment. Not required for our current application – leave blank.
- pv – Present Value. This must be a negative number and represents the starting point from which we wish to calculate the CAGR. In our application we want to calculate the CAGR for 7 full years, so our input for pv will be the EPS from 2004 (period 0), converted to a negative number: -2.88
- fv – Future Value. This is the ending point for our CAGR calculation, in this case 7.40.
- type – Type. Not required for our current application – leave blank.
- guess – Guess. Not required for our current application – leave blank.
This returns the correct CAGR of 14.43% for CAT’s EPS over the 7 year period from 2004 through 2011. We can do a simple check by running the numbers manually, as shown in the following spreadsheet: