Question Details

(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:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Sep 05, 2019

PRICE: $18

Solution~000200077971.zip (25.37 KB)

Buy this answer for only: $18

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)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 05, 2019

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.

Order Now