# Building BDT model in EXCEL – Define Calculation Cells: Calculate Prices from Lattice

Continuing with the definition of the calculation cells of the Black-Derman-Toy (BDT) model in EXCEL, we will see how prices are determined from the initial yield rates and from the three state price lattices in the post below.

Based on the lattices we now derive the prices as follows:

### c.1.         Initial Price

This is simply the present value of a zero coupon bond of 1 due at duration t discounted to time 0 (i.e. the valuation date), that is

Initial Pricet = exp (-initial yield ratet×t)

For example the price for a zero coupon bond of 1 due at duration 2.5 is:

Initial Price2.5 = exp (-0.84%×2.5) = 0.9792

### c.2.         Price from Lattice

This again is the present value of a zero coupon bond of 1 due at duration t discounted to time 0. However in this instance we do not use the initial rates but the state prices derived in b.1. above.

The price at duration t is the sum of all the entries in this lattice for duration t. For example the Price from Lattice2 = Sum of all state prices for duration 2 in the lattice = 0.0614+0.2462+0.3702+0.2473+0.0619 = 0.9871

### c.3.         Price_up & Price_down

These are prices if the user was now standing at duration 0.5 (assuming semiannual compounding) instead of at duration 0, where Price_up denotes a Price move up from node 0 and Price_down denotes a Price move down from node zero. Effectively if the Price at node 0 were S, then Price_up would be Su and Price_down would be Sd. These prices are needed in order to compute the volatility inherent in the short rate binomial tree.

Again the price at duration t is the sum of all the entries in the related lattice for duration t. For example Price_up3= Sum of all state prices for duration 3 in the Price_up Lattice(b.2.) =

0.0298+0.1500+0.3016+0.3029+0.1520+0.0305=0.9669

In this post, we reviewed how the calculation cells for prices were defined for the BDT interest rate model. In the next post, we consider how these prices are used to determine the yield rates and yield rate volatility of the lattice.