Browse By

Interest Rate Models: Steps for building Black, Derman and Toy (BDT) model in Excel: Define & Set Solver Function & Results

Earlier we had considered how to define the various elements (input, output and calculation cells) of the Black-Derman-Toy (BDT) interest rate model in EXCEL. In this post we will link all these pieces together by using the Solver Function of the EXCEL worksheet. Once this has been done we will run the Solver Function to arrive at a solution for the median rates and their time varying volatilities (sigmas).

Define & Set Solver Function & Results

Set Solver Function

The next step in the process is to calibrate the outputs of the model with the inputs of the model. This is done as follows:

In the MS Excel 2007 edition go to the Data Tab and Click on Solver. The following image opens up.

  • “Set Target Cell” to the calculation cell for dt.
  • “By Changing Cells” will be the cells for the median rates and sigmas, and
  • “Subject to the Constraints” will be as follows:

Prices from Lattice = Initial Prices
Yield Volatilities from Lattice = Initial Volatility Rates

 Once these are set, press the Solve button. The Solver function should then calculate a feasible solution for the median rates and the sigmas.

Results

The results of the solver function for our illustration are given below:

(Note: In our illustration the initial and final results are the same as we had already run the solver function prior to this write up.  In reality the initial dummy values may be significantly different from the final results).

In this post we reviewed how the Solver Function is set up in this EXCEL construction of the BDT interest rate model. In particular we defined the target and change cells and the constraints with regard to the prices and yield volatilities. After this we ran the Solver Function which converged to a result for the median rates and time varying volatilities (sigmas). In the posts that follow we will see how these results will be used to price bonds and options.

3 thoughts on “Interest Rate Models: Steps for building Black, Derman and Toy (BDT) model in Excel: Define & Set Solver Function & Results”

  1. Pingback: Interest Rate Modelling Posts Index | Learning Corporate Finance
  2. Trackback: Interest Rate Modelling Posts Index | Learning Corporate Finance
  3. Kira says:

    Hello, I’m building BDT model to price options on bonds, your posts appeared very useful and helpful. Everything is clear, but I have stucked up with Solver.
    I can’t understand what cells you have chosen for the Solver, should I set all cells in median and sigma, how should I equal Prices from lattice to initial prices.
    I receive “No feasible results have been found”.
    Could you please send me a screeenshot with the cells you’re using or explain it in more details.
    I would appreciate your help.

  4. Jawwad says:

    Dear Kira

    Let me see if I can dig out my notes. Regards

    Jawwad

Comments are closed.

Comodo SSL