options and futures project

1. Write a spreadsheet to analyze an individual investor’s decision to buy 1000 shares of a common stock on margin, and protect the downside with 10 puts. Your spreadsheet should compute a random path for the daily prices of the common stock, and the random path should cover a wide range of possible price fluctuations. The time period to be covered is 90 days. Also, if the stock prices goes up, your spreadsheet should have the investor sell the first 10 puts and buy 10 more puts with a higher exercise price to protect the gain that has been achieved. The new puts would have a shorter time to expiration because the time period of the experiment covers only 90 days. To see how there would be a trigger point for selling the first set of puts nd buying a new set with a higher exercise price, assume that the beginning price of the common stock is $40 and the exercise price of the 10 puts is $35. If the stock price rises to $42.50, your spreadsheet should have the investor sell the puts with exercise price of $35 and buy 10 puts with exercise price of $40. The output of your spreadsheet should include a trajectory for the dollar value of the investor’s position.

There is an Excel add-in that computes the value of a put using the Black-Scholes formula. Use that. Do not do a lot of coding to value the Black-Scholes formula. There are dozens of add-ins that will do that computation. Instead, work on the “if” statements and the random upward trajectory of the daily stock price.