How to Use Excel to Calculate Forfeiture Rates

Stock options have become increasingly common incentives for non-executive employees.

Employee stock options have been granted more often in recent years, though historically, they had been reserved for executive management only. Especially at competitive companies or within competitive industries, not every employee remains with the company long enough for his options to vest. Companies must plan for this and factor it into the financial statements by calculating a forfeiture rate to apply to options recorded as an expense according to FAS123R.

Instructions

1. Review the company's stock option recording method and make sure it is performed according to FAS123R guidelines. FAS123R, renamed ASC 718, is a ruling issued by the Financial Accounting Standards Board that requires stock options to be recorded on the financial statements at their fair market value. Additionally, options must be recorded as compensation expense on the income statement.

2. Review the company's policy on forfeiture rates. Studies find that executives do not forfeit options as much as other employees. This may result from the fact that company CEOs and their Boards of Directors work hard to prevent the senior management team from leaving the firm. This implies that companies may benefit from using different option forfeiture rates for different levels of employees.

3. Group the options according to the date they were granted. This creates a series of grants on which the forfeiture calculations are performed. The forfeitures need to be calculated each month, and then annualized to arrive at the yearly rate of forfeiture.

4. Calculate the monthly forfeiture using Microsoft Excel or another spreadsheet program. In one cell of the spreadsheet, type the equal sign to indicate you are entering a formula, and then type an open parenthesis, the number of options forfeited during the month, and a closed parenthesis. Continuing the formula, in the same cell, type a forward slash, then repeat the open parenthesis, type the number of unvested options at the beginning of the month, and type a closing parenthesis. To remove outliers in the monthly average, take the average of 60 days of data.

5. Calculate annual forfeiture rates by annualizing the monthly forfeitures. In Excel, set up a formula as follows: equal sign, 1 minus open parenthesis <insert average monthly forfeiture rate> close parenthesis to the power of 12. In Excel, use the "^" character (minus the quotation marks) before a number to use it as an exponent.

References

• Equity Methods: Best Practices in FAS 123R Forfeiture Rate Estimation
• Frederic w. Cook & Co., Inc.: Accounting for Stock Compensation Under FASB ASC Topic 718; September 2009