Building BDT model in EXCEL – Define Output Cells

2 mins read

In the second stage of the construction of the Black-Derman-Toy (BDT) one factor interest rate model in EXCEL we will look at how the output cells to the model are assigned.

The median rates (except for the median rate at t =0) and sigmas are the solved for cells that result when the model outputs are calibrated to the observed inputs as defined by the initial yield rates and volatilities. These cells are derived by running a solver function in excel that equates:

  • the prices derived using the constructed BDT short rate binomial tree to the prices derived using the initial yield rates
  • the yield volatilities present in the computed price lattices (assuming one shift up and down from the starting node) to the initial volatilities of the spot rates

Median rate, rt = short rates that lie on the symmetry of the binomial interest rate tree, primarily either equal to the rates on the mid-branch of the tree or the average of the two short rates around the centre. The median rate at t = 0 is set equal to the zero rate applicable to the first interval. That is for a semi-annual compounding tree the median rate at t=0 is equal to the 6 month zero coupon rate.

Sigma, ?t = Time varying volatility inherent in the short rate tree.

ut =exp (?t?dt)=These up movements are the proportion by which the price rises in one period. If the initial price is S the price will rise to Su or will decline to Sd in one period where d =1/u. For example, u0.5 =exp (35%×?0.5)=1.2808.

Initially dummy values are entered in the median rates and sigmas cells. The actual results will be arrived at once the solver function is run as mentioned earlier.

In this post we saw how output cells for the BDT model, i.e. the median rates of the BDT short rate binomial tree, their time varying volatilities (sigmas) and the up-movement for the BDT short rate binomial tree, were assigned in EXCEL. In the next posts we will be defining the calculation cells, in particular the short rate binomial tree, state price lattices, price cells, yield cells and yield volatility from lattice cells.

Comments are closed.