he 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/fas123r.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 .”)
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) 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.
Exhibit 1 (below) illustrates an Excel stock-price tree based on the following assumptions:
Current stock price of $30.
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
Exhibit 2 , below, presents an Excel template that calculates the option’s fair value. Columns J through M are added to exhibit 1 ’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
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).
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>5(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.
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.
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 email@example.com, firstname.lastname@example.org u and email@example.com .
How to Excel at Options Valuation
Build a flexible, spreadsheet-based lattice model for better calculations.
BY LUIS BETANCOURT, CHARLES P. BARIL AND JOHN W. BRIGGS
December 1, 2005