Home > Forum Home > Excel Portfolio Optimization Template > Calculating Downside Risk Share

Calculating Downside Risk

Excel Help for Calculating Downside Risk in Excel Portfolio Optimization Template

Forum TopicPost Reply Login

Calculating Downside Risk

Rate this:
(3/5 from 1 vote)
ApplaudWe 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
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
sdtmp = sd / nd
sdtmp = sdtmp ^ 0.5
STDEVD = sdtmp
Exit Function
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).

 Excel Business Forums Administrator
 Posted by on
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Rate this:
(3/5 from 1 vote)
J'ai l'accès au vba du module de téléchargement des données, mais pas la mot de passe pour le module optimizer.
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Calculating Downside Risk in the