Options are one of the most fascinating and versatile financial instruments. While trading in stocks or commodities, you can bet on only one thing, i.e., the direction of the price. You simply buy when you are bullish and sell when you are bearish. It means that for making money while trading stocks, you need to successfully predict or anticipate the future direction of the price, which is one of the most difficult tasks. But that’s not the case with options. Options let you play not only on the direction of the price of an underlying asset but also on time and volatility. Besides it, the huge amount of leverage it provides makes it one of the greatest games ever. And the good news is, we have created a completely automated options strategy payoff calculator excel sheet. You just need to input the details of your options trade, and the excel sheet will calculate your maximum profit potential, probable risk and all other metrics related to your trade. Read on to know more.
Why should you trade Multiple Options at a time?
By trading multiple options simultaneously, you can create your desired options strategy. Options strategies provide complete flexibility to the traders to manifest their views about the market into their trading position. A trader can choose or build any options strategy based on his views about the underlying asset and also his own risk and reward expectations. With the right knowledge and tools in hand, there is absolutely no limit. And at Trading Tuitions, we are providing you both.
Let me ask you a question. What’s the risk-reward profile of a long call option? Well, that’s easy. We all know that there is unlimited profit potential and the risk is limited to the amount of premium paid. But when it comes to complex multiple leg options strategies, such as Butterfly, it is difficult to analyze the profit-loss potential that quickly. A convenient and quick way to envision what happens with option strategies as the value of the underlying asset changes is with the use of a profit and loss diagram, known as a “payoff diagram”.
Also Read: How to trade Options using Market Profile?
What is a Payoff Diagram?
Payoff diagrams are a graphical representation of how a certain options strategy may perform over a variety of expiry prices enabling a trader to gain an understanding of potential outcomes. These graphs help us understand the risk and reward for a particular options strategy at a glance. The vertical axis or Y-axis of the diagram shows profits or loss generated by a certain strategy on expiry, while the horizontal axis or X-axis reflects the price of the underlying asset on options expiry day.
PAYOFF DIAGRAM OF A LONG CALL
Image Source: https://www2.poems.in.th/home/derivatives/en/options04.htm
Looking at a payoff diagram for a strategy, we get a clear picture of how the strategy may perform at various expiry prices. By seeing the payoff diagram of a call option, we can understand at a glance that if the price of underlying on expiry is lower than the strike price, the call options holders will lose money equal to the premium paid, but if the underlying asset price is more than the strike price and continually increasing, the holders’ loss is decreasing until the underlying asset price reach the breakeven point, and since then the call options holders profit from their long call positions.
Also Read: Diagonal Spread: Low risk Options Strategy
Options Strategy Payoff Calculator: How to Use?
Step 1: Download the Options Strategy Payoff Calculator excel sheet from the end of this post and open it.
Step 2: Select the option type and input the quantity, strike price, premium, and spot price. Quantity should be negative if you are shorting a particular option.
Step 3: Repeat step 2 for all the legs your strategy contains. Quantity for rest of the legs should be set to 0. For example, suppose you want to analyze the payoff of a 2- leg strategy. Enter the details in the first two legs and ensure that quantity for the rest of the legs is 0.
Step 4: If your strategy consists of buying or selling stock or futures such as in covered call or protective put, enter its quantity, futures price and spot price in leg 5. If not, set the quantity to 0.
You will automatically get payoff table and diagram along with the net premium flow and maximum profit and loss potential for your strategy.
Step 5: If you want to know your profit or loss for a particular expiry price, enter it and you will get the profit/loss value if the underlying expires at that price.
Download Link
Click on the below link to download Options Strategy Payoff Calculator excel sheet. Once you open the sheet, input the values as per the above instructions and validate your payoff diagram.
Options Payoff Calculator Excel Sheet
If you want to calculate theoretical option prices, implied volatility and payoff (upto 10 legs) in a single spreadsheet, check out this post.
Thank you so much for learning post again sir…
Thanks for your kind words.
If we edit this sheet in Call / Put column, it automatically shows Zero, please suggest.
thanks for the excel sheet. Any chance I can see daily values. My intention is to enter at the start of the series and square off within 10-12 days. Any way you can incorporate daily values. Would be very helpful
Hi Arrush,
Unfortunately, daily values are not possible in the current sheet as it would require calculation of greeks and it would be a whole new dimension. Maybe in future, we will try uploading such a sheet. Stay tuned.
Do you have template of calendar spread and iron Condor on your website.
is the profit and loss showing in excel is amount or the premium..
how to add leg 5 and 6 ???
Sir, I want to add leg 5 and 6, please guide.
and one more thing we have to enter 1 lot or 75 quantity please let me know….
Well that’s up to you. If you enter 1 as quantity, figures shown would be for one single share. But if you want to analyse your position for the whole lot, you can enter the quantity in multiples of the lot size.
Hi Rahul, the sheet shows the net profit or loss figure (before commissions) for your position.
Great Excel sir.
Hi,
What is the future price to enter in Leg 5 for options? We don’t have any future price for Options right?
Hi Ravi,
It’s the future price of underlying security
Thank you Soooo much Harsh Sir ! Article is very helpful & Payoff calculator is Asset…hats off!
It would be great ,if you could please suggest best strategy for bank nifty.
Thanks Prashant for your kind words. Regarding your question, there is no one single best strategy for bank nifty or any other instrument. What works for one may not work for the other and what works at a time may not work other time. But Yes, we will try to share some good strategies soon. Stay tuned.
I appreciate your hard work on preparing such excel sheet. Thank you Sir ☺
One question.
Can we use this excel for our daily intraday trade or it will be helpful only on weekly expiry days.
Hi Amit,
We have tested this on on weekly/monthly, but you can try it on intraday too
How do I find the futures price for a Stock?
I was quite impressed by the efforts done in the calc.
I was back-testing this calculator today on May 22nd,
The result was quite good for weekly options expiring May 21, 2020 however when I entered monthly options data for expiry May 28, 2020; the calculator failed. I have used multiple values none of those gave any positive result.
My question is that is this calc for weekly options or for the next day trade only?
Please reply.
Hi Victor,
Can you please elaborate what do you mean by “failed” ?
thanks very much by heart
Sir, hats off! keep it up…
Thanks for your kind words Mehul!
Hi sir very well briefly described the cocept
Great excel sheet and very helpful.
Thank You for sharing free of cost.
Now I don’t have to pay to Sensibull or Opstra for guessing the Pay off of expiry day.
I can plan my trades easily.
Daily I am spending most of the time with this excel sheet.
I salute the creator of this excel sheet from my heart.
God bless you.
Your hard work is materialized.
Thanks for your kind words Mangesh
Thanks for sharing the excellent tool for free
keep up the good work
Hi, Thank you for all the work you have put in to create this Excel worksheet. In the individual legs, is the spot price the same as the current stock price or have I misunderstood ? Thanks
Hi Sunil,
Yes, you are correct. Its the current price of stock
Thank you very much for the spreadsheet This is an incredibly helpful tool!
Thank you for the excel. Great work
pls confirm if the excel available with daily values.
Thankyou very much
Sir
Thank you very much for the wonderful excel. I have a doubt about the way we use it. How to enter “buy or sell” in the excel sheet. Please clarify. For instance in the first leg buy CE abd in the second leg sell CE. How to enter?
What about selling Call and Put Options. The sheet plots only Buying
Sorry, I understand negative quantity is considered Shorting ….
How to find the expiry price. do we require any formula to calculate the expiry price? if yes so tell us how to find the expiry price. which is mentioned in the Excel file.
That’s your predicted expiry price. The sheet will show you the approx P&L for the expiry price you enter