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.

Using Arithmetic Formula

You’ve calculated the 5-year CAGR formula in Excel.

5 year cagr formula excel Arithmetic Formula


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

Utilizing the RATE Function

The results should look like the image shown below.

5 year cagr formula excel using RATE Function


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%

Applying the POWER Function

The output should look like the screenshot given below.

 5 year cagr formula excel using POWER Function


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%

Using RRI Function

Your result should look like the image shown below.

5 year cagr formula excel using RRI Function


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.

Applying GEOMEAN Function

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

Using Fill Handle

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

Using GEOMEAN function

The output should appear in the picture shown below.

5 year cagr formula excel with GEOMEAN Function


Method 6 – Employing IRR Function

Steps:

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

Inserting Zeros

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

Using IRR function

The CAGR value should be equal to 15%.

5 year cagr formula excel with IRR Function


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%

Utilizing XIRR Function

The results should look like the picture given below.

5 year cagr formula excel with XIRR Function


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.

3 Year CAGR Formula in Excel

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

Applying RRI function

Your output should look like the screenshot given below.

3 Year CAGR Formula in Excel


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo