About BDT spreadsheet:



About BDT spreadsheet:

The spreadsheet contains a 6-six step binomial tree for the Black-Dermon-Toy model.

There are two macros that you will need to use:

create_binomial_tree

ReCalculate

Before you can use solver from a macro, you will have to follow the procedure below:

1) Open the spreadsheet HandoutBDT

2) Press Alt-F11, this will bring up the Visual Basic Editor

3) Pull down menu Tools->References, this will bring up the dialog box for available references

4) Scroll down the list and check the box for "visual basic for applications"

5) Click the browse button and look for the file tsolver.xla or solver.xls on your hard-drive, it will most likely be in c:\Program Files\MSOffice\Library\Solver .

6) Select solver.xla, click OK

7) Close the Visual Basic Editor and return to Excel

1) Setting up the 6 step binomial tree:

There is a macro in the spreadsheet that will set up an N-step binomial tree for you.

To run the macro:

pull down the menu: Tools ->Macro->Macros

select create_binomial_tree from the list of macros

click Run

The purpose of running the macro is to reset default values to reasonable numbers, as the Excel solver is not very robust. After running ReCalculate a few times with different initial term structures, Excel solver might fail to find any solutions. If that happens, run the create_binomial_tree macro.

2) How would I compute the term structure?

• Enter the initial term structure: You will need enter maturity (in years), interest rates and annualized volatility for period(0) and state(0) in the sheet rates

• run macro ReCalculate

3) Layout of the binomial tree explained:

There will be three trees on two different sheets, for computational convenience.

Sheet rates:

At the nth level of the tree, there will be n different possible term structures.

The table below illustrates a typical level in the tree:

Period(I)=2. There are 2+1=3 possible states at the beginning of the second period (namely the UU state UD and DD state). The states are indexed by j=0,1,2, respectively. The index j refers to the number of downs, and is stored in cell just above cell "rate". The number in the cell right next to state (and above cell "volatility") is the probability that the system will be in state j during period I (which is useful in calculating value of bond options.)

.

Each state has its own interest rate term structure and volatility term structure and boxed individually.

|Period(i) |2 | | | | | | |

|State(j) | |0 |0.2500 |1 |0.5000 |2 |0.2500 |

|Steps(k) |Maturity |rate |volatility |rate |volatility |rate |volatility |

|1 |0.5 |8.164% |6.74% |8.981% |6.74% |9.879% |6.74% |

|2 |1 |4.082% |4.77% |4.490% |4.77% |4.939% |4.77% |

|3 |1.5 |2.721% | |2.994% | |3.293% | |

Sheet prices:

There are two trees in the Sheet prices:

• The first one is identical to the interest rate/volatility tree, except that we store the corresponding zero-coupon bond price and volatility factors instead.

• The second one stores the arbitrage-free constraints that have to be satisfied at each node, don't worry about it unless something goes wrong.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download