Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

=SUMPRODUCT(GEOMEAN(A1:A3+1))

This will add 1 to each value before taking the geometric mean.

- David


Biff wrote:
Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

It worked. Thanks.

"David Hilberg" wrote:

=SUMPRODUCT(GEOMEAN(A1:A3+1))

This will add 1 to each value before taking the geometric mean.

- David


Biff wrote:
Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff

"Jerry W. Lewis" wrote:

How do you want negative numbers handled? The geometric mean is not usually
defined with negative numers, because the nth root of a negative number
(product including an odd number of negatives) is a complex number (involving
the imaginary number i=SQRT(-1)).

Jerry

"Biff" wrote:

I need to calculate a geometric mean for an array of numbers of which some
are negative. The geomean function in Excel does not calculate if there are
negative numbers in the array. I'm not sure what the purprose of the
function is if you can't include negative return numbers, but that is the
fact. Does anyone know how a work around or another way to calculate the
geometric mean with an array of negative numbers included?

Biff wrote...
. . . I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after
year 1 the return is +25%, year 2 -25% and year three +25%, the
arthemetic mean gives me 8.33% which is not reality, while the
geometric mean is 5.4%. The multiplication of the the returns
plus one should be positive before taking the nth root. Does
this make sense?

You have to add 1 to the percentage returns (converting them into
ratios of ending values to beginning values), then take the geometric
mean of these POSITIVE values, then subtract 1 from that result. You'd
either need to use either of the following array formulas.

=GEOMEAN(Range_of_Percentages+1)-1

or

=EXP(AVERAGE(LN(Range_of_Percentages+1)))-1

On Wed, 25 Jul 2007 17:28:00 -0700, Biff
wrote:

Hi Jerry,

I noticed my last reply didn't go through. I am trying to calculate the
annualized rate of return for a portfolio over 3 years. If after year 1 the
return is +25%, year 2 -25% and year three +25%, the arthemetic mean gives me
8.33% which is not reality, while the geometric mean is 5.4%. The
multiplication of the the returns plus one should be positive before taking
the nth root. Does this make sense?

Biff


To handle this problem, Biff, you merely add 1 to your percentage changes, and
then subtract 1 from the result.

So your series is

1.25
0.75
1.25

You can also do this within a single **array** formula

e.g.

K1: 25%
K2: -25%
K3: 25%

=GEOMEAN(1+K1:K3)-1

(array-entered)


--ron