Option Payoff Excel Tutorial

[Quanto]

Petr Houstecky, the founder of Macroption and the author and programmer of options tools (see About) offers a useful Excel course titled Option Payoff Excel Tutorial showing step-by-step how to compute all the important properties (metrics) of any options strategy, like Payoff, MaxWin, MaxLoss, BreakEven, RiskRewardRatio etc.:
https://www.macroption.com/option-payoff-excel/
These are practical payoff calculations for the option expiration date.
To be able to go through the tutorial and successfully replicate the calculations, you only need basic Excel skills.
Highly recommended if you want to learn the practical maths behind options.
Can also be useful for other programming languages, ie. as a practical reference.

 

[FSU]

The problem with this and all other options payoff calculations is they assume when the stock closes at the exact price where a short option is centered, it yields max profit. This is just not the case unless you are dealing with a cash settled index.

Since you can exercise an option until 530 pm on expiration, even out of the money options retain value. These models assume they are worthless.

If you have a long butterfly in a volatile stock such as SMCI and it closes exactly at your short strike, these models will assume a max payoff. In fact, your short option may still have substantial premium here on the close. So, you really aren't able to achieve the max value predicted in these calculations.

 

[Quanto]

The problem with this and all other options payoff calculations is they assume when the stock closes at the exact price where a short option is centered, it yields max profit. This is just not the case unless you are dealing with a cash settled index.

Since you can exercise an option until 530 pm on expiration, even out of the money options retain value. These models assume they are worthless.

If you have a long butterfly in a volatile stock such as SMCI and it closes exactly at your short strike, these models will assume a max payoff. In fact, your short option may still have substantial premium here on the close. So, you really aren't able to achieve the max value predicted in these calculations.

With which options strategy does this happen? (I can't believe it happens with all).
So, can you provide a complete example dataset that demonstrates the problem you mean?

I just hope we mean both the same thing: auto-exercise/auto-assignment happening at the expiration of the option's life. [only if necessary]

FYI: at expiration the payoff formula will be applied. This formula knows only your strike and the current underlying stock price. Any premium of the option is irrelevant, as well IV etc.

 

[FSU]

With which options strategy does this happen? (I can't believe it happens with all).
So, can you provide a complete example dataset that demonstrates the problem you mean?

Lets assume you are long the SMCI 860/870/880 call butterfly. With this model and others, it will assume the max profit will occur if SMCI closes at 870 on expiration, saying the butterfly is worth 10. It assumes that the 870 call is worth zero and the 860 call is worth 10. But that's not the case. On the close, the 870 call may be trading for 5. There is still value in these options because you can exercise them for 90 minutes after the close.

Because of this there is no real way to get max value (10) out of this butterfly. No one will buy it at this price, and you won't be able to exercise out of it for this price as you won't know if you will be assigned on your short calls.

 

[Quanto]

Lets assume you are long the SMCI 860/870/880 call butterfly. With this model and others, it will assume the max profit will occur if SMCI closes at 870 on expiration, saying the butterfly is worth 10. It assumes that the 870 call is worth zero and the 860 call is worth 10. But that's not the case. On the close, the 870 call may be trading for 5. There is still value in these options because you can exercise them for 90 minutes after the close.

Because of this there is no real way to get max value (10) out of this butterfly. No one will buy it at this price, and you won't be able to exercise out of it for this price as you won't know if you will be assigned on your short calls.

@FSU, what would happen if you do not interfere and let the system handle everything?
Ie. this happens not at 4 p.m. when the RMH closes, but sometime later (you gave 5 p.m.). You have to be patient. At my broker this work happens on the next day, ie. takes up to 24 hours! .
I and these payoff formulas mean exactly this very case.
See also
https://www.investopedia.com/terms/a/automaticexercise.asp
https://www.investopedia.com/terms/e/expirationdate.asp

Ie. the following distinction should clear everything

 

[FSU]

@FSU, what would happen if you do not interfere and let the system handle everything?
Ie. this happens not at 4 p.m. when the RMH closes, but sometime later (you gave 5 p.m.). You have to be patient. At my broker this work happens on the next day, ie. takes up to 24 hours! .
I and these payoff formulas mean exactly this very case.
See also https://www.investopedia.com/terms/a/automaticexercise.asp

Being patient and waiting doesn't help. At the close you can't sell the spread for maximum profit and if you wait, you are at risk of not being assigned on your short calls. Your long calls in the SMCI example would be exercised, but if the stock falls further after hours, your shorts won't be exercised (and you won't be assigned) so you will come in long stock the next day.

These calculation models are perfect for cash settled options, not for equites where assignment is unknown at the close.

 

[Quanto]

Lets assume you are long the SMCI 860/870/880 call butterfly. With this model and others, it will assume the max profit will occur if SMCI closes at 870 on expiration, saying the butterfly is worth 10. It assumes that the 870 call is worth zero and the 860 call is worth 10. But that's not the case. On the close, the 870 call may be trading for 5. There is still value in these options because you can exercise them for 90 minutes after the close.

Because of this there is no real way to get max value (10) out of this butterfly. No one will buy it at this price, and you won't be able to exercise out of it for this price as you won't know if you will be assigned on your short calls.

Your assumption that MaxPnL is $10 is incorrect!
It's slightly less than $10. Proof: https://optioncreator.com/stfs7ou

 

[FSU]

Your graph is showing P/L. Based on the prices you typed in you are paying .19 for the butterfly, so if it went out at 10, your P/L would be 10 - .19.

My point is that it won't be worth 10. Thats were this system fails.

 

[Quanto]

Your graph is showing P/L. Based on the prices you typed in you are paying .19 for the butterfly, so if it went out at 10, your P/L would be 10 - .19.

My point is that it won't be worth 10. Thats were this system fails.

I rather think you do something wrong, as you have shown in your prev. postings...

Never mind, I just have to wonder about what you believe is a problem.
This is not a problem at all. Just come down to Earth, ie. to reality.
So, you expect (wrongly) $10, and get instead only say $9.95. And this is your "big problem"?... Oh, man! This is a luxory problem, IMO, if any at all.

 

[longandshort]

@Quanto you don’t seem to understand what @FSU is saying lol. While these options calculators in excel are “helpful” for new options traders, they shouldn’t be relied on for actual analysis as their assumptions can lead to significant divergence from reality.