### Employee Stock Options - Part 3

Monte Carlo simulation is a problem solving methodology that uses a large number of random simulations to obtain a probability of success for a given situation. Case in point:

I want to know the probability that in 5 years my options will be worth less than they are worth today assuming a 5% discount rate.

Here's the process:

**Determine Average Return and Standard Deviation of Return**- I looked at the last 5 years of annual returns, calculated the average and used Excel to calculate the standard deviation. For my company, these values were: 13.5% annual return w/ a 35% standard deviation.**Create Random Returns**- I used the Random Number Generator in Excel's Analysis Toolpack to create 5 columns of 10,000 normally distributed (using the avg. return and std. dev. numbers from step 1) returns. The columns are years 1-5 and the 10,000 rows represent different potential 5 year periods.**Calculate Cumulative 5-Year Return**- create a sixth column that multiplies each of the returns together to get the cumulative return. Remember to add 1 to each return in the formula: = (1+Year 1)*(1+Year2)*...(1+Year 5)**Calculate the Value of Each of Your Options in Year 5**- create a column for each of your grants and calculate their theoretical value at the end of year 5. Here's the formula:

=if(5YearReturn * CurrentStockPrice <= OptionStrikePrice , 0 , ((5YearReturn * CurrentStockPrice) - OptionsStrikePrice)* NumberOfShares)**Add the 5 Year Values Together and Discount**- sum up the value of your options in Year 5 and multiply this value by (1+ discount rate)^0.2 . This gives you the net present value of your options.**Sort the Entire Spreadsheet by The Five Year Value**- sort ascending**Calculate the Probabilities***-*here's the fun (if you're a geek like me). How many five year values are zero? Divide this number by 10,000 and you'll have the probability of your options being valueless at the end of 5 years (1.3% for me). How many rows have values less than the current exercise value of your options? 34% for me. Greater than double today's value? 51% for me.

A few reservations:

- Result is highly dependent on the statistics you calculated in Step 1.
- Only you can determine your risk or, in most cases, loss tolerance.
- This IS NOT investment advice.
- This does not analyse the tax implications of exercising options.

In Part 4, I'll introduce to a commercial program I found that goes well beyond my rudimentary analysis.

## 1 Comments:

Stock option are for the birds.

Post a Comment

## Links to this post:

Create a Link

<< Home