I was recently asked by a visitor to this website to demonstrate how to calculate max pain for a specific ticker and expiration. The ticker was the SPY ETF and last Friday 12/12/14 was the option expiration. He had calculated max pain himself and his results did not match my values.
I manually did the calculations in Excel for SPY. I'd like to share my results with all of you. This post demonstrates exactly how I calculate max pain for any ticker and expiration. You may download my Excel file.
First, I use either Yahoo Finance or Google Finance as a data source. The Raw Data section of this website shows that data, although in a slightly compiled form. The data for our example is in the table below. It contains the call and put cash values, but let's ignore those for now. They are the values we will calculate. What is important in this table is (1) the various strike prices and (2) the call and put open interest.
The next step is to go through each individual strike price, assume it's the close price on the expiration and calculate what each of the open contracts would be worth at that close. It's easier to work the calls and puts separately. We will start with the calls.
The intrinsic value is all that is left on expiration for an option. For a call option, you find the intrinsic value from the close price minus the strike price. After you have the intrinsic value, you multiply by the open interest which is the number of open contracts. Finally, you multiple by 100 shares in each contract. This gives you the value of the call options. Keep in if the strike is above the close, the option has zero intrinsic value.
([Close Price] - [Strike]) x [Open Interest] x 100
As we see in the image to the right, if the SPY price were to drop to 185, nearly all the calls would expire worthless. The only calls to have any value would be the 18 calls at the 180 strike. Using the formula, the cash value of the calls would be (185-180) x 18 x 100 = 9,000.
If it were to close at 188, then the 180 calls and 185 calls would have some value. The 180 strike has a value of (188-180) x 18 x 100 = 14,400. The 185 strike has a value of (188-185) x 54 x 100 = 16,200. The total value of all the calls is 14,400 + 16,200 = 30,600.
You simply keep going like this through all the strikes. This is the part where C# or Excel comes in handy.
Please note that if you look back at the data table, the call cash value at 185 and 188 strikes is 9,000 and 30,600 respectively. These are the values we just calculated by hand.
Puts are just a little different. They don't have intrinsic value until the stock price is below the strike price. The formula for puts is
([Strike] - [Close Price]) x [Open Interest] x 100
For example, if SPY closed at 165, the 170 strike puts have the value (170-165) x 37,023 x 100 = 18,511,500.
The 175, 180, 185 puts and so on also have value, but I'm not going to do those all here. Again, C# or Excel is very useful.
After all of this, we need the total call cash and total put cash at each strike. These values are the green call bars and red put bars on the max pain charts. Max pain is the strike where the least amount of calls and puts have value. For the SPY 12/12/14 options, this was the 206 strike price.
You may download the Excel File showing all of the calculations.