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 model and a multi-lattice model.
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 To $100.0000
Stock Dividend Yield, g 3.0000%
O of the Stock Price, Annual 50.0%
Call Exercise Price, IQ $105.0000
Put Exercise Price, Kp 5110.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:
You will develop four Black-Scholes-merton (BSM) values, and you should have four BSM templates. use the layout that follows. The assumptions for this tab should tie to the main assumptions tab. You should obtain the following results:
The lattice should have 52 period steps. Including the time zero point, there will be 53 time points. There will be 11 lattices. The examples that follow show time zero and the first six time steps for each lattice. You should obtain the following results:
There should be a rsult tab that summaries all of the results. you should obtain the following results:
