Calculate the Modified Sharpe Ratio with Excel

This Excel spreadsheet calculates the Modified Sharpe Ratio.

The standard Sharpe Ratio is only appropriate for normally-distributed returns, where the entire distribution can be summarized through the mean and the variance.

But many modern investment vehicles, such as hedge funds and bonds, display fat-tailed returns, in which there is the potential for extreme losses.  In these situations, the standard Sharpe Ratio underestimates risk and should not be used.

The potential for extreme losses can be quantified through the modified Value at Risk, which takes into account the skew and kurtosis of the returns distribution.Modified Sharpe Ratio

The standard Sharpe Ratio is the effective return (μ – rf) divided by the variance (σ2).  However, the modified Sharpe Ratio is the effective return divided by the modified Value at Risk, and is defined by the following equations.

μ and σ are the mean and standard deviation, S is skew, K is kurtosis, zc is the quantile of the distribution and Z is the Cornish-Fisher asymptotic expansion for the quantile of a non-gaussian distribution.

Download Excel spreadsheet to calculate Modified Sharpe Ratio


4 thoughts on “Calculate the Modified Sharpe Ratio with Excel

  1. Hello,

    I have one question concerning the confidence level. Is this value of 0.01 assumed or do I have to count it somehow?

    It will be helpful for me to know it. I need to calculate the modified sharpe ratio for my master thesis and do know how to come about this confidence level.
    Thank you a lot in advance.

    best regards

    Joanna

    1. Hi,
      Did you find the answer to your question?
      Cause I have the same question and don know the answer.
      Would you mind letting me know if you have any information?

  2. I am looking at your example for the calculation of MVAR.

    Your equation states MVAR = mean – Z*Standard Deviation

    Yet your excel spreadsheet = mean + Standard Deviation*Z

    Can you please clarify what is the right calculation? Or are you replacing the (-) with a (+) because the “Z” coefficient is a negative?

    Your speedy reply is greatly appreciated

    Tejinder

  3. Hi Tejinder,

    If you look at the spreadsheet you have a negative Zcf score and so Zcf is actually -Z in the above formula. They are then consistent.

    Mark

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 9 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)