How to "Excel" at Options Valuation: Build a Flexible, Spreadsheet-Based Lattice Model for Better Calculations
Charles P. Baril, Luis Aníbal Alonso Betancourt, John W. Briggs · Journal of accountancy online/Journal of accountancy · 2005
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 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 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 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. …