Final Project – FIRE 650

Refer to the template spreadsheet provided.  Stock A has an annualized volatility equal to 18% for which you have just written an out-of-the-money 26 week call option.  The risk free rate is 2% per annum and the strike price is $100.  There is another asset called the “index” which has an annualized volatility equal to 15% and whose returns have covariance 0.0216 with Stock A. 

Express Stock A, the Index, the call option and the Forward price in column P in dollars and cents).  Account for the running cost of the hedge in column N and the beta neutral hedge in column S in thousands of dollars.  Express the Hedge, Exercise $, Option Sale and Profit (cells N31-N34) as well as the Forward Profit/Loss in thousands of dollars.

1.       Complete column G which denotes the remaining time to expiration. 

2.      Model the price dynamic for Stock A in column H as Brownian motion.

3.      Model the price dynamic for the Index in column R as Brownian motion.

4.      Calculate Stock A’s beta to the Index in cell U2.

5.      After completing columns I-J, compute the call price in K2 and then fill in the dynamic call prices to expiration (K3-K28).

6.      Estimate the option’s delta in column L and construct the dynamic delta hedge in columns M-O assuming the interest rate is 2% per annum.

7.      In cells N31-N33 link in the cost of the Hedge, the revenue received should the option be exercised and the cash flow from the sale of the call option.  In cell N35, compute the Net Profit from this hedged short position.  (Hint: in cell N32 include a logical operator that can tell whether the option finishes in the money or not:  “=IF(H28>A2,1,0)*100*100”.  Note in cell O32, I have code that “prints” whether the option is OTM or ITM).

8.      Short a forward contract on Stock A and show how the price of the forward changes through time in column P.  Construct and label a graph of the spot and forward prices and identify the “basis” on the graph with an arrow and a text box.

9.      In cell Q28, calculate the profit from the short forward contract in week 26.

10.  Finally, suppose that you are long 100,000 shares of Stock A and want to hedge downside risk using the Index.  Construct the value of the beta neutral hedge in column S.

Your spreadsheet should have highlighted the net Profit from the dynamic covered call strategy, the profit from the short forward and the beta neutral hedge using the Index (all in $000).  

Attachment:- Final project sheet.xlsx