Please I need helpwith this project. It is due friday

Excel programming: option pricing with six-step binomial tree 1. This project is an individual project. You may not work with others. 2. The total point for this project is 15% of your course grade. 3. You need to submit an Excel file. Use examples in the lecture notes as a reference. Fully understand the examples in notes before you start to work on this project. 4. 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. 5. For the Binomial Model (8% of course grade): a. Based on input variables, compute u, d, r, p, and 1-p. r T T T rd N N N u=e 1 ; d=e 1 ; r=e 1; p= ud annual b. Produce five trees, S, CE, PE, CA, and PA, and EEP (early exercise premium) for CA and PA. c. 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. d. 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. e. 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. f. Do not turn in two-period trees. They are used for only demonstration purposes. 6. For the Black-Scholes Model (7% of course grade): a. Fill-in the entries in the Black-Scholes section of the spreadsheet (Below the Binomial Model). b. Fill-in the Summary Table c. Make a copy (NOT a cell reference) of your initial stock price into cell F90. d. Complete the three Data Tables and Graph them. 7. For The Entire Assignment: a. Make your Excel file easy and pleasant to read. Carefully and clearly label everything. You may use any format (including the one given in the next page) 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%. b. Rename your Excel file by adding your name, e.g., for Prof. Barrett the file USM1_FIN_617_Week06_OptionModel.xlsx would become USM1_FIN_617_Week06_OptionModel _Barrett_Brian.xlsx. Do not use any other file names! c. Check your work carefully before you submit your file to me. If you resubmit, you first submission will be deleted. Input S Input $100.00 X $109.0 0 Binomial Ca $3.13 Binomial Pa $10.05 period 0 Stock $100.0 0 rAnnual Annual T N u d r p 1-p 10.00% 36.00% 0.25 2 -8.61% 1.26% 54.73% 45.27% Ce $3.13 Pe $9.44 9.42% EEP(Ca ) EEP(Pa ) period 1 $109.4 2 period 2 $119.7 2 $100.0 0 IV(call) IV(put) $10.72 $0.00 $0.00 $9.00 $0.00 $25.47 $0.00 $0.61 $91.39 period 0 Europea n Call Option period 1 $83.53 period 2 $10.72 $5.80 $3.13 $0.00 period 0 Europea n Put Option America n Call Option period 1 $9.44 $9.00 $16.25 $0.00 period 2 $10.72 $5.80 $3.13 period 2 $0.00 $4.02 $0.00 period 0 period 1 $0.00 $0.00 period 0 America n Put Option period 1 $25.47 period 2 $0.00 $4.02 $10.05 $9.00 $17.61 $0.00 $25.47 Stock Exercise rAnnual s Annual t Input N 6 Period 1 Period 2 Period 3 Period 4 Period 5 European Put Option European Call Option Stock Period 0 Make a Data Table of each Option Black-Scholes Option Pricing Model Variable Notatio n Solution 0.0% div. yield delta 0.00% adj. stock S' d1 d1 d2 d2 N(d1) N(d1) N(d2) N(d2) call C put P 4.0% C+bond C+PV(X) 4.5% P+stock P+S' 5.0% 0.5% 1.0% To get the Cumulative Standard Normal of d1 or d2, use =NORM.S.DIST(x,TRUE), where x is the cell address for d1 or d2, and TRUE indicates that it is the cumulative distribution. 1.5% 2.0% 2.5% 3.0% 3.5% 5.5% Summary Table 6.0% Intrinsic Call Value 6.5% Intrinsic Put Value 7.0% Binomial Option Values European Call Make a Data Table of each Option European Put Call Time Value 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 Yellow cells below. 19% Implied Volatility using the Black-Scholes Model 21% given the market price of a Call = 22% What is volat using BS Call 23% given the market price of a Put = 24% What is volat using BS Put 25% 20% 26% Indicate your Goal Seek Inputs below Goal Seek Call implied s Set Cell To Value Type your Initial Stock Price here Make a Data Table of each Option (HINT: In the data table, refer to th (Do NOT use a cell reference!) By Changing Goal Seek Put implied s Set Cell To Value By Changing Also, do not use this copied price anywhere in your solution other than where it is already linked! ($10) ($8) ($6) ($4) ($2) $0 $2 $4 $6 $8 $10 $12 $14 $16 $18 $20 $22 $24 $26 $28 $30 u d r p 1-p Score on Binomial Portion Score on Black-Scholes Portion Period 6 Excess Exercise Premium for American Call, EEP(CA) = American Put Option American Call Option Excess Exercise Premium for American Put, EEP(P A) = Make a Data Table of each Option Value as a function of the annualized risk-free Rate Bin-Call Bin-Put (European) BS_Call (European) $0.00 $0.00 $0.00 BS-Put $0.00 Put Graph Here: Make a Data Table of each Option Value as a function of the annualized standard deviation Bin-Call Bin-Put (European) BS_Call (European) $0.00 $0.00 $0.00 BS-Put Put Graph Here: $0.00 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!) Bin-Call Bin-Put (European) BS_Call (European) BS-Put $0.00 $0.00 $0.00 $0.00 Put Graph Here: 75.00 75.00 out of 75 Do NOT write here - it is for grading Black-Scholes Option Pricing Model Summary Table Implied Volatility using the Black-Scholes Mod Data Tables Charts on Pricing Model 27.00 18.00 sing the Black-Scholes Model 10.00 10.00 10.00 Binomial and Black-Scholes Sample Solutions If your inputs are: Input Stock Exercise rAnnual s Annual t N u d $100.00 $109.00 10.52% 33.36% 0.36 6 3.39% -3.28% Then you should get these answers: Intrinsic Call Value 0.00 Intrinsic Put Value 9.00 Binomial Option Values European Call 1.39 European Put 6.34 Call Time Value 1.39 Put Time Value -2.66 Black-Scholes Option Values European Call 5.9298 European Put 10.8800 Call Time Value 5.93 Put Time Value 1.88 Implied Volatility using the Black-Scholes Model given the market price of $4.71 What is volat using BS Ca 28.1879% given the market price of $10.88 What is volat using BS Pu 33.3585% If you use these inputs (in green) in your solution, you should get the answers shown. r p 1-p 0.63% 58.65% 41.35%