# Building BDT model in EXCEL – How to utilize the results of a BDT interest rate model: Pricing Options

In this post, we will consider how the Black-Derman-Toy (BDT) short rate binomial tree will be used to price options on bonds.

The BDT model may also be used to price put or call options on bonds. For the purpose of calculating these prices it is important to generate the entire short interest rate tree until the expiration of the option. The prices of the underlying coupon bearing instrument are calculated based on the projected short rates on the option expiration date. As these short rates are for the 6-month tenor, a spread is added to them prior to price calculation based on the outstanding term of the bond (from option expiration date to maturity). The spread is taken as the historical spread between the relevant yield rate based on outstanding term to maturity of the bond and the 6 month yield rate. This method implicitly assumes that the yield rates of all maturities are perfectly correlated at all times with the 6 month yield rate and that existing spreads are representative of future spreads between these rates.

The payoffs for European put and call options are calculated at the option expiration date for each projected short rate and then are discounted to time 0 (in our case 27-July-2010) using the risk neutral probabilities (0.5) and the binomial interest rate tree to arrive at the prices of the put and call options.

Let us assume that there is a European put option on the bond issue mentioned earlier. The option expiration dates falls immediately after the relevant coupon payment date on 27-July 2012. The strike price of the put option is equal to the par value of the bond, i.e. 100.

## Step 1: Deriving the short rate tree

Using the median short rates and the up movements together with the procedure for deriving the short rates given above, the binomial tree for 6 month (0.5) intervals till the option’s expiration date is as follows:

## Step 2: Calculating the projected short rates, prices and payoffs of the put

We need the projected yield rates on the option expiration date. For this we first add a spread to the projected short rates determined in the interest rate tree above. Again the spread is added because the interest rate tree only projects the short 6- month rates and we need a yield that is consistent with the outstanding term of the underlying bond (which is 1 year in this case).

Using the excel price formula we derive the projected prices of the underlying bond using yield = projected yield rates and settlement date = option expiration date. Once these prices are obtained the payoff under the put premium is determined for each projected price where the payoff = maximum {Strike – Price of bond, 0}. The details of this calculation are given in the table below:

## Step 3: Calculating the put price at time 0

We now discount the payoffs using the short rate tree and the risk neutral probabilities to arrive at the put price as at 27-July-2010 as follows:

*0.5×(1.45+0.45) ×exp(-1.34%×0.5)=0.94

**0.5×(0.94+0.22) ×exp(-0.64%×0.5)=0.58

***0.5×(1.08+0.34) ×exp(-0.21%×0.5)=0.71

Hence the price of a European put as at 27-July-2010 on a 3-year fixed income bond expiring on 27-July 2012 immediately after the coupon payment date of the underlying bond is 0.71.

We reviewed the step-by-step process of obtaining the value of options, in particular the price of a put option on a bond.

In the next posts we will be looking at the construction of yet another interest rate model in EXCEL; the multifactor Heath-Jarrow-Merton (HJM) interest rate model.