Modified Duration in VBA

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




Function MacDur(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

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)
            MacDur = MacDur + (i / Frequency) * ((ParValue * CouponRate / Frequency) / (1 + (rate / Frequency)) ^ i)
        Next i
        MacDur = MacDur + TimeToMaturity * ((ParValue) / (1 + (rate / Frequency)) ^ (Frequency * TimeToMaturity))
        MacDur = MacDur / Price
    '
Else
    MacDur = TimeToMaturity
End If

End Function