Excel programming: Option pricing with a six-step binomial tree

This project is an individual project. You may not work with others. The total point for this project is 15% of your course grade.

You need to submit an Excel file. You 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, not (1+r)T.

For the Binomial Model (8% of course grade):

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)

Build five trees, S, CE, PE, CA, and PA, and EEP (early exercise premium) for CA and PA.

One stock tree: there should be only three unique formulas in the stocks tree: a root, an up node, and a down node. The rest of the nodes should be done by copy/paste one of the three unique formulas. Use color-coding to show the same formulas. You should not use the power function.

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. Use color-coding to show the same formulas.

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.

You should first implement the two-step trees outlined on the slide “When Binomial Tree Meets Excel” (around slide 115) the revised PPT notes (https://drive.google.com/drive/folders/1hfhuF18G67tcme3fwzJoLdCjgm7Wf-RY?ths=true) to match all answers before you attempt the six-step trees. Your (BIN/BS) answers should be identical to mine. If your answers are not the same as mine, you need to work on yours until they are the same. Otherwise, there is no point in going to the six-step trees because your answers are already wrong. Keep your implementations of the two-step trees on a sheet in the Excel file as a part of your assignment.

I have posted an instructional video. You may view it: https://drive.google.com/file/d/1ex3QOMlvR7rQIcklNKK6L84-T7dJdFLf/view.

For the Black-Scholes Model (7% of course grade):

Use your assigned input numbers (cells B3:F3) from the file FIN_617_Option_Project_Input Numbers.xlsx that is on Blackboard.

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.

For the Entire Assignment:

Make your Excel file easy and pleasant to read. Carefully and clearly label everything. You may use any format as long as it is clear. Format all prices to two decimal places, with a dollar sign in the front, e.g. $23.45. Format all percentages as a percent, e.g., 12.34%.

Rename your Excel file by adding your name, e.g., for Prof. Barrett the file FIN_617_Option_Project_LName_FName.xlsx would become FIN_617_ Option_Project_Barrett_Brian.xlsx. Do not use any other file names!

Check your work carefully before you submit your file on Blackboard. If you resubmit, your first submission will be deleted.