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

Stock	Exercise	rAnnual	Annual	T	Cmarket (C83)	Pmarket (C85)
$94	$94	2.70%	17.70%	0.28	$4.06	$3.28

You need to submit an Excel file. You have six input cells: S, X, r_annual, _annual, T, and N=6. All other cells should be formulas and automatically computed. Note that the risk-free rate (r_annual) 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.

Build five trees, S, C^E, P^E, C^A, and P^A, and EEP (early exercise premium) for C^A and P^A.

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 C^A = C^E, which means in your C^A tree, you need to program the early-exercise feature of C^A.

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.

Excess Exercise Premium for American Call, EEP(C.4) = Excess Exercise Premium for American Put, EEP(P. )= Black-Scholes Option Pricing Model Make a Data Table of each Option Value as a function of the annualized risk-free Rate Indicate your Goal Seek Inputs below Type your Initial Stock Price here Make a Data Table of each Option Value as a function of the underlying stock price (HINT: In the data table. refer to the initial price in B2. not the one at the left!) (Do NOT use a cell reference!) Also, do not use this copied price anywhere in your solution other than where it is already linked! Binomial and Black-Scholes Sample Solutions If vour inbuts are: Then you should get these answers: IntrinsicCallValueIntrinsicPutValueBinomialOptionValuesIfyouusetheseinputs(inOrange)inyoursolution,youshouldgettheanswersshown. Black-Scholes Option Values European Call European Put Call Time Value Put Time Value Implied Volatility using the Black-Scholes Model Excess Exercise Premium for American Call, EEP(C.4) = Excess Exercise Premium for American Put, EEP(P. )= Black-Scholes Option Pricing Model Make a Data Table of each Option Value as a function of the annualized risk-free Rate Indicate your Goal Seek Inputs below Type your Initial Stock Price here Make a Data Table of each Option Value as a function of the underlying stock price (HINT: In the data table. refer to the initial price in B2. not the one at the left!) (Do NOT use a cell reference!) Also, do not use this copied price anywhere in your solution other than where it is already linked! Binomial and Black-Scholes Sample Solutions If vour inbuts are: Then you should get these answers: IntrinsicCallValueIntrinsicPutValueBinomialOptionValuesIfyouusetheseinputs(inOrange)inyoursolution,youshouldgettheanswersshown. Black-Scholes Option Values European Call European Put Call Time Value Put Time Value Implied Volatility using the Black-Scholes Model