Excel Growth Rates
How to Calculate BOTH Types of Compound Growth Rates in Excel
To calculate the correct growth rate you need to be clear about
what you want your growth rate to signify. Here's how to work it
out.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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.
This figure illustrates them both.
In this article, I’ll show you how to calculate and chart
both rates. If you’d like to follow along, you can
download the
workbook for free at this link.
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 at the right, 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
numberofyears 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/D31
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)^121
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.
Take Your Next Steps
First, you can
download a
free copy of the workbook with these examples here.
Second, if you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
Tags: #CAGR, #FAGR, #INDEX, #LOGEST, #GROWTH
