This Excel spreadsheet helps you calculate the Omega Ratio, a financial benchmark created by Shadwick and Keating in 2002.
The Omega Ratio is defined by the following equation.
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
Excel Spreadsheet to Calculate the Omega Ratio
May I know what figure should insert in the Legend column?? Thanks very much
@Cherrie – you don't need to insert anything in the Legend table. It's simply a color code to help you understand the spreadsheet
I might be wrong but I believe that the denominator in the computation of the Sortino ratio is slightly wrong. It divides the sum of squared return differences by 12 (the number of observations), instead of 11 – to count for the fact that it is a sample, not population.
@Tim – the Sortino Ratio in the spreadsheet gives the same resut as that by computed by the Matlab formula here (given the same data and MAR). I think the spreadsheet is probably correct (unless I'm missing something…)
Hello, I am wondering how you derive this way of computing Omega through the sum of positive and negative excess return from the initial ratio including cumulative density function?
Thank you very much in advance.
@Harry – the appendix of this paper demonstrates how you go from the integral definition of the Omega Ratio to definition involving excess returns
I just found the same, thank you very much!:)
Actually, I just have a last question to be sure I have well understood.
The paper shows that we can derive the following ratio:
E[max(x-L,0)]/E[max(L-x,0)]
I guess that the sum is the excel file is an estimator of these expectations:
E[max(x-L,0)] = 1/n * Sum (Rt above MAR)
E[max(L-x,0)] = 1/n * Sum (Rt below MAR)
with both 1/n cancelled
If it is well the calculation, my question is:
Is it reasonnable to assume that returns have the same weight or probability of 1/n instead of a weight derived from an empirical distribution such as:
E[max(x-L,0)] = Sum (Rt above MAR) * f(x)
E[max(L-x,0)] = Sum (Rt below MAR) * f(x)
Thank you in advance and sorry for this long question!
@Harry,
A very wise and considerate man by the same given name taught me a lot about utility theory. I think you identify one of the two problems with the omega ratio – it assumes a linear utility for gains and losses. If the utility of gains = utility of losses the investor is also assumed to be indifferent and would participate in seemingly unreasonable fair gambles for the chance of catapulting their current wealth or decimating it.
Essentially, omega is a degree 1 upper partial moment (UPM) / lower partial moment (LPM), whereby
UPM = 1/n * Sum(Rt above MAR)^UPMDegree
LPM = 1/n * Sum(Rt below MAR)^LPMDegree
The other problem is that the omega denominator (LPM degree 1) is NOT the CDF of the distribution because it is not normalized.
UPM and LPM accommodate additional risk and gain preferences that are compliant with the fourfold pattern of risk behaviors identified in Prospect Theory while maintaining expected utility theory compliance. PT itself fails due to its single reference point value function which covers only two of the four risk profiles, but that is another conversation all together!