Option valuation lattice model assignment
Dr. Michael A. Crain
Florida Atlantic University
c©Michael A. Crain, 2018
1
1 Due date
See Canvas.
2 Assignment description
You will be working individually on the assignment. This assignment will give you practice in
uilding a binomial lattice model and observing how option prices change when model inputs change.
Your beginning point will be the lattice example in Pratt (2008:602). You will be building a binomial
lattice model in your spreadsheet software by replicating the one from the text. After replicating
Pratt’s model, you will be changing some of the model inputs and observing the effect on the option
price. The assignment components are:
1. Build a model in Excel or other spreadsheet software that replicates the entire option pricing
model shown by Pratt (2008:602) in his exhibit. When you have completed your model, the
esults of your formulas should produce the identical results shown by Pratt.
Your model will use formulas to derive the model outputs. Put differently, you are not
hard-coding Pratt’s model outputs. Rather, you are coding the relevant formulas into you
model. For instance, the text shows stock price model outputs in the range of cells C22 to
H27 and option prices in the C32 to H37 range. Each of the cells in the lattice structure has a
formula, including the cells showing a hyphen. Your model should also have formulas in the
elevant output cells whether they show an amount or hyphen.
Good modeling technique suggests that complex models should differentiate input and output
cells. I suggest that you employ good practice technique by using color shading to differentiate
input and output cells. The values in rows 2 through 5 and rows 7 through 10 are model
inputs and would have one fill color. (Row 6 is an output.) The remainder of the model with
outputs would have shading of a different color. Input cells could use a fill color of light blue
and output cells could use the color yellow. Further, good technique requires a color code in
the spreadsheet that explains the meaning of each color. In practice, a color explanation tends
to appear at the top of each spreadsheet. For this assignment, I suggest that you put you
color explanation at the bottom of the spreadsheet so that you can keep the cell references in
Pratt (cells A1, A2, and so on) in your model.
2. Change the stock’s volatility (standard deviation of returns) from 29.59% to 40% and observe
the price of the call option.
3. After resetting the stock’s volatility to the original value shown by Pratt of 29.59%, change
the dividend from $10 to $15 and observe the price of the call option.
4. Write a short report for a business audience that describes how the option price changes when
(i) increasing the stock’s volatility and (ii) increasing the stock’s dividend, described above in
2
the second and third items. In addition, describe the theoretical reasoning for why the option
price moves in a certain direction when increasing volatility and increasing dividends. Use
good writing style appropriate for the intended audience (e.g., active voice).
3 Deliverable
You will be submitting one Excel file and one Word file (or equivalent file formats). Submit you
assignment via Canvas in the area where you found the assignment materials.
Excel file. Your Excel file will have several worksheets. The first worksheet should be information
showing only (1) your name, (2) the week of this class assignment, (3) course number, (4) professor’s
name and (5) semester. The second worksheet will be only your lattice model replicating Pratt as
described above. Your third and fourth worksheets will be your lattice model changing the volatility
and dividend, respectively, described earlier and showing the results of these changes.
Word file. The first page of your Word file should be information showing only (1) your name,
(2) the week of this class assignment, (3) course number, (4) professor’s name and (5) semester. The
subsequent pages will be your short report for a business audience as described above. There is no
need to follow APA formatting guidelines for this assignment.
If your submissions do not follow these instructions, I will deduct points.
3
Due date
Assignment description
Deliverable