Excel is a popular and useful tool for modeling growth and analyzing data. One such case is the calculation of CAGR. In this regard, Excel becomes a handy tool to perform such calculations effortlessly. Keeping this in mind, this article demonstrates 7 useful ways how to calculate 5 year CAGR using formula in Excel.

## What Is CAGR?

First of all, letâ€™s dwell a little upon what “CAGR” is.

**CAGR** stands for *Compound Annual Growth Rate* which is a measure of growth on the assumption that it compounds at a constant rate over a specified period of time. Typically, financial professionals use **CAGR** to evaluate and contrast numerous possibilities and come to a more informed decision.

## Formula to Calculate CAGR in Excel

Now, the formula to calculate the *CAGR *is as follows:

where,

**Final Value**is the closing value of the growth model.**Initial Value**refers to the beginning value of the model.**Year**represents the time interval in years, months, quarters, etc.

## Calculate 5 Year CAGR Using Formula in Excel: 7 Ways

Suppose consider the Yearly Growth Rate of the ExcelDemy Subscribers dataset shown in the **B4:C10** cells, which shows the Year and Number of Subscribers respectively. Here, we want to calculate the *Compound Annual Growth Rate* of this data model, therefore, without further delay letâ€™s explore all the nitty-gritty of each method with appropriate illustrations.

Here, we have used the *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Method 1__: Using Arithmetic Formula

First and foremost, letâ€™s kick things off by applying the arithmetic formula of **CAGR** in Excel.

📌 ** Steps**:

- At the very beginning, go to the
**C12**cell >> and enter the formula given below.

`=(C10/C5)^(1/5)-1`

Here, the **C5 **and **C10** cells refer to the *Initial* and *Final Values* while the 5 represents the *Year*.

📃 *Note: **Please make sure to press the CTRL + SHIFT + % keys on your keyboard to change the CAGR value to a percentage.*

Thatâ€™s it. Youâ€™ve calculated the 5 year CAGR formula in Excel. Itâ€™s that simple!

__Method 2__: Utilizing the RATE Function

If youâ€™re one of those people who enjoy using Excel functions, then the following methods have you covered. On this occasion, weâ€™ll use **the RATE function** to calculate the growth rate of the subscribers and **the ROW function** to return the *Initial* and *Final Values*. So, letâ€™s see it in action.

📌 ** Steps**:

- In the first place, move to the
**C12**cell >> type in the equation given below.

`=RATE(ROW(C10)-ROW(C5),,-C5,C10)`

In the above equation, the **C5 **and **C10** cells indicate the *Initial* and *Final Values* while the 5 represents the *Year*.

**Formula Breakdown**

**ROW(C10) â†’**returns the serial number of a reference. Here, the**ROW**function returns the row number of the**C10**cell.**Output â†’ 10**

**ROW(C10)-ROW(C5) â†’**becomes**Â**- 10-5
**â†’ 5**

- 10-5
**RATE(ROW(C10)-ROW(C5),,-C5,C10) â†’**becomes**RATE(5,,-C5,C10) â†’**returns the interest rate per period of a loan or investment. Here, 5 is theargument that represents the number of periods, the*nper*argument is left blank, the*pmt***C5**cell refers to theargument which indicates the*pv**Initial Value*of*26,455,*and the**C10**cell is theargument that points to the*fv**Final Value*of*38,871*.**Output â†’ 8%**.

Finally, the results should look like the image shown below.

**Read More:Â **CAGR Formula in Excel

__Method 3__: Applying POWER Function

Alternatively, we can apply **the POWER function** to compute the 5-year CAGR formula of the growth model. For instance, the **POWER** function returns the result of the ratio of the *Final *to *Initial Value *raised to the reciprocal of the *Year *value.

📌 ** Steps**:

- Initially, navigate to the
**C12**cell >> insert the following expression.

`=POWER(C10/C5,1/5)-1`

In this expression, the **C5 **and **C10** cells point to the *Initial *and *Final* Values respectively whereas, 5 refers to the *Year* value.

**Formula Breakdown**

**POWER(C10/C5,1/5)-1â†’**returns the result of a number raised to a power. Here,**C10/C5**is the*number*argument that refers to the ratio of the*Final*to*Initial Value*. Following, 1/5 represents the*power*argument that indicates the raised indices.**Output â†’ 8%**

Finally, the output should look like the screenshot given below.

**Read More:**Â How to Calculate End Value from CAGR in Excel

__Method 4__: Using RRI Function

Normally, **the RRI function** estimates the corresponding interest rate for the growth of an investment. However, it can also be used to calculate the compound annual growth rate. It’s simple and easy; just follow along.

📌 ** Steps**:

First, insert the following formula into the **C12** cell.

`=RRI(5,C5,C10)`

Specifically, the **C5** and **C10** cells represent the *Initial* and *Final Values*, in contrast, **5 **is the number of *Years*.

*📃** Note: You can open the *

*Format Cells**dialog box by pressing*

