Modified Duration in VBA

Here is a VBA function that easily allows you to calculate the modified duration on any bond.




Function ModDur(Price As Double, ParValue As Double, CouponRate As Double, Frequency As Double, TimeToMaturity As Double)
Dim i As Integer, P(10000) As Double, r(10000) As Double, rate As Double, MacaulayDur As Double

If Frequency > 0 Then
    'Approximates the YTM using the secant method
        P(1) = 0
        r(1) = 1
        For j = 2 To 9999
            For i = 1 To (Frequency * TimeToMaturity)
                P(j) = P(j) + ((CouponRate * ParValue / Frequency) / ((1 + (r(j) / Frequency)) ^ (i)))
            Next i
            P(j) = P(j) + ParValue / ((1 + (r(j) / Frequency)) ^ (Frequency * TimeToMaturity))
        
            If P(j) = P(j - 1) Then
                rate = r(j)
                Exit For
            Else
                r(j + 1) = r(j) - (P(j) - Price) * (r(j) - r(j - 1)) / _
                    (P(j) - (P(j - 1)))
            End If
        Next j
    '

    'Calcultes the Macaulay Duration
        For i = 1 To (Frequency * TimeToMaturity)
            MacaulayDur = MacaulayDur + (i / Frequency) * ((ParValue * CouponRate / Frequency) / (1 + (rate / Frequency)) ^ i)
        Next i
        MacaulayDur = MacaulayDur + TimeToMaturity * ((ParValue) / (1 + (rate / Frequency)) ^ (Frequency * TimeToMaturity))
        MacaulayDur = MacaulayDur / Price
    '

    'Calculates the Modified Duration
        ModDur = MacaulayDur / (1 + (rate / Frequency))
    '
Else
    'Approximates the YTM using the secant method
        P(1) = 0
        r(1) = 1
        For j = 2 To 9999
            P(j) = P(j) + ParValue / ((1 + (r(j))) ^ (TimeToMaturity))
        
            If P(j) = P(j - 1) Then
                rate = r(j)
                Exit For
            Else
                r(j + 1) = r(j) - (P(j) - Price) * (r(j) - r(j - 1)) / _
                    (P(j) - (P(j - 1)))
            End If
        Next j
    '
    
    ModDur = TimeToMaturity / (1 + rate)
End If

End Function