YTM, Duration and Convexity in VBA

Use this Excel program to calculate the yield to maturity, Macaulay duration, modified duration and convexity of a bond and estimates the amount that the bond's price will change with a given shift in the yield curve.


Download Excel Spreadsheet to Calculate YTM, Duration and Convexity


The code underlying the procedure is as follows:


Public TimeToMaturity As Double, CouponRate As Double, ParValue As Double, y As Double
Public Frequency As Double, P() As Double, r() As Double, Price As Double
Public i As Integer, j As Integer, rate As Double
Public ChangeInYield As Double

Function YTM(Price As Double, ParValue As Double, CouponRate As Double, Frequency As Double, TimeToMaturity As Double)
Dim i As Integer, j As Integer, P(10000) As Double, r(10000) As Double

'Approximates the YTM using the secant method
    If Frequency > 0 Then
        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
                YTM = 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
    Else
        YTM = (1 + ((ParValue - Price) / Price)) ^ (1 / TimeToMaturity) - 1
    End If
'

End Function

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

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))
    '
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

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

Function ChBondPrice(Price As Double, ParValue As Double, CouponRate As Double, Frequency As Double, TimeToMaturity As Double, ChangeInYield As Double)
Dim i As Integer, P(10000) As Double, r(10000) As Double, rate As Double, ModifiedDur 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
        ModifiedDur = MacaulayDur / (1 + (rate / Frequency))
    '

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

    'Finds the Estimated Percentage Change in Price
        ChBondPrice = -ModifiedDur * ChangeInYield + (0.5 * Convexity * (ChangeInYield ^ 2))
    '
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
    '
    
    ModifiedDur = TimeToMaturity / (1 + rate)
    Convex = (TimeToMaturity ^ 2 + TimeToMaturity) / (1 + rate) ^ 2
    
    ChBondPrice = -ModifiedDur * ChangeInYield + (0.5 * Convexity * (ChangeInYield ^ 2))

End If

End Function