**CTRL**+**1**and changing the cell formatting to percentage.**Formula Breakdown**

**RRI(5,C5,C10)â†’**returns an equivalent interest rate for the growth of an investment. 5 is theargument representing the number of periods, the*nper***C5**cell is theargument which is the*pv**Initial Value*of*26,455,*and the**C10**cell is theargument referring to the*fv**Final Value*of*38,871*.**Output â†’ 8%**

Subsequently, your result should look like the image shown below.

**Read More:Â **How to Calculate Future Value When CAGR Is Known in Excel

__Method 5__: Applying the GEOMEAN Function

Conversely, we can use the **GEOMEAN** function to enter a series of *Growth Factors* and obtain the geometric mean of this array which is the CAGR value. Now, allow me to demonstrate the process in the steps below.

📌 ** Steps**:

- To begin with, go to the
**D6**cell >> enter the formula given below.

`=C6/C5`

In this case, the **C5 **and **C6** cells represent the *Number of Subscribers *for the *Years 2020* and *2021*.

- Then, use the
**Fill Handle**Tool to copy the formula into the cells below.

- Next, insert the
**GEOMEAN**function into the**C12**cell.

`=GEOMEAN(D6:D10)-1`

For instance, the **D6:D10** series points to the *Growth Factor* values for the Years *2021* to *2025*.

**Formula Breakdown**

**GEOMEAN(D6:D10)-1 â†’**returns the geometric mean of an array or range of positive numbers. Here,**D6:D10**is the*number1*argument that refers to the series of*Growth Factors*.**Output â†’ 8%**

Ultimately, the output should appear in the picture shown below.

**Read More:Â **Excel Formula to Calculate Average Annual Compound Growth Rate

__Method 6__: Employing IRR Function

Although **the IRR function** calculates the internal rate of return for a series of cashflows, it can be re-purposed to get the CAGR value. However, we have to manipulate the dataset to get the desired results. Hence, letâ€™s see the process in detail.

Now, assuming the Yearly Growth Rate of Revenue dataset shown in the **B4:C11** cells, which shows the *Year* and *Revenue* respectively.

📌 ** Steps**:

- To start, insert zeros in the cells containing the intermediate
*Revenue*values.

- Later, copy and paste the expression into the
**Formula Bar**.

`=IRR(C5:C10)`

In this situation, the **C5:C10** array indicates the *Revenue *values for the *Years 0* through *10*.

**Formula Breakdown**

**IRR(C5:C10) â†’**returns the internal rate of return for a series of cash flows. Here,**C5:C10**is the*values*argument that refers to the series of*Revenues*.**Output â†’ 15%**

Consequently, the CAGR value should be equal to *15%*.

__Method 7__: Utilizing the XIRR Function

Last but not least, weâ€™ll employ **the XIRR function** which returns the non-periodic cash flows, so weâ€™ll modify our existing dataset to show the *Dates *instead of the *Year*, such that we can compute the CAGR value.

📌 ** Steps**:

- First, proceed to the
**C16**cell >> type in the equation given below.

`=XIRR(C13:C14,D13:D14)`

For example, the **C13:C14** and **D13:D14 **range of cells refer to the *Initial* and *Final Values *corresponding to the *Number of Subscribers* and the *Date*.

**Formula Breakdown**

**XIRR(C13:C14,D13:D14)â†’**returns the internal rate of return for a schedule of cash flows. Here,**C13:C14**is the*values*argument that refers to the*Initial*and*Final Values*for the*Number of Subscribers*. Following,**D13:D14**represents the*dates*argument indicating the*Initial*and*Final Values*for the*Dates*.**Output â†’ 8%**

Eventually, the results should look like the picture given below.

**Read More:Â **How to Calculate CAGR with Negative Number in Excel

## How to Calculate 3-Year CAGR Using Formula in Excel?

So far, weâ€™ve discussed the 5-year CAGR formula in Excel. If you want to obtain the CAGR for a different number of years, for instance, 3 years, the following section answers this exact question. In this case, weâ€™ll apply **the COUNT function** to count the number of years in the dataset and obtain the CAGR value with the **RRI **function. So, follow along.

📌 ** Steps**:

In the first place, enter the formula into the **C10** cell.

`=COUNT(B5:B8)-1`

Here, the **B5:B8** cells represent the number of *Years*.

- In turn, type in the
**RRI**function in the**C11**cell as shown below.

`=RRI(C10,C5,C8)`

On this occasion, the **C5** and **C8** cells represent the *Initial* and *Final Values* and the **C10 **cell is the *Year *value.

*📃** Note: You can press CTRL + SHIFT + %Â shortcut to change the cell formatting to percentage.*

Finally, your output should look like the screenshot given below.

**Practice Section**

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

To sum up, this article shows 7 effective methods on how to calculate 5-year CAGR using formulas in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here.

**Related Articles**

**<< Go Back to Compound Interest in Excel |Â ****Excel for Finance**** | ****Learn Excel**