This article introduces the Upside Potential Ratio, a risk-adjusted investment performance benchmark.It also presents two methods of calculating this performance benchmark in Excel, one using a worksheet formula and another using VBA.
Several performance benchmarks use the standard deviation of the returns as a proxy for investment risk. The Sharpe Ratio, for example, is equal to the effective return divided by the standard deviation of the returns.
Standard deviation, however, penalizes both upside variation and downside variation equally. This does not match the risk preferences of most investors. Generally, investors want to encourage upside variation, but penalize downside variation.
The Sortino Ratio partially addresses this failing of the Sharpe Ratio. It is the effective return divided by the downside deviation. The Sortino Ratio favors investments with the highest return but the least downside volatility.
The Upside Potential Ratio is a further refinement that better addresses the risk preferences of investors. It is equal to the variation of the returns above a minimum acceptable return divided by the variation of the returns below a minimum acceptable returns. This favors investments with stable growth above a minimum acceptable return.
It is is defined by this equation.
- MAR is the minimum acceptable return, and is chosen to match the investor’s goals
- R are the empirical investment returns
- Pr is the probability of making that return
The numerator is the first order higher partial moment. The denominator is the square root of the second order lower partial moment.
Upside Potential Ratio in Excel
Assuming the empirical returns are in cells B8:B19 and the minimum acceptable return is in cell D4, the Upside Potential Ratio is given by this Excel matrix formula
This formula must be entered with CTRL+SHIFT+ENTER.
Upside Potential Ratio in VBA
Function UpsidePotentialRatio(returns As Range, MAR As Double) As Variant Dim n As Integer Dim i As Integer Dim upside As Double Dim downside As Double
n = returns.Rows.Count upside = 0 downside = 0 For i = 1 To n If returns(i) > MAR Then upside = upside + (returns(i) - MAR) * (1 / n) End If If returns(i) < MAR Then downside = downside + (returns(i) - MAR) ^ 2 * (1 / n) End If Next i
If downside > 0 Then UpsidePotentialRatio = upside / Sqr(downside) Else UpsidePotentialRatio = "undefined" End If
This spreadsheet implements both methods.