# How to Calculate the Annual Growth Rate in Excel – 3 Methods

### Method 1 – Calculate the Compound Annual Growth Rate in Excel

This is the basic formula:

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

This is the sample dataset.

Steps:

• Select any cell in your dataset (Here, E5) to store the CAGR.
• Enter the following formula.
`=(C15/C5)^(1/(11-1))-1`
• C15 = End Value
• C5 = Start Value
• 11 = Time Period (there are 11 Date records in our dataset)
• Press Enter.

You will get the Compound Annual Growth Rate. You can also Calculate the Monthly Growth Rate in Excel!

### Method 2 – Compute the Compound Annual Growth Rate with the XIRR Function in Excel

The syntax for the 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, declare the Start Value and the End Value in other cells.

The dataset that consists of Date and Sales Value. The first value, \$1,015.00 in the Sales Value column (Column C) is in F5 and the last value, \$1,990.00 in the Sales Value column (Column C) is in F6. Store the End Value as a negative value with a minus sign (-) before it.

The first date, 1-30-2001, in the Date column (Column B) is in G5 and the last date, 1-30-2011 in the Date column (Column D) is in G6.

Steps:

• Choose any cell in your dataset (Here, F9) to display the result of the CAGR.
• Enter the following formula.
`=XIRR(F5:F6, G5:G6)`
• F5 = Start Sales Value
• F6 = End Sales Value
• G5 = Start Date Value
• G6 = End Date Value
• Press Enter.

The Compound Annual Growth Rate is calculated using the XIRR function.

### Method 3 – Determine the Average Annual Growth Rate in Excel

The mathematical formula is:

=(End Value – Start Value)/ Start Value

This is the sample dataset.

Steps:

• Select any cell in your dataset (Here, D6) to display the AAGR.
• Enter the following formula,
`=(C6-C5)/C5`
• C6 = End Value
• C5 = Start Value
• Press Enter.

The Average Annual Growth Rate is calculated.

• Drag the Fill Handle to apply the formula to the rest of the cells.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF