# How to Calculate 3-Year CAGR with Formula in Excel: 7 Ways

## Method 1 – Using Conventional Formula to Calculate 3-Year CAGR

Steps

• To calculate the CAGR in Excel, select cell C10.
• Write down the following formula.
`=(C8/C5)^(1/3)-1`

• Press Enter to apply the formula.

## Method 2 – Applying the RATE Function

Steps

• To calculate the CAGR in Excel, select cell C10.
• Write down the following formula.
`=RATE(ROW(C8)-ROW(C5),,-C5,C8)`

• Press Enter to apply the formula.

Breakdown of the Formula

RATE(ROW(C8)-ROW(C5),,-C5,C8): The ROW function returns the row number of the C8 and C5 cells. The RATE function returns the interest rate per period of a loan or investment. 3 is the upper 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 \$15250, and the C8 cell is the fv argument that points to the Final Value of \$25412.

## Methods 3 – Utilizing the POWER Function to Calculate 3-Year CAGR

Steps

• To calculate the CAGR in Excel, select cell C10.
• Write down the following formula.
`=POWER(C8/C5,1/3)-1`

• Press Enter to apply the formula.

Breakdown of the Formula

POWER(C8/C5,1/3)-1: The function returns the result of a number raised to a power. C8/C5 is the number argument that refers to the Final to Initial Value ratio. 1/3 represents the power argument that indicates the raised indices.

## Method 4 – Using the RRI function

Steps

• To calculate the CAGR in Excel, select cell C10.
• Write down the following formula.

`=RRI(3,C5,C8)`

• Press Enter to apply the formula.

Breakdown of the Formula

RRI(3,C5,C8): The function 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 \$15250, and the C8 cell is the fv argument referring to the Final Value of \$25412. The final output is 18.56%.

## Method 5 – Applying the GEOMEAN Function to Compute 3-Year CAGR

Steps

• Calculate the growth factor.
• Set the growth factor 0 on cell D5, which denotes the first year of our calculation.
• Select cell D6
• Write the following formula.
`=C6/C5`

• Press Enter to apply the formula.

• Drag the Fill Handle icon down the column.

• Calculate the CAGR.
• Select cell C10.
• Write down the following formula.
`=GEOMEAN(D6:D8)-1`

• Press Enter to apply the formula.

Breakdown of the Formula

GEOMEAN(D6:D8)-1: The function returns the geometric mean of an array or range of positive numbers. D6:D8 is the number 1 argument for the series of Growth Factors. Here, the output is 18.56%.

## Method 6 – Utilizing IRR Function

Steps

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

• Select cell C11.
• Write down the following formula.
`=IRR(C5:C8)`

• Press Enter to apply the formula.

Breakdown of the Formula

IRR(C5:C8): The function returns the internal rate of return for a series of cash flows. C5:C8 is the values argument referring to the Revenues series. The output is 20.95%.

## Method 7 – Applying XIRR Function to Calculate 3-Year CAGR

Steps

• Put the initial and final sales amount and dates in a different place.

• Select cell C14.
• Write down the following formula.
`=XIRR(C11:C12,D11:D12)`

• Press Enter to apply the formula.

Breakdown of the Formula

XIRR(C11:C12,D11:D12): The function returns the internal rate of return for a schedule of cash flows. C11:C12 is the values argument that refers to the Initial and Final Values for the sales amount. D11:D12 represents the dates argument indicating the Initial and Final Values for the Dates.

## Things to Remember

• To change the CAGR value to a percentage, press CTRL+SHIFT+”%” keys on your keyboard. Alternatively, you can open the Format Cells dialog box by pressing CTRL+1 and changing the cell formatting to a percentage.
• Excel’s compound annual growth rate formula can determine how much the investment should return annually at a constant growth rate.
• The #VALUE! error is most likely present if you encounter any Excel CAGR formula errors.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF