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

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

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.

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

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.

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

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

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

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

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.

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

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.

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

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

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

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.

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

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

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

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

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