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

### Steps:

• Go to the C12 cell >> and enter the formula given below.

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

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

Note: Press the CTRL + SHIFT + % keys on your keyboard to change the CAGR value to a percentage.

Youâ€™ve calculated the 5-year CAGR formula in Excel.

### Steps:

• 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. 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. 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%.

The results should look like the image shown below.

### Steps:

• 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. 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%

The output should look like the screenshot given below.

### Steps:

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%

Your result should look like the image shown below.

### Steps:

• Go to the D6 cell >> enter the formula given below.

`=C6/C5`

The C5 and C6 cells represent the Number of Subscribers for the Years 2020 and 2021.

• Use the Fill Handle Tool to copy the formula into the cells below.

• Insert the GEOMEAN function into the C12 cell.

`=GEOMEAN(D6:D10)-1`

The D6:D10 series points to the Growth Factor values for 2021 to 2025.

Formula Breakdown

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

The output should appear in the picture shown below.

### Steps:

• Insert zeros in the cells containing the intermediate Revenue values.

• Copy and paste the expression into the Formula Bar.

`=IRR(C5:C10)`

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. C5:C10 is the values argument that refers to the series of Revenues.
• Output â†’ 15%

The CAGR value should be equal to 15%.

### Steps:

• Proceed to the C16 cell >> type in the equation given below.

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

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. C13:C14 is the values argument that refers to the Initial and Final Values for the Number of Subscribers. D13:D14 represents the dates argument, indicating the Initial and Final Values for the Dates.
• Output â†’ 8%

The results should look like the picture given below.

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

### Steps:

Enter the formula into the C10 cell.

`=COUNT(B5:B8)-1`

The B5:B8 cells represent the number of Years.

• Type in the RRI function in the C11 cell as shown below.

`=RRI(C10,C5,C8)`

The C5 and C8 cells represent the Initial and Final Values and the C10 cell is the Year value.

Note: Press CTRL + SHIFT + %Â  shortcut to change the cell formatting to percentage.

Your output should look like the screenshot given below.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!