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.