This Excel spreadsheet calculates the Sortino Ratio for an investment, a measure of risk-adjusted return. Investments that emphasize their Sortino Ratio often try to minimize their losses as a part of their trading strategy.
Investors can use a range of measures to gauge the suitability of investments. The Sharpe Ratio, for example, measures the risk-efficiency of investments by using standard deviation to represent risk. A disadvantage of this approach is that this penalizes both downside and upside volatility. Given that investors don’t mind (and indeed welcome) large upwards movements in price, the Sharpe Ratio may not best represent investor psychology. The Sortino Ratio, however, only penalizes downside risk, and is defined as
The Target Return is either a minimum acceptable return (as in the original definition), or a risk-free rate. Lower Sortino Ratios signify investments with a greater risk of large losses and should be avoided by risk-averse investors.
There’s some uncertainty about whether you simply set all values above the minimum acceptable return to zero, or discard those values entirely when computing the downside risk. Setting values above the MAR to zero reduces the computed risk; some feel that this is not consistent with the spirit of the Sortino Ratio and remove these values entirely instead. Given the flexible definition of the Sortino Ratio, then as long as you use a consistent definition to compare the the risk efficiency of investments this may not matter.
Sortino Ratio in VBA
Function SortinoRatio(returns As Range, MAR As Variant) As Variant Dim n As Integer Dim i As Integer Dim avgReturn As Double Dim mm2d As Double Dim downDev As Double n = returns.Rows.Count avgReturn = WorksheetFunction.Average(returns) moment2d = 0 For i = 1 To n If returns(i) - MAR < 0 Then mm2d = mm2d + ((returns(i) - MAR) ^ 2) End If Next downDev = Sqr(mm2d / n) If downDev > 0 Then SortinoRatio = (avgReturn - MAR) / downDev Else SortinoRatio = "undefined" End If End Function
This Excel spreadsheet implements both a manual worksheet method and a VBA function to calculate the Sortino Ratio