### 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**

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

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

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

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.

**Download Practice Workbook**

**Related Articles**

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