It’s common to want to calculate period growth rates for historical figures. Surprisingly,
there’s no simple formula for doing it. If you’re dealing with negative starting values, it’s much more difficult.
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.
Finally, we’ll look at series that begin with negative numbers. Mathematically, exponential growth calculated from such a series has no meaning, so what do we do?
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. Compound 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 or download the final file CalculateGrowth.xlsx.
If you create a worksheet, make sure to name the ranges StartingAmount 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 |
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.
Trends That Begin With Negative Values
If you’re reading this, you already know that calculating exponential growth from a series that starts with nonpositive numbers (zero or negative numbers) is impossible. Using the methods above, we can see that they all fail.
You have a few options:
Add a scalar to your values
Unfortunately, the choice of scalar determines the growth. To put it clearly, if you add a small scalar – just a big enough number to bring the negative values positive – your growth rate will be very large.
If you add a HUGE scalar – add one million to each of the periods shown above – your growth rate will be close the zero.
This method is not going to yield meaningful results.
Use polynomial or linear estimation
Polynomial and linear estimation will be able to calculate an estimated change period over period, but these typically are not what we mean when we talk about growth rates. The equations given will be useful in projecting values a few periods into the future, and that may be good enough for your purposes.
Get to the root of the numbers
Is the series you’re analyzing a single set? For instance, if you’re analyzing profit over a number of periods, you may begin with negative values. However, if you break that profit into two separate series – revenue and expense – you’ll find that you can express both as positive numbers. Using the methods above, analyse the two (or more) series separately and then recombine them.
We hope you find this article useful and will contact us with your comments and questions.