How to "excel" at options valuation: build a flexible, spreadsheet-based lattice model for better calculations.

The guidance from FASB is clear: Companies must determine and report the fair value of stock options they use to compensate employees. But because employee stock options can't be traded publicly, their fair value is not readily available and must be estimated using option-pricing models. FASB Statement no. 123(R), Share-Based Payment (www.fasb.org/pdf/ fas123(R).pdf), allows entities to use any valuation model that is based on established principles of financial economic theory and reflects all substantive characteristics of the options. Both the Black-Scholes-Merton and lattice models meet these criteria. The former's relative simplicity makes it popular with smaller companies--but it may not be adequate for public companies whose employees often exercise their options early. That calls for calculations a lattice model can better accommodate. (For more information, see "Compare and Contrast," page 58.)

Neil J. Beaton, CPA/ABV, partner in charge of valuation services at Grant Thornton LLP in Seattle, said his firm has performed numerous engagements related to FASB Statement no. 123(R) and found a lattice model to be substantially more flexible than a Black-Scholes model, especially with respect to restricted employee stock option nuances such as vesting, early exercise and blackout periods. "Once we built our initial lattice model," he said, "conforming it to the widely varying requirements of our diverse client base was fairly easy and has produced results more accurate than would have been possible with a Black-Scholes model alone."

But even if employers know which valuation model works better for them, they still may have doubts about how to build it. An earlier JofA article (see "No Longer an 'Option,'" JofA, Apr. 05, page 63 or www. aicpa.org/pubs/jofa/apr2005/eaton.htm) explained the workings of the Black-Scholes-Merton model. This month's article provides detailed instructions for building a lattice model by making the necessary calculations in Excel.

One company that chose to implement such a model is the Marysville, Ohio-based Scotts Co., a manufacturer of horticultural products. Its CFO, Chris Nagel, CPA, told the JofA in the April article on Black-Scholes that he preferred the lattice model because of its exceptional ability to capture assumptions about options' term and volatility.

"We had adopted Black-Scholes but now believe a lattice model is appropriate for valuing options," Nagel said. "To value options, you have to make assumptions about the likely term and volatility, and I think a lattice model captures those variables better."

Because the lattice model makes it easy to vary assumptions and inputs over time, entities that grant a great many stock options to their employees will prefer its flexibility to the relatively rigid restrictions of the Black-Scholes-Merton model, which is more suitable for companies whose employee compensation includes few stock options. A lattice model can be complex for a company to implement, though. "Luckily, I'm not the one who has to grind through the numbers," Nagel said.

But what if, in your company, you are the CPA who performs that function? If that's the case, follow the examples below that illustrate the structure and functions of a lattice model.

THE BASICS

A lattice model assumes the price of stock underlying an option follows a binomial distribution, a type of probability distribution in which the underlying event has only one of two possible outcomes. For example, with respect to a share of stock, the price can go up or down. Starting at a point we'll call time period zero, the assumption of either upward or downward movements over a number of successive periods creates a distribution of possible stock prices. This distribution of prices is referred to as a lattice, or tree, because of the pattern of lines used to graphically illustrate it. The lattice model uses this distribution of prices to compute the fair value of the option.

Exhibit 1 (below) illustrates an Excel stock-price tree based on the following assumptions:

* Current stock price of $30.

* Risk-free interest rate of 3%.

* Expected dividend yield of 0%.

* Stock-price volatility of 30%.

* Option exercise price of $30.

* Option term of four years.

[ILLUSTRATION OMITTED]

At the grant date, year 0, the stock price is $30 (cell B7). The model assumes that stock prices will increase at the risk-free interest rate (B15) minus the expected dividend yield (B16), then plus or minus the price volatility (B12) assumed for the stock. Thus, during year 1, the stock price increases by the risk-free rate, 3%; is unaffected by the assumed 0% expected dividend yield; and then either increases or decreases by 30% due to the expected volatility.

The formula for cell E12, the year 1 upward path, is =D21*(1+B15-B16)*(1+B12). For the downward path, the formula for E29 is =D21*(1+B15-B16)* (1-B12). The resulting two possible outcomes for the stock price at the end of year 1 are an increase to $40.17 (E12) or a decrease to $21.63 (E29). In lattice terminology these two possibilities are referred to as nodes. Two similar possibilities for the end of year 2 emanate from each of the year 1 nodes. With the number of nodes doubling in each successive time period, the tree grows to 16 nodes after four years.

