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:

1. 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.
2. 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.
3. 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)
4. 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)
   
   
5. 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.
6. Sort the Entire Spreadsheet by The Five Year Value - sort ascending
7. 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.