Date: Tuesday, April 10, 2007
Read: Option Valuation and Dividend Payments (UVA-F-1523)
Network File: bscholes.xls (The same file used in Session 7)
Class Objective:
Build a Black-Scholes model in excel and make extensions that account for dividend payments, and understand differences between European and American call options in terms of early exercise.
Assignment:
Your assignment is to use Excel to construct the Black-Scholes Model and then use that to price options.
The basic Black-Scholes model is
Call Option Value =
where
,
P = underlying asset value,
X = exercise price,
t = time to maturity,
sigma = volatility,
and rf = continuously compounded risk-free rate.
In building the model, volatility, sigma, is defined as annual volatility (e.g., 0.25 means 25% per year). Time, t, is defined in terms of years. If there are 122 days to maturity,then t = 122/365 or 0.33425 years. N(d) is the cumulative normal distribution for a value of d. Excel has a function available to calculate this: NORMSDIST(d). Using this will return the cumulative normal distribution value for a particular calculated value of d. Note that in Excel the natural log function is LN(.), and the exponential is EXP(.).
Assignment Questions:
Use the following information to answer the questions.
Assume today’s date is September 1, 2006. The market information on XYZ stock is as follows:
Firm: XYZ
Current stock price = $47.50
Volatility = 0.25
Risk-free rate of interest = 5.0% (continuously compounded)
Expected dividend payments in 2006-2007:
| Dividend | Ex-date | Payable date |
| $0.50 | Dec 1, 2006 | Dec 15, 2006 |
| $0.50 | Mar 1, 2007 | Mar 15, 2007 |
| $0.75 | Jun 1, 2007 | Jun 15, 2007 |
| $1.00 | Sep 1, 2007 | Sep 15, 2007 |
1. Build an Excel-based Black-Scholes model and price a European call option with a maturity of 61 days, November 1, 2006. Using the same inputs, how does the value compare with the value from the BSCHOLES.xls value?
2. Using your Excel model, what is your estimate of the price of a European call option with a maturity date of December 15, 2006 and an exercise price of $45?
3. Using your Excel model, what is the value of a European call option with a maturity date of September 2, 2007 and an exercise price of $45? How does this compare with the known dividend model in BSCHOLES.xls?
4. Using your Excel model, how would you price a European call option with a maturity date of September 2, 2008 and an exercise price of $45?
5. Compare the values of a European call option with a maturity of May 31, 2006 and an exercise price of $45 using a known dividend approach and a constant dividend yield approach. Are the values different? If so, why? Which approach is the better representation of reality?
6. Would an American call option with a maturity of 120 days and an exercise price of $45 ever be exercised early? If so, when and at what price would you choose to exercise early?
7. Based on the technical note discussion (Option Valuation and Dividend Payments), how would you adapt your Excel model to value the option in question 2 if it were an American option? What would you have to do if it the option in question 3 were an American option?