Exhibit 1 also contains the probabilities for each node on the tree. For example, at the end of year 2, the stock price of $53.79 (F8) has a probability of 0.25 (F9), which is the probability of two successive upward price movements. With a probability of 0.50 that the price will increase in any year, the probability of two successive upward movements is 0.25 (F9). In fact, two nodes reflect a stock price of $28.96 at the end of year 2 (F16 and F25). F16 represents the result of an upward movement in price in year 1 followed by a downward movement in year 2; F25 reflects a downward price movement in year 1 followed by an upward movement in year 2. Similar to the probability of two successive periods of upward price movement, the probabilities for F17 and F26 are 0.25.

The probability (that is, 0.0625) for each terminal node (column H) corresponds to four successive movements in the stock price.

CARRY ON CRUNCHING

After developing a stock-price tree, the next step is to calculate the intrinsic value of the option at each terminal node by subtracting the option's exercise price (B8) from the stock price at that node. If the stock price at the option's expiration date exceeds the exercise price, the option is said to have intrinsic value and the options are assumed to be exercised. Otherwise, the option has no intrinsic value.

Exhibit 2, below, presents an Excel template that calculates the option's fair value. Columns J through M are added to exhibit l's stock price tree (hidden here for simplicity). This example presumes that option holders will not exercise their options early. Rows 5 through 20 represent the 16 terminal nodes from column H in exhibit 1.

In column K the intrinsic values of the option at the corresponding nodes are computed using Excel IF statements to determine whether the stock prices at those nodes exceed the option's exercise price. For example, cell K5's formula is =IF(H5>B8,H5-B8,0). That formula calculates and displays the option's intrinsic value, $66.44, the amount by which the terminal stock price exceeds the exercise price for the path reflecting four successive upward price movements. Column K shows the option is "in the money" or has intrinsic value at K5, K6, K7, K9 and K13 of the 16 terminal nodes.

