View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default what is geometric mean or return

I assume you mean the geometric mean __of__ returns. But even then, it is
unclear whether you want an explanation of the geometric mean, or if you
know that much and you simply want to know how to compute it in Excel. It
might have been much clearer if you had taken the time to elaborate on your
question in the body of your message.

If an investment is worth $100 one year and $110 the next year, the
(unrealized) return on investment is 110/100 or 110/100 -1. Some texts call
the first form the "gross return" and the second form the "simple return".
I prefer to call the second form the percentage change.

If you have a time series of such percentage changes and you want to compute
the compounded average growth rate, the answer is given by computing the
geometric mean of the percentage changes.

If the percentage changes are in A1:A100, the geometric mean might be
computed using the following array formual (commit with ctrl-shift-Enter,
not just Enter):

=geomean(1+A1:A100) - 1

However, GEOMEAN is not always reliable with large amounts of data. A more
reliable alternative is to compute the arithmetic mean of the log returns
and convert it to the geometric mean using the following array formula:

=10^average(log(1+A1:A100)) -1