Calculate the probability of making money in an option trade with this free Excel spreadsheet.
Buying and selling options is risky, and traders need tools to help to gauge the probability of success.
Many techniques exist, but the simplest is based upon understanding the math behind a normal distribution curve
These equations gives the probability of a successful trade for a
- European put finishing in the money (that is, the probability that the strike price is above the market price at maturity).
- European call finishing in the money (that is, the probability that the strike price is below the market price at maturity)
- N() is the normal distribution function, and is equivalent to Excel’s NORMSDIST() function
- S is the stock price
- X is the strike price
- v is the implied volatility
- T is the time to expiry
These equations are closely related to the Delta of an option. Traders often use delta as approximation of the likelihood of an option finishing in the money.
Delta is given by this equation,
where r is the risk-free rate. As you can see, Pcall, Pput and Δ are closely related.
A delta of 1 indicates that the option price moves in lock-step with the stock price. A delta of 1 also means that the option will be in the money at expiration.
However, delta assumes that stock prices have a log-normal distribution. This isn’t, however, always the case.
This spreadsheet implements the equations above and helps you calculate the probability that your trade will be successful. The spreadsheet also calculates the desired stock price range for a winning trade, given a probability
Download Excel Spreadsheet to Calculate Success Probability for Option Trading
The feature described in your last sentence, “The spreadsheet also calculates the desired stock price range for a winning trade, given a probability” does not seem to work… Care to look at it?
Try removing the “/100” in the formulas for lower and upper stock price (Cells B23 and C23 in the original file) and it should work.The resulting formulas will look like this:
Lower Stock Price
=EXP(($C$7)*SQRT($C$8/365)*-1*NORMSINV(A23))*$C$5
Upper Stock Price
=EXP(($C$6)*SQRT($C$8/365)*NORMSINV(A23))*$C$5
If I want to know the lower strike price and the upper strike price for a particular winning probability, what call and put IV should I use? The ones for the ATM options?
Is the formula assuming a daily implied volatility? If I had a 30 day implied volatiliy would that change the formula? In that case what should I divide by in the sqrt(T)?
Hi, thanks for the spreadsheet! However, the “Probability of Stock Expiring in Range” doesn’t work as expected.
If I use $25 stock, 50 days, 40% call/put and 99% chance of winning it returns upper/lower ranges of 25.09 and 24.91. Seems way too tight for a 99% chance of success?
I had a similar result testing spy at 205 . I’m using iPad excel . I took out the divide by 100 of volatility in the formula and seems a fix .
Also , the normdist function in my spreadsheets seems to take several values like mean , deviations etc. your function seems to use one cell . Can you explain ?
Hi Dasinv, I have the same issue. The Stokcs expiring in a range calculator does not seem to work. Would you have a fix for it? as it’s otherwise a great tool. Thnx, Bulent
You state that the log normal distribution isn’t always a good approximation for stock prices . Having traded a while I see this with black swan long tails . Could we adapt this to utilise a distribution that more accurately matches the stock .
A) what distribution would we use?
B) could we enter historical data to create a custom distribution curve that better matches
C) once we have this distribution how do we replace the normdist with the new curve ?
Lovely work btw .
Thank you very much for this spreadsheet and for the fix.
And what if i want to find What is the probability percent that the price remains between 940 – 1020
I entered in the following info.
Current Stock Price = 25
Call Implied Volatility = 40%
Put Implied Volatility = 40%
Days to Expiration = 50
None of the probablilities recalculate.
What am I doing wrong?
I don’t understand how to interpret these numbers. Terms used in the explanation above are not the terms used in the example. Here’s what I understand looking at the formulas:
– (Formula) Stock Price = (Example) Target Stock Price
– (Formula) Strike Price = (Example) Current Stock Price
Looking at Call Probability, if I put the Target Stock Price to 2000, the” Winning Probability” gives 100% and “Close Above Target” is 0%. Looks like “Winning Probability” should say “Losing Probability”.
Am I missing something?