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