Compound Average Growth Rate

[jake lamotta]

Does anyone have any ideas about a formula for CUMULATIVE Compound
Average Growth Rate (CAGR)
for budgeting purposes???

 

[Fred Smith]

Do we assume you have a bunch of individual line items, which have a growth
rate, for which you are calculating CAGR using the RATE function? Then you
want to sum the line items, and calculate the CAGR for the total line?

If so, calculate CAGR on the total line the same way you do it for the line
items -- feed RATE your term (likely the same as the line item), PV (sum of
line item PVs) and FV (sum of line item FVs).

It not, post back with more details.

 

[Norman Harker]

Hi Jake!

Compound Average Growth Rate?

i = (FV/PV)^(1/n)-1

Where:
FV = Value at end of periods counted by n
PV = Value at start of periods counted by n
n = number of periods

i will be the rate per period.

For example if 100 accumulates to 150 in 5 years the CAGR is found
using:
=(150/100)^(1/5)-1
Returns: 8.44717711976985%

You can calculate variously in Excel.

From formula above.

Or:
=RATE(nper,pmt,pv,fv,type,guess)
Note that starting value (PV) is input as negative and end value (FV)
as positive. The rate returned will be the rate per period counted by
NPER argument

Or:
=IRR(values,guess)
Where values is the range containing the cash flows. The rate returned
will be the effective rate for the period used in the cash flow.

Or:
=XIRR(values,dates,guess)
Returns the annual effective growth rates.
Where values are set against specific dates.

All are fairly well covered in Help but by all means give a few
examples of your dates and the figures and they can be slotted into
formulas with appropriate proofs and checks that all is working OK.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.