Option Pain Calculator in Excel

I’ve become somewhat interested in the Max (Option) Pain Theory recently.  In short, it’s a way to predict the price of a stock on a certain date using open option interest.  The theory, which has been around for a while, is based on the fact that stock prices tend to gravitate towards a point where the majority of options will expire worthless.  More detailed explanations can be found at the following Seeking Alpha article.

There are a number of online max option pain calculators: OptionPain, OptionCalc, MaxPain, MaxPa.in, Strike Pegger, etc.  Unfortunately, these online calculators do not have much historical data, so it’s difficult to determine how well the theory predicts stock prices at option expiration.  I’ve created a Max Pain calculator in Excel that allows you to enter historical option interest data to back-test how well the theory works.  For more on how Max Pain is actually calculated, see Optionetics.

From my limited analysis, the theory is somewhat useful for certain stocks.  The stocks should have a ton of open option interest like AAPL.  Not surprisingly, the theory works better as the option expiration date nears, i.e. the forecast tends to be more correct at two weeks vs. four weeks from expiration.  People (e.g. Travis Lewis at AAPLPain) are using variations on the theory to trade stocks and options successfully.

Tagged , , ,

One thought on “Option Pain Calculator in Excel

  1. danksmith says:

    Excellent article. You are one of the few who seem to realize the open contracts has an effect. The process is (1) retail buys a call. (2) MM sells the call. (3) MM hedges the short position by buying stock. (4) MM adjusts hedge as needed to remain nuetral. (5) adjustment drives price to max pain strike. By that line of reasoning, the more open contracts there are, the larger the hedge and the greater pressure toward max pain. You’d be surprised how few consider that. I propose the same at http://maximum-pain.com . I am also working historical data for back testing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: