# Options Max Pain Calculator: Excel Sheet

Max Pain theory in Options Trading is probably one of the newest bust most popular strategies for Options trading. This theory is backed upon the fact that about 95% of option buyers lose money. We have personally traded based on max pain and the success ratio is pretty cool. This article intends to explain max pain theory of options and how to profit from it. Also, we would go through Options max pain calculator excel sheet which can be downloaded from the end of this post.

# Max Pain Theory: Definition and Usage

The idea behind Max Pain theory is the fact that Option writers tend to manipulate the expiry price of stock, index or commodity so that they benefit the most out of their positions. Although there is no authentic proof that how they manipulate the prices, yet this theory is widely accepted. As 95% of the options expire worthless and options is a zero sum game, this theory seems very realistic and logical. By definition, Max Pain is the strike price where if the underlying settles on expiry will cause maximum loss or maximum pain to the option buyers. Guess who would benefit from their loss? Yes, its Option sellers. Most of these Option Sellers are large investors / institutions or hedge funds that will hedge their positions and have the ability to manipulate the stock price to suit their open Options position.

There are enormous ways in which you can benefit from Option pain. Basically, max pain tells you the approximate level at which the contract is going to expire. If you know know this level, you can simply write Call options slightly above this level and pocket the premiums on expiry. Similarly, you can write Put options below this level. If you are a conservative option trader you can employ different hedging strategies like Bull Spreads or Bear Spreads.

# Options Max Pain Calculator

Max pain is calculated using the Open Interest of options. The calculation is fairly simple and is summarized below:

1. List down the different strikes of the option chain along with their open interests.
2. For each strike, calculate the profit/loss incurred to the option writers if the underlying expires at that level. This calculation should sum up the profit/loss for put as well as call option at that strike price.
3. The strike price at the the loss is minimum is termed as Max Pain strike price.

# Automated Excel sheet for Max Pain calculation

We have created Options max pain calculator excel sheet which does all the background calculations and shows the Max Pain strike price. This excel sheet is automated and you need not to manually enter any data into the sheet. Currently this excel sheet supports both Nifty and Banknifty from NSE. When you open the sheet it would throw some warnings which can be safely ignored. The sheet would refresh every 5 minutes.

Below are few screenshots:

Please see the below link to download Options max pain calculator excel sheet. Mostly everything is self explanatory, but please let us know if you have any queries. Also, send us your feedback on how to improve this sheet.

#### Related Posts

1. Anandhan

Sir Please add column Change in OI with this file

2. Karan Mehta

SIR, WHEN YOU HAD TRADED PERSONALLY, WHAT SUCCESS RATIO YOU GOT & FOR HOW MANY YEARS YOU HAD TRADED, KINDLY UPDATE.

Hi Karan,

Based on my experience, writing call/puts for 10% above/below max pain gave 90% success rate.

• Karan Mehta

SIR, ARE YOU SAYING THAT IF MAX PAIN PRICE IS AT 8700, WRITING PRICE FOR CALL SHOULD BE 9500 + & PUT SHOULD BE 7900, KINDLY GUIDE & UPDATE.
THANKS & REGARDS

• karan ji woh maximum pain main call ya put buy karna hai toh kaise malum hota hai sir please reply

3. Murali

Hi Sir,
Can you please share your thoughts for below query
Is the max pain analysis give benefits only when it is close to expiry of contract or even can we use this for intraday, if yes please quote an example! Which will be really helpful.
Thank you for updating with new analysis and strategies.

Hi Murali,
Trading based on max pain would not be much helpful in intraday. You should take positions atleast 10-15 days before expiry

• Murali

Thank you so much sir!
As you mentioned in the comment 10% above or below Max pain gives 90% success, it means either we need write 8800 Call or 8600 put is it right!!

• yesterday morning in that max pain in that max pain value index is 18500 but i cannot identify to buy ce or pe but they give that level is very superb

4. Karan Mehta

SIR, KINDLY GUIDE AS HOW & WHEN TO ENTER THE TRADE AS PER MAX PAIN ( AT THE START OF THE EXPIRY ETC.) AND WHEN TO EXIT THE TRADE.
THANKS & REGARDS

Hi Karan,
We recommend to enter the trade 10-15 days before expiry, and exit on expiry day.

5. Karan Mehta

SIR, YOU ARE SAYING 10% ABOVE OR BELOW THE MAX PAIN PRICE GIVES 90% SUCCESS RATIO, SO IF THE MAX PAIN PRICE IS 8700, WRITING PRICE FOR CALL WILL BE 9500 + & PUT WILL BE 7900, KINDLY GUIDE & UPDATE.
THANKS & REGARDS

Yes Karan, that’s correct.

• Karan Mehta

SIR, LAST TRADED PRICE OF 9500 CALL IS 1.10 & 7900 PUT IS 2.55, DUE YOU ADVICE TO TAKE SUCH TRADE. IN CASE SUCCESS RATIO IS EVEN 100%, YOU WILL EARN APP. 24 POINTS IN A YEAR = RS. 1800 LESS BROKERAGE ETC. 600 = NET PROFIT 1200 IN A YEAR ON AN INVESTMENT OF APP. 50,000/- IN CASE YOU KEEP SAME AMOUNT IN BANK FD YOU WILL EARN APP. 4000 AS INTEREST IN A YEAR, KINDLY ADVICE HOW WISE IT WILL BE TO TRADE WITH THIS STRATEGY.

Hi Karan,

Risk and reward are always inversely proportional. You cannot expect a better profit if you are looking for 100% success ratio. While if you decrease the threshold % to 5 or less, then your profit will dramatically increase. But there would be a fair chance of loss too. Its just a game of probability 🙂

