Calculating Growth Rates In Excel

The Growth formula in Excel is an array formula meaning that it takes several arrays of data as input and outputs an array of data which can be difficult to understand if your knowledge of statistics ain't what it used to be.
We are going to look at several other methods for calculating growth including a manually-written formula, a charting method and one method using Goal Seek.

Formula Method

What most of us want from the Growth formula is a simple number representing the period over period growth rate of a series of numbers. Constant Annual Growth Rate (CAGR) is a typical example.

The formula for CAGR is not difficult. For calculating growth from a single start time and a single end time it's sufficient. In other words, if we have a value for revenue in Year 1 and a revenue figure for Year 10 and we aren't concerned about the years between we would set up the spreadsheet shown below, given that the formula is:

=((End Value/Start Value)^(1/(Periods - 1)) -1
Year 1	110.06
Year 10	260.83
=((B3/B2)^(1/9))-1 ==>	10.06%

While it is a single number we're after, the equation and the rather bare result above may leave you wondering just whether or not you've done it right. An answer is one thing, but assurance is still another. We should set up a spreadsheet to use the growth figure in a Predicted column and show all of our years' figures in an Actual column with a variance between the two. You can create your own from the graphic below, If you create a worksheet, make sure to name the ranges Starting Amount and Growth.

Starting Amount			100
Growth Rate			10.06%
Period	Predicted	Actual	Variance
0	=StartingAmount	100	=C5-B5
1	=B5*(1+Growth)	105	=C6-B6
2	=B5*(1+Growth)	110	=C7-B7
3	=B5*(1+Growth)	140	=C8-B8

 From the calculated columns of numbers, we can see how far off our predicted growth is from the actual numbers we started with. Of course, it would be even easier to see with a graph.

Chart Method

Let's a create a graph just from our original periods and actual values and leave the predicted values alone for a moment. As it turns out, we can get a growth value from the charting process itself.

Place your data in the format you see above and create an XY scatter chart - not a standard line chart. It makes a difference if your periods are other than 1, 2, 3 because a standard line chart treats each point as the 1st, 2nd, 3rd. A scatter chart actually reads the values in column A and treats them not as ordinal numbers but as actual values. If you skip periods because data is missing, or if your periods start at a number other than 1, you'll definitely see a big difference when you add a trendline.

After you have created the XY scatter chart as above, right click on the data series and you'll see the menu above. Click on Add Trendline...

In the Add Trendline... dialog box, indicate Exponential as the type of curve to fit your actual data to. You must click the box near the bottom of the dialog box to Display Equation on chart as shown below.

When your chart is updated, it will have an equation of the form y=b * e g*x where g is the growth rate.



Here, we see the coefficient is 0.0984 or 9.8%. Notice that the CAGR formula gave us a growth rate of 10.1%. The two are different largely because CAGR used only the start and end periods in its calculations, where the curve fitting used all of the data.

Goal Seek Method

Again, we could certainly create predicted, actual and variance columns to see how well our curve fits the data. In fact, we could sum the differences and use Goal Seek to try to find out if another, better growth rate exists.

The issue with merely summing the differences is that some predicted values will be greater and some smaller than actual values. Even if the differences are very large, an equal number of positive and negative values could lead us to believe we have a good fit when we don't.

Let's square the variances and then sum them. Squares are always positive and a square will exaggerate big differences and ignore smaller differences. You've probably heard of this method of curve-fitting as least squares.

When the spreadsheet is set up (below), we'll go to the Data tab on the ribbon, Click What-If Analysis > Goal Seek and tell Excel to try to get the sum of the squares in E18 to zero by changing the Growth Rate in C2. Of course, we'll never actually get to zero, but Excel will keep trying different values - thousands of them - until it gets as close to zero as it can.

Goal Seek is a little like the child's game of warmer, colder where one party keeps telling the other if they're getting closer to the secret object (warmer!) or farther away (colder!). Excel just keeps trying to get warmer and warmer until nothing it tries gets any warmer.



Here, the closest we can get our squares to zero is when the growth rate is 10.06%.

You can see that different numerical methods give slightly different results. You may wish to try all three on your data to get a feel for the best approximation of period growth.