Introduction

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

tree

S

Su

Sd

2

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ƒ°

=

~

n

i

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) =

!

(1 )

!( )!

i n i n

p p

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

other.

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

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

S Su

Sd

c cu

cd

3

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

Black-Scholes price.

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

4

(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

and dividends.

(a) Using the entire series of stock prices, what is your estimate for the (historical)

volatility?

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:

SETTLEMENT

Ex

Price

Prev.

Price

Opening

Trade

High

Low

Price

Change

$Value

of

Chng

Volume

Prev.

O/P

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.

5

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

, 1

1

30

1

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

show that

1

0,1 1,2 , 1

1 1 1

(0, )

30 30 30

1 1 1

365 365 365

n

n n

b t

F F F

+

+

= ~ ~ ~

+ + +

c .

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

6

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

options listed.

For the risk-free rate, use the continuously compounded risk-free rates calculated in the

previous question.

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

stand for?)

(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

interesting?

Now, find the option implied volatilities for the four June 15 put options. (What does the 15

stand for?)

(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

interesting?

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

7

5. Portfolios

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

H-L).

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

implied volatilities.

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

portfolio?

(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

8

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?