Bootstrapping OIS-adjusted Libor curve in VBA

OIS discounting has been hot topic for the last few years, since most of the collateralized OTC swaps are valued by this methodology. In this blog post, I will present simple example algorithm for bootstrapping OIS-adjusted Libor curve from market data (OIS zero-coupon curve, Libor par swap curve). This bootstrapped curve can then be used to generate floating leg cash flows, when valuing collateralized interest rate swap with all cash flows and collateral in the same currency. Final product will be just one simple VBA worksheet function to be used in Excel.

VALUATION 101

In essence

• Instead of using Libor zero-coupon curve for cash flow discounting, all swap cash flows are present valued with discount factors calculated by using OIS zero-coupon curve. 
• The use of OIS zero-coupon curve for discounting collateralized swap cash flows is justified, because posted collateral earns overnight rate and collateral value is mark-to-market value of a swap. In order to equate these two cash flows (collateral value, mark-to-market value of a swap), discount factor for both cash flows has to be calculated by using OIS curve.
• Cash flows for swap fixed leg are still constructed by using ordinary Libor par swap rates. 
• Another impact of OIS valuation hits into construction of floating leg coupon rates, which are technically forward rates.
• In the "old world", we bootstrapped Libor zero-coupon curve, from which we calculated discount factors and forward rates (for constructing floating leg coupons) at the same time. Only one curve was needed to accomplish this procedure. 
• Because all swap cash flows are now discounted with OIS zero-coupon curve and ordinary Libor par swap rates are still used for constructing swap fixed leg cash flows, forward rates have to be "adjusted" slightly, in order to equate present value of all swap cash flows to be zero.
• Technically, we end up with a system of linear equations, in which we equate OIS-discounted floating cash flows with OIS-discounted fixed cash flows and solve for the unknown forward rates.

Material, which has helped me to understand this subject a bit better is the following: technical notes written by Justin Clarke, teaching material by Donald J. Smith and Barclays research paper by Amrut Nashikkar. These papers have worked numerical examples, as well as theoretical issues covered thoroughly.

VBA FUNCTION

 

Option Explicit
'
Public Function OIS_bootstrapping(ByRef curves As Range) As Variant
    '
    ' import source data from Excel range into matrix
    Dim source As Variant: source = curves.Value2
    '
    ' create all the needed matrices and define dimensions
    Dim nSwaps As Integer: nSwaps = UBound(source, 1)
    Dim fixed As Variant: ReDim fixed(1 To nSwaps, 1 To 1)
    Dim float As Variant: ReDim float(1 To nSwaps, 1 To nSwaps)
    Dim forward As Variant: ReDim forward(1 To nSwaps, 1 To 1)
    '
    ' counters and other temp variables
    Dim i As Integer, j As Integer, k As Integer, nCashFlows As Integer
    Dim OIS_DF As Double, OIS_Rate As Double, t As Double
    '
    ' loop for cash flows processing
    nCashFlows = nSwaps: k = 0
    For i = 1 To nSwaps
        '
        ' create OIS discount factor
        OIS_Rate = source(i, 2): t = source(i, 1)
        If (t <= 1) Then OIS_DF = 1 / (1 + (OIS_Rate * t))
        If (t > 1) Then OIS_DF = 1 / (1 + OIS_Rate) ^ t
        '
        ' create sum of fixed leg pv's for each individual swap and create all
        ' cash flows (excluding coupon rate) for floating legs for each individual swap
        For j = 1 To nSwaps
            If (j <= nCashFlows) Then
                fixed(j + k, 1) = fixed(j + k, 1) + 100 * source(j + k, 3) * OIS_DF
                float(i, j + k) = 100 * OIS_DF
            Else
                ' replace empty array value with zero value
                float(i, nSwaps - j + 1) = 0#
            End If
        Next j
        '
        k = k + 1: nCashFlows = nCashFlows - 1
    Next i
    '
    ' solve for implied forward rates, which are going to be used to generate coupons
    ' for floating legs. matrix operation: [A * x = b] ---> [x = Inverse(A) * b]
    ' where A = float (N x N), x = forward rates (N x 1), b = sum of swap fixed leg pv's (N x 1)
    forward = WorksheetFunction.MMult(WorksheetFunction.MInverse(WorksheetFunction.transpose(float)), fixed)
    OIS_bootstrapping = forward
End Function
'

EXAMPLE CALCULATION

The following Excel screenshot presents bootstrapped OIS-adjusted forward curve (column G) and OIS valuation for collateralized 2Y interest rate swap. For the sake of simplicity, this example assumes that the payments for the both fixed and floating legs takes place quarterly. Swap fixed cash flows has been constructed by using Libor par swap rates. Floating leg cash flows has been constructed by using bootstrapped OIS-adjusted forward curve. Finally, all cash flows are discounted by using OIS discount factors (column F). The present value of all swap cash flows is zero. Worksheet function input range has been marked with yellow color and function output range has been marked with blue color.

Presented forward curve construction scheme applies to a specific case, in which collateralized interest rate swap has the both cash flow legs and collateral in the same currency. Moreover, it is assumed that the payment frequency is the same for the both swap legs. Successful replication of the forward curve bootstrapping result was achieved, when testing VBA worksheet function with the cases presented in above-mentioned papers by Smith and Clarke.

Thanks for reading.

-Mike