If you search the web to learn how to calculate a compound growth rate in Excel, you’ll likely find instructions for calculating only one type of growth rate. That’s unfortunate, because in business, we frequently need to calculate at least TWO types of growth rates.
The following figure illustrates them both. And in this article, I’ll show you how to calculate and chart both rates.
The left chart illustrates the traditional perspective for calculating the Compound Annual Growth Rate (CAGR).
This calculation measures the annual rate that would grow the starting value to the ending value.
Suppose, for example, that the first red dot in the CAGR chart represents the value of your investment in a stock, and the line represents the growth in the stock’s value over 14 years. If you sell the stock at the end of that time, the CAGR represents the annual growth rate of your investment from the beginning to the end.
With the CAGR calculation, the values between the time you bought and sold the stock don’t matter; instead, only the purchase price and the selling price matter.
The second chart illustrates the Fitted Average Growth Rate (FAGR). This rate represents the average growth rate returned from an exponential curve fitted to the 14 periods shown. With this method, the value for each period DOES matter, because each value affects the average growth rate that the fitted curve displays.
Suppose, for example, that the blue line in the FAGR chart illustrates your sales for a particular product during each of the 14 periods. The red line represents the average growth trend fitted to your sales history. And the growth rate for the red line represents the average growth rate for your sales during that time.
If the 14 periods represent years of performance, the FAGR calculates the average annual growth rate. If the periods represent months of performance, the FAGR calculates the average monthly growth rate, which you easily can convert into an annual growth rate.
Here’s how to calculate both the CAGR and FAGR growth rates in Excel…
How to Calculate the Compound Annual Growth Rate in Excel
As shown here, to calculate CAGR you divide the ending value by the beginning value to find one plus the total growth percentage during the time of the investment.
Then, to find the annual growth rate, you take that value to the power of 1 divided by the number of years for which you held that investment. And then you subtract 1.
To illustrate, suppose that this figure shows the MONTHLY growth of an investment:
Cell B3 contains the beginning value and cell B17 contains the ending value. The number of years is equal to 14 months divided by 12 months in a year, or 14/12 years. And also, 1 divided by this number of years is equal to the inverse of the fraction, or 12/14. So the CAGR formula is…
C20: =(B17/B3)^(12/14)-1
To prove the growth rate is correct, the Proof formula is…
F20: =B3*(1+C20)^(14/12)
That is, the ending value is equal to the beginning value times one plus the annual growth rate taken to the number-of-years power.
How to Calculate the Fitted Average Growth Rate in Excel
This figure illustrates the general approach to calculating the FAGR…
Here, column B and C of the table are identical to the columns in the previous figure. Column D in the table, labeled AvgGrth, shows values for the curve fitted through the 14 periods, and the red line in the chart plots this column’s values.
Excel offers at least two ways to calculate the periodic growth rate that column D and the red line represent.
Monthly FAGR, Method #1
The first method relies on values in the AvgGrth column. The formula that generates those values relies on Excel’s GROWTH function. This function returns numbers in an exponential growth trend that match known data points. Its syntax is…
GROWTH(known_y’s, [known_x’s], [new_x’s], [const])
…where Y and X are used in the equation: y = b*m^x. And from another perspective…
- known_y’s is the range of y (vertical) values plotted in the chart.
- [known_x’s] is the optional range of x (horizontal) values plotted in the chart.
- [new_x’s] is the optional current x value for which you want GROWTH to return a corresponding y value.
- [const] is the optional logical value that specifies whether you want to force the constant b to be equal to 1. You’ll typically omit this argument, causing GROWTH to calculate normally.
This is the formula I used to return the value for Monthly Rate #1 in the FAGR figure…
D3: =GROWTH($B$3:$B$17,$C$3:$C$17,C3)
…and then I copied the formula down the column in the table.
The formula that calculates the first FAGR rate simply divides the second value in column D of the table by the first value, and then subtracts 1. Specifically, the formula is…
D20: =D4/D3-1
Because all values in column D reflect the same growth rate, I could have found the same result if I had divided any value in the column by its preceding value, and then subtracted one.
Monthly FAGR, Method #2
This method calculates the monthly growth rate directly, without relying on the values in column D. The formula relies on Excel’s LOGEST function, which can return a variety of statistics that can describe an exponential curve that matches known data points.
The LOGEST syntax is…
LOGEST(known_y’s, [known_x’s], [const], [stats])
…where Y and X are used in the equation: y = b*m^x. And from another perspective…
- known_y’s is the range of y (vertical) values plotted in the chart.
- [known_x’s] is the optional range of x (horizontal) values plotted in the chart.
- [const] is an optional logical value that specifies whether to force the constant b to equal 1. As with the [const] argument for the GROWTH function, you’ll typically omit it.
- [stats] is an optional logical value that specifies whether to return additional regression statistics.
If the stats argument is FALSE or omitted, LOGEST returns an array with two values. The first value is equal to one plus the growth rate; the second value is the y intercept…which is equal to cell D3 in the previous figure.
So when you enter a LOGEST formula normally, it returns the first value in the array, which, again, is the growth rate plus 1.
Therefore, this is the formula I used to return the value for the Monthly Rate #2 in the FAGR figure…
D21: =LOGEST($B$3:$B$17,$C$3:$C$17)-1
Notice that because this formula relies only on the values in column B and C of the table, it doesn’t rely on the GROWTH function used in column D.
Convert the Monthly FAGR to an Annual Rate
Because we’re assuming that both methods of calculating the FAGR rely on monthly numbers, they both return a monthly growth rate. Therefore, if you want to calculate the equivalent annual growth rate, you need to use this formula…
D22: =(D20+1)^12-1
Notice that you do NOT multiply the monthly rate in cell D20 by 12. Instead, you take 1 plus the monthly rate to the 12th power, and then you subtract 1. You should take the same approach whenever you convert monthly growth rates to annual growth rates.
Confirm Your Calculations
You can confirm your calculations whether you’re working with a monthly or an annual growth rate. You have two ways to do so.
The first way to confirm the monthly rate is to start with the first point on the curve returned by the GROWTH function, and then grow the value to the last point on the curve:
H20: =D3*(1+D20)^14
The first way to confirm the annual rate is to start with the first point again, but you grow the value to the last point by taking 1 plus the annual growth rate to the power of the number of years in your data. Here’s that formula:
H21: =D3*(1+D22)^(14/12)
In both cases, the formulas return a value that equals the ending value returned by the GROWTH function in cell D17.
The second way to confirm the monthly rate is to start with the Y intercept, and then grow the value to the last point on the curve:
H20: =INDEX(LOGEST($B$3:$B$17,$C$3:$C$17),2)*(1+D20)^14
(Remember that when the [stats] argument is FALSE or omitted, LOGEST returns an array with two values. The second of these is the Y intercept, which Excel’s INDEX function returns in this formula.)
The second way to confirm the annual rate is to start with the Y intercept again, but you grow the value to the last point by taking 1 plus the annual growth rate to the power of the number of years in your data. Here’s that formula:
H21: =INDEX(LOGEST($B$3:$B$17,$C$3:$C$17),2)*(1+D22)^(14/12)
In all cases, the formulas return a value that equals the ending value returned by the GROWTH function in cell D17.
Using the FAGR with Negative Growth Rates
Finally, I should mention that the FAGR calculations work exactly the same way if your growth rates are negative rather than positive, as the following figure illustrates.
