## (Solved) Excel programming: option pricing with six-step binomial tree 1. This project is an individual project. You may not work with others. The total point...

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
râˆ’d
N
N
N
u=e âˆ’1 ; d=e
âˆ’1 ; r=e
âˆ’1; p=
uâˆ’d
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.
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

Solution details:
STATUS
QUALITY
Approved

This question was answered on: Sep 05, 2019

Solution~000200077971.zip (25.37 KB)

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

STATUS

QUALITY

Approved

Sep 05, 2019

EXPERT

Tutor