This Excel spreadsheet helps you calculate the Omega Ratio, a financial benchmark created by Shadwick and Keating in 2002.
F(x) is the cumulative probability distribution (i.e. the probability that a return will be less than x), r is a threshold value selected by the investor and a,b are the investment intervals. It is effectively equal to the probability weighted gains divided by the probability weighted losses after a threshold.
The Sharpe Ratio only takes into account the first two moments of a return distribution, the mean and variance. This is misleading because
- return distributions with significantly different shapes can have the same mean and variance, and hence the same Sharpe Ratio. This can often mislead investors about the downside potential of an investment.
- not all investments are normally distributed, or are well described just by their mean and variance.
The Omega Ratio contains much more information about the return distribution, including the mean, variance, skew and kurtosis and is especially valuable for non-normal investments (such as Hedge funds, options, futures and derivatives).
Several years ago, hedge funds were subject to large losses which were in no small part due reliance on standard mean-variance optimization techniques (as pioneered by Markowitz). These approaches use mean and variance as a proxy for risk. However, this presupposes normally distributed returns; this is not the case for leveraged investments like hedge funds. Since then, the Omega Ratio and other similar approaches have grown in popularity because they favour investments with a lower probability of extreme loss.
The Omega Ratio has several important features
- Given the choice between portfolios with the same predicted return, investors should favour the portfolio with the highest Omega Ratio. This maximizes the potential for making the desired level of return, and minimizes the probability of extreme losses.
- The entire returns distribution, including all the higher moments, is encoded in the Omega ratio
- It is equal to one when the threshold value is the average return
- It decreases as the threshold return increases
- It does not minimize volatility, but reduces the probability of extreme losses
Calculate the Omega Ratio in Excel
The Omega Ratio is easily calculated in Excel with a matrix formula. If cells A1:A15 contain the investment returns, and B1 contains the threshold return, then the Omega Ratio is
where the curly braces indicate a matrix formula entered by CTRL+SHIFT+Return.
Excel can also be used to maximize the Omega Ratio of a portfolio by varying the investment weights. This asset allocation technique is very different to the traditional mean-variance approach.
Calculate the Omega Ratio in VBA
In the following VBA function, the first argument is a range of investment returns, and the second is the threshold return.
Function OmegaRatio(returns As Range, threshold As Double) As Variant Dim n As Integer Dim i As Integer Dim upside As Double Dim downside As Double n = returns.Rows.Count downside = 0 upside = 0 For i = 1 To n If returns(i) < threshold Then downside = downside + (threshold - returns(i)) End If If returns(i) > threshold Then upside = upside + (returns(i) - threshold) End If Next i If downside <> 0 Then OmegaRatio = upside / downside Else OmegaRatio = "undefined" End If End Function
This Excel spreadsheet implements both methods