##

(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.

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

**Solution details:**
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)

×
Please Enter Your Payment Email Address To Receive Solution.