Building BDT model in EXCEL – Define Input Cells

2 mins read

This is the first of seven posts where we will be considering the step- by- step process of building the Black-Derman-Toy (BDT) one factor interest rate model in EXCEL. In this post we will see how the input cells to the model, among other things the initial yield rates and volatilities, are defined.

Assign Input cells on the excel sheet. These are:

Term = Tenor being considered, example 3 years, 5 years, 10 years etc. This usually corresponds with the tenor of the instrument being valued.

No. of intervals = Number of compounding periods in the tenor. For semi-annual compounding this would be Term ×2. This usually corresponds with the number of future coupon payments of the instrument being valued.

Length of intervals (dt) = Duration of the compounding period in years. In our illustration and in the rest of the document we assume a semi-annual compounding period. It is calculated as Term/ No. of Intervals. This usually corresponds with the coupon paying frequency of the instrument being valued. For now it is just important to know that this is an important cell in the computational process as it is the target cell of the Solver Function to be defined later (see section 4 below). The cell therefore has to contain a formula and not an input.

Valuation Date = Date on which the short rate tree will be calculated. This usually corresponds with the date on which the instruments price is being calculated.

Date = Refers to the dates on which the future short rates will be derived, i.e. the future nodes of the short rate binomial tree. For an instrument being priced on its issue date this would correspond with its future coupon payment dates.

t = Duration since the valuation date. It may be noted that if a tenor of 3 is chosen with semi-annual compounding then the durations to be considered will be 0 to 3.5 at half yearly intervals. If the tenor is 5 with annual compounding then the durations to be considered will be 0 to 6 at yearly intervals. This is necessary for calculating “Yield_up” and “Yield_down” described in section 3d.

Initial yield rate = These are the continuously compounded zero-coupon rates derived from the par term structure. This course assumes that the reader has already derived the zero coupon rates which are now available for input.

Initial volatility rate = This is the spot rate volatility term structure derived by obtaining a time series of zero coupon rates, calculating a return series for each rate, calculating the standard deviation for each return series and finally scaling this standard deviation to get annualized volatilities. This course assumes that the reader has already completed this exercise and the volatilities are now available for input.

In this post we saw how input cells for the BDT model were assigned in EXCEL. In the next post we will consider how to define the output cells.

Comments are closed.