We recently added an option to the portfolio optimization template to account for downside risk only for the portfolio. This essentially employs the Sortino ratio as opposed to the Sharpe ratio by using downside standard deviation as the ratio denominator instead of overall standard deviation. In order to make this calculation we created a user defined function for calculating downside risk that we thought could be useful for other applications and therefore decided to share. The user defined function can be entered into a VBA module and then used as a worksheet function with the observation range.
VBA Code:
Function STDEVD(ir As Range) On Error GoTo FuncFail: Dim r As Variant Dim rt As Single Dim sd As Single Dim nt As Single Dim nd As Single Dim avg As Single Dim sdtmp As Single rt = 0 rd = 0 nt = 0 nd = 0 For Each r In ir If Application.WorksheetFunction.IsNumber(r) = True Then rt = rt + r nt = nt + 1 End If Next avg = rt / nt For Each r In ir If Application.WorksheetFunction.IsNumber(r) = True Then If r < avg Then sd = sd + ((avg - r) * (avg - r)) nd = nd + 1 End If End If Next sdtmp = sd / nd sdtmp = sdtmp ^ 0.5 STDEVD = sdtmp Exit Function FuncFail: STDEVD = 0 End Function
The resulting Excel formula is then =STDEVD([Enter cell range here]).
The function could, of course, be modified to calculate upside standard deviation (or either with an extra parameter).
