Array Formulas for Geometric Standard Deviation and Sharpe

Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.

I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:

=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)

A Column: Dates
D Column: Monthly returns in multiplier format

I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:

=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100

And Sharpe with this formula:

=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)

Any help you can offer will certainly save me a ton of pain -- head's
beginning to hurt!

It is not at all clear to me what you are doing, but your IF() formula
in GEOMEAN only returns a numeric value if the condition is satisfied.
Since it is difficult to take logs of non-numeric values, you probably
want to do the formula like
=(EXP(STDEV(IF(Work!$A9:$A236<B2,LN(Work!B9:B236))))^SQRT(12)-1)*100

I have never seen a practical use for a back transformed standard
deviation of logged data. Assuming that you want to calculate a
confidence interval, you probably want to calculate the interval on the
log scale and then exponentiate the interval endpoints, instead of
exponentiating the standard deviation estimate.

Jerry

Zeelotes wrote:

> Since I got such fantastic help with the last quesiton let me try one that
> I've been beating my head against the wall on for two weeks now.
>
> I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
> the following formula:
>
> =((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)
>
> A Column: Dates
> D Column: Monthly returns in multiplier format
>
> I want to do the same thing as this with GSD using this basic formula --
> that is, only do the returns based on the IF statement:
>
> =(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100
>
> And Sharpe with this formula:
>
> =(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)
>
> Any help you can offer will certainly save me a ton of pain -- head's
> beginning to hurt!

On Mon, 17 Oct 2005 19:03:52 +0800, "Zeelotes" <[email protected]> wrote:

>Since I got such fantastic help with the last quesiton let me try one that
>I've been beating my head against the wall on for two weeks now.
>
>I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
>the following formula:
>
>=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)
>
>A Column: Dates
>D Column: Monthly returns in multiplier format
>
>I want to do the same thing as this with GSD using this basic formula --
>that is, only do the returns based on the IF statement:
>
>=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100
>
>And Sharpe with this formula:
>
>=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)
>
>Any help you can offer will certainly save me a ton of pain -- head's
>beginning to hurt!
>

Just a comment on your Sharpe ratio computation.

The Sharpe Ratio is not independent of the time period over which it is
measured.

So, for example, using yearly returns, and some benchmark which represents the
risk-free return.

"It is a simple matter to compute an ex post Sharpe Ratio using a spreadsheet
program. The returns on a fund are listed in one column and those of the
desired benchmark in the next column. The differences are computed in a third
column. Standard functions are then utilized to compute the components of the
ratio. For example, if the differential returns were in cells C1 through C60, a
formula would provide the Sharpe Ratio using Microsoft's Excel spreadsheet
program:

AVERAGE(C1:C60)/STDEV(C1:C60)"

So Sharpe is using an arithmetic average of differential returns, and the
standard deviation of these returns in his computations. He does recommend for
comparison purposes that various strategies be "annualized"

See http://www.stanford.edu/~wfsharpe/art/sr/sr.htm for a copy of his article.


--ron

Jerry: Thanks. The formula works perfectly. The purpose of this formula
is to determine the volatilty on a series of monthly stock / index returns.
Many in the investing community have found this approach to be optimal for
determining the historical volatility of a particular trading strategy.

Ron: Yes, you are right. The method I'm employing is exactly based on the
presentation by Sharpe himself. I am annualizing the results using the
SQRT(12) at the end of the formula.

Do you have any suggestions on how to convert the Sharpe formula so that it
can include the IF statement that I require? Thanks for the fantastic
solution to the GSD formula.

Just like the other formulas

=(AVERAGE(IF(B49:B276<B2,B49:B276))/STDEV(IF(B49:B276<B2,B49:B276)))*SQRT(12)

All of these are array formulas that must be array entered. If there
are blank cells in B49:B276, then you would need to expand to

=(AVERAGE(IF(ISNUMBER(B49:B276)*(B49:B276<B2),B49:B276))/STDEV(IF(ISNUMBER(B49:B276)*(B49:B276<B2),B49:B276)))*SQRT(12)

to keep empty cells from being interpreted as zeros.

Jerry

Zeelotes wrote:

> Do you have any suggestions on how to convert the Sharpe formula so that it
> can include the IF statement that I require? Thanks for the fantastic
> solution to the GSD formula.

Jerry: Absolutely first rate. Thanks so much for taking the time to help
out.