# Generalized Procedure for Building Short Rate Trees in Excel / VBA

In their 2014 paper John Hull and Alan White derive 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. The only difference between the various models is the function , which is explained briefly here and in detail in the paper.

To better understand the model we implemented it in Excel/VBA. The resulting code is not meant for production purposes or any large scale applications. It does however capture all the aspects of the models in executable code. As such we hope it will benefit everybody trying to understand the details of the paper. To test our implementation we reproduced the results of the first example mentioned in the paper on page 10. The example considers the short rate model:

which is transformed using

into

with

.

For example’s sake, we use

In a previous blog we explained how the required can be found for the first timestep using only an Excel spreadsheet. If one wants to reproduce the complete results of the example some VBA code is required as it can not be done using only spreadsheet formulas. In the spreadsheet attached to this blog we did exactly that. The spreadsheet works using only the embedded VBA code and requires no additional installation (other than Excel to run). The code use the Levenberg Marquardt method to find the that minimizes the difference between the discounts found in the market and those produced by the model. The algorithm used was originally part of MINPACK developed by Jorge More, Burt Garbow, and Ken Hillstrom at Argonne National Laboratory. The algorithm was translated from Fortran to VBA by Vanna and shared on quantcode.com. We could use the VBA version with only very small modifications for this project.

Our spreadsheet solution for the “Generalized Procedure for Building Trees for the Short Rate” contains 3 sheets:

• Info
• input
• result

Info gives some information about the spreadsheet like the name, version and description. The input sheet is where the spreadsheet is configured for use. The user needs to set the parameters for the model in the cells D14, D15 and D16. The spreadsheet only works for the specific example, but can be easily adapted by changing the VBA code to take different F and G functions. Starting from cell B21, the term structure needs to be enter using maturity, zero rate and discount. The code needs maturity 0 in cell B21 and corresponding discount 1.0 in cell D21 to work properly. In the rows below you can add as many timesteps as you wish providing the spacing is as the entered in cell D16, i.e. 0*, 1*, 2/, ... (The spreadsheet ships with the discounts entered from the 2014 Hull and White paper). Once everything is configure, just hit the “Build Tree”-button and the spreadsheet will do the rest.

The results of the tree construction can be found on the result sheet. On this sheet in cell B8 a timestamp is given of when the results have been last updated. Cell B13 gives the value of the error function, which should be very small (<1e-20) if a minimal has been found. The rates at each level in the tree are presented in the table starting cell B15. Some intermediate results are given in the table starting from cell B26. The key results, namely the transition matrix and Arrow Debreu prices, are given in the tables starting cell B34 and B52. Note that the tables will move from location in the spreadsheet to adapt to size of previous tables for different settings on the input tab.

Happy tree building.

The spreadsheet is available here:

Hull White 2014 - Example 1

References:

A Generalized Procedure for Building Trees for the Short Rate and its Application to Determining Market Implied Volatility Functions by John Hull and Alan White

and the paper here: