spot_img

CAGR

 

There’s no CAGR function in Excel. However, simply use the RRI function in Excel to calculate the compound annual growth rate (CAGR) of an investment over a period of years.

1. The RRI function below calculates the CAGR of an investment. The answer is 8%.

Compound Annual Growth Rate

Note: the RRI function has three arguments (number of years = 5, start = 100, end = 147).

2. The CAGR measures the growth of an investment as if it had grown at a steady rate on an annually compounded basis. We can check this.

Compounded Annually

which is the same as:

Steady Rate

Note: again, number of years or n = 5, start = 100, end = 147, CAGR = 8%.

3. Knowing this, we can easily create a CAGR formula that calculates the compound annual growth rate of an investment in Excel.

A2 = A1 * (1 + CAGR)n

end = start * (1 + CAGR)n

end/start = (1 + CAGR)n

(end/start)1/n = (1 + CAGR)

CAGR = (end/start)1/n – 1

4. The CAGR formula below does the trick.

CAGR formula in Excel

Note: in other words, to calculate the CAGR of an investment in Excel, divide the value of the investment at the end by the value of the investment at the start. Next, raise this result to the power of 1 divided by the number of years. Finally, subtract 1 from this result.

spot_img
Previous articleWhat-If Analysis in Excel
Next articleCreate a Calendar in Excel