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

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

## Add a Comment