Section III Spreadsheet Programing (20 points)

An Executive Stock Option Model

Because of the feature of the freeze-out period, (also known as "vesting") period, employee options are often modeled as "forward stating options".A forward start option with time to maturity T starts at-the-money or proportionally in or out-of-the money after a known elapsed time t in the future.The strike price is set equal to a positive constant a times the asset price S after the known time t.Rubinstein (1990) uses the following formula for the call option:[1]

Consider an employee who receives a call option with forward start three months from today.The options start 10% out-of-the-money, time to maturity is one year form today, the stock price is 60, the risk-free interest rate is 8%, the continuous dividend yield is 4%, and the expected volatility of the stock is 30%. In other words, S = 60,a = 1.1, t = 0.25, T = 1, r = 0.08, b = 0.08-0.04 = 0.04, and s = 0.30.

Build a spreadsheet model to calculate the call price with panels for inputs and panels for the model, similar to the Black-Scholes model example we used in Week 4.

Submit the spreadsheet with interim results, such as d₁, N(d₁) etc., before you reach your final value of the call.

[1] Rubinstein's model is based upon Black-Scholes' option pricing model.Notice, the input a actually is X/S, b is the carrying cost, which is equal to r - d, where r is the risk free rate and d is the dividend yield, assuming the company pays dividends.Beware that t stands for the "vesting" period, T is the maturity.Make sure that your model copies the formula exactly as it shows here.