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.

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

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

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

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

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

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

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

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

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

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

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

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

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