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.

 

 

 

Strike
Put OI
Put Cash
Call OI
Call Cash
Total Cash
% Diff
165
6084
3500582850
0
0
3500582850
6453.6
170
37023
2976240850
0
0
2976240850
5471.95
175
30099
2470410350
0
0
2470410350
4524.96
180
37863
1979629350
18
0
1979629350
3606.15
185
9911
1507779850
54
9000
1507788850
2722.8
188
4145
1227643450
0
30600
1227674050
2198.38
189
3943
1134679150
15
37800
1134716950
2024.35
190
25073
1042109150
1034
46500
1042155650
1851.06
190.5
5591
997077800
10
102550
997180350
1766.86
191
14414
952326000
11
159100
952485100
1683.19
191.5
2122
908294900
10
216200
908511100
1600.86
192
21560
864369900
1030
273800
864643700
1518.73
192.5
1940
821522900
5
382900
821905800
1438.72
193
16053
778772900
240
492250
779265150
1358.89
193.5
15769
736825550
23
613600
737439150
1280.59
194
12422
695666650
10
736100
696402750
1203.76
194.5
12599
655128850
8
859100
655987950
1128.1
195
13315
615221000
561
982500
616203500
1053.62
195.5
5855
575978900
16
1133950
577112850
980.43
196
4640
537029550
110
1286200
538315750
907.8
196.5
27035
498312200
101
1443950
499756150
835.61
197
18229
460946600
287
1606750
462553350
765.96
197.5
4244
424492450
190
1783900
426276350
698.05
198
26342
388250500
1429
1970550
390221050
630.55
198.5
6034
353325650
270
2228650
355554300
565.64
199
32610
318702500
682
2500250
321202750
501.33
199.5
5369
285709850
813
2805950
288515800
440.14
200
43275
252985650
1196
3152300
256137950
379.52
200.5
20198
222425200
2022
3558450
225983650
323.07
201
65817
192874650
1505
4065700
196940350
268.7
201.5
32383
166614950
809
4648200
171263150
220.62
202
62033
141974400
4159
5271150
147245550
175.66
202.5
25772
120435500
3068
6102050
126537550
136.89
203
48230
100185200
3956
7086350
107271550
100.82
203.5
17519
82346400
4806
8268450
90614850
69.64
204
67277
65383550
52697
9690850
75074400
40.55
204.5
18192
51784550
12264
13748100
65532650
22.68
205
54255
39095150
40874
18418550
57513700
7.67
205.5
39024
29118500
9495
25132700
54251200
1.56
206
30320
21093050
32689
32321600
53414650
0
206.5
12601
14583600
15878
41144950
55728550
4.33
207
51520
8704200
42363
50762200
59466400
11.32
207.5
17428
5400800
40765
62497600
67898400
27.11
208
20493
2968800
37932
76271250
79240050
48.34
208.5
10161
1561450
17774
91941500
93502950
75.05
209
13230
662150
33854
108500450
109162600
104.36
209.5
813
424350
12156
126752100
127176450
138.09
210
2512
227200
66509
145611550
145838750
173.03
210.5
363
155650
17499
167796450
167952100
214.43
211
422
102250
29648
190856300
190958550
257.5
211.5
314
69950
30605
215398550
215468500
303.38
212
137
53350
25742
241471050
241524400
352.16
212.5
51
43600
22445
268830650
268874250
403.37
213
62
36400
2786
297312500
297348900
456.68
213.5
0
32300
1033
325933650
325965950
510.25
214
0
28200
1140
354606450
354634650
563.92
214.5
0
24100
1085
383336250
383360350
617.7
215
42
20000
5076
412120300
412140300
671.58
215.5
0
18000
27
441158150
441176150
725.94
216
0
16000
171
470197350
470213350
780.3
217
0
12000
180
528292850
528304850
889.06
217.5
0
10000
775
557349600
557359600
943.45
218
0
8000
3077
586445100
586453100
997.92
219
0
4000
4020
644943800
644947800
1107.43
220
40
0
1846
703844500
703844500
1217.69
221
0
0
92
762929800
762929800
1328.31
222
0
0
1276
822024300
822024300
1438.94
222.5
0
0
28
851635350
851635350
1494.38
225
0
0
0
999697600
999697600
1771.57
227.5
0
0
4
1147759850
1147759850
2048.77
230
0
0
2
1295823100
1295823100
2325.96

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.

Tags: