Ulcer Index Calculation - Custom Function in Excel

If you are looking to find the Ulcer index of a return stream, best described here, then follow the steps below:

• Hit ALT + F11 to access the VBA editor
• Insert>New Module
• Copy and paste the code below into the module:

Function UlcerIndex(MyArray As Range)

'Copyright Mark Reichert 2011 mark_reichert@cox.net

Dim CurValue As Double
Dim MaxValue As Double
Dim MyCell As Range
Dim CurDD As Double
Dim MaxDD As Double
Dim CurDD_Sqrd As Double
Dim SumOfSqrs As Double
Dim NumOfDataPnts As Integer

MaxValue = 0
MaxDD = 0
CurValue = 1000
CurDD = 0
SumOfSqrs = 0
NumOfDataPnts = MyArray.Rows.Count

For Each MyCell In MyArray
       CurValue = CurValue * (1 + MyCell)
       If CurValue > MaxValue Then
       MaxValue = CurValue
       Else
       CurDD = (CurValue / MaxValue - 1)
           If CurDD < MaxDD Then
           MaxDD = CurDD
           End If
              
       End If
CurDD_Sqrd = CurDD ^ 2

SumOfSqrs = SumOfSqrs + CurDD_Sqrd


Next MyCell


UlcerIndex = (SumOfSqrs / NumOfDataPnts) ^ 0.5
   
End Function
  
End Function

• Hit Save, then ALT + F11 to get back to Excel
• In any excel cell type =UlcerIndex( and select the % returns you would like to calculate the Ulcer Index for.