Excel/VBA : Optimizing smooth Libor forward curve using Solver

In order to value fixed income derivatives cash flows, relevant forward rates and discount factors have to be defined from bootstrapped zero-coupon curve. In a Libor world, we use cash and FRA contracts (or futures contracts) in a short-end of the curve, while in a long-end of the curve we use relevant swap contracts. Let us assume for a moment, that we bootstrap zero-coupon curve, in order to define those forward rates and discount factors on a quarterly basis. While bootstrapping usually gives a smooth curve for a short-end of the curve, it will usually fail to do this for a long-end of the curve. This happens, because there is no relevant swap contracts available in a long-end of the curve and hereby, we need to do a lot of interpolation in a long-end of the curve for swaps. The resulting forward curve then looks like a chain of waterfalls.

This time, I wanted to present a cool way to solve those forward rates by using numerical optimization scheme. The topic itself has been thoroughly covered in chapter three in excellent book by Richard Flavell. In a nutshell, we first define the shortest end of the curve (using cash and forwards) and then we just guess the rest of the forward rates. After this, we minimize sum of squared differences between adjacent forward rates, subject to constraints. In this case, constraints are present values of swaps, which by definition will be zero. When this optimization task has been performed, the resulting forward curve will successfully re-price the current swap market.

In Excel worksheet, there has to be three named ranges to be used by VBA program:

• Objective function (sum of squared differences between adjacent forward rates)
• Constraints (swap PV for each swap contract used)
• Decision variables (forward rates to be solved)

Screenshots below are showing required Excel worksheet setups along with optimized Libor forward curve, corresponding capture of Bloomberg smoothed forward curve and the actual VBA program needed to perform optimization task. Note, that for this example, I have been defining only the first 3-month Libor spot rate fixing. The rest of the forward rates are going to be solved. When setting up VBA program, remember to include references to Solver and Microsoft Scripting Runtime libraries.

Finally, thanks for reading my blog.
-Mike

' Worksheet : Libor
Option Explicit
'
Private Sub btnSolveLibor_Click()
    '
    ' define Excel ranges for objective function, decision variables and constraints
    Dim objectiveFunction As Range: Set objectiveFunction = Range("_objectiveFunction")
    Dim changingVariables As Range: Set changingVariables = Range("_changingVariables")
    Dim constraints As Range: Set constraints = Range("_constraints")
    '
    ' create parameter wrapper
    ' for objective function, decision variables and constraints, address of range is needed
    Dim parameters As New Scripting.Dictionary
    parameters.Add PRM.objectiveFunction, CStr(objectiveFunction.Address)
    parameters.Add PRM.changingVariables, CStr(changingVariables.Address)
    parameters.Add PRM.constraints, CStr(constraints.Address)
    parameters.Add PRM.maxMinVal, CInt(2) ' max=1, min=2, valueof=3
    parameters.Add PRM.userFinish, CBool(True)
    parameters.Add PRM.assumeNonNegative, CBool(False)
    parameters.Add PRM.relation, CInt(2) ' lessOrEqual=1, equal=2, greaterOrEqual=3
    parameters.Add PRM.formulaText, CStr("0.0")
    '
    ' create instance of constrained optimization model implementation
    Dim xlSolver As ISolver: Set xlSolver = New ConstrainedOptimization
    xlSolver.solve parameters
    '
    ' release objects
    Set xlSolver = Nothing
    Set parameters = Nothing
End Sub
'
'
'
'
'
' Module : Enumerators
Option Explicit
'
' enumerator needed for parameter wrapper
Public Enum PRM
    objectiveFunction = 1
    maxMinVal = 2
    valueOf = 3
    changingVariables = 4
    userFinish = 5
    assumeNonNegative = 6
    constraints = 7
    relation = 8
    formulaText = 9
End Enum
'
'
'
'
'
' Module : XLSFunctions
Option Explicit
'
Public Function GetSumOfSquaredDifferences(ByRef values As Excel.Range) As Double
    '
    Dim v As Variant: v = values.Value2
    Dim sumOfSquaredDifferences As Double
    Dim i As Integer
    '
    ' calculate sum of squared differences between all adjacent values
    For i = 1 To (UBound(v) - 1)
        sumOfSquaredDifferences = sumOfSquaredDifferences + ((v(i + 1, 1) - v(i, 1)) * (v(i + 1, 1) - v(i, 1)))
    Next i
    GetSumOfSquaredDifferences = sumOfSquaredDifferences
End Function
'
Public Function IRSwapLiborPV(ByRef forwards 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(T) + (1 - DF(T))
    ' where Q(T) is sumproduct of fixed leg 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 Double
    Dim Q 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
        ' first rate is always spot rate, calculate first spot discount factor
        If (i = 1) Then
            DF = 1 / (1 + f(i, 1) * floatingLegTenor)
        End If
        '
        ' other rates are always forward rates
        If (i > 1) Then
            ' solve current spot discount factor using previous spot discount factor
            ' and current forward discount factor : DF(0,2) = DF(0,1) * DF(1,2)
            DF = DF * (1 / (1 + f(i, 1) * floatingLegTenor))
            If ((currentTimePoint - CDbl(nextFixedLegCouponDate)) = 0) Then
                Q = Q + DF
                nextFixedLegCouponDate = nextFixedLegCouponDate + 1
            End If
        End If
    Next i
    IRSwapLiborPV = (-swapRate * Q + (1 - DF)) * notional
End Function
'
'
'
'
'
' Class module : ISolver
Option Explicit
'
' ISolver interface to be implemented
Public Function solve(ByRef parameters As Scripting.Dictionary)
    ' no implementation
End Function
'
'
'
'
'
' Class module : ConstrainedOptimization
Option Explicit
'
Implements ISolver
'
' implementation for ISolver interface
' solver model for constrained optimization tasks
Private Function ISolver_solve(ByRef parameters As Scripting.Dictionary)
    '
    ' reset solver model
    solver.SolverReset
    '
    ' create optimization task
    solver.SolverOk _
        parameters.Item(PRM.objectiveFunction), _
        parameters.Item(PRM.maxMinVal), _
        parameters.Item(PRM.valueOf), _
        parameters.Item(PRM.changingVariables)
    '
    ' add constraints
    solver.SolverAdd _
        parameters(PRM.constraints), _
        parameters(PRM.relation), _
        parameters(PRM.formulaText)
    '
    ' solve optimization model
    solver.SolverOptions AssumeNonNeg:=parameters.Item(PRM.assumeNonNegative)
    solver.SolverSolve parameters.Item(PRM.userFinish)
End Function
'