In a nutshell, justification for OIS-adjusted forward rates is the following :

- In the "old world", we first bootstrapped Libor zero-coupon curve, from which we calculated Libor discount factors and Libor forward rates (for constructing floating leg coupons) at the same time. Only one curve was ever needed to accomplish this procedure.
- In the "new world", since all swap cash flows are discounted using OIS discount factors and par swap rates are still used for constructing swap fixed leg cash flows, forward rates (OIS-adjusted Libor forward rates) have to be slightly adjusted, in order to equate present value of all swap cash flows back to be zero.

Screenshots below are showing required Excel worksheet setups along with optimized OIS-adjusted Libor forward curve and required additions to existing VBA program needed to perform this optimization task. In order to validate the optimized OIS-adjusted Libor forward curve, a 10-year vanilla swap has been re-priced using optimized OIS-adjusted Libor forward rates and given set of fixed OIS discount factors.

When setting up VBA program, first implement the program presented in this blog post. After this, add two new functions (IRSwapOISPV, linearInterpolation) presented below into module XLSFunctions. Finally, remember to include references to Solver and Microsoft Scripting Runtime libraries.

Thanks for reading this blog.

-Mike

Public Function IRSwapOISPV(ByRef forwards As Excel.Range, ByRef OISDF As Excel.Range, ByVal swapRate As Double, _ ByVal yearsToMaturity As Integer, ByVal floatingLegPaymentFrequency As Integer, ByVal notional As Double) As Double ' ' PV (fixed payer swap) = -swapRate * Q_fixed + Q_float ' where Q_fixed is sumproduct of fixed leg OIS discount factors and corresponding time accrual factors ' and Q_float is sumproduct of adjusted libor forward rates, OIS discount factors and corresponding time accrual factors ' assumption : fixed leg is always paid annually ' since fixed leg is paid annually and maturity is always integer, time accrual factor will always be exactly 1.0 Dim f As Variant: f = forwards.Value2 Dim DF As Variant: DF = OISDF.Value2 Dim Q_fixed As Double Dim Q_float As Double Dim floatingLegTenor As Double: floatingLegTenor = 1 / CDbl(floatingLegPaymentFrequency) Dim nextFixedLegCouponDate As Integer: nextFixedLegCouponDate = 1 Dim currentTimePoint As Double: currentTimePoint = CDbl(0) ' Dim i As Integer For i = 1 To (yearsToMaturity * floatingLegPaymentFrequency) currentTimePoint = currentTimePoint + floatingLegTenor ' ' update floating leg Q Q_float = Q_float + f(i, 1) * linearInterpolation(currentTimePoint, OISDF.Value2) * floatingLegTenor ' ' update fixed leg Q, if current time point is coupon payment date If ((currentTimePoint - CDbl(nextFixedLegCouponDate)) = 0) Then Q_fixed = Q_fixed + linearInterpolation(currentTimePoint, OISDF.Value2) nextFixedLegCouponDate = nextFixedLegCouponDate + 1 End If Next i IRSwapOISPV = (-swapRate * Q_fixed + Q_float) * notional End Function ' Public Function linearInterpolation(ByVal maturity As Double, ByRef curve As Variant) As Double ' ' read range into Nx2 array Dim r As Variant: r = curve ' Dim n As Integer: n = UBound(r, 1) ' ' boundary checkings If ((r(LBound(r, 1), 1)) > maturity) Then linearInterpolation = r(LBound(r, 1), 2): Exit Function If ((r(UBound(r, 1), 1)) < maturity) Then linearInterpolation = r(UBound(r, 1), 2): Exit Function ' Dim i As Long For i = 1 To n If ((r(i, 1) <= maturity) And (r(i + 1, 1) >= maturity)) Then ' Dim y0 As Double: y0 = r(i, 2) Dim y1 As Double: y1 = r(i + 1, 2) Dim x0 As Double: x0 = r(i, 1) Dim x1 As Double: x1 = r(i + 1, 1) ' linearInterpolation = y0 + (y1 - y0) * ((maturity - x0) / (x1 - x0)) Exit For End If Next i End Function '