Use Excel to calculate Hull-White Trinomial Tree

Hull-White Model (赫爾懷特模型)



In financial mathematics, the Hull–White model is a model of future interest rates. In its most generic formulation, it belongs to the class of no-arbitrage models that are able to fit today's term structure of interest rates. It is relatively straightforward to translate the mathematical description of the evolution of future interest rates onto a tree or lattice and so interest rate derivatives such as bermudan swaptions can be valued in the model.

The first Hull–White model was described by John C. Hull and Alan White in 1990. The model is still popular in the market today.

The Hull-White Model satisfy the following stochastic differential equation:



This article use Excel as a tool to generate a Hull-White trinomial tree. Hull-White Trinomial Tree is a lattice model where, at each node, the interest rate can go upward, middle, or downward.

Since Hull-White Model is a mean-reverting model, the interest rate cannot go infinitely large or small. Therefore, if the node goes too far away (more precisely, j > 0.184 / (alpha * delta t) ), the interest rate branches will change shape, as illustrated below:

The generated tree will be looked as follows:

In the Hull-White Model, the following parameters are required before we do our calculations:

1. Term

It is the number of years that we want to generate for our tree

Allowed values: 1~80

2. Step

The step we want to generate for our tree

Allowed values: 1 (yearly), 2 (half-yearly), 4 (quarterly), 12 (monthly)

3. Alpha
A factor that determinate the rate how fast the interest rate reverts to mean
Allowed values: Any

4. Sigma
The volatility of the interest rate
Allowed values: Any

5. Spot Rates
The Zero Coupon Rate as observed at t = 0
The number of Spot Rates required is (Term*Step)

Then, copy the following code in VBA as an user-defined function:

To call the function, type in
=HwTree(Term, Step, Spot_Rates, Alpha, Sigma, Return_Type)
(Ctrl-Shift-Enter)
The amount of rows must be 2*jmax + 1
where jmax is the smallest integer larger than ( Alpha * Step ) / 0.184

Where return type allowed are 1, 2, 3 or 4
1 is the short rate of the Hull-White Model
2 is the Andrew-Debreu Price
3 is the discount price to time zero
4 is the probability of reaching the node

You can download the excel directly at:
https://docs.google.com/file/d/0B5esrphcTfIca3BGWFJDR0JORnM/edit?usp=sharing

References:
Option, Future and other Derivatives (8th Edition) - John C. Hull
http://en.wikipedia.org/wiki/Hull%E2%80%93White_model
http://lombok.demon.co.uk/financialTap/interestrates/hwtrinomialtree
http://janroman.dhis.org/finance/Hull%20&%20White/HWImp.pdf