These days it’s easy to download statistical tables in Excel from governmental and other sources. Sometimes quite long series are presented, and it is useful, in doing a little analysis, to calculate annual growth rates for two series in order to compare them, let us say the growth in the sales of Company B and Company C.
Excel provides an excellent function to let us do that easily. Let me demonstrate that function using tabular data as these might appear in a spreadsheet. AGR in the table represents Annual Growth Rate:
| A | B | C | |
| 1 | 2000 | 100 | 3,545 |
| 2 | 2001 | 101 | 3,670 |
| 3 | 2002 | 102 | 3,809 |
| 4 | 2003 | 103 | 3,765 |
| 5 | 2004 | 104 | 4,013 |
| 6 | 2005 | 105 | 4,013 |
| 7 | 2006 | 106 | 4,029 |
| 8 | 2007 | 107 | 4,567 |
| 9 | 2008 | 108 | 4,580 |
| 10 | 2009 | 109 | 4,576 |
| 11 | 2010 | 110 | 4,836 |
| 12 | AGR in % | 0.96 | 3.15 |
Let us say that B and C are companies, and these are data from 2000 through 2010. To calculate the compounded annual growth rate experienced by Company B, you would type the following formula into cell B-12:
=RATE((A11-A1),,-B1,B11)*100
The results, as shown above, is growth at the rate of 0.96 percent, round that to 1 percent a year. Let’s look at the formula. Ignoring the references embedded within it, it calls for:
=RATE(number of years,,-First Year, Last Year)
In the trade’s jargon, this is rendered as =RATE(n,,-PV,FV) where n stands for number of periods (they could be months, days, years), PV stands for Present Value or Starting Value, FV stands for Future Value or Ending Value. Present and Future are used because the same formula is also used to calculate interest required to produce a future value at the end. But we’re making a special use of this formula to calculate compounded annual growth rate.
In the first mention of the function, I’m letting Excel calculate for me the value of n (number of years) by deducting the beginning year in Cell A1 from the ending year in cell A11. There are 11 years here, but we’re ignoring the first, therefore n=10. Note carefully that two commas follow each other. One variable is being left blank, Payment. We’re not calculating an annuity and hence we’ll ignore Payment, usually abbreviated PMT. Note also that the beginning value must be rendered as a negative (held in B1) and the ending value as positive (in B11). The function is designed to return the answer in percent. Without adding that * 100, we would be getting 1% as our answer. I prefer to get the percent as a raw number, thus with decimals and without the % symbol. So I modify the formula by adding * 100 behind the last parenthesis.
Turning now to Company C, =RATE(10,,-C1,C11) will return 3%. Adding *100 will produce 3.15 — or however many decimals you like to see. Company C was growing at a rate slightly above 3 percent annually.
The underlying math formula for this calculation is the following:
-n
√ PV/FV = 1 + i
The -nth root of PV divided by FV equals 1 plus the growth rate. By deducting 1 from the result we get the actual rate. Multiplying that by 100 gives us the percent.
If we want to get fancy, we can also translate this equation into an Excel command as follows:
=(EXP((1/-10)*(LN(B1/B11)))-1)*100
In this I have simply typed in 10 for the number of years. The first phrase could also be keyed as EXP(1/-(A11-A1)). It takes more keying to type this formula, hence I prefer to use the RATE function instead.
You can also calculate a growth rate using a slightly advanced hand calculator, e.g. a Texas Instruments TI-30 SLR or the like. The calculator needs to have parentheses and an x√y key. You type the following on the calculator’s keyboard:
(1 ÷ ((PV ÷ FV) x√y n)) -1 =
What you get is a fraction. Multiply that fraction by 100, and you have the compounded annual growth rate for the series described by PV to FV. It’s simple with the right calculator.
Filed under: Uncategorized Tagged: | Caclculation, Formulas, Growth Rate
