F303 INTERMEDIATE FINANCE - SPRING 1997 - HOLDEN, MYSKER, WEDIG
PROJECT 3: BLACK-SCHOLES OPTION PRICING AND IMPLIED VOLATILITIES
Overview
Using either Excel or Lotus for Windows, do the following:
- Calculate the cumulative normal and the normal density for values of "d" from -3.0 to 3.0 in increments of 0.1. Then graph both the cumulative normal and the normal density on the same scatter diagram with "d" on the x-axis.
- Calculate the call prices now and put prices now using the respective Black-Scholes formulas for X=10, r=3%, Sigma=60%, T=1, S0 ranging from 0 (actually .0000001) to 20 in increments of 2. Calculate call intrinsic values and put intrinsic values for for X=10 and S0 ranging from 0 to 20 in increments of 2. Graph both the call price now and the call intrinsic value on the same scatter diagram with on the x-axis. Similarly, graph both the put price now and the put intrinsic value on the same scatter diagram with on the x-axis.
- Given data from the February 27^{th} issue of the Wall Street Journal, calculate the implied volatility from S & P 500 index put options (not index call options) for X values of $780, $790, $800, $810, and $820 and for contract maturity dates in March and April (not any other maturity date). Using the Solver, set the difference between the model price and the market price equal to zero by changing the stock standard deviations. Graph the implied volatility on a scatter diagram with the exercise price X on the x-axis.
- Turn in your spreadsheet on an 3 &1/2 inch disc.
- Print out and turn in all of your results. Specifically, hand in the following:
- The table calculated in (1) above with the cumulative normal and the normal density.
- The graph generated in (1) above.
- The table calculated in (2) above with the call price and intrinsic value and the put price and intrinsic value.
- The call graph and put graph generated in (2) above.
- The table calculated in (3) above with the implied volatilities.
- The graph generated in (3) above with the implied volatilities.
Key Dates
Date |
Activity |
3/7 |
Project 3 Drop-in Help Lab from 1:00-5:00 p.m., in BU415, BU417, BU419, and PV151 |
3/13 |
Project 3 is due |
Relevant Spreadsheet Commands
Goal |
Excel Command |
Lotus Command |
1. To fill a selected range with a series: |
Edit Fill Series OK
or grab fill handle and drag |
Range Fill (fill-in dialog box) OK
or Range Fill by Example |
2. To calculate the natural log: |
=LN(A1) |
@LN(A1) |
3. To calculate the cumulative normal: |
=NORMDIST(A1,0,1,TRUE) |
@NORMAL(A1,0,1,0) |
4 To calculate the exponential: |
=EXP(A1) |
@EXP(A1) |
5. To use a solver: |
Tools Solver
(fill-in dialog box) Solve |
Range Analyze Solver
(fill-in dialog box) Solve |