How to calculate CAGR in Excel

CAGR Calculation in Excel RRI

CAGR or Compounded Annual Growth Rate is an investing and business-specific calculation to measure the annualized return of an investment over a given period of time. Here is an example to calculate CAGR in Excel.

CAGR formula Excel

In order to calculate the compound annual growth rate, we need to use the CAGR excel formula.

=RRI (nper, pv, fv)
CAGR Formula Excel RRI

Where,

RRI = This is referred to as the average required rate of interest for the growth of an investment.

Nper = This is states to the number of periods for an investment return (N)

Pv = This means a present value of an investment (P0 as initial investment value)

Fv = This mentions the future value of an investment (P1 as ending value)

CAGR calculation in Excel

For example, in order to calculate the CAGR in Excel, you need to put the requisite values as shown in above CAGR excel format.

So, enter the following CAGR formula in the Excel sheet to calculate the value of the compound annual growth rate.

= RRI (E5,E4,E6)
CAGR Excel Formula RRI

This CAGR is calculated by using the RRI formula in Excel from the given data of investment.

In this example,

Cell E4: This is presenting initial value or present value (PV) of an investment as $10,000

Cell E5: This refers to a number of periods (nper) as 5 years

Cell E6: This states the ending value or future value (FV) of an investment as $13,500

Cell E7: Calculated filed

Result

Thus, the Compounded Annual Growth Rate (CAGR) or average rate of return of an investment will be 6.19%, if you invest money for 5 years, which will have $10,000 as an initial investment (present value) with the future value of $13,500.

Download CAGR calculator Excel template using RRI formula

You can also use the online CAGR calculator excel for swift analysis and results.

Academic formula of CAGR

CAGR = (initial value / ending value) ^ (1 / time in years) – 1
Academic CAGR formula
Manual Formula CAGR in Excel

Where;

Cell E6 = Ending value

Cell E4 = Initial value

Cell E5 = Time period

Thus, we can use both generic formula and excel formula to calculate the CAGR value. Both calculations retrieve the same answer.

How to calculate CAGR in Excel with negative number

GEOMEAN Excel function for CAGR

In order to calculate the CAGR in Excel with a negative number, you need to use the GEOMEAN formula in excel.

Firstly, and most importantly you need to calculate the percentage of change during each period from start to end as shown in column E.

Secondly, find out the growth factor by adding 1 + percentage of change as calculated in column F.

Lastly, use the GEOMEAN function in excel to calculate the CAGR value.

Formula of GEOMENA in Excel

=GEOMEAN (number1, number 2, number 3,….)
GEOMEAN function in Excel

number = This refers to the values for which you need to measure the average value of CAGR

The formula in cell F12 is:

= GEOMEAN (F6:F10) – 1
GEOMEAN Formula

Download CAGR Excel template using GEOMEAN

Add a Comment

Your email address will not be published. Required fields are marked *