Binomial Option Pricing Model for Employee Stock Options

Executive Summary

Business Problem

A common problem for accountants in today’s world is determining an item’s ‘fair market value.’ This is especially difficult for assets that are not traded on an exchange (such as the New York Stock Exchange). One example of this dilemma is an employee stock option. Many employees of public companies receive stock options as part of their compensation package. If these employees pass away before exercising the options, the fair value of the options must be included in the decedent’s estate for tax purposes. The issue is that these options have different terms than those which are traded on the stock market. So how do we value these employee stock options? Fortunately, there are a few methods available for calculating the fair value of an employee stock option, one of which is the binomial option pricing method.

System Description

The problem is that a binomial option pricing model requires some tedious calculations. In this project, I have utilized excel VBA to automate the calculation, formatting, and reporting involved with using a binomial option pricing model to value employee stock options. All that is required of the user are the relevant financial inputs for the option pricing model (gathered using a user form) and the program will calculate the fair value using (loops and arrays) and subsequently email the results to a colleague to review the work.

http://files.gove.net/shares/files/13w/metcalf6/VBA_project_write-up.pdf
http://files.gove.net/shares/files/13w/metcalf6/VBA_binomial_model_project_Final.xlsm