In a previous we blog we reproduced example 1 of the 2014 paper John Hull and Alan White “Generalized Procedure for Building Short Rate Trees”. In the paper the authors derive a generalized method for the construction of short rate trees. This generalization is interesting as it allows for one tree (or lattice) construction algorithm for all one-factor short rate models.
Previously (in the spreadsheet : Hull White 2014 - Example 1) we reconciled the result using a complete VBA solution using the Levenberg-Marquardt method to minimizes the difference between the discounts found in the market and those produced by the model. Our algorithm was originally part of MINPACK which was developed by Jorge More, Burt Garbow, and Ken Hillstrom at Argonne National Laboratory. Here we have used a more recent version of the algorithm that we ported to Excel: LevmarExcel. This setup allows for solving more complex lattice calibration, which effectively means that more timesteps can be added into the spreadsheet. It is also a pretty good demo of what LevmarExcel can do for you.
Note: you will need download the LevmarExcel XLL and add it to you Excel Add-ins to make the spreadsheet work.
The spreadsheet is available here:
References:
LevmarExcel : uglyduckling.nl/software/levmarexcel/
The original blog - Generalized Procedure for Building Short Rate Trees in Excel / VBA is available here