Excel formula for calculating average annual growth rate (AAGR)

Two measures are frequently used with financial data: average annual growth rate (AAGR) and compound annual growth rate (CAGR) (CAGR).

While certain built-in Excel formulae can assist you in determining growth rates, you will need a small workaround to compute AAGR and CAGR (nothing too complex though).

I’ll explain how to compute the Average Annual Growth Rate (AAGR) in Excel in this lesson.

Let me briefly go over what AAGR is and what it means before I explain how to calculate it in Excel.

Click to skip to that section if you only want to know how to compute it in Excel.

This instruction explains: 1. AAGR stands for average annual growth rate. 2. Excel formula for calculating AAGR, or average annual growth rate 3. AAGR vs CAGR

1. AAGR stands for average annual growth rate.

As the name implies, the Average Yearly Growth Rate (AAGR) is the average of the annual growth rates.

For instance, if I inform you that, over the last five years, your stock portfolio has increased with an AAGR of 10%, what I mean is that you have gained 10% of the value each year (at the end of the year as compared with the beginning of the year).

This could not imply that it increased by exactly 10% annually. It simply implies that it provided an overall AAGR of 10% after the fifth year.

Investors frequently use the average annual growth rate to monitor the performance of their portfolios or to determine the direction of a company’s revenue, cost, and profit trends.

Let’s see how to compute the AAGR in Excel right now.

2. Excel formula for calculating AAGR, or average annual growth rate

It takes two steps to compute the Average Annual Growth Rate (AAGR) in Excel (unlike CAGR, which can be calculated with a single formula).

You must first determine the growth rate for each year to compute the average growth rate across the numerous years. Only then can you determine the AAGR.

Let’s say you know the values for each year as follows:

The initial value was $100,000.

End of year 1 value = $135,000
End of year 2 value = $145,000
End of year 3 value = $170,000
End of year 4 value = $200,000

Using the information above, you can use the formula below to get the growth rate for each year:

=(Ending Value / Begin1ning Value) -

You may obtain the following growth rates using the aforementioned dataset:

Growth rate for Year 1 = $135,000 / $100,000 – 1 = 35.0%
Growth rate for Year 2 = $145,000 / $135,000 – 1 = 7.4%
Growth rate for Year 3 = $170,000 / $145,000 – 1 = 17.2%
Growth rate for Year 4 = $200,000 / $170,000 – 1 = 17.6%

Now, you can use Excel to compute the average yearly growth rate by entering the formula below:

=AVERAGE(C3:C6)

And is it possible to accomplish this with one Excel formula?

Yes… You may!

The algorithm that will calculate your AAGR value using the year-by-year data you have is shown below:

=AVERAGE((B3:B6-B2:B5)/B2:B5)

The formula above may be used to obtain the result if you are using Office 365 and have access to dynamic arrays.

If you’re using Excel 2010, 2013, or 2016, you must enter this formula using Control + Shift + Enter (hold the Control and the Shift key and then press the Enter key).

I have a thorough lesson on how to compute CAGR in Excel if that’s something you’re interested in learning (that covers multiple formula ways to calculate it in Excel)

3. AAGR vs CAGR

If I didn’t discuss the distinction between the Average Annual Growth Rate (AAGR) and the Compound Annual Growth Rate, this lesson wouldn’t be complete (CAGR).

Both formulas compute the annual growth rate, but AAGR fully disregards the effect of compounding that builds up over time.

CAGR, on the other hand, would consider the compounding impact.

Although AAGR is a useful indicator for determining the general growth trend, it occasionally causes the growth rate to be overestimated.

For instance, in the example below, the investment’s value is the same in Years 1 and 5, but the AAGR would lead you to assume that it increased in value at a rate of 4.6%. (which is not true). Contrarily, CAGR provides an accurate picture by displaying a growth rate of 0%.

CAGR is more commonly employed in financial research and reporting due to its error-prone nature.

AAGR is still useful in the financial industry and is frequently used to determine the general trend.

Thus, this is a simple Excel formula for calculating the average annual growth rate. To achieve the same result, you may either use the single array formula or the more time-consuming method of first computing the growth rate of each year and then taking an average.