How to Calculate Compound Annual Growth Rate in Excel

Mar 18, 2020

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

Compound Annual Growth Rate

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.

Compounded Annually

which is the same as:

Steady Rate

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

end = start * (1 + CAGR)n

end/start = (1 + CAGR)n

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

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

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

#Tutorial#How To#Functions

How To Use sparklines to show data trends In Excel

How to Count the Number of Cells that Contain Specific Text