Many times we get posed with the question of calculating CAGR (Compounded Annual Growth Rate) and AAGR (Average Annual Growth Rate). For layman's not knowing what are these, following are good resources to introduce people to CAGR and AAGR.
CAGR – http://www.investopedia.com/terms/c/cagr.asp
AAGR – http://www.investopedia.com/terms/a/aagr.asp
The formula to calculate CAGR is very simple. RATE function can be used to calculate CAGR. We need to know only PV, FV and NPER. Let's assume that you invested $10,000 (hence, this is PV ) and it has become $16,448 (hence, this is FV) after the end of 5 years. So, to calculate CAGR, we will use following formula –
=RATE(5,,-10000,16448)
=RATE(5,,10000,-16448)
Hence, in the above formulas, you need to put either PV as negative or FV as negative. Both can not be positive or negative at the same time.
If you don't have FV but a series of fixed payments (say investment of 3000 every year), you can use –
=RATE(5,-3000,10000)
=RATE(5,1000,-10000)
Now, let's come back to AAGR. There is no single formula for AAGR. If your data set is laid like below, you can use following formula to calculate AAGR –
=SUMPRODUCT((B3:B11-B2:B10)/(B2:B10))/(ROWS(B2:B10))
