How to calculate CAGR in Excel.
Investopedia has a great definition of 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.
A simple way of saying it is that the same growth rate is applied to each of (n) periods, taking you from the beginning value (BV) to the ending value (EV). It doesn’t matter what the actual ups and downs are in the intervening results. You just apply the same growth rate each period to get to the ending value. That growth rate is the CAGR.
The formula is a little tricky. It is (EV/BV)^(1/n) – 1 where
EV is the ending value
BV is the beginning value
n is the number of periods
^ is the power or exponent symbol
One great trick is to use the RATE function in Excel.
=RATE(nper, pmt, pv, fv, type, guess)
=RATE(n, 0, -BV, EV)
note: enter 0 for the pmt, and you can leave off type and guess as they are optional
Here are the formulas in action:
And here are the results:
* One more trick on the CAGR formula. Instead of using the ^ symbol in the formula
(EV/BV)^(1/n) – 1 you can use the POWER function like this:
=POWER((EV/BV),(1/n)) – 1