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:

Options Max Pain Calculator

Options Max Pain Calculator Chart

Download link for Options Max Pain Calculator

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.

Max Pain Calculator: Download Link

33 Comments

  1. 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.

      • 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

  2. 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

      • 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

  3. 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.

  4. 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

      • 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 🙂

          • 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

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

    • 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.

    • Hi Rajesh,

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

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

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

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

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

  10. 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

Leave a Reply

Your email address will not be published. Required fields are marked *