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

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