Convexity in VBA

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




Function Convex(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
    '

    'Finds the Bond's Convexity
        For i = 1 To (Frequency * TimeToMaturity)
            Convex = Convex + (i / Frequency) * ((i + 1) / Frequency) * ((ParValue * CouponRate / Frequency) / (1 + (rate / Frequency)) ^ i)
        Next i
        Convex = Convex + TimeToMaturity * (((TimeToMaturity * Frequency) + 1) / Frequency) * ((ParValue) / (1 + (rate / Frequency)) ^ (Frequency * TimeToMaturity))
        Convex = Convex / (1 + (rate / Frequency)) ^ 2
        Convex = Convex / Price
    '
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
        
    '
    
    Convex = (TimeToMaturity ^ 2 + TimeToMaturity) / (1 + rate) ^ 2
End If

End Function


Convexity in VBA




Function Convex(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
    '

    'Finds the Bond's Convexity
        For i = 1 To (Frequency * TimeToMaturity)
            Convex = Convex + (i / Frequency) * ((i + 1) / Frequency) * ((ParValue * CouponRate / Frequency) / (1 + (rate / Frequency)) ^ i)
        Next i
        Convex = Convex + TimeToMaturity * (((TimeToMaturity * Frequency) + 1) / Frequency) * ((ParValue) / (1 + (rate / Frequency)) ^ (Frequency * TimeToMaturity))
        Convex = Convex / (1 + (rate / Frequency)) ^ 2
        Convex = Convex / Price
    '
Else
    Convex = TimeToMaturity ^ 2
End If

End Function