Modified Sharpe Ratio

meghanand · May 13th, 2011, 6:39 pm

Does anyone have excel spreadsheet showing calculation for Mvar?

acastaldo · May 13th, 2011, 10:09 pm

Suppose you have the daily logarithmic return data in B2:B52Enter the following formulas in these cells:C1:Mean D1:=AVERAGE(B2:B52)C2:Sigma D2:=STDEV(B2:B52)C3:S D3:=SKEW(B2:B52)C4:K D4:=KURT(B2:B52)C5:fp D5:=NORMSINV(0.05) (assumes 5% var, modify accordingly)C6:zcf D6:=D5+(D5^2-1)*D3/6+(D5^3-3*D5)*D4/24-(2*D5^3-5*D5)*D3^2/36C7:mVaR D7:=-D1-D2*D6Cell D7 gives the modified or Cornish Fisher Var (as a positive number)Good luck. For comments see here.

meghanand · July 12th, 2011, 2:02 pm

Thanks. It works well. Just a question on Kurtosis, do we need to add 3 to excel result of kurtosis as excel provides 'excess kurtosis'.

ZhuLiAn · July 12th, 2011, 2:25 pm

excess kurtosis in excel 2010 but you could check in the help

samirkhan · November 11th, 2011, 7:45 pm

It's probably a few months too late, but here's a spreadsheet to calculate the modified Sharpe Ratio