Barbell Hedging in VBA

Use this Excel program to calculate the ideal weights for a barbell hedge in VBA.


Download Excel Spreadsheet to calculate ideal weights for Barbell Hedging


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

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
    ModifiedDur = TimeToMaturity
    Convexity = TimeToMaturity ^ 2
    ChBondPrice = -ModifiedDur * ChangeInYield + (0.5 * Convexity * (ChangeInYield ^ 2))
End If

End Function

Sub DumbellHedge()

'Creates Necessary Variables
    Dim Weight(3) As Double, Prices(3) As Double, ModifyDur(3) As Double
    Dim Parameters(3, 5) As Double
'

'Sets Values
    For i = 1 To 5
        Parameters(2, i) = Range("C" & i + 4).Value
        Parameters(1, i) = Range("C" & i + 11).Value
        Parameters(3, i) = Range("C" & i + 18).Value
    Next i

    Prices(2) = Parameters(2, 1)
    Prices(1) = Parameters(1, 1)
    Prices(3) = Parameters(3, 1)
    
    For i = 1 To 3
        ModifyDur(i) = ModDur(Parameters(i, 1), Parameters(i, 2), Parameters(i, 3), _
            Parameters(i, 4), Parameters(i, 5))
    Next i
'

'Finds the Ideal Weights for the liability and assets:
    Weight(2) = 1
    
    Weight(1) = (ModifyDur(3) - ModifyDur(2)) / (ModifyDur(3) - ModifyDur(1)) * _
                (Prices(2) / Prices(1))
                
    Weight(3) = (ModifyDur(2) - ModifyDur(1)) / (ModifyDur(3) - ModifyDur(1)) * _
                (Prices(2) / Prices(3))
'

'Prints Values
    Range("F15").Value = Weight(2)
    Range("F16").Value = Weight(1)
    Range("F17").Value = Weight(3)
    Range("F20").Value = Weight(1) * Prices(1) + Weight(3) * Prices(3) - Weight(2) * Prices(2)
    Range("F21").Value = Weight(1) * Prices(1) * ModifyDur(1) + Weight(3) * Prices(3) * ModifyDur(3) - Weight(2) * Prices(2) * ModifyDur(2)
'

End Sub