I'm making a spreadsheet to compare several option strategies. Is there a way I calculate the POP (probability of profit) or the Delta. A rough estimation will do for me.
There are a number of Option Pricing Models. The most commonly known one is Black Scholes. There are lots of sources on the web that offer the formula as well as downloadable Excel spreadsheets. Google: "Black Scholes Formula Delta"
FWIW, all pricing components affect the value of delta which is also an approximation of the probability that an option will expire in-the -money. Most are known and linear so the effect on delta can be calculated (carry cost, passage of time, etc.) and affects all options collectively. The wild card is implied volatility which can dramatically affect ITM and OTM delta and that can alter your option strategy calculations. It's another layer of complexity that you might want to consider in your strategy comparisons.
If stocks price is a random walk with a constant absolute variance, then the value of an option depends only on the difference between the strike price and the spot price (note that it is often modeled as constant percentage variance, in which case it depends on the ratio between the two). Thus, the derivative of the option price with respect to the strike price should be the same (magnitude) as the derivative with respect to the spot price. The latter is delta, and if you have a spreadsheet with option prices varying by strike price, you can approximate the former. Keep in mind what I said about absolute variance versus percentage variance, though. This will skew the results the larger the difference between strike prices.
However, as D Stanley says, delta, probability of being in the money, and probability of profit are all distinct concepts. Under certain assumptions, delta is the same as the probability of being in the money, but if those assumptions don't hold, then they are different. The probability of being in the money is the probability of the spot price being more than the strike price. The probability of profit is the probability of the spot price being greater than the strike price plus what you paid for the option. So to get POP for a particular strike price, you should find delta for the option whose strike price is the first strike price plus the current option value for that strike price.
So, as a rough approximation for the POP for an option with strike price S_1 and option price p_1, take (p_2-p_3)/h, where:
p_2 is the option price at strike price (S_1+p_1)
p_2 is the option price at strike price (S_1+p+h)
h is a small value.
If an approximate calculation will work for you, then you can check out the below article:
They've shared it directly for some strategies as shown below:
Also I guess, if one knows the logic behind the construction of any option strategy, this article will be a great help to reach to a workable PoP calculation.