# How to Calculate 5 Year CAGR Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Suppose consider the Yearly Growth Rate of 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 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
• 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 the nper argument that represents the number of periods, the pmt argument is left blank, the C5 cell refers to the pv argument which indicates the Initial Value of 26,455, and the C10 cell is the fv argument that points to the Final Value of 38,871.
• Output → 8%. Finally, the results should look like the image shown below. ### 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. ### 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 the nper argument representing the number of periods, the C5 cell is the pv argument which is the Initial Value of 26,455, and the C10 cell is the fv argument referring to the Final Value of 38,871.
• Output → 8% Subsequently, your result should look like the image shown below. ### Method 5: Applying 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.  • 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. ### 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 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. ## 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. ## Related Articles Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  