How to calculate average percentage growth in Microsoft Excel

Ever been stuck trying to work out how to calculate average percentage growth in Microsoft Excel? Paolo Lenotti of Excel with Business is on hand to show you how

Average percentage growth is not an official accounting or bookkeeping term, but it is still a very useful measure of gain (or loss) over a period of time (of an investment, revenue, profit, etc.). This can help to assess a client’s past performance and, ideally, predict likely future performance.

Calculating average growth in Microsoft Excel

There are different ways of calculating average growth in Excel (e.g. LOGEST, LINEST, lines of best fit, etc.) and some of these will give different results. Here’s the simplest: an annual level of growth that would take you from first year’s level to the last.

Suppose you have profit figures year-on-year as follows:

Microsoft Excel graph example

The line on the graph shows average growth in line with our definition.

We use this simple formula:

=100*(<final value>/<initial value>)^(1/<number of years’ growth>)-100

For the data in the example above, which spanned seven years’ of growth, this formula was used:

=100*(C10/C7)^(1/7)-100 (where cells C7 & C10 contain the first & last data values)

This gives a result of 2% annual average growth.

Technically this is called Compound Annual Growth Rate (CAGR).