Magazine article The CPA Journal

Valuing Stock Options: A Cost-Effective Spreadsheet Template

Magazine article The CPA Journal

Valuing Stock Options: A Cost-Effective Spreadsheet Template

Article excerpt

The December 1994 decision by the FASB to drop the requirement for an expense charge for options does not eliminate the problem of option valuation. The focus will now be on the notes to the financial statements rather than the income statement. Prior to this decision, the Board was considering a number of alternatives for valuation. One possibility appeared to be to give a haircut to the results of the methods included in the exposure draft. This haircut would allow for factors such as nonmarketability and waiting periods inherent in employee stock options that are not included in models used in the exposure draft. If the Board goes this route, there is still value in knowing how to compute a stock option value using the Black-Scholes Option-Pricing Model as illustrated in the FASB's exposure draft.

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 ((at)std).

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

(Information omitted)

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:

(Table omitted)

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:

(Information omitted)

Then construct the equations to compute d1 and d2:

(Equations omitted)

The most cumbersome aspect of the spreadsheet is determining the normal distribution values for d sub 1 and d sub 2 . …

Search by... Author
Show... All Results Primary Sources Peer-reviewed


An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.