To calculate annual growth over multiple periods, **Compound Average Growth Rate (CAGR)** and **Annual Average Growth Rate (AAGR)** are the two most common and efficient methods in Excel. In this article, you will learn how to calculate the **Compound and Average Annual Growth Rate** in Excel.

**Table of Contents**hide

**Download Workbook**

You can download the free practice Excel workbook from here.

**Calculate the Compound and the Average Annual Growth Rate in Excel**

In this section, you will learn how to calculate the **Compound Annual Growth Rate (CAGR)** manually and with the **XIRR function** and how to calculate the **Average Annual Growth Rate (AAGR)** in Excel.

**1. Calculate the Compound Annual Growth Rate in Excel**

You can calculate the growth rate from the beginning investment value to the ending investment value where the investment value will be augmenting over the given period of time with the **CAGR**.

In mathematical terms, there is a basic formula to calculate the **Compound Annual Growth Rate**.

The** formula** is:

**=((End Value/Start Value)^(1/Time Periods)-1**

We can easily apply this formula to find the **Compound Annual Growth Rate** for our dataset shown below.

The steps to calculate the **Compound Annual Growth Rate** in Excel are discussed below.

**Steps:**

**Pick any cell**from your dataset (in our case it is**Cell E5**) to store the**CAGR**.- In that cell, write the following formula,

`=(C15/C5)^(1/(11-1))-1`

Here,

**C15**= End Value**C5**= Start Value**11**= Time Period (there are 11records in our dataset)*Date*

- Press
**Enter**.

You will get the calculated **Compound Annual Growth Rate** for your data in Excel.

**Read More:**** How to Calculate Monthly Growth Rate in Excel (2 Methods)**

**2. Compute the Compound Annual Growth Rate with the XIRR Function in Excel**

If you want to calculate the **Compound Annual Growth Rate** with only a formula, then with Excel’s **XIRR **function you can do that.

Excel’s **XIRR** function returns the internal rate of return for a series of investments that may or may not occur on a regular basis.

The** syntax** for **XIRR** function is:

**=XIRR(value, date, [guess])**

**Parameter Description**

Parameter | Required/ Optional | Description |
---|---|---|

value |
Required | A schedule of investment flow that corresponds to a series of cash payment dates. |

date |
Required | A series of cash payment dates that corresponds to a schedule of investment flow. Dates should be entered through the DATE function or through Excel Format options or as a result of other functions or formulas. |

[guess] |
Optional | A number to guess which is close to the result of XIRR. |

Before applying the **XIRR** function, you have to declare the ** Start Value** and the

**in other cells so that you can use them later inside the formula. And that is exactly what we have done with our dataset shown below.**

*End Value*** **

According to our dataset that consists of ** Date** and

**, we stored the first value,**

*Sales Value***$1,015.00**from the

**column (**

*Sales Value***Column C**) in

**Cell F5**and the last value,

**$1,990.00**from the

**column (**

*Sales Value***Column C**) in

**Cell F6**. Remember to store the

**End Value as a negative value**. meaning, with a

**minus sign (-)**before it.

Similarly, we stored the corresponding first date, **1-30-2001**, from the ** Date** column (

**Column B**) in

**Cell G5**and the last date,

**1-30-2011**from the

**column (**

*Date***Column D**) in

**Cell G6**.

Steps to calculate the **Compound Annual Growth Rate with the XIRR function** are given below.

**Steps:**

**Pick any cell**from your dataset (in our case it is**Cell F9**) to store the result of the**CAGR**.- In that cell, write the following formula,

`=XIRR(F5:F6, G5:G6)`

Here,

**F5**= Start Sales Value**F6**= End Sales Value**G5**= Start Date Value**G6**= End Date Value

- Press
**Enter**.

You will get the calculated **Compound Annual Growth Rate with the XIRR function** for your data in Excel.

**Read More:** **How to Use the Exponential Growth Formula in Excel (2 Methods)**

**Similar Readings**

**How to Calculate Cumulative Percentage in Excel (6 Easy Methods)****Excel Formula to Add Percentage Markup [with Calculator]****Formula to calculate percentage of grand total (4 Easy Ways)****Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)****How to Use Profit and Loss Percentage Formula in Excel (4 Ways)**

**3. Determine the Average Annual Growth Rate in Excel**

Till now you have been learning how to get the **Compound Annual Growth Rate **for your data in Excel. But this time we will show you how to measure the **Average Annual Growth Rate (AAGR)** for your data in Excel.

You can predict the average annual growth rate by factoring in the existing and the upcoming value of investment regarding the time period per year in Excel with **AAGR**.

To calculate the **Average Annual Growth Rate** of every year, the mathematical formula is:

**=(End Value – Start Value)/ Start Value**

We can easily apply this formula to find the **Average Annual Growth Rate** for our dataset shown below.

The steps to calculate the **Average Annual Growth Rate** in Excel are discussed below.

**Steps:**

**Pick any cell**from your dataset (in our case it is**Cell D6**) to store the**AAGR**.- In that cell, write the following formula,

`=(C6-C5)/C5`

Here,

**C6**= End Value**C5**= Start Value

- Press
**Enter**.

You will get the calculated **Average Annual Growth Rate** for the specific data from your dataset in Excel.

- Now drag the
**Fill Handle**to apply the formula to the rest of the cells in the dataset.

You will get the calculated **Average Annual Growth Rate** for all the data from your dataset in Excel.

**Read More: How to Calculate Revenue Growth Rate in Excel (3 Methods)**

**Things to Remember**

- When storing the
for calculating the*End Value***Compound Annual Growth Rate (CAGR)**with the**XIRR**Function, you must write the value with a**minus sign (-)**. - While calculating the
**Average Annual Growth Rate (AAGR)**, the first cash flow result is optional. - If you get the result in the decimal format then you can change the format into the percentage format from the
**Number Format**option in Excel.

**Conclusion**

This article explained in detail how to calculate **the Compound and Average Annual Growth Rate** in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

**Related Articles**

**Subtract a Percentage in Excel (Easy Way)****How to Calculate Grade Percentage in Excel (3 Easy Ways)****Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)****How to Calculate Discount Percentage Formula in Excel****Calculate Variance Percentage in Excel (3 Easy Methods)****How to Calculate Bacterial Growth Rate in Excel (2 Easy Ways)**