Hi,

Would you by chance have the excel answers and workings for the following questions?

Record your answers in the same worksheet. The assignment will be automarked so you must

record your answers in the designated cells of the worksheet. Failure to comply with this

requirement may result in your answers not being picked up meaning a zero mark.

Direct all queries to Hedging Assignment Forum in Moodle.

Marked to total of 10, which accounts for 10% of overall assessment for the unit.

To submit your assignment, go to the unit's site in Moodle, click on "Hedging Assignment"

under "Assessments and Exams" and follow the instruction to upload your Excel file (and

nothing else). Do not change the name of the Excel file.

1 mark deducted per day for late submission without prior consent from myself

You should be able to finish this assignment after lecture 9.

Your portfolio:

A stock is currently trading at 55. You hold a portfolio of the following instruments:

Long 200 shares of stock

Long 200 puts with a strike of 50 and maturity of three months (T=13/52)

Short 200 calls with a strike of 60 and maturity of three months (T=13/52)

All of the options are European options and each option is on 1 share.

This portfolio information and information on interest rate and dividend are contained in the attached

Excel file (rows 14).

Prices of various options (including the ones held in your portfolio) are listed in the Excel file (see rows

612).

Requirements:

a. Based on the option prices, compute their implied volatility using the BlackScholes model.

Record your answers (in Sheet1) in range D7:D12 (6x0.25=1.5 marks).

b. Based on the computed volatilities and information provided, compute delta, gamma and

vega of the 6 options. Record your answers in range E7:G12. (18x0.25=4.5 marks).

c. What is the objective of the strategy employed in your portfolio? Write the answer (A, B, C or

D) in cell B14 (0.25 mark).

A. Income

B. Insurance

C. Long volatility

D. Short volatility

d. Compute the portfolio's value and write the answer in cell B15 (0.25 mark).

e. Compute the portfolio's delta and write the answer in cell B16 (0.25 mark).

f. Compute the portfolio's gamma and write the answer in cell B17 (0.25 mark).

g. Compute the portfolio's vega and write the answer in cell B18 (0.25 mark). Note: you may see

an alternate formula that expresses vega as option price change for 1 percentage point

increase in volatility. Do not use that formula, use the one in the lecture note instead.

For the following parts, treat them as independent from one another. That is, all of them pertain to

the original portfolio at the original prices.

h. Use delta and gamma to approximate the portfolio's value if the stock price suddenly

increases by $3. Write the answer in cell B19 (0.25 mark).

i. What is the additional share position in order to make the portfolio delta neutral? E.g. 10

means short 10 shares, +15 means long 15 shares. Write the answer in cell B20 (0.5 mark).

j. What are the positions in the stock and 55strike call in order to make the portfolio both delta

and gamma neutral? Write the answers in cells B22 and B23 respectively (2x0.25=0.5 mark).

k. What is the net cash flow of achieving delta and gamma neutrality for the portfolio? Write the

answer in cell B24 (0.25 mark).

l. What are the positions in the stock and 55strike put in order to make the portfolio both delta

and gamma neutral? Write the answers in cells B26 and B27 respectively (2x0.25=0.5 mark).

m. What is the net cash flow of achieving delta and gamma neutrality for the portfolio using the

strategy in (l)? Write the answer in cell B28 (0.25 mark).

n. Compute the delta of a bull spread using calls with strikes of 55 and 60. Write the answer in

cell B29 (0.25 mark).

o. Compute the gamma of a butterfly spread using calls with strikes of 50, 55 and 60. Write the

answer in cell B30 (0.25 mark).

Note:

After opening the Excel file for the first time, widen column A of sheet "Assignment" to

completely see the questions.

Use 4 decimal places for delta, gamma, vega, volatility.

Use 2 decimal places for portfolio values.

Round the number of shares and options to the nearest 1.

Use " " for short positions. Example: 10 means short 10 shares/options. 10 means long 10

shares/options.

Make sure your answer is worksheet "Assignment" in the file. Do not use this worksheet for

anything else apart from recording the answer. Do not insert rows or columns in this

worksheet. If you want to perform calculations, use other worksheets.

Make sure your answers to calculation questions are numeric, not text.

Putcall parity suggests the implied volatilities for a European call and a European put on the

same asset, with the same maturity and the strike price, should be the same. Since the option

prices provided are expressed with only 2 decimal points, you may get very slightly different

implied volatilities for the call and the put. That difference is normal.