Calculating CAGR for historical data with Excel

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]

  • nperNumber of periods. In our case this is the number of years that we wish to calculate the CAGR for (7 years).
  • pmtPayment. Not required for our current application – leave blank.
  • pvPresent 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
  • fvFuture 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.
  • guessGuess. Not required for our current application – leave blank.

=RATE(7,,-2.88,7.40)

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:

Tagged , ,

7 thoughts on “Calculating CAGR for historical data with Excel

  1. […] rate of 7%. This is arguably the most realistic of the three methods. See my article titled, “Calculating CAGR for historical data with Excel” for a quick tutorial on how to easily find compound annual growth […]

  2. […] when I use this valuation method, I use the EPS 5-yr CAGR (compound annual growth rate), since it smooths out any fluctuations in earnings growth over time. […]

  3. […] rate has been over the past number of years. I’ve recently written another article on how to easily calculate compound annual growth rates in Microsoft Excel using historical stock data. Rather than have to dig up the past ten annual […]

  4. Robert Butterworth says:

    Hi Dustin,
    Thanks for sharing all these great articles. I’m going through around 45 stocks from Australia and Taiwan trying to figure out appropriate EPS growth, estimates for future EPS, future PE, and of course my target prices. It’s easy to estimate EPS growth when the past EPS are all fairly “normal”, but very difficult when there is volatility (especially as you mention, in the couple of years after the 2007/2008 GFC). I enjoyed reading the above articles on calculating CAGR based on past EPS. However, did you realize that the CAGR calculation is heavily sensitive to the starting and ending values? For example, try starting in a year where there’s a negative or very low EPS. CAGR doesn’t make any sense. So, you have to try to figure out a good starting year, one that you think is normal enough. In fact the CAGR can vary wildly based on which starting year you use. That’s what I’m trying to figure out now. I’m experimenting with taking an average of say the three year period 5 years ago and using that as my starting year EPS for the VAGR calculation, rather than using any single year. Or, maybe use some kind of moving average. What do you think? I’d appreciate any suggestions, and hope my observation about the starting year being important is also helpful to you.
    Cheers,
    Robert Butterworth.

    • Robert Butterworth says:

      Sorry, VAGR –> CAGR

    • Dustin Small says:

      Hi Robert – glad you’re enjoying my articles! Yes, you’re absolutely right that the starting and ending years are critical to the CAGR calculation. In fact, since they’re the only two years input into the equation they are the ONLY years that affect the result. Any volatility between the start/end is disguised by a smooth rate.

      If only a small number of the years exhibit volatility (for example maybe one bad year out of five), I probably wouldn’t do anything and just accept the CAGR calculated with the understanding that there may be some volatility that is not accounted for. In the case where the starting or ending years are anomalous, I would do what you suggested and look at multiple timeframes – perhaps the 3-yr, 5-yr, and 10-yr CAGR and see if there’s any consistency at all that you can use to make a decision. If the company’s EPS data is completely all over the map with no consistency whatsoever then unfortunately the CAGR calculation is not going to be of much use to you in estimating future growth. In these situations it’s best to look at the company’s fundamentals and plans for future growth and make an educated estimate on your own as to future growth prospects. Published analyst estimates can be of assistance in making these determinations as well.

      Hope this helps – let me know if you need any more help.

  5. Erik says:

    Great article, worth mentioning might be its quite easy to get with a calculater

    7,40/2,88^(1/7) gives the same answer

    Anyways thanks for great writing

    //

Leave a Reply

Your email address will not be published. Required fields are marked *