The purpose of this assignment is to have you solve a number of problems using a variety of
spreadsheets that will be provided to you. For the most part, the spreadsheets are designed so
that you need merely enter the input variables, which are indicated by the red font. You
should only have to change the variables with the red font. Some other colours are used as
well, for example, in some cases the solution is given in blue font.
NOTE: any input variables that happen to be in the spreadsheet when you receive them
should be considered to be irrelevant. You will provide all the relevant input variables.
For each spreadsheet, answer the questions provided. The questions will be indicated by
letters, (a), (b), etc. Generally, each question is worth 1 mark. Please do not hand in a
spreadsheet unless explicitly asked to do so.
Introduction to the Spreadsheets Puts&Dvd.xls and Call&Dvd.xls
NOTE: before opening the spreadsheets Call&Dvd.xls and Puts&Dvd.xls, you need to go to Tools,
Add-Ins, and make sure therefs a check mark beside Analysis ToolPak-VBA. Also, make sure
that therefs a check mark beside Solver.
(To do this with Office 2007, click on the Microsoft Office button on the upper left corner.
When the window comes up, look for Excel Options at the bottom of the window. Then click
gAdd-Insh. Then, in the Manage box at the bottom of the window, select Excel Add-ins, and
then hit Go. Make sure there are ticks beside Analysis ToolPak-VBA and Solver.)
Many of the bond functions require the Analysis ToolPak-VBA add-in. In the past it has been
difficult to send files to students because of these bond functions. Therefore, I have removed
the gequalh sign from all the bond functions. So, you have to go to each of the cells with the
light blue background and insert an equal sign in front of the formula. (A useful trick is to hit
the F2 key, then the Home key, then type the = key.) First, go to cell Q2, and insert the equal
sign. Then go to cell R2, insert the equal sign, and copy and paste that formula into the
remaining cells in that row (down to cell BE2). (Alternatively, you may gdragh the equation
across the row.)
The files Puts&Dvd.xls and Call&Dvd.xls calculate option prices using the binomial model we
learned in class. One thing that each of the spreadsheets has in common, is that the binomial
is represented in the spreadsheets as
So, as prices move horizontally, they increase. So, for example, when the price of the
underlying asset is given in row 21, if the price moves up, it stays in row 21, if the price
drops, it moves down to row 23 (in the next column). (In general, once the price is in row 21
it canft move back up to row 19, despite appearances; the best it can do is move horizontally.)
Also, the call (or put, depending on the spreadsheet) prices are given right below the stock
prices. For example, if the stock price is given in row 21, then the corresponding American
call price appears in row 22 (in blue font), directly below the stock price (given in black
bold). So, in a sense, one gnodeh in this case, is two cells high and contains information
about the price of the underlying asset, and the American call (or put).
The price of the European call (or put) is calculated in column BB, to the right of the binomial
tree. This uses the formula
Price = f = e.rTEp[fT] = e.rTƒ°
i n Binom i n p f
, ( , , ) ,
where i = the number of guph ticks, n = total number of steps in the tree, the payoff fi,n is the
same notation used in Ch. 19, and Binom(i, n, p) is the binomial probability of having i up
ticks out of n steps when the probability of an up tick is equal to p. (In fact, Binom(i, n, p) =
i n i n
i n i
Note that these spreadsheets were also designed so that they could (fairly) easily be made
larger. You should be able to use Copy and Paste in order to make these trees as big as you
like. Note, however that if you do make the tree larger, you need to change the number for n
(in cell L5). The time to maturity, T, and the number of steps, n, are independent of each
Acquaint yourself with the option pricing spreadsheets, Puts&Dvd.xls, Call&Dvd.xls TrinCall.xls
and TrinPut.xls. At the top of each spreadsheet youfll find all the input variables: r = rate =
risk-free rate per annum (continuous compounding), q = dividend yield per annum (also with
continuous compounding), S = current stock price, X = exercise price, s = sigma = volatility
per annum, as well as the Settlement Date (normally the date on which the option was
traded), the Option Expiry Date, and for the two spreadsheets Puts&Dvd.xls and Call&Dvd.xls
we also have Next Dividend Payment Date, the Dividend Payment, and the Number of
Dividends per year. The spreadsheet automatically calculates T = time to expiration (in
years, assuming 365 days in a year). Most of the cells containing input variables are
named. You can see the name of a cell by looking at the box in the upper left-hand corner of
The values used in the Binomial tree, u, d, a, p, Smax, and Smin, are calculated from these input
variables, as are the values, d1, d2, etc., used in the Black-Scholes formula (given in cell F11).
Note that the spreadsheets use the Control Variate Technique to get better estimates of the
American option. This technique is described in Hull Ch. 20, section 20.3 p. 440 (8th Ed.) or
slide 48 of my Ch. 20 lecture notes.
Now letfs look at some of the features of Puts&Dvd.xls and Call&Dvd.xls in more detail.
Row 15 gives the dividend payments, row 16, the discount factor, and row 17 calculates the
present value of all remaining dividends. The counting numbers in row 18, and in column B
are just used to calculate the various prices in the tree.
To make sure that the dividend payment dates are precise, Ifve added some bond functions in
cells R2 . BE2. These bond functions sometimes misbehave when theyfre uploaded onto the
internet, so Ifve removed the gequalh signs. You will need to insert the equal signs yourself,
but before you do that, go to Tools, Add-Ins, and make sure therefs a check mark beside
Analysis ToolPak-VBA. Now, add an gequalh sign to cell R2, then COPY and PASTE that cell to
the end of the row (cell BE2). (These instructions are also given in the spreadsheets. Note
that this only applies to the spreadsheets Puts&Dvd.xls and Call&Dvd.xls.
Look at the formula in cell C19 (the stock price). The IF statement is merely designed to
make it easier to expand the binomial tree by using Copy and Paste. If you copy this equation
to an area that is below the tree, it produces a period, g.h.
Now look at the formula in cell C20 (American option). This cell contains the same IF
statement as in the previous case, but it also contains the MAX statement that involves the
early exercise option.
1. Implied Volatility
For this problem youfll need to use Solver in EXCEL to find a put optionfs implied volatility.
(NOTE: Goal Seek may also work.) Here wefll use Puts&Dvd.xls. Suppose you observe a put
option with price P = $2.00, and suppose that the input variables are: r = 6% (compounded
continuously), q = 0, S = 40, X = 40, Option Expiry Date = settlement + 130, and the stock
pays no dividends, but sigma is unknown. We can use EXCEL to solve for sigma. This is
done using Solver, as we now describe. This approach is also used in question 4 below.
You can ignore the dividend payment if you set Dividend pmt equal to 0. If this is done, the
next dividend payment date and number of dividends per year become irrelevant.
We now want to use Excelfs Solver to find implied volatilities for each option. For a given
option, first enter the input data (stock price, settlement date, maturity date, risk-free rate and
exercise price) into the spreadsheet Puts&Dvd.xls. Next, enter the Market price in cell M11.
Here is a nice trick for finding the implied volatility.
(Step 1) Under Tools, click Solver. (For Office 2007, you go to Data, and then on the top
right-hand side, youfll see Data Analysis, and then Solver. Again, make sure that
Solver has first been gadded inh.)
(Step 2) Under Set Objective, enter the cell containing the American option price, I11 (or
click cell I11 in the spreadsheet). For the European put price use cell F11, the
(Step 3) Next to Equal to, click the circle beside Max.
(Step 4) By Changing Cells: enter the cell containing the value of sigma, E5.
(Step 5) Subject to the constraints: click Add, and enter the equation I11 = M11, (or F11 = M11
for the European put) and click Solve. Cell E5 in your spreadsheet will now contain
the implied volatility for this option.
(a) What is the implied volatility for this option if it is a European put?
(b) Repeat the above procedure assuming that the option is an American option (i.e., for
Set Target Cell, enter the cell containing the American put price). What is the implied
volatility for the American put?
(c) Very briefly (in two or three sentences maximum) explain why we get a lower
implied volatility when we assume that it is an American put.
2. Historical Volatility for the S&P/ASX 200
We want to estimate the S&P/ASX 200fs volatility using past prices. We can use the data in
S&P ASX 200-AXJO.xls to estimate the historical volatility. (Source: yahoo.com.au) Using the
technique described in class, (see slide 14-10, or section 14.4, p. 304, 8th Ed. of the textbook)
youfll first calculate the daily continuously compounded returns, and then the (annualized)
volatility. Use the adjusted close price (column G), which ordinarily adjusts for stock splits
(a) Using the entire series of stock prices, what is your estimate for the (historical)
Next, letfs use this procedure to see how the volatility has changed over time. Suppose that
the daily continuously compounded returns are in column H. Move to the first free column (i,
say) and go to row 52. Calculate the (annualized) volatility using the first 50 daily returns [so,
in my spreadsheet, in cell i52, I would just type =STDEV(H3:H52)*252^0.5]. Now COPY this
formula and PASTE it into the remaining cells in the column. This will give you a time series
of volatilities (where each volatility is calculated based on 50 daily returns.)
(b) Whatfs the maximum volatility over the period? The minimum? The average? (Use
MAX, MIN and AVERAGE in EXCEL.)
(c) Graph the time series of volatilities using GraphWizard. Does volatility seem to be
constant? (Just use your judgment.)
(d) Wefll compare historical volatilities to option implied volatilities in a later question.
3. Interest Rates and SPI Futures
For this question, I have decided to use interest rate futures to find the risk-free rates for our
options. Wefll do the option pricing in the following question, but for now let us calculate
the continuously compounded risk-free rates, and use them to find dividend yields implied by
SPI futures prices.
For the interest rates, wefre using 30 Day Interbank Cash Rate Futures (and one 90 Day Bank
Accepted Bills Futures). The Settlement prices can be found in the Newspaper data. The
newspaper is from 21 August, 2014, which means that all the data is from the previous date.
So, for these problems, the settlement date is 20 Augst, 2014. In case you canft read the
column headings, here they are:
These headings are the same for the 30 Day Interbank Cash Rate Futures (and the 90 Day
Bank Accepted Bills Futures) as well as the SPI 200 futures and options.
Wefll assume that the August contracts give us the 30-day spot rate. Using this rate and the
futures rates we can find the spot rate for each maturity.
ASIDE: For those who are interested, herefs how we use the quoted forward prices to
calculate the risk-free rates needed for our option pricing. First, note that the futures prices
are quoted prices. Suppose the August futures price is 95.00. Then the corresponding
interest rate is 100 . 95.00 = 5%. (These calculations are done in column E.) Letfs call this
interest rate F0,1. The second contract (with maturity in September) gives us the forward rate
that is locked in from date t1 to date t2 = t1 + 30 days, denoted by F 1,2, and so on. Ifm
assuming that the spot rate uses simple interest, so in general, a futures contract that matures
at date tk would deliver a 30-day zero coupon bond that matures 30 days later, at date tk+1, and
this bond would have a locked-in price of
365 k k F +
which does assume simple interest, and for convenience assumes a face value of $1. Let
b(0, tn+1) be the price (today) of a zero coupon bond that pays $1 at date tn+1. It is possible to
0,1 1,2 , 1
1 1 1
30 30 30
1 1 1
365 365 365
F F F
= ~ ~ ~
+ + +
For the June maturity, the last term in this product uses a maturity of 90 days. These
calculations are done in column G. (Note that the final futures contract used matures at date
tn, but the zero coupon bond matures at date tn+1. This is reflected in the difference between
the futures maturity month in column C and the Zero coupon bond maturity month in column
K.) Now, these bonds give us the discount rates we need for pricing options and futures, but
we need to find their yields using continuous compounding. So, if we write the bond price
using continuous compounding, i.e., 1 1 1 (0, ) exp( ) n n n b t r t + + +
= . , then we can solve for rn+1:
1 1 1 ln[ (0, )] / n n n r b t t + + +
= . . These calculations are done in column J.
Enter the futures prices in column D. Use settlement prices for 30 Day Interbank Cash Rate
Futures, but note that the final price is actually for a 90 Day Bank Accepted Bills Futures.
This is needed for the June option maturities.
(a) What are the continuously compounded risk free rates for each maturity? You can
present a table of these values. Include the quoted futures prices in your table (to
make it easier for me to mark). These rates will be used to find futures prices and
option prices below.
As a quick application, letfs find dividend yields for SPI 200 futures. Here we assume that
the August futures price is the spot price, S. We then use the formula F = Se(r . q)T to solve for
the dividend yield, q. Enter the SPI 200 settlement prices for each maturity in the appropriate
cells in column C. These contracts mature on the Friday after the third Thursday of the
maturity month (i.e., the day after the third Thursday). Enter these dates in the appropriate
cells in column D. Using the continuously compounded risk-free rates from the top of the
spreadsheet, enter the correct risk-free rate for each maturity in the appropriate cells in
column F. The dividend yield for each contract is given in column G.
(b) What are the (continuously compounded) dividend yields for each maturity? You
can present a table of these values. Include the futures prices, maturity dates, and
risk-free rates in your table (to make it easier for me to mark).
4. Newspaper Prices
For this question, we are using the Newspaper data pdf file for SPI 200 index options. The
underlying asset for these options is the SPI 200 futures, so these are options on futures, and
they are American options. The maturity date is the same as for the corresponding futures
contract, with the same maturity month as the option. If you canft read the headings in the
pdf file, the headings are the same as in the previous question.
Using the spreadsheet Puts&Dvd.xls, we want to find implied volatilities for some of the
For the risk-free rate, use the continuously compounded risk-free rates calculated in the
We now want to use Excelfs Solver to find implied volatilities for each option. See question
2 above for a description of how to use Solver to find implied volatilities.
Find the option implied volatilities for the six December 14 put options. (What does the 14
(a) Show me your inputs for this problem (i.e., the cells with red font). This is in order
to make it easier for me to mark the question.
(b) Show me a table of strike prices and implied volatilities for these options. Is there
any evidence of a volatility skew or smile?
(c) Graph the implied volatilities as a function of the strike prices. Is the pattern
Now, find the option implied volatilities for the four June 15 put options. (What does the 15
(d) Again, show me your inputs for this problem (i.e., the cells with red font). This is
in order to make it easier for me to mark the question.
(e) Show me a table of strike prices and implied volatilities for these options. Is there
any evidence of a volatility skew or smile?
(f) Graph the implied volatilities as a function of the strike prices. Is the pattern
(g) How do all the option implied volatilities compare to the range of historical
volatilities found in question 2 above? Use your judgement; e.g., compare the
range of historical volatilities to the range of implied volatilities from Question 1
and see if they overlap substantially.
Using Portfolio.xls, assume that r = 6% (compounded continuously), q = 0, S = 26.80, and s =
0.30. Suppose the following options are available on this stock:
Call/Put Time to Maturity (days) Strike Price
Call 30 26.00
Call 30 26.50
Call 60 26.00
Call 60 26.50
Call 60 27.00
Call 90 26.50
Put 30 26.50
Put 30 27.00
Put 60 26.50
Put 90 26.00
There may be space on the spreadsheet for more options. You can ignore those other rows if
you just assume that the number of contracts you hold in those options (column B) is equal to
zero. In column C you enter the type of option; so, e.g., if you type call, the spreadsheet
calculates the value of the call (column G) and each of the Greek letters for the call (columns
For the maturity dates, enter =TODAY + 30, etc in column D. The spreadsheet is designed to
continuously update todayfs date, so you wonft be able to type in actual maturity dates for
this question. Enter the strike prices in column E. Note that the spreadsheet also allows for
different implied volatilities (column F), but at the moment, Ifve set each implied volatility
=SIGMA, so if you enter the volatility into cell D7, you donft need to worry about the other
When using the graphs, if either T1 or T2 are greater than one of your maturity dates, then
that option will not appear in that graph. Also, in general, you may have to click on the
graphfs x-axis to re-set the minimum and maximum values. Note that there are two graphs,
one using a non-smoothed plot and the other (to the right of the first) using a smoothed plot.
The market convention in Australia for equity options is that one option contract is actually
for options on 100 shares, usually. This is shown in column A.
(a) Assume that you buy 1000 of each of the above options contracts (but no shares or
futures). Report the value of this portfolio, and of all the portfolio gGreeksh. Also,
report how the value of the portfolio changes when various inputs change.
(b) How many shares of the underlying asset must you buy or sell if you want the above
portfolio to be (approximately) delta-neutral? (You should round off to the nearest
integer.) With this delta-neutral portfolio, what is the effect of increasing or
decreasing S by 1%? Is it (approximately) symmetrical? Was it symmetrical in the
previous case? If not, why not? What would be the effect of increasing or
decreasing S by 1% if the portfolio delta was still zero but gamma was the negative of
its current value? What is the simplest way to form such a negative gamma
(c) Now assume that you own 50,000 shares of the underlying asset as well as 1000 of
each of the options. We want to use the call option with the largest delta, and the put
option with most negative delta. With these two options use Solver to find out how
many of these options you have to hold (i.e., be long or short) in order to be both
delta and gamma-neutral. [HINT: you can set gamma equal to zero subject to the
constraint that delta equals zero. You do this while changing two cells in column B.
Again, round off to the nearest integer number of options.]
(d) Hand in a copy of the first page of this spreadsheet showing the delta and gammaneutral
portfolio. (This is to make it easier for me to mark.)s
Graphing the payoff of a combination of options.
(e) Assume that you buy 1000 put options with strike price 26.50 and 60 days to
maturity. How many 60-day 27.00 call options must you sell in order to pay for this
put option? (In other words, you want the initial value of this combination of put and
calls to be equal to zero dollars.assuming there are no other assets in this portfolio.)
(f) Explain briefly why a hedger might want this combination of options.
(g) Consider the graph of the value of this combination after T1 = 30 days, and after T2 =
59.99 days. Use either the non-smoothed graph or the smoothed graph (to the right of
the first graph).whichever you prefer. Hand in a copy of your graph.
6. Comparing the Spreadsheets
Consider an American put option with S = 90, X = 100, s = 20%, r = 12%, q = 8% (both with
continuous compounding) and T = 0.25 years. The correct price of the put (based on a
10,000 step binomial tree) is 10.1978.
We havenft discussed the TrinPut.xls or the Analytic.xls spreadsheets in class, but you should be
able to enter the correct inputs in each spreadsheet. If you want more details about trinomial
trees, see the aside in the Ch. 20 lecture notes.
There is one thing that you MUST do in order to use the Analytic.xls spreadsheet. This
spreadsheet requires that you use Solver. First, enter the input variables, as usual (i.e., the
cells with red font). Now, to find the call price, use Solver to set the Call Equation (cell J16)
equal to zero by changing S* (cell A16). To find the put price, use Solver to set the Put
Equation (cell J22) equal to zero by changing S** (cell A22). These instructions are also
given at the bottom of the spreadsheet. The interesting thing about this spreadsheet is that S*
represents the price at which you should exercise the call immediately, and S** is the price at
which you should exercise the put option. This is interesting information. For more on this
analytical approximation, see the Aside at the end of Ch. 20, if youfre curious.
(a) Calculate the price of the American put using three spreadsheets: Puts&Dvd.xls,
TrinPut.xls, and Analytic.xls. What is the put value according to each spreadsheet?
Which spreadsheet comes closest to the true value?
7. Early exercise of the option
(a) Using Puts&Dvd.xls, I want you to write an IF statement that will tell you whether or
not to exercise an American put early for a given stock price at a given step. Write
the statement so that it will produce an gXh if you are to exercise, and an gOh
otherwise. Write the IF statement as if you were applying it to the option price in cell
F26 (i.e., corresponding to the lowest stock price at step 3.)
(b) Now I want you to apply the formula youfve written above. Use the input values from
question 7 above. At the 16th step of the tree (column S), what is the highest of the
stock prices in that column at which you would exercise the put?