In column M the intrinsic values of the option are multiplied by their respective probabilities (column L). Then the present value of each is determined using the risk-free interest rate (B15). The formula in cell M5, --PV(B15,J5,, K5*L5), computes the present value of the probability-weighted intrinsic value for the topmost terminal node (H5) in exhibit 1. (Editor's note: Normally Excel's PV function returns a negative value because Excel considers present value to be the outflow required to pay for future inflows. To prevent any confusion, cell M5's PV statement begins with a negative sign and therefore expresses the present value as a positive.) Thus, the $3.69 present value represents the $66.44 intrinsic value weighted by its 0.0625 probability and discounted at a 3% rate for four years. Corresponding formulas in cells M6 through M20 calculate the intrinsic value for each of the other 15 terminal nodes in column H of exhibit 1. The summation (M22) of column M, $8.56, is the option's fair value and the amount of expense to be recognized. A fuller application of the lattice model will allow CPAs to consider changes in stock price and other factors on at least a weekly basis.

BEYOND THE BASICS

The lattice model has a key advantage over its Black-Scholes-Merton counterpart; it offers CPAs several ways to incorporate assumptions about the projected early exercise of options. One approach, demonstrated in FASB Statement no. 123(R), assumes the options will be exercised if the stock price reaches a selected multiple of the exercise price.

Exhibit 3, below, illustrates this approach using a 2.0 early exercise factor (cell B9) that assumes all options will be exercised for pretermination nodes in years 3 or earlier if the stock price reaches $60--double the $30 exercise price. With all other assumptions being held constant in exhibit 3, the stock-price tree presented in exhibit 1 remains valid. Note that the stock's price reaches $60 prior to expiration only on the path (cell G6 in exhibit 1) that represents three successive years of upward price movements. In exhibit 3 the options are assumed exercised with a gain to the employee of $42.02 (K13)--the difference between the year 3 $72.02 stock price (G6 in exhibit 1) and the $30 exercise price (B8).

[ILLUSTRATION OMITTED]

When early exercise is considered, each node on the stock price tree must be examined to determine whether the options will be exercised early. Thus, exhibit 3 contains 30 rows--one for each node in the exhibit 1 tree. The formula in cell L13, =IF(AND (G6>=(B8*B9),L5=0,L8 =0),0.5^J13,0), examines whether the cell G6 stock price in the exhibit 1 tree equals or exceeds the early exercise multiple. If the stock price meets this criterion and early exercise has not occurred in prior periods, the probability (G7) of this exhibit 1 node is multiplied by the option's intrinsic value (K13) and discounted by the risk-free interest rate (B15) to determine the path's present value (M13).

Because the exercise price multiple is not met for any other pretermination nodes, a probability of zero is specified in cells L5 to L6, L8 to L11 and L14 to L20. Of the 16 potential termination nodes in exhibit 1, the uppermost two (H5 and H7) are exercised early at the end of year 3. Since they are not outstanding in year 4, their corresponding cells in exhibit 3 (L22 and L23) have a probability of zero. In year 4 the intrinsic values for the 14 paths not previously truncated are probability-weighted and discounted to determine their present values (that is, the probability is multiplied by the option's intrinsic value and discounted by the risk-free interest rate to determine the path's present value). The total of the present values of all the individual potential paths (M13 and M24 through M37) is the option's fair value, $8.46.

A lattice model also can accommodate additional expectations regarding when and the extent to which employees exercise their options. For example, rather than assuming that all the options are exercised if the stock price reaches a selected multiple of the exercise price, a lattice model also can permit the assumption that only a certain percentage of outstanding options are exercised.

MEASURING UNPREDICTABILITY

Another benefit of the lattice model is that it can accommodate assumptions that vary over time. Exhibit 4, below, presents a stock-price tree that assumes the stock's volatility decreases from 30% to 24% over the option's four-year life.

Exhibit 4 shows how to specify individual volatility factors for each year of the option's term (cells B12 through B15). The impact of decreasing the stock's volatility in later years is evident on the tree's top branch. After four successive periods of stock-price increases, the stock's price in cell H5 ($87.78) is less than it is ($96.44) in the corresponding cell of exhibit 1. The lower volatilities reduced the magnitude of the stock price increases on the top branch. A similar tempering effect can be seen in cell H36 on the bottom branch, where the stock's price in exhibit 4 ($9.57) is greater than it is in exhibit 1 ($8.11). The lower the volatility, the lower the option's fair value.

Editor's note: This article uses a simplified example to illustrate how a lattice model works. In the exhibits, the option term is only four years--much shorter than the 10-year fife of a typical employee stock option. So in practice the calculations will be more extensive than in these exhibits and companies may have to divide the time period into additional intervals.

Compare and Contrast

Black-Scholes-Merton model

* Was developed for the valuation of exchange-traded options.

* Is the most commonly used closed-form valuation model.

* Is adequate for companies that do not grant many stock options.

* Makes it easier to compare the financial results of different companies using it.

* Is simpler to apply than a lattice model because it is a defined equation.

* Cannot accommodate data describing unique employee stock option plans.

* Does not allow you to vary assumptions over time.

* Assumes options are exercised at maturity.

* Uses estimated weighted averages for expected volatility, dividend rate and risk-free rate, which it assumes are constant over the term of the option. (These weights, calculated outside of the model, are based on the company's past experience. If no such data exist, the company follows the guidance in SEC Staff Accounting Bulletin no. 107 (www.sec.gov/ Interps/account/sab107.pdf.)

* Uses an option's estimated weighted average life--rather than its term--to consider the possibility of early exercise when computing the option's fair value.

Lattice model

* Is more complex to apply than the Black-Scholes model.

* Provides more flexibility to companies that grant many stock options.

* Requires staff with considerable technical expertise.

* Can accommodate assumptions related to the unique characteristics of employee stock options.

* Can accommodate assumptions that vary over time.

* May lead to more accurate estimates of option compensation expense.

* Is flexible enough to calculate the effects of changes in volatility factors, risk-free interest rates, dividends and estimates of expected early exercise over the option's term.

* Requires data analysis to develop its assumptions.

* Requires in-house programming or third-party software.

* May be the only appropriate model in some circumstances--for example, when an option's exercise is triggered by a specified increase in the underlying stock price.

AICPA RESOURCES

CPE 

* Accounting for Stock Options and Other Stock-Based Compensation (textbook, # 732087JA).

* Infobytes: Stock Options and Other Share-Based Compensation Accounting (online courses):

* Audit Considerations.

* Disclosures.

* Nonpublic Company Considerations.

* Measuring the Share-Based Payment.

* History and Summary of FASB 123(R).

For information about Infobytes, see product no. BYTXX12JA at www.cpa2biz.com/infobytes.

Publication

Investment Valuation: Tools and Techniques for Determining the Value of Any Asset, 2nd edition (hardcover, # WI414883P0200DJA).

For more information about these resources or to place an order, go to www.cpa2biz.com or call the Institute at 888-777-7077.

Know the Options

Unlike stock options that are traded on an exchange, employee stock options

* Can be exercised, but not sold or transferred.

* Cannot be exercised during "blackout" periods, which companies typically declare just before releasing their earnings or at other times to prohibit employee purchases or sales of company stock or options.

* Typically have terms of 10 years, in contrast to most traded options' terms of less than one year.

* Are subject to vesting periods of up to four years, during which the options cannot be exercised, and are forfeited by those who leave the company before becoming vested.

* Often are exercised early for reasons such as divorce, separation from service or financial need.

CHARLES P. BARIL is a professor and LUIS BETANCOURT, CPA, and JOHN W. BRIGGS are assistant professors at James Madison University's School of Accounting in Harrisonburg, Va. Their respective e-mail addresses are [e-mail], betanclx @jmu.edu and [e-mail].