Excel Formula to Calculate Average Annual Compound Growth Rate

In financial data, it is very much important to know the annual growth rate. Average annual compound and annual growth rate help to distinguish where the revenue goes from the starting 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.

Sample Dataset of Average Annual Compound Growth Rate Formula in Excel


Download Practice Workbook


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


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

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.
=(C5-C4)/C4

Average Annual Growth Rate Formula in Excel

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

  • Press 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.

Average Annual Growth Rate Formula in Excel

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.

Average Annual Growth Rate Formula in Excel

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 following formula in cell E13 or into the Formula Bar.
=AVERAGE(E5:E11)

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.
=C5/C4-1

Average Annual Growth Rate Formula in Excel

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

  • Press 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.

Average Annual Growth Rate Formula in Excel

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.
=AVERAGE(E5:E11)

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.

Average Annual Growth Rate Formula in Excel

Read More: How to Calculate End Value from CAGR in Excel (6 Methods)


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 the demonstration of how you can calculate CAGR.

As I want to show the CAGR in Percentage form so 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

Average Annual Compound Growth Rate Formula in Excel

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.
=(C5/$C$4)^(1/D5)-1

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

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

Average Annual Compound Growth Rate Formula in Excel

  • 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.
=AVERAGE(E5:E11)

Average Annual Compound Growth Rate Formula in Excel

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 (2 Methods)


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.
=POWER(C5/$C$4,1/D5)-1

Average Annual Compound Growth Rate Formula in Excel

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 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.

Average Annual Compound Growth Rate Formula in Excel

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.
=AVERAGE(E5:E11)

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.

Average Annual Compound Growth Rate Formula in Excel

Read More: Reverse Compound Interest Calculator in Excel (Download for Free)


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 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.
=(C5/$C$4)^(1/(ROW(C5)-ROW($C$4)))-1

Average Annual Compound Growth Rate Formula in Excel

Here, I divided the Revenue of Ending Year by Starting Year then used 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 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.

Average Annual Compound Growth Rate Formula in Excel

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.
=AVERAGE(E5:E11)

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: Methods to Apply Continuous Compound Interest Formula 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)

Average Annual Compound Growth Rate Formula in Excel

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.
=RRI(D5,$C$4,C5)

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 ENTER key to get the Compound Annual Growth Rate.

Average Annual Compound Growth Rate Formula in Excel

  • 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.
=AVERAGE(E5:E11)

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.

Average Annual Compound Growth Rate Formula in Excel

Read More: How to Use Compound Interest Formula in Excel


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.
=RATE(D5, ,-$C$4,C5)

Average Annual Compound Growth Rate Formula in Excel

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 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.

Average Annual Compound Growth Rate Formula in Excel

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.
=AVERAGE(E5:E11)

Average Annual Compound Growth Rate Formula in Excel

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.

Average Annual Compound Growth Rate Formula in Excel

Read More: A Daily Compound Interest Calculator in Excel (Template Attached)


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 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.
=RATE(ROW(B5)-ROW($B$4), ,-$C$4,C5)

Average Annual Compound Growth Rate Formula in Excel

  • Press 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.

Average Annual Compound Growth Rate Formula in Excel

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.
=AVERAGE(E5:E11)

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.

Average Annual Compound Growth Rate Formula in Excel

Read More: CAGR Formula in Excel: With Calculator and 7 Examples


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

The XIRR function is the updated function to calculate 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.

Average Annual Compound Growth Rate Formula in Excel

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.
=XIRR(C14:C15,D14:D15)

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.

Average Annual Compound Growth Rate Formula in Excel

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: Compound Interest Formula in Excel: Calculator with All Criteria


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.

Average Annual Compound Growth Rate Formula in Excel


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo