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

There are no personal links in this question, I have attached screenshots of the excel set up but does not need to look exactly like the screenshots. All the input numbers are in the orange cells (I have inputed that into in to the screenshots) but they table below would be the numbers needed to be inputed. Really need help getting the formulas. I have been working on it for two weeks and keep getting error message upon error message.

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

1. 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.
2. For the Binomial Model:
   1. Based on input variables, compute u, d, r, p, and 1-p.

1. Build five trees, S, C^E, P^E, C^A, and P^A, and EEP (early exercise premium) for C^A and P^A.
2. 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.
3. 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.
4. 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.
5. first implement the two-step trees
6. For the Black-Scholes Model ):
   1. input numbers (cells B3:F3) from the first part
   2. Fill in the entries in the Black-Scholes section of the spreadsheet (Below the Binomial Model).
   3. Fill in the Summary Table
   4. Make a copy (NOT a cell reference) of your initial stock price into cell F90.
   5. Complete the three Data Tables and Graph them.

Stock Exercise Annual OAnnual N U d r p 1-p Score on Binomial Portion Input $94.00 $94.00 2.70% 17.70% 0.28 6 Score on Black-Scholes Portion 75.00 Period 0 Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 Excess Exercise Premium for American Call, EEP(CA) = Excess Exercise Premium for American Put, EEP(PA) = Stock Ameri European can Call Option Call Option Ameri European can Put Option Put OptionMake a Data Table of each Option Value as a function of the annualized risk-free Rate Black-Scholes Option Pricing Model Bin-Call Bin-Put (European) BS_Call (European) BS-Put Put Graph Here: $0.00 $0.0 $0.00 $0.00 Variable Notation Solution 0.0% div. yield delta 0.00% 0.5% adj. stock S 1.0% To get the Cumulative Standard Normal of d1 d1 1.5% d2 d2 dl or d2, use =NORM.S.DIST(x, TRUE), 2.0% N(d1) N(d1) where x is the cell address for dl or d2, and 2.5% N(d2) N(d2) TRUE indicates that it is the cumulative 3.0% distribution. call C 3.5% put P 4.0% C+bond C+PV(X) 4.5% P+stock P+S' 5.0% 5.5% Summary Table 6.0% Intrinsic Call Value 6.5% Intrinsic Put Value 7.0% Binomial Option Value: European Call Make a Data Table of each Option Value as a function of the annualized standard deviation Bin-Call Bin-Put European Put (European) BS_Call (European) BS-Put Put Graph Here: Call Time Value $0.00 $0.0 $0.00 $0.00 Put Time Value 12% Black-Scholes Option Values 13% European Call 14% European Put 15% Call Time Value 16% Put Time Value 17% 18% Use "COPY" and PASTE as VALUES to get the answers into the 19% Yellow cells below. 20% Implied Volatility using the Black-Scholes Model 21% given the market price of a Call = $4.0600 22% What is volat using BS Call 23% given the market price of a Put = $3.2800 24% What is volat using BS Put 25% 26%Indicate your Goal Seek Inputs below Goal Seek Call implied o Type your Initial Stock Price here Make a Data Table of each Option Value as a function of the underlying stock price Set Cell $94.00 (HINT: In the data table, refer to the initial price in B2, not the one at the left!) To Value Bin-Call Bin-Put (Do NOT use a cell reference!) (European) BS_Call (European) BS-Put By Changing Also, do not use this copied price anywhere in $0.00 $0.00 $0.00 $0.00 your solution other than where it is already Put Graph Here: linked! $84 Goal Seek Put implied o $86 Set Cell $88 To Value $90 By Changing $92 $94 $96 $98 $100 $102 $104 $106 $108 $110 $112 $114 $116 $118 $120 $122 $124Binomial and Black-Scholes Sample Solutions If your inputs are: Stock Exercise I'Annual OAnnual T N u d r p 1-p Input $160.00 $164.00 6.60% 17.90% 0.4 6 1.90% -1.87% 0.44% 61.21% 38.79% Then you should get these answers: Intrinsic Call Value 0.00 If you use these inputs (in Orange) in your Intrinsic Put Value 4.00 solution, you should get the answers Binomial Option Values shown. European Call 3.11 European Put 2.83 Call Time Value 3.11 Put Time Value -1.17 Black-Scholes Option Values European Call 7.3535 European Put 7.0806 Call Time Value 7.35 Put Time Value 3.08 Implied Volatility using the Black-Scholes Model given the market price $7.7200 What is volat using BS 18.8101% given the market price $7.3600 What is volat using BS 18.5940%