Reference no: EM131319026

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.