You are prohibited from discussing this assignment with other students. Construct an Excel spreadsheet that determines the following values:
1. Black-Scholes-Merton model call option without dividends.
2. Binomial model European call option without dividends.
3. Binomial model American call option without dividends.
4. Black-Scholes-Merton model call option with dividends.
5. Binomial model European call option with dividends.
6. Binomial model American call option with dividends.
7. Black-Scholes-Merton model put option without dividends.
8. Binomial model European put option without dividends.
9. Binomial model American put option without dividends.
10. Black-Scholes-Merton model put option with dividends.
11. Binomial model European put option with dividends.
12. Binomial model American put option with dividends.
The spreadsheet should have a title tab that includes: (1) the purpose; (2) the author; and (3) any references. An example is as follows:
|
Purpose:
|
Determine the value of European and American call and put options using the Black-Scholes-Merton model and a multi-lattice model.
|
|
Author:
|
Ty Taylor, CFA; [email protected]
|
|
References:
|
1. Fundamentals of Futures and Options Markets, 8th Edition, John C. Hull.
|
|
|
2. Valuing Employee Stock Options, Johnathan Mun.
|
|
|
3. Real Options Analysis, 2nd Edition, Johnathan Mun.
|
The spreadsheet should have an assumptions tab to which all calculations are tied. If a user of the spreadsheet wants to change an assumption, they should be able to so by going to one place. I will test each student's spreadsheet to make sure this works correctly. Use the following assumptions:
|
Assumptions
|
|
|
Valuation Date
|
01/11/16
|
|
Option Expiration Date
|
01/11/18
|
|
Risk-Free Rate of Return, r
|
2.0000%
|
|
Stock Price T0
|
$100.0000
|
|
Stock Dividend Yield, g
|
3.0000%
|
|
o of the Stock Price, Annual
|
50.0%
|
|
Call Exercise Price, KC
|
$105.0000
|
|
Put Exercise Price, KP
|
$110.0000
|
There should be a section in which "intermediate calculations" are shown. This may by on the same tab as the assumptions. An example follows. Your intermediate calculations should be equal to these calculations:
|
Intermediate Calculations
Option Life in Years, T
|
2.000000
|
= (Expiration Date - Valuation Date)ƒ365.5
|
|
Time Step, Annual, 6t
|
0.038462
|
= Option Life in Years ƒNumber of Lattice Steps
|
|
Stock Price Up Move, u
|
1.103027
|
= Exp(1)^[o(6t^0.5)]
|
|
Stock Price Down Move, d
|
0.906596
|
= 1ƒu
|
|
Risk-Neutral Up Probability, 0.479423 = {[Exp(1)^(r6t)] - d} ƒ (u - d) Without Dividends, p
Risk-Neutral Down Probability, 0.520577 = 1 - p Without Dividends, (1-p)
Risk-Neutral Up Probability, With
0.473547 = {[Exp(1)^((r-g)6t)] - d} ƒ (u - d)
Dividends, p
Risk-Neutral Down Probability,
0.526453 = 1 - p
With Dividends, (1-p)
|
You will develop four Black-Scholes-Merton (BSM) values, and you should have four BSM templates.