Duration Gap in VBA

Here is a VBA program that easily allows you to calculate the duration gap between a firm's assets and liabilities.


Download Excel Spreadsheet to calculate duration gap in ALM




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, NewEtoA As Double
Public NumberAssets As Integer, ValueAssets As Double, DurAssets As Double, ConvexAssets As Double
Public NumberLiabilities As Integer, ValueLiabilities As Double, DurLiabilities As Double, ConvexLiabilities As Double
Public ValueEquity As Double, AssetData As Variant, LiabilityData As Variant
Public APrice() As Double, AParValue() As Double, ACouponRate() As Double, AFrequency() As Double, ATimeToMaturity() As Double
Public LPrice() As Double, LParValue() As Double, LCouponRate() As Double, LFrequency() As Double, LTimeToMaturity() As Double
Public AChBondPrice() As Double, LChBondPrice() As Double, AChBP As Double, LChBP As Double
Public ADuration() As Double, AConvexity() As Double, LDuration() As Double, LConvexity() As Double
Public EtoA As Double, DurGap As Double, ChNWtoAssets As Double, ChRates 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 * (1 + (1 + ((ParValue - Price) / Price)) ^ (1 / 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 / Frequency))
    '
Else
    ModDur = TimeToMaturity
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
    Convex = TimeToMaturity ^ 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
    ModifiedDur = TimeToMaturity
    Convexity = TimeToMaturity ^ 2
    ChBondPrice = -ModifiedDur * ChangeInYield + (0.5 * Convexity * (ChangeInYield ^ 2))
End If

End Function

Sub DurationGap()
    'Calculates Rudimentary Data from Sheet
        NumberAssets = Application.WorksheetFunction.Count(Range("H3:H10008"))
        ValueAssets = Application.WorksheetFunction.Sum(Range("H3:H10008"))
        NumberLiabilities = Application.WorksheetFunction.Count(Range("N3:N10008"))
        ValueLiabilities = Application.WorksheetFunction.Sum(Range("N3:N10008"))
    '
    
    'Loads Data From Sheet
        Sheets("DurationGap").Activate
        ChRates = Range("C5").Value
        AssetData = Range("H3:L" & NumberAssets + 2)
        LiabilityData = Range("N3:R" & NumberLiabilities + 2)
    '
    
    'ReDim Asset and Liability Arrays
        ReDim APrice(NumberAssets) As Double
        ReDim AParValue(NumberAssets) As Double
        ReDim ACouponRate(NumberAssets) As Double
        ReDim AFrequency(NumberAssets) As Double
        ReDim ATimeToMaturity(NumberAssets) As Double
        ReDim ADuration(NumberAssets) As Double
        ReDim AConvexity(NumberAssets) As Double
        ReDim AChBondPrice(NumberAssets) As Double
        ReDim LPrice(NumberAssets) As Double
        ReDim LParValue(NumberAssets) As Double
        ReDim LCouponRate(NumberAssets) As Double
        ReDim LFrequency(NumberAssets) As Double
        ReDim LTimeToMaturity(NumberAssets) As Double
        ReDim LDuration(NumberLiabilities) As Double
        ReDim LConvexity(NumberLiabilities) As Double
        ReDim LChBondPrice(NumberLiabilities) As Double
    '
    
    'Loads Data into Arrays
        For i = 1 To NumberAssets
            APrice(i) = AssetData(i, 1)
            AParValue(i) = AssetData(i, 2)
            ACouponRate(i) = AssetData(i, 3)
            AFrequency(i) = AssetData(i, 4)
            ATimeToMaturity(i) = AssetData(i, 5)
        Next i
        For i = 1 To NumberLiabilities
            LPrice(i) = LiabilityData(i, 1)
            LParValue(i) = LiabilityData(i, 2)
            LCouponRate(i) = LiabilityData(i, 3)
            LFrequency(i) = LiabilityData(i, 4)
            LTimeToMaturity(i) = LiabilityData(i, 5)
        Next i
    '
    
    'Ensures Data Quality
        DurAssets = 0
        DurLiabilities = 0
        ConvexAssets = 0
        ConvexLiabilities = 0
        AChBP = 0
        LChBP = 0
    '
    
    'Calculates Individual Durations and Convexity
        For i = 1 To NumberAssets
            ADuration(i) = ModDur(APrice(i), AParValue(i), ACouponRate(i), AFrequency(i), ATimeToMaturity(i))
            AConvexity(i) = Convex(APrice(i), AParValue(i), ACouponRate(i), AFrequency(i), ATimeToMaturity(i))
            AChBondPrice(i) = ChBondPrice(APrice(i), AParValue(i), ACouponRate(i), AFrequency(i), ATimeToMaturity(i), ChRates)
        Next i
        For i = 1 To NumberLiabilities
            LDuration(i) = ModDur(LPrice(i), LParValue(i), LCouponRate(i), LFrequency(i), LTimeToMaturity(i))
            LConvexity(i) = Convex(LPrice(i), LParValue(i), LCouponRate(i), LFrequency(i), LTimeToMaturity(i))
            LChBondPrice(i) = ChBondPrice(LPrice(i), LParValue(i), LCouponRate(i), LFrequency(i), LTimeToMaturity(i), ChRates)
        Next i
    '
    
    'Calculates Duration of Assets
        For i = 1 To NumberAssets
            DurAssets = DurAssets + APrice(i) * ADuration(i)
        Next i
        DurAssets = DurAssets / ValueAssets
    '

    'Calculates Convexity of Assets
        For i = 1 To NumberAssets
            ConvexAssets = ConvexAssets + APrice(i) * AConvexity(i)
        Next i
        ConvexAssets = ConvexAssets / ValueAssets
    '

    'Calculates Change in Asset Prices
        For i = 1 To NumberAssets
            AChBP = AChBP + (APrice(i) * AChBondPrice(i))
        Next i
    '

    'Calculates Duration of Liabilities
        For i = 1 To NumberLiabilities
            DurLiabilities = DurLiabilities + LPrice(i) * LDuration(i)
        Next i
        DurLiabilities = DurLiabilities / ValueLiabilities
    '

    'Calculates Convexity of Liabilities
        For i = 1 To NumberLiabilities
            ConvexLiabilities = ConvexLiabilities + LPrice(i) * LConvexity(i)
        Next i
        ConvexLiabilities = ConvexLiabilities / ValueLiabilities
    '
    
    'Calculates Change in Liability Prices
        For i = 1 To NumberLiabilities
            LChBP = LChBP + (LPrice(i) * LChBondPrice(i))
        Next i
        LChBP = -LChBP
    '
    
    'Calculates Duration Gap and Equity to Assets
        DurGap = DurAssets - (DurLiabilities * ValueLiabilities / ValueAssets)
        EtoA = 1 - ValueLiabilities / ValueAssets
        ChNWtoAssets = (AChBP + LChBP) / ValueAssets
        NewEtoA = 1 - (ValueLiabilities + LChBP) / (ValueAssets + AChBP)
    '

    'Prints Values
        Range("F5").Value = DurAssets
        Range("F6").Value = DurLiabilities
        Range("F7").Value = ConvexAssets
        Range("F8").Value = ConvexLiabilities
        Range("F10").Value = DurGap
        Range("F11").Value = EtoA
        Range("F13").Value = ChNWtoAssets
        Range("f14").Value = NewEtoA
'

End Sub