Excel Hack

CAGR function in Excel

There is no function in Excel that can calculate CAGR directly. However, another function can be used to calculate CAGR. We will introduce a function that can find the CAGR, which is smoother than using the CAGR formula, so if you have any trouble about CAGR formula, please try using these function.

Functions for which CAGR can be calculated

RRI function

=RRI(nper,pv,fv)

POWER function

=POWER(number,power)

GEOMEAN function

=GEOMEAN(number1,[number2],…)

Use RRI function

The RRI function is originally intended to calculate what percentage of investments or savings should be invested until the target amount is reached, but it can also be used to calculate CAGR. The format of the RRI function for CAGR is =RRI(Periods,Beginning value,Ending value).

Total Time: 1 minute

Enter a formula

Enter a formula

Enter =RRI(3,B2,B5) in any cell.

CAGR is calculated

CAGR is calculated

Press Enter to calculate the CAGR.

Set the percent style

Set the percent style

Press Ctrl + Shift + 5 to set a percent style to make it easier to understand.

Use POWER fuction

The POWER function is a function to calculate powers. The formula for obtaining CAGR is =POWER(Ending Value/Begining Value,1/Periods)-1.

Enter a formula

Enter =POWER(B5/B2,1/3)-1 in any cell.

CAGR is calculated

Press Enter to calculate the CAGR.

Set the percent style

Press Ctrl + Shift + 5 to set a percent style to make it easier to understand.

Use GEOMEAN function

The GEOMEAN function returns the geometric mean of an array or range of positive data. The formula =GEOMEAN(range)-1 is used to find the CAGR. The range argument specifies the cell in which the year-to-year comparison was calculated.

Select cells

First calculate the year-to-year comparison for each year.

Press Shift + (Down Arrow key) to select cells C3 to C5.

Enter a formula

Enter =B3/B2 in cell C3.

Press Ctrl+Enter

Press Ctrl + Enter copied the formulas to all columns and the year over year ratio was obtained.

Enter GEOMEAN fuction

Enter =GEOMEAN(C3:C5)-1 in any cell.

CAGR is calculated

Press Enter to calculate the CAGR.

Set the percent style

Press Ctrl + Shift + 5 to set a percent style to make it easier to understand.