Option Pricing

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.


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).

Creat 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.

Value a put option on the stock maturing in one year with a strike price
of $4.00

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)

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(.)