For many years I have been using the formula

(most recent value-oldest value)/oldest value

to calculate growth in Excel. It has recently occured to me that this is not
ideal for the datasets I am currently using (a number of performance
measures by year from 2000 to 2004 inclusive). The issue for me is that
where an institution had a 'bad year' in 2000 (i.e. achieved an
uncharacteristically low score), their growth value will be considerable,
even if the values in 2001, 2002, 2003 & 2004 had all been very similar (or
even fell in that period, so long as the 2004 value is still greater than
the value in 2000).

I am considering switching to a growth measure which averages the year on
year growth, but before I do, I'd just like to check what other people do -
if you've encountered this issue I'd be interested in & grateful for your
feedback.

Many thanks,

Tom

Tom Letcher
Data Analyst
Evidence Ltd
103 Clarendon Road, LEEDS, LS2 9DF, UK
t: +44 (0) 113 384 5684
f: +44 (0) 113 384 5874
e:
w: www.evidence.co.uk

Tom,

You could calculate the compound growth rate. An example for annual compound rates:

=(1+(most recent value-oldest value)/oldest value)^(1/(most recent year - oldest year))-1

which simplifies to

=(most recent value/oldest value)^(1/(most recent year - oldest year))-1

HTH,
Bernie
MS Excel MVP


"Tom Letcher" wrote in message
...
For many years I have been using the formula

(most recent value-oldest value)/oldest value

to calculate growth in Excel. It has recently occured to me that this is not ideal for the
datasets I am currently using (a number of performance measures by year from 2000 to 2004
inclusive). The issue for me is that where an institution had a 'bad year' in 2000 (i.e. achieved
an uncharacteristically low score), their growth value will be considerable, even if the values in
2001, 2002, 2003 & 2004 had all been very similar (or even fell in that period, so long as the
2004 value is still greater than the value in 2000).

I am considering switching to a growth measure which averages the year on year growth, but before
I do, I'd just like to check what other people do - if you've encountered this issue I'd be
interested in & grateful for your feedback.

Many thanks,

Tom

Tom Letcher
Data Analyst
Evidence Ltd
103 Clarendon Road, LEEDS, LS2 9DF, UK
t: +44 (0) 113 384 5684
f: +44 (0) 113 384 5874
e:
w: www.evidence.co.uk

Thanks Bernie. This method is appropriate for things (like population and
financial investments) that grow exponentially I believe, is that right?
Although some of my data are financial, they are allocations, so will not
grow (much as the institutions might like them to!) in that way.

In case it helps, this is an example of the type of data I'm using:

1999-2000 2000-2001 2001-2002 2002-2003 2003-2004
602,510 693,462 802,933 816,473 829,214


I'm grateful for your input.

Best wishes,

Tom

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Tom,

You could calculate the compound growth rate. An example for annual
compound rates:

=(1+(most recent value-oldest value)/oldest value)^(1/(most recent year -
oldest year))-1

which simplifies to

=(most recent value/oldest value)^(1/(most recent year - oldest year))-1

HTH,
Bernie
MS Excel MVP

Tom Letcher wrote:
For many years I have been using the formula
(most recent value-oldest value)/oldest value
[....]
I am considering switching to a growth measure
which averages the year on year growth

A simple arithemtic average of each year's growth
rate gives a potentially misleading number, depending
on how you intend to use it.

Consider the following end-of-year values in series:
$1000, $1500, $1000. The yearly growth rates are
+50% and -33%, and the arithmetic average is +8.33%.
But if you used 8.33% as the "average annual growth
rate", you would expect to have $1174 after 2 years,
not $1000.

Some financial institutions do like to report 8.33%.
It certainly looks better than 0% ;-). But it is
misleading.

Bernie Deitrick wrote:
You could calculate the compound growth rate.
[....]
=(most recent value/oldest value)^(1/(most recent year - oldest year))-1

And alternatively:

=RATE(lastyear - firstyear,, -firstvalue, lastvalue)

I prefer to use the financial functions because it
takes some of the mystique out of the calculation.
And the financial functions have broader application.

Tom Letcher wrote:
This [Bernie's] method is appropriate for things
(like population and financial investments) that grow
exponentially I believe, is that right? Although some
of my data are financial, they are allocations, so will
not grow [...] in that way.

Your first posting said that you are trying "to
calculate growth", and you previously calculated the total
percentage gain (loss) over many years. That gave the
impression of compounded growth.

In case it helps, this is an example of the type
of data I'm using:
1999-2000 2000-2001 2001-2002 2002-2003 2003-2004
602,510 693,462 802,933 816,473 829,214

It appears that you are saying that the amount allocated
at the beginning of 2000 (693) is unrelated to the amount
at the end of 1999 (510), for example -- other than perhaps
due to policy.

If that is the case, then your original method does seem
incorrect, and your proposal to compute the arithmetic
average of each year's growth rate does seem to be correct.

Tom, try this.

Lay your data out in rows as you have done, using only the last year of
each year pair (e.g., 2001-2002 becomes 2002), with the values directly
beneath (e.g., 802933).

In the next row convert the values to logarithms (e.g. log(802933)).

Then use the SLOPE function on the log values and the year values.

You'll get a number like .0384. That's your rate of growth.


"Tom Letcher" wrote:

Thanks Bernie. This method is appropriate for things (like population and
financial investments) that grow exponentially I believe, is that right?
Although some of my data are financial, they are allocations, so will not
grow (much as the institutions might like them to!) in that way.

In case it helps, this is an example of the type of data I'm using:

1999-2000 2000-2001 2001-2002 2002-2003 2003-2004
602,510 693,462 802,933 816,473 829,214


I'm grateful for your input.

Best wishes,

Tom

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Tom,

You could calculate the compound growth rate. An example for annual
compound rates:

=(1+(most recent value-oldest value)/oldest value)^(1/(most recent year -
oldest year))-1

which simplifies to

=(most recent value/oldest value)^(1/(most recent year - oldest year))-1

HTH,
Bernie
MS Excel MVP

I'm really grateful for all your inputs, people, thank you all very much for
your help. I'll talk this through with colleagues here (some of whom are
already talking up the advantages of staying as we are!) & decide how best
to take things forward from here.

Best wishes,

Tom