Unfortunately, pricing an option using the Black-Scholes model requires an accountant to solve a series of complex equations or acquire sophisticated financial software. Accountants, however, can create their own spreadsheet template to greatly simplify option-pricing computations. Use of a template also allows the accountant to manipulate option variables to determine their projected effects on an option's price, and, in turn, the effect on the income statement or footnote disclosures.

Here is a simple and cost-effective approach to using a firm's existing spreadsheet program to construct a template to value an option using the Black-Scholes Option-Pricing Model modified for dividend payments. Once a template is constructed, an accountant need only input six variables to determine the value of the option.

What You Need

Most of the inputs needed to determine the price of an option are easily obtained, Options granted to employees specify at what price they can purchase shares of the firm's stock. This amount is known as the exercise price. Options generally have a stated expiration date; the period between this date and the date the options are granted is defined as the option's term. You must also know the price of the firm's stock at the time the option is granted, the stock price. If the firm pays dividends on a regular basis, an expected dividend yield over the term of the option must be estimated. The option-pricing formula also requires you to determine a risk-free rate of interest. The simplest way to obtain this rate is to find out the current yield on Treasury bills or other government securities with a maturity date close to that of the option. The final and the most difficult piece of the puzzle is the expected volatility of the stock price over the term of the option. The best source for determining the expected volatility of a stock price is to determine the annualized standard deviation of the stock price over some prior period of time. Your own professional judgement will have to determine the appropriate length of this prior period; the exposure draft recommended only that more than a few months of data be used for long-term options. Most spreadsheets have a function to compute standard deviations (

The option-pricing program can be set up as follows:

AB

1StockPrice

2ExercisePrice

3Term(inyears)

4Risk-FreeRate

5DividendYield

6Variability

Writing formulas for this template will be simplified by assigning names to these inputs using your spreadsheet's range-naming feature. All equations are presented for a Lotus 1-2-3 spreadsheet. You may have to make some minor changes if you use different software. The following names are used:

VariableCellRangeName

StockPriceB1SPRICE

ExercisePriceB2XPRICE

TermB3TERM

Risk-FreeRateB4RATE

DividendYieldB5YIELD

VariabilityB6VAR

Building the Spreadsheet

Central to valuing an option using Black-Scholes is computing the value of two intermediate variables, d1 and d2. To compute these variables, first assign range names as follows:

VariableCellRangeName

d1E1DONE

d2E2DTWO

Then construct the equations to compute d1 and d2:

DE

1d1((

RATE*TERM)/

(VAR*TERM.5))+.5

*VAR*TERM.5)

2d2+DONE-VAR*TERM.5

The most cumbersome aspect of the spreadsheet is determining the normal distribution values for d.sub.1 and d.sub.2. Perhaps the most efficient approach is to employ a formula providing a close approximation of the normal distribution values. First enter the following formulas in the cells as shown:

E

13+DONE

14

15+1/(1+.2316419*E14)

16+.3989423*

17+1-E16*E15*((((1.33274*E15-

1.8212556)*E15+1.781478)*E15-

.3565638)*E15+.3193)

18(Leavethiscellblank)

19

Copy the formulas located in cells E13 through E19 into cells F13 through F19. In cell F13, replace the formula with +DTWO. Cells E19 and F19 now contain the normal distribution values of d1 and d2. Assign them the following range names:

CellRangeName

E19ND1

F19ND2

The final intermediate step involves computing the present value of the expected dividends over the term of the option.

This can be accomplished by constructing a table in an out of the way part of the spreadsheet. The following table will serve for options with terms of 10 years or less.

XY

11

22

33

44

55

66

77

88

99

1010

11total

Remember to place a $ in front of all range names if you copy formulas from one cell to another, rather than type each equation individually. Assign this total dividend value (cell Y11) the range name DIVTOTAL.

Putting It All Together

All that remains is to construct a formula to put the pieces of the puzzle together. Return to the area of the spread-sheet where the initial inputs are located, and below them format an area to display numbers in the currency format. Then-

AB

9Optionwithout(SPRICE*ND1)-

dividends(XPRICE*

-1)*ND2)

10EffectofDividends+DIVTOTAL

11TotalOptionValue+B9-DIVTOTAL

Testing the Model

A misplaced comma or an omitted operator can create serious errors in your spreadsheet. To test the program, enter the following data, taken from the FASB exposure draft, into the spreadsheet:

AB

1StockPrice$50

2ExercisePrice$50

3Term(inyears)6

4Risk-FreeRate6.5%

5DividendYield1.5%

6Variability.30

Note that the cells have been formatted so that dollar and percentage signs are displayed. This is a display feature only; the equations presented here require that interest rates be entered as 0.065 and 0.015.

Once the variables have been entered, the result should resemble:

AB

9OptionWithoutDividends$21.66

10EffectofDividends$3.63

11TotalOptionValue$18.03

Other values are: d1=.8981; d2=.1632; N(d1)=.8152; and N(dz)=.5640. If your results mirror those above, the equations have been transcribed properly. You may wish to invoke your spreadsheet's cell protection feature so that the equations cannot be inadvertently changed or erased. Also, remember to store a back-up file on a well-labeled floppy disk.

Thomas E. Wilson, Jr., PhD, CPA, Suzanne Pinac Ward, PhD, CPA, and Dan R. Ward, DBA, are associated with The University of Southwestern Louisiana. Larry D. Guin, is with Murray State University.

Editor's Note: If the FASB adopts a method for valuing stock options that would permit the use of a version of the Black-Scholes Option- Pricing Model, the authors have agreed, if feasible, to revise their spreadsheet accordingly. Our present plans are to make this revised version available for downloading to our readers on our computer bulletin board service that will he available in the spring. Its availability and instructions on downloading will be contained in the appropriate issue of The CPA Journal.