Simple Options Valuation Spreadsheet Program

James N. Bodurtha, Jr.
School of Business
Georgetown University
Washington, DC 20057
U.S.A.

Copyright 1987, 1988
Financial Labs, Inc.
All Rights Reserved

Click for OPTSIMPL.WK1 spreadsheet or OPTSIMPL.XLS spreadsheet,
and save to open in spreadsheet.

Contents

A) Calculating Option Prices

B) Valuing American Options on Futures

C) Implying an Option Volatility

D) Interest Rate Input Warning

E) Discrete Changes in Current Yield (e.g. Stock Dividends)

F) Worksheet Protection

G) OPTSIMPL Inputs by Underlying

Disclaimer

This program is based on the original binomial option pricing work of Cox, Ross and Rubinstein (1979), as extended by Cox and Rubinstein (1985), and Bodurtha and Courtadon (1987) for the currency option application. The program is for learning purposes. These programs are under development, and license to use and/or copy them must be obtained explicitly. In either case, proper attribution must be made to the source of the program and its logic. Furthermore, possession of these valuation programs in no way confers the right to use Lotus 123. Users also must obtain the right to use Lotus 123 (Lotus Development Corp., copyright 1985) on their own. Finally, users of these programs must be aware that they are subject to error. Under no circumstances will any party involved in their development or dissemination be liable for any special, consequential or incidental damages arising from their use.

Simple Options Valuation Spreadsheet Program

This documentation provides a description of the required inputs to the programs, and instructions on how to access the file to calculate option prices. A few other points of interest are discussed. Of these points, the interest rate warning and discrete current yield sections should be reviewed.

A) Calculating Option Prices

1. Required Data

Spot Price: Underlying asset current price.

Current Yield: Yield paid on a position on the underlying asset matching the option's maturity (may be calculated with Lotus @Date function).

Cost of Funds: The cost of funds for the number of days to maturity (a zero coupon rate).

Option Type: 1 for a call, and -1 for a put.

Exercise Price: Price at which the underlying can be bought (for calls) or sold (for puts) on or through expiration (in the same units as current price).

Days to Maturity: Number of days between current date and option expiration.

Annual Volatility: Volatility of the underlying asset price scaled to an annual (365 day) basis.

2. Insert option program disk in the floppy drive. Alternatively, copy the program from the network to a floppy disk.

3. To obtain a fresh spreadsheet, load Lotus 123 or other spreadsheet.

4. Retrieve the file OPTSIMPL.WK1. The 123 command is

/F[ile]R[etrieve]OPTSIMPL~

(The ~ symbol indicates to strike enter.)

5. The following screen will appear.

******************************************************************************

STRIKE [ENTER] TO BEGIN.

SIMPLE OPTIONS VALUATION PROGRAM FOR LOTUS 123

COPYRIGHT 1987

FINANCIAL LABS, INC.

ALL RIGHTS RESERVED

VERSION 1.23

THIS APPLICATION IS BASED ON THE ORIGINAL BINOMIAL OPTION PRICING WORK OF COX, ROSS AND RUBINSTEIN (1979), AS EXTENDED BY COX AND RUBINSTEIN (1985), AND BODURTHA AND COURTADON (1987) FOR THE CURRENCY OPTION APPLICATION. USERS OF THESE LOTUS 123 SPREADSHEET PROGRAMS MUST BE IN COMPLIANCE WITH THE TERMS OF THE PROGRAM DOCUMENTATION.

STRIKE [ENTER] TO BEGIN.

******************************************************************************

6. Strike enter.

7. The following option spreadsheet calculator appears.

8. The input cells are highlighted in column C, rows 151 to 157.

9. Follow directions at the bottom of the screen to calculate.

10. Note: each option price should take about ten seconds to compute. A 30 step binomial approximation is used. An adjustment is made for high current yield assets, if necessary.

11. To leave Lotus directly give the command: /QY.

B) Valuing Futures Contract Options

The model will handle these option contracts with no difficulty. However, there are two changes in the required inputs. These are

1. The current futures price replaces the spot price.

2. The yield on the underlying asset should be set equal to the cost of funds.

The relationship between spot prices, current yields, the cost of funds and the futures price makes this adjustment appropriate.

C) Implying an Option Volatility

An easy way to determine implied volatility is simply to note current market option input parameter and price levels, and then try different levels of volatility to match up the model price with the current market price. A useful trick is to use two specific initial guesses: one volatility too high and one too low. Then, a relatively close approximation to the actual implied volatility can be linearly interpolated from these two volatilities, the two related option values and the option value for which the implied volatility is sought. This approach will be quicker than simple iterative search schemes because the American model takes a fair amount of time to run in Lotus.

The following MS Excel spreadsheet and macro workbook is also available to calculate implied volatilities. The input and output structure of this spreadsheet follows the structure of OPTSIMPL.WK1 and OPTSIMPL.XLS.

Click for optimpvl.xls spreadsheet,   and save to open in spreadsheet.

D) Interest Rate Input Warning

This model makes no adjustment of the current yield and cost of fund inputs. Such an adjustment should be made which is consistent with the conventions in the underlying instrument, e.g. 360 day bank year. The option models require that continuously compounded rates be input into the model. To make an adjustment from quoted market rates to continuously compounded rates, a formula can be entered in the cells B9 (labeled RF, the current yield) and B10 (labeled RD, the cost of funds) as appropriate. These adjustments will become of predominant importance as the option maturity is lengthened.

E) Discrete Changes in Current Yield (e.g. Stock Dividends)

The model is built under the assumption that cash paid to owners of the underlying, such as dividends and interest, are paid continuously at a constant rate over the life of the option. This assumption is relatively accurate for valuing puts generally, and calls on bonds, commodities, currencies and stock index portfolios. When current yield (dividend) is not paid daily at a roughly constant rate, the model may be off. The American model will almost certainly misvalue stock call options when the stock is about to pay a large dividend. In this case, the owner of an American call option may well want to exercise their call prior to the dividend, because they know that the stock price will fall by roughly the amount of the dividend on the stock ex-dividend date.

To use our model to treat the case when one dividend will be paid over the life of the option, we suggest valuing two related European options. These European options have different expiration dates, and slightly different inputs. The expiration date for the first European option is the dividend record date and the expiration date for the second option is the actual expiration date. The American option value on the dividend paying stock can be approximated as the maximum of these option values.

The inputs for these two "European options" differ. The inputs for the first expiration date option should be the same as those in our original example, except that the maturity is shorter and

Current yield set to zero (no dividend is actually paid in the period, the option holder gets full right to the share proceeds)

Cost of funds should correspond to the period to the record date, not the option maturity

The second or true expiration date call will have inputs equivalent to those in our original example, except that the stock price must be decreased by the amount of the dividend. This adjustment is made to account for the fact that holding the option to maturity means that the call owner did not own the stock on the dividend record date and has no claim to the dividend.

The value of the American option on a dividend paying stock is then approximated by the maximum of the value of these two European options. This approximation was suggested by Fischer Black, and works relatively well. The approach can also be extended to handle more than one dividend, though the approximation is usually less satisfactory in this case.

F) Worksheet Protection

The worksheet is protected, and only the highlighted cells may be changed. To tailor the spreadsheet to meet your needs, you will want to disable the protection. Protection is turned off by the following command:

/W[orksheet]G[lobal]P[rotection]D[isable]~

Protection is turned back on by the following command:

/W[orksheet]G[lobal]P[rotection]E[nable]~

G) OPTSIMPL Inputs by Underlying
(Wall Street Journal and Other Information [all are $ pricing])