How to Calculate Compound Annual Growth Rate in Excel

What Is Compound Annual Growth Rate – CAGR?

Compound annual growth rate (CAGR) is the rate of return that would be required for an investment to grow from its beginning balance to its ending balance, assuming the profits were reinvested at the end of each year of the investment’s lifespan.

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

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

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

which is the same as:

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

1. 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)ⁿ

end = start * (1 + CAGR)ⁿ

end/start = (1 + CAGR)ⁿ

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

CAGR = (end/start)^1/n - 1

1. The CAGR formula below does the trick.

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.