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) |
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 the 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) |
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 the 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 the CAGR calculator Excel template using the RRI formula
You can also use the online CAGR calculator Excel for swift analysis and results.
The academic formula of CAGR
| CAGR = (initial value / ending value) ^ (1 / time in years) – 1 |
Where;
Cell E6 = Ending value
Cell E4 = Initial value
Cell E5 = Time period
Thus, we can use both the generic formula and the Excel formula to calculate the CAGR value. Both calculations retrieve the same answer.
How to calculate CAGR in Excel with a negative number
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,….) |
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 |