From the database “Share data FINAL.xlsm” choose one company (ASTRAZENECA)

Using the Black Scholes model in the OptAll.xls spreadsheet, and the chosen company, carry out the following:

a) Calculate the volatility of the share price as the standard deviation of the percentage changes over the year 2016.

b) Prepare a table of 1-year call and put option quotes taking the first entry in 2017 (02/01/17) and strike prices that are: at the money (the spot) and 2%, 4% and 10% above and below the spot. Take the risk-free rate as 1%.

c) Prepare a contingency table for an at the money call option using the quote in part (b) with possible maturity prices of spot and 3% and 7% above and below the spot.

d) Construct a collar for a client who wishes to purchase the share on 02/01/17 and have protection from a 4% decrease in the price of the share over 2017.