In this post, we continue with the definition of the calculation cells of the Black-Derman-Toy (BDT) model in EXCEL by seeing how yields are determined from the prices calculated from the state price lattices. We use these determined yields to derive the yield volatility of the lattice.
Calculate Yields
As mentioned earlier the model is calibrated by equating the prices from the lattice with the prices computed using initial rates. These prices have been calculated above. The second set of rates to be calibrated is the initial volatility rates. In order to compare the initial volatility from the model (i.e. the yield volatility from the lattices) with the initial volatilities observed, yields associated with Price_up and Price_down have to be calculated.
These are determined as follows:
Yield_upt=-ln(Price_upt)/(t-1)
Yield_downt=-ln(Price_downt)/(t-1)
For t > 1 time step up (0.5 in our semi-annual example)
(Again note that by t-1 we mean the duration one time step before t. For semi-annual compounding if t is 1 the duration t-1 is 0.5; for annual compounding if t is 1 the duration t-1 is 0)
For example, Yield_up1.5=-ln(0.9928)/(1) = 0.73%
And Yield_down1.5=-ln(0.9953)/(1) = 0.47%
Calculate Yield volatility from Lattice
This is calculated using the Yield_up and Yield_down rates calculated above.
Specifically,
In this post we reviewed how the calculation cells for yields and yield volatility was defined for the BDT interest rate model. In the next post we tie together all the various aspects of the model, i.e. the input, output and calculation cells, by using the Solver Function of the EXCEL worksheet.
Comments are closed.