How do I calculate CAGR in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

CAGR = ((FV/PV)^(1/n)) - 1
where FV is the future value, PV is the present value, and n is the number
of years.

(and next time, please ask your question in the body of the message, not
just in the subject line)
 
CAGR = ((FV/PV)^(1/n)) - 1
where FV is the future value, PV is the present value, and n is the number
of years.
Click to expand...

Another way, which might be easier to remember:

=rate(n, 0, -pv, fv)

However, sometimes Excel's RATE() function requires a "guess"
argument :-(.

In either case, note that that depends on the information that you
have at hand. For example, that is __not__ how to compute CAGR if all
you have is the annual growth rates for N years -- or 12*N months.
(and next time, please ask your question in the body of the message, not
just in the subject line)
Click to expand...

I second that. Invariably, the subject deserves some embellishment
such as more information, as suggested by my comment above.
 
I believe the correct formula for excel is as follows:

=((Last#/First#)^(1/((count(data range))-1))-1)

The rate calculated from this formula can be tested by multiplying the first number ("First#" in the formula listed above) by the rate and continuing to multiply each subsequent number by the rate until you reach the nth point. That final number should equal the last number ("Last#" in the formula listed above).

For example, if an actual trend is as follows (if each data point is in the same excel row):
cell A1: 229,363
cell B1: 225,309
cell C1: 191,707
cell D1: 146,023

The CAGR as calculated by my formula above will be -14.0%

The formula for the values above would look like this:
=((D1/A1)^(1/((COUNT(A1:D1))-1))-1)

It can be proved by applying the decline rate of -14% to 229,363 (and subsequent results). The trend will look as follows:
n1: 229,363
n2: 197,313 (calculated)
n3: 169,741 (calculated)
n4: 146,023 (calculated) - THE SAME VALUE AS cell D1!

Hope this helps...
 
Last edited:
Back
Top