Microsoft Excel UDF macro to calculate Internal Rate of Return, Annualised Volatility, Annualised Downside Volatility and Maximum Drawdown of timeseries

Timeseries_UDF.bas

Function DIRR(first_price_cell As Range, first_date_cell As Range) As Double

    ' Gets various useful numbers
    Dim first_row As Long, last_row As Long, price_column As Long, date_column As Long, sheet As Worksheet
    first_row = first_price_cell.Row                ' first row
    price_column = first_price_cell.Column          ' price column
    date_column = first_date_cell.Column            ' date column
    Set sheet = first_price_cell.Worksheet          ' sheet for price
    last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row        ' get the last row
    
    ' Find last numeric value
    Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1
        last_row = last_row - 1
    Loop
    
    DIRR = (sheet.Cells(last_row, price_column).Value2 / sheet.Cells(first_row, price_column).Value2) ^ (1 / WorksheetFunction.YearFrac(sheet.Cells(first_row, date_column).Value, sheet.Cells(last_row, date_column).Value, 1)) - 1

End Function

Function DVOL(first_price_cell As Range) As Double

    ' Gets various useful numbers
    Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet
    first_row = first_price_cell.Row                ' first row
    price_column = first_price_cell.Column          ' price column
    Set sheet = first_price_cell.Worksheet          ' sheet for price
    last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row        ' get the last row
    
    ' Find last numeric value
    Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1
        last_row = last_row - 1
    Loop
    
    ' Setup variables
    Dim returns() As Double
    ReDim returns(1 To (last_row - first_row))
    
    Dim prices() As Variant
    prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2              ' copy prices to new array so we don't recalc
    
    For i = 1 To last_row - first_row
        ' Calculate return and store in array
        returns(i) = Log(prices(i + 1, 1) / prices(i, 1)) ^ 2
    Next i
    
    DVOL = Sqr(252 * WorksheetFunction.Average(returns))
    
End Function

Function DDVOL(first_price_cell As Range) As Double

    ' Gets various useful numbers
    Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet
    first_row = first_price_cell.Row                ' first row
    price_column = first_price_cell.Column          ' price column
    Set sheet = first_price_cell.Worksheet          ' sheet for price
    last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row        ' get the last row
    
    ' Find last numeric value
    Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1
        last_row = last_row - 1
    Loop
    
    ' Setup variables
    Dim returns() As Double
    ReDim returns(1 To (last_row - first_row))
    
    Dim prices() As Variant
    prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2              ' copy prices to new array so we don't recalc
    
    ' Calculate average of negative returns
    Dim avg As Double, j As Long
    j = 1
    avg = 0
    For i = 1 To last_row - first_row
        If prices(i + 1, 1) / prices(i, 1) < 1 Then
            avg = ((j - 1) * avg + (Log(prices(i + 1, 1) / prices(i, 1)) ^ 2)) / j
            j = j + 1
        End If
    Next i
    
    DDVOL = Sqr(252 * avg)
    
End Function

Function DMDD(first_price_cell As Range) As Double

    ' Gets various useful numbers
    Dim first_row As Long, last_row As Long, price_column As Long, sheet As Worksheet
    first_row = first_price_cell.Row                ' first row
    price_column = first_price_cell.Column          ' price column
    Set sheet = first_price_cell.Worksheet          ' sheet for price
    last_row = sheet.Cells(sheet.Rows.Count, price_column).End(xlUp).Row        ' get the last row
    
    ' Find last numeric value
    Do Until IsNumeric(sheet.Cells(last_row, price_column).Value2) Or last_row = 1
        last_row = last_row - 1
    Loop
    
    Dim max As Double
    max = 0
    
    Dim prices() As Variant
    prices = sheet.Range(sheet.Cells(first_row, price_column), sheet.Cells(last_row, price_column)).Value2              ' copy prices to new array so we don't recalc
    
    For i = 1 To last_row - first_row
        ' have we reached a new high watermark?
        If prices(i, 1) > max Then
            max = prices(i, 1)
        End If
        
        ' or a new lower mdd watermark?
        If prices(i, 1) / max - 1 < DMDD Then
            DMDD = prices(i, 1) / max - 1
        End If
        
    Next i

End Function