Binomial Option Pricing and Black - Scholes Model
Attached is word doc describing requirements as well as the Excel template. Please let me know if you can help me with this request.
Excel programming: option pricing with six-step binomial tree
You need to have six input cells: S, X, rannual, σannual, T, and N=6. All other cells should be formulas and automatically computed. Note that the risk free rate (rannual) is continuously compounded and that you need to use the EXP function.
For the Binomial Model:
Based on input variables, compute u, d, r, p, and 1-p.
u=e^((σ√T)/N)-1; d=e^(-(σ√T)/N)-1; r=e^((r_annual×T)/N)-1; p=(r-d)/(u-d)
Produce five trees, S, CE, PE, CA, and PA, and EEP (early exercise premium) for CA and PA.
Four option trees: there should be only two unique formulas for each option tree: one formula for all leaf nodes and one formula for all non-leaf nodes. Your file should allow me to copy a formula from a leaf node and paste it onto a different leaf node in the same tree. I can also copy a formula from a non-leaf node and paste it onto a different non-leaf node in the same tree. Your options tree should remain correct.
One stock tree: there should be only three unique formulas in the stocks tree: root, up node, and down node. The rest of the nodes should be done by copy/paste one of the three unique formulas. You should not use the power function.
You may not use the property that CA = CE, which means in your CA tree, you need to program the early-exercise feature of CA.
Do not turn in two-period trees. They are used for only demonstration purposes.
For the Black-Scholes Model:
Fill-in the entries in the Black-Scholes section of the spreadsheet (Below the Binomial Model).
Fill-in the Summary Table
Make a copy (NOT a cell reference) of your initial stock price into cell F90.
Complete the three Data Tables and Graph them.