Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
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! |
#3
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
On Mon, 17 Oct 2005 19:03:52 +0800, "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$23 6))^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 |
#4
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
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. |
#5
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
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. |
#6
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
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. |
#7
|
|||
|
|||
Array Formulas for Geometric Standard Deviation and Sharpe
Jerry: Absolutely first rate. Thanks so much for taking the time to help
out. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|