Financial Risk Management using Derivagem software and Excel
Answer this question using the data on daily closing prices provided in the spreadsheet Assignment Data.xlsx for Securities A, B, and C, for the time period from July 2, 2012 to June 30, 2014.1
a) For each security in the data, calculate: (i) the expected daily return; (ii) the annualized volatility (1 mark); and (iii) the correlations between stocks Based on your results: (iv) comment on which combination of two securities you would expect to provide the highest diversification potential.
b) Consider a possible investment comprising Security A and Security B. Assume that no short-selling is allowed. Using Excel: (i) produce a chart showing alternative risk-return combinations from this portfolio; and (ii) interpret your results, in comparison to investing in either of the individual securities. Also calculate the weights for portfolios consisting of these two securities that yield: (iii) the maximum expected return; (iv) the minimum variance; and (v) the maximum Sharpe ratio assuming a daily risk-free rate of 0.014%.
c) Now assume that an investor is interested in combining all three securities into an optimal portfolio. Assume that no short-selling is allowed. Using Excel: (i) construct a spreadsheet to calculate the optimal weights for each of the stocks such that they maximize the expected portfolio return for a given standard deviation of portfolio return; and (ii) create a plot of the efficient frontier for the three risky assets. Also calculate the weights for portfolios consisting of the three securities that yield: (iii) the maximum expected return; (iv) the minimum variance; and (v) the maximum Sharpe ratio assuming a daily risk-free rate of 0.014%.
d) Compare your results in parts (b) and (c) above, with respect to the portfolio providing the maximum expected return, the minimum variance portfolio, and the portfolio with the maximum Sharpe ratio.
1
(2) This question refers to the same price data as Question 1. Assume that on December 31, 2013, when the Security A price was $77.80, a trader sold 150,000 European call options on Security A with strike price K=$80 and expiration date June 30, 2014.
Suppose that the option premium was $500,000. Further assume that the annualized standard deviation of Security A returns is 15%, the annualized risk-free rate is 3% and that Security A does not pay any dividend during the time period from December 31, 2013 to June 30, 2014.
Using the DerivaGem software, apply the Black-Scholes formula to calculate:
(i) the price of the option; (ii) delta; (iii) gamma; (iv) vega; and (v) rho of the option. Use weeks as the time unit with respect to time to exercise, i.e., from December 31, 2013 to June 30, 2014 there are 26 weeks corresponding to 0.5 years), and interpret each result.
Further, using Excel provide graphs and explanation/interpretation showing: (vi) the relationship between the value of the option and the strike price; (vii) the delta of the option as a function of the stock price; (viii) the relationship between the gamma of the option and volatility; (ix) the relationship between vega of the option and the stock price; and (x) the relationship between rho of the option and the stock price. b) (i) Explain what delta neutrality means and how the trader can hedge to make the option portfolio delta neutral, including a numerical example in your answer. Assume that the trader rebalances the portfolio on a fortnightly basis, i.e., on the following dates to preserve delta neutrality: 14/1/2014, 28/1/2014, 11/2/2014, 25/2/2014, 11/3/2014, 25/3/2014, 8/4/2014, 22/4/2014, 6/5/2014, 20/5/2014, 3/6/2014, 17/6/2014. Provide a table that contains for each of the above dates: (ii) the underlying price; (iii) the current delta of the option; (iv) the number of units of underlying purchased/sold (1 mark); (v) the cost of underlying purchased/sold; (vi) the cost of interest assuming an annualized risk-free rate of 3%; and (vii) the cumulative cost of the hedge strategy.
(3) This question refers to the same price data as Question 1. Assume that an investor is interested in monitoring the volatility of Security B, and estimates a GARCH(1,1) as well as an EWMA model for this security using data from July 2, 2012 to June 30, 2014 ('model estimation period').
a) (i) Using Excel, estimate using maximum likelihood a GARCH(1,1) model at a daily frequency for Security B and report its key parameters; (ii) provide a plot of the estimated volatility in the model estimation period based on the estimated GARCH(1,1) model; (iii) report the estimated long-run average volatility; (iv) report the volatility forecasts for all trading days in the month of July, 2014; and (v) provide a plot of the volatility forecasts for the month of July, 2014.
b) Assume that the investor applies two EWMA models with =0.75 and =0.92. For both models, to start the EWMA calculations, set the variance forecast at the end of the first day in the model estimation period equal to the square of the return on that day. Complete the following tasks: (i) a plot of the estimated volatility in the model estimation period for both models; (ii) interpret the differences between the two graphs; (iii) report the volatility forecasts from each EWMA model for July 1, 2014; and (iv) interpret the EWMA forecasts on July 1, 2014 with respect to the GARCH(1,1) forecast.
c) (i) On June 30, 2014, what volatility should be used to price a call option on Security B that expires on September 30, 2014 (count the number of trading days to expiry and assume that there are 252 trading days per year); (ii) Using the DerivaGem software and assuming that the annualized risk-free rate is 3%, calculate the price of the option on June 30, 2014 given a strike price K=$62. Further, calculate the delta, gamma and vega of the option; and (iii) Using DerivaGem also provide a graph of delta, gamma, and vega with respect to the price of the underlying.