• Karan Mehta

SIR, MY CALCULATION IS AS PER YOUR GUIDANCE.
DECREASE IN THRESHOLD WILL DECREASE YOU SUCCESS RATIO, IN CASE THRESHOLD IS DECREASED TO 1%, SUCCESS RATIO MAY BE 25% OR LESS

6. Ashish

The nifty MAX pain is not working for this month. I have tried changing the symbolCode=-10003 but no luck. Please check

Thanks for reporting Ashish. The sheet has been corrected now.

7. sir in the maximum pain how can i find the it is the call or put please reply sir i am waiting ur replr

• woh maximum pain main index point correct de raha hai but usmain call buy karna hai ya put buy karna hai woh kaise malum karna hai sir please reply and one thing intraday ke liye bhi bahut acha hai sir 9th dec 2016 ke din 18500 ka maximum pain bataya tha sir aur call option main acha profit huwa compare to other index woh call buy karna hai ya put buy karna hai woh malum kaise karna hai please reply it is a humble request i am waiting ur reply

Hi Jairam,

Max pain tells you the approximate level at which the contract is going to expire. If you know know this level, you can simply write Call options slightly above this level and pocket the premiums on expiry. Similarly, you can write Put options below this level.

8. call ya put buy karna hai woh kaise malum karnha hai sir

9. Rajesh

Sir, Max Pain for Stocks, if possible.

Hi Rajesh,

It would be difficult to create an Excel sheet for all stocks. Please specify few of them so that we can help.

• Anand

You can select the most liquid stocks like, Reliance, DLF, etc etc for Max Pain of Stocks

10. Ravi

Hi,
I fail to understand in the table why does it zero “0” out at 9100 and not at 9400

11. Ravi

Hi,
I fail to understand in the table why does it zero “0” out at 9100 and not at 9400

12. abhijeet

Sir i buy 8200 PE , 8300PE, 8400 PE and 8400 CE pls suggested me which one close near by expiry

13. mangesh

Sir,Nice formula,
Can you please share same formula in F&O stock also….

14. atul

Hello, Are we able to download Bank Nifty data in excel, the way one is able to download the Nifty 50 data, using webquery?

15. Koushal

Hello sir, I’m confused with the values.
May i know how to find thses call and put values?

Hi Koushal,

Call and Put values would be populated automatically. No manual work is needed

16. Shailendra Saxena

Thank you, Calculations are OK but the data in all the fields is to be entered manually, as the auto update function is not active in file which I downloaded from here

17. Dear Sir,
I was just going through your max pain spread sheet. I didn’t understand the Put value formula “=IFERROR(SUMPRODUCT(\$B2:B\$14,\$C2:C\$14)-\$B2*SUM(\$C2:C\$14),0)”
given in the spread sheet at cell E2. How did u get the value “0” in cell E14 at the strike price “9800”. Just was trying to learn this forumla. I would be happy if u can elaborate on this formula specially the “0” part

18. rahul

HI Team,
The attached spread-sheet is not working properly. Could you please upload a new version with BNF tab in working condition.

Hi Rahul,

Sorry about that. We checked it at our end and the spreadsheet is working as expected. Can you please send the screenshot to support@tradingtuitions.com

19. PG

I downloaded the sheet and check for BNkNifty max pain it is showing as 18300 but as of banknifty is at 24498 and seems bullish. It is really impossible unless somekind of crash. Also, I noticed that it shows data till 18900 which may be wrong. Can you check and correct the error please?
BTW I am impressed with your work and its really wonderful Thanks a lot.

Can you please re-download the file and check? The issue with BNF has been fixed.

20. praveen sharma

in bank nifty max pain pl.specify contract if it is current month expiry or weekly.
if month expiry can v have the same for weekly or it will be risky to be avoided ?

Hi Praveen,

It is monthly expiry.

21. Santosh

Hi Santosh,

Can you please try again. I can download it without any issues

22. Sristi

Hi based on Max pain If market opens sharp down or sharp up beyond the level on expiry day it is an super bumper offer and in 52 weeks on bank nifty expiry 10 to 12 Thursday happens like this .on other it’s very closer and we can enter after 2 pm if we have a chance
By the way may I know is it working for f&o stocks which has good call put OI ?if yes name of those like sbin, canbank, tatasteel like that? do you have any data to back test it. Your valuable feedback is much appreciated thank you

Hi Sristi,

Thanks for your insights on Banknifty! Yes, this strategy works for F&O stocks with good call/put ratio, but unfortunately I don’t have any backtest results as of now.

23. nimesh jethva

Dear Sir,
I downloaded Open option analysys for nifty and bank nifty and also option pain.
you deed very good job.
is it possible to incorporate max pain graph in Option anaysys sheet of nifty and banknifty, so that we get all data at one place.
thank you.

24. Venkat

PAINNIFTY sheet is not getting refreshed automatically. Even BNF sheet is getting refreshed only once we click on Refresh.

25. Justin Joseph

Hi Admin, I am looking to calculate the Max pain myself. Doing a cumulative calculation of all the available strike prices while assuming the market expires at the current spot rate, so I deduct the total points loss on the strike price from there. Once done, I cumulatively add the total loss on every strike price and add the no for call & put… is that the right way to go? I can send a sample copy of my file.. if required to reference further.

Urgently need help please.

26. Nihar Bhuva

Hello sir. Can I calculate in same sheet for stock options also??? Please reply.

27. Nilesh_NR7

Thanks 4 sharing.

28. Yeshodeep

By using Max pain how can we buy option..? What will be the strategy..? Please explain sir..