Option Pricing:
Valuing Options and investigating the properties of the Black Scholes model. The answer must be in the form of an Excel spreadsheet, with a supporting explanation to explain the working in the spreadsheet. The spreadsheet needs to show how the formulae work. It has been suggested that in order to make it easier to lay out our answers, we can follow the below layout - however we can ultimately do it however we wish, provided the layout is simple enough to present to the class discussion.
Problem 1. Suppose that a non dividend-paying stock has price of $5.00 and annualised volatility of 20%. The risk free one month interest rate is 3% (so if you deposit $100 for a month, you will receive $100.25 back at the end of the month).
a. Create a 12-step tree showing the evolution of the stock price over the next year.
The hint given in class: the Excel computation is easier if you draw the trees like this, so that up movements are depicted in the spread sheet along the rows:
If you prefer a different approach then this, that is fine, provided it is very clear. So far, there should be one tree on your spreadsheet. Parts b and c each require you use an additional tree to value a derivative.
b. Value a put option on the stock maturing in one year with a strike price of $4.00
c. Value a derivative security which pays out the square of the stock value in one year. (For example, if the terminal value of the stock is 5 then the derivative pays out 25 at maturity, and so on)
Problem 2. Use the Black Scholes formula to value a one year call option on this stock with a strike price of 6. Compute the delta hedge. If you sold this option on 100 shares (i.e. the purchaser would have the right to buy 100 shares at maturity), what exactly would be your hedge?
• You will find the following facts helpful:
• N(.) is NORMSDIST(.) in Excel
• e(raised to power of x) is EXP(X) in Excel
• In(S/X) is the LN(S/X) in Excel
• You can take a square root in Excel using the function SQRT(.)