## Saturday, June 4, 2016

### Excel/VBA : Optimizing smooth OIS-adjusted Libor forward curve using Solver

Optimization for Libor forward curve has been presented in this blog post. This time, we will adjust the presented optimization procedure in such way, that OIS-adjusted Libor forward rates are going to be solved for a given fixed set of swap rates and OIS discount factors.

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.
All the relevant issues have been clearly explained in this research paper by Barclays.

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.

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