I received the following as the result of a query to Google for a spreadsheet model to do accounting calculations related to SFAS 123 option valuation. The formula delineated for cell H1, i.e., ((@1n(B1/B2)+(b4-B5)*B3)(b6*b3^.5))+.5*b6*b3^.5, uses "@" which I am unfamiliar with.

Can anyone help me decipter this foumula? The entire download for the complete spreadsheet model follows:

[COLOR=Blue]VALUING STOCK OPTIONS: A REVISED SPREADSHEET TEMPLATE

By Thomas E. Wilson, Jr., Suzanne Pinac Ward, Dan R. Ward, and

Larry D. Guin

The FASB has issued SFAS No. 123, Accounting for Stock-Based Compensation, requiring disclosure of the value of stock options granted to employees in exchange for services. As in the exposure draft, the FASB employs the Black-Scholes option pricing model in their illustration for option pricing. However, the model employed by FASB in the statement is a refined version of the one used in the exposure draft. The following spreadsheet program allows accountants to construct an option pricing template that values options consistent with SFAS No. 123. The template differs from an earlier version (The CPA Journal, March 1995) in its computation of the effect of dividends on option values. A more precise approximation of the normal distribution is also employed.

What You Need

As before, six variables are needed to compute the value of an option. They are the--

Stock Price--the price of the firm's stock at the time the option is granted;

Exercise Price--the price at which optionholders may purchase shares of the firm's stock;

Term (in years)--the period between the date the options are granted and their expiration date;

Risk-Free Rate--the risk-free rate of interest;

Dividend Yield--expected dividends as a percentage of the stock price over the option term;

Variability--the expected volatility of the stock price over the option term.

Building the Model

The option-pricing program can be set up in rows and columns in the spreadsheet as follows:

A B

1 Stock Price 50

2 Exercise Price 50

3 Term (in years) 6

4 Risk-free Rate .075

5 Dividend Yield .025

6 Variability .3

The data in column B are taken from the first option pricing illustration in SFAS No. 123. Using these inputs as the spreadsheet is constructed will provide a way to check that the equations have been entered properly. Next, in another area of the spreadsheet, enter the following:

H I

1((@1n(B1/B2) +H1- B6*

+(B4-B5)*B3) B3^.5

(B6*B3^.5))+

.5*B6*B3^.5

Cells H1 and I1 should have values of .7756 and .0408 respectively. Those familiar with the Black-Scholes model will recognize these cells as the intermediate values labeled d1 and d2 in many versions of the model. The next step is to determine the normal distribution values for d1 and d2. The following formulas provide a close approximation of the normal distribution values. First enter the following formulas in the cells as shown:

H

3 1/((2*@pi)^.5*@exp((H1^2)/2))

4 @if(H1>0,1/(1+.33267*H1),1/(1-.33267*H1))

5 @if(H1>0,1-(H3*(.4361836*H4-

.1201676*H4^2+.937298*H4^3)),H3*(.4361836*H4-

.1201676*H4^2+.937298*H4^3))

Now, copy the formulas located in cells H3 through H5 into cells I3 through I5. These cells should now contain the following numbers: H3=.2952; H4=.7948; H5=.7810; I3=.3986; I4=.9866; I5=.5162.

The formula generating the option value can be placed in the area of the spreadsheet where the initial inputs are located. This new section should be formatted to display numbers in the currency format. You may also wish to modify the formula to round up to two decimal places.

A B

9 Option Value (@exp(B5*B3*-1)*B1*H5)

(B2*@exp(B4*B3*-1)*I5)

The option value should be $17.15, which is the result in the illustration in SFAS No. 123. *