How to Calculate the Annual Growth Rate in Excel - 3 Methods

1. Calculate the Compound Annual Growth Rate in Excel

There is a basic formula to calculate the Compound Annual Growth Rate.

=((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

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