Sunday, December 04, 2005

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.


At 12:51 PM, Anonymous PENNY STOCK INVESTMENTS said...

Stock option are for the birds.


Post a Comment

Links to this post:

Create a Link

<< Home

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License.