Browse By

How to build a Black Derman Toy Model (BDT) in EXCEL

Building Black Derman Toy Model in Excel.

This course presents a step by step methodology for building a one factor BDT model in EXCEL.

STEP 1: Define Input Cells

In the following post we consider the required inputs for the model. These include among other things the initial yield rates and their volatilities. Other inputs are the term of the instrument being valued, the number and length of time step intervals considered, the valuation date and the duration to each future node:

Interest Rate Simulation Models: Steps for building Black, Derman and Toy (BDT) model in Excel: Define Input Cells

STEP 2: Define Output Cells

Next, we will assign the output cells in the sheet – the cells that will present the resulting values once the formula cells are defined and the solver function is linked up to them and run. The outputs of the model include the median rates, sigmas (time varying volatilities) and the up movements or proportions by which prices increase which are used in for the construction of the BDT short rate tree.

Interest Rate Simulation Models: Building Black, Derman and Toy (BDT) in Excel: Define Output Cells

STEP 3: Construct a short rate binomial tree

In the next stage the calculation or formula cells are defined. The first step in this process is the setting up of the short rate binomial tree constructed using the median rates, sigmas and up movements of the output cells:

Interest Rate Simulation Models: Steps for building Black, Derman and Toy (BDT) model in Excel: Define Calculation Cells: Construct short rate binomial tree

STEP 4: Construct state price lattices

The next interim step is the construction of three price lattices, one derived at node 0, one at node 1 and one at node -1:

Interest Rate Simulation Models: Steps for building Black, Derman and Toy (BDT) model in Excel: Define Calculation Cells: Construct State Price Lattices

STEP 5: Calculate prices from lattices

We next define Prices- initial prices derived from the initial yield rates, and prices from each of the lattices defined in Step 4:

Interest Rate Simulation Models: Steps for building Black Derman Toy model in Excel: Define Calculation Cells: Calculate Prices from Lattice

STEP 6: Calculate yields and yield volatilities from lattice

The final set of calculation cells involves calculating the yields from the lattice prices and then the volatilities from the resulting yields:

Interest Rate Simulation Models: Steps for building Black Derman Toy model in Excel: Define Calculation Cells: Calculate Yields & Yield volatility from Lattice

STEP 7: Define & Set Solver Function. Run Solver & View Results

The last step of the BDT model construction links the input, output and calculations cells together using the Solver Function. The Solver function is then run to arrive at the solved for median rates and sigma values:

Interest Rate Simulation Models: Steps for building Black Derman Toy model in Excel: Define & Set Solver Function & Results 

Running the Solver function in Step 7 calibrates the model to the current spot yield curve and its volatility term structure.

An alternate method of calibration involves solving for the median rates and sigmas by minimizing the difference between the model and actual observed prices of very liquid instruments as of the valuation date. This is illustrated for US Treasury government bonds in the following post:

Using US Treasuries to calibrate the Black Derman Toy Model

Workaround the limitations of EXCEL’s Solver functionality
EXCEL Solver functionality does have its limitations when there are a large number of adjustable cells In such cases Solver will fail to compute. In other instances even when the number of adjustable cells are within Solver’s capability, the function repeatedly returns an error or may return a message that it cannot find feasible results.

A work around method to these limitations is to utilize Solver in a piece meal fashion across the entire range of results. This methodology is discussed in the following post:

BDT interest rate model – Limitations with EXCEL’s Solver Functionality and Workaround

 

2 thoughts on “How to build a Black Derman Toy Model (BDT) in EXCEL”

  1. Pingback: Using US Treasuries to calibrate the Black Derman Toy (BDT) Model | Finance Training Course
  2. Trackback: Using US Treasuries to calibrate the Black Derman Toy (BDT) Model | Finance Training Course
  3. Pingback: BDT interest rate model – Limitations with EXCEL’s Solver Functionality and Workaround | Finance Training Course
  4. Trackback: BDT interest rate model – Limitations with EXCEL’s Solver Functionality and Workaround | Finance Training Course

Leave a Reply

Comodo SSL