# Excel Formula to Calculate Average Annual Compound Growth Rate

Get FREE Advanced Excel Exercises with Solutions!

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

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

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.

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.

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

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

### 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`

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

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

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

### 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.
`=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 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.
`=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.

### 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)`

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

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

• 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.
`=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.

### 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.
`=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.

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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

Advanced Excel Exercises with Solutions PDF