Calculate 5 Year CAGR Using Formula in Excel: 7 Ways

Method 1 – Using Arithmetic Formula

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.

Method 2 – Utilizing the RATE Function

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.

Method 3 – Applying POWER Function

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.

Method 4 – Using RRI Function

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.

Method 5 – Applying the GEOMEAN Function

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.

Method 6 – Employing IRR Function

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

Method 7 – Utilizing the XIRR Function

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%