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 '