Excel Formula to Calculate Average Annual Compound Growth Rate

In financial data, it is very important to know the annual growth rate. Average annual compound and annual growth rate help to distinguish where the revenue goes from the start of the company. In this article, I’m going to explain and discuss the average annual compound growth rate formula in Excel.

To make the explanation easy to understand, I’m going to use a sample dataset that represents Revenue information for a particular year. The dataset has 3 columns. These are Year, Revenue/Year, and Year Count.

What is Growth Rate?

Annual Growth Rate (AAGR) —> To determine the long-term trends you can use the Annual Growth Rate. Any investor can get an idea of whether the company is growing or not. Annual growth rate can measure profits or loss, cash flow expenses, etc.

Growth Rate = (Ending value - Beginning value) / Beginning value

Mainly it calculates each year’s growth average.
The drawback of AAGR is it can’t determine the overall risk.

Compound Annual Growth Rate —> CAGR calculates and determines returns depending on the starting and ending revenue/balance. Where we assume that at the end of each year the revenue is reinvested.

Compound Annual Growth Rate =(Ending value / Beginning value)1/Number of Years - 1

Calculate Average Annual Compound Growth Rate Using Formula in Excel: 8 Ways

1. Calculate Average Annual Growth Rate

1.1. Basic Method to Calculate AAGR

To calculate the annual growth rate, you can use the basic formula of growth rate. Then later I will use the AVERAGE function to calculate the average annual growth rate which is known as AAGR.

STEPS:

First, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the AAGR.

Here, I divided the Subtraction revenue value of the Ending year and the Starting year by the revenue value of the Starting year to get the Growth Rate.

• Press the ENTER key to get the Annual Growth Rate.

Here, the resultant value is in Decimal format but I want to present it in a Percentage format.

To do so,

• Select cell E5.
• Then, open the Home tab >> from the Numbers group >> select % sign.

Here, you will get the result in percentage form.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Growth Rate for years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Annual Growth Rate (AAGR).

• Select cell E13
• Then, type the formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the AAGR.

1.2. Alternate Method

To calculate the annual growth rate, you can use the alternate basic formula of growth rate. Then later I will use the AVERAGE function to calculate the average annual growth rate which is known as AAGR.

STEPS:

First, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the AAGR.

Here, I divided the Ending value by the Starting value and then Subtracted -1 to get the Growth Rate.

• Press the ENTER key to get the Annual Growth Rate.

To convert the Decimal point into a Percentage follow the steps explained in method 1.1.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Annual Growth Rate (AAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the AAGR.

2. Basic Method to Calculate Average Compound Annual Growth Rate

By using the basic method you also can calculate the average compound annual growth rate (which is also known as CAGR) formula in Excel. Later, I will calculate the average CAGR.

Let me show you a demonstration of how you can calculate CAGR.

As I want to show the CAGR in Percentage form I will choose the Percentage format for the Growth Rate column.

First, select the cell where you are supposed to place your formula then select Percentage from the Number Group.
Select cell E5 >> select % from Number group

STEPS:

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

Here, I divided the Revenue of the Ending Year by the Starting Year and then used the Power of (1/Year Count). Finally Subtracted -1 from the calculated value.

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the Average CAGR.

Read More: How to Calculate CAGR with Negative Number in Excel

3. Using the POWER Function to Calculate Average Compound Annual Growth Rate

You also can use the POWER function to calculate Compound Annual Growth Rate.

STEPS: 

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

Here, in the POWER function, I used the Quotient of the Revenue of Ending Year and Starting Year as a number then used (1/Year Count) as power. Finally Subtracted -1 from the calculated value.

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the Average CAGR.

4. Using the ROW Function to Calculate Average Compound Annual Growth Rate

If you want, you also can use the ROW function instead of using the year count manually to calculate the Compound Annual Growth Rate.

STEPS: 

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

Here, I divided the Revenue of the Ending Year by the Starting Year then used the Power of (1/Year Count).
Here, to get the Year Count I used the difference of ROW function.
Finally Subtracted -1 from the calculated value.

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the Average CAGR.

Read More: How to Calculate Future Value When CAGR Is Known in Excel

5. Using the RRI Function to Calculate Average Compound Annual Growth Rate

You can use the RRI function to calculate the Compound Annual Growth Rate.

Before diving into the procedure, let me introduce the RRI function to you.

RRI(nper, pv, fv)

The meaning :

Nper —> represents the Net number of years/periods.

Pv —> Considering investment it is the Present Value.

Fv —> Considering investment it is the Future Value.

STEPS: 

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

Here, in the RRI function, I selected the D5 cell as nper which is the Year Count. Then selected the C4 cell as pv and the C5 cell as fv. Here, I used Absolute Cell Reference in cell C4 which is Year Count as I want to use the AutoFill.

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the CAGR.

6. Using the RATE Function to Calculate Average Compound Annual Growth Rate

The RATE function also calculates the Compound Annual Growth Rate.

STEPS: 

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

Here, in the RATE function,

D5 represents nper    : 1 for 1 year (Revenue of the first year)
C4 represents pv        : $56789 (Negative in the formula)
C5 represents fv         : $63456

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the CAGR.

Read More: How to Calculate End Value from CAGR in Excel

7. Using Combined Formula to Calculate Average Compound Annual Growth Rate in Excel

Instead of using the Year Count manually, you can use the ROW function along with the RATE function to calculate the Compound Annual Growth Rate.

STEPS: 

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E5.
• Then, type the following formula in cell E5 to get the CAGR.

• Press the ENTER key to get the Compound Annual Growth Rate.

• You can use the Fill Handle to AutoFill the formula for the rest of the cells.

Here, I got the Compound Annual Growth Rate for the years 1/1/2014 to 1/8/2021.

Now, I will calculate the Average Compound Annual Growth Rate (CAGR).

• Select cell E13
• Then, type the following formula in cell E13 or into the Formula Bar.

Here, in the AVERAGE function, I selected the cell range E5:E11 as a number1 to calculate the average annual growth rate.

• Press ENTER to get the CAGR.

8. Using the XIRR Function to Calculate Average Compound Annual Growth Rate in Excel

The XIRR function is the updated function to calculate the Compound Annual Growth Rate in a blink.

STEPS: 

• Before using the XIRR function you will need to convert the Ending Year value in negative form.

To begin with, select any cell where you want to keep your resultant value.

• I selected cell E14.
• Then, type the following formula in cell E14 to get the CAGR.

Here, in the XIRR function, I selected the range C14:C15 as values where it contains the revenue of the starting and ending year and D14:D15 as dates where it contains the starting and ending year.

• Press ENTER key to get the Compound Annual Growth Rate.

Now, to convert the Decimal value into a Percentage,
Select cell E14 >> from Number Formats >> select Percentage

Here, the CAGR value is converted into a Percentage.

Read More: CAGR Formula in Excel

Things to Remember

AAGR and CAGR both calculate the annual growth rates. Whereas AAGR completely overlooks the effect of compounding that accumulates over the years.

But, CAGR doesn’t ignore the compounding impact over the years.

Practice Section

I’ve provided a practice sheet to practice the explained methods.

Download Practice Workbook

Conclusion

In this article, I have shown several to calculate the average annual compound growth rate formula in Excel. These ways will help you to get the report of growth rate easily based on your requirements. Feel free to comment down below for any types of queries and suggestions.

Related Articles

• How to Calculate 3-Year CAGR with Formula in Excel
• How to Calculate 5 Year CAGR Using Excel Formula
• How to Create CAGR Graph in Excel