# Calculating Downside Risk

## Excel Help for Calculating Downside Risk in Excel Portfolio Optimization Template

### Calculating Downside Risk

Rate this:
(3/5 from 1 vote)
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 VariantDim rt As SingleDim sd As SingleDim nt As SingleDim nd As SingleDim avg As SingleDim sdtmp As Singlert = 0rd = 0nt = 0nd = 0For Each r In ir    If Application.WorksheetFunction.IsNumber(r) = True Then        rt = rt + r        nt = nt + 1    End IfNextavg = rt / ntFor 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 IfNextsdtmp = sd / ndsdtmp = sdtmp ^ 0.5STDEVD = sdtmpExit FunctionFuncFail:STDEVD = 0End 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).