Option probability is an important concept in trading from both the seller’s and buyer’s perspectives. These values are also helpful in volatile markets, both high and low volatile ones. In this article, we will discuss how to create an option probability calculator in Excel. Also, you can download and use the workbook to use it as a template.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below either to try it yourself or use it as a template.
Overview of Option Probability
By definition, probability is the chance of an event occurring among all the possible outcomes. So, the option probability indicates the probability of an option existing in the money (ITM) or out of the money (OTM) in the time period before expiration.
We call an option in the money when the strike price is lower than the stock market’s price and out the money when it is above. This term describes the intrinsic value of an option. In the money has positive and out the money has negative intrinsic value. The former is more likely to be exercised and costs more. At the same time, the latter has a lower premium option and is less likely to be exercised.
The option probability formula is:
Where, UP = Underlying Price
SP = Strike Price
V = Current Volatility
t = Time of expiration (% of the year value)
Once we find that, we can calculate the call probability by the following formula.
And the put probability by:
There is also another approach to calculating this probability. This is according to the Black-Scholes theorem. According to that, we need to calculate the auxiliary parameters d1 and d2 first. We can calculate d1 by the following formula.
To calculate, we need to use the following formula.
Then we can calculate the call price using this formula.
After that, we can calculate the call probability function for that.
Next, we need to put price probability after calculating the put price from this formula.
Here, Y = Underlying Price
Z = Strike Price
V = Volatility
i = Interest ration (in %)
d = Dividend Value
t = Time of Expiration
And N() indicates the normal distribution of the parameter in the parentheses.
Step-by-Step Procedure to Create an Option Probability Calculator in Excel
Now we will put the theories and formulas described above into use and make an option probability calculator out of it in Excel. We will use different functions to make formulas that can help us with these calculations. For a better understanding, we have divided the process into different steps. Follow along to see how we can create an option probability calculator in Excel.
Step 1: Prepare Spreadsheet for Particulars
First, we will prepare the spreadsheet to insert the particulars and have a place for the outputs. So that it can work as our desired calculator. We have opted to go for the following.
We have placed cells for inputs on the left and left some cells for outputs on the right.
Step 2: Insert Input Values
Now let’s insert the input values. This is not a necessary step. But we will do that to avoid errors showing on the spreadsheet for now.
If you don’t insert values here, the output cells on the right will show some errors. But if your formulas from the following steps are correct, you can continue on. Excel will correct them automatically once you will enter input values later.
Step 3: Calculate Delta Value for Call Option
Now we will calculate the delta value for the call option as a part of the option probability calculator in Excel. This delta value will indicate the probability of the particular. We are gonna use a combination of EXP, NORM.DIST, LN, POWER, and SQRT functions to formulate the formula for the purpose.
Follow these steps to see how we can calculate that.
- select the cell you want to store the delta value for the call option. Here, we have selected cell F5.
- Then enter the following formula in the cell.
- After that, press Enter.
This way you can calculate the delta value for the call option of the probability calculator in Excel.
- How to Use Continuous Probability Distribution in Excel
- Create Joint Probability Table in Excel (with Easy Steps)
- How to Get Simulation Probability in Excel (with Easy Steps)
- Find Discrete Probability Distribution in Excel
- How to Model Uniform Probability Distribution in Excel (3 Ways)
Step 4: Compute Delta Value for Put Option
Similarly, we will calculate the delta value for the put option for the input values. Here, we will use EXP, NORM.DIST, LN, POWER, and SQRT functions too for this sake.
We will use kind of similar steps:
- First, select cell G5.
- Then write down the following formula.
- Finally, press Enter.
Step 5: Calculate Probability from Delta Values
These delta values we have just calculated are the probabilities themselves. But these are probabilities calculated relative to 1. If we convert the values per 100 as we can see in the formulas we can use the following steps. We will additionally need the ABS function to convert the negative put option to a positive one.
- First, select cell F6 and insert the following formula.
- Then press Enter. This is the probability from the delta for the call option.
- To calculate the same for the put option, select cell G6 and write down the following formula.
- Finally, press Enter.
This way we can create the option probability calculator and calculate such in Excel.
Read More: How to Calculate Conditional Probability in Excel (2 Easy Ways)
Step 6: Determine Probability from Black-Scholes Approximation
Now let’s calculate the call and put options using the Black-Scholes theorem. This isn’t really required. But we are adding it to compare it with the values from the previous ones. We will follow a similar approach for the option probability values in this calculator in Excel. In the following steps, we will utilize combinations of NORMIDST, LN, and SQRT functions.
- First, select cell F7 and write down the following formula.
- Then press Enter.
- Next, select cell G7 and write down the following formula.
- Finally, press Enter.
This way we can also find the probabilities from the Black-Scholes approach in the option probability calculator in Excel.
Let’s finally add some modifications to the spreadsheet to make it stand out.
Read More: How to Calculate Probability in Excel (3 Practical Examples)
So this is how we can create an option probability calculator in Excel. Hopefully, you have grasped the idea of the formulas and the concept of creating such calculators. If you wish, you can download the workbook from the download section and use it as your template. All you need to do is insert your values in the inputs section.
I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below. For more guides like this, visit Exceldemy.com.
- How to Find Marginal Probability in Excel (2 Simple Ways)
- How to Calculate Empirical Probability with Excel Formula
- Calculating Probability in Excel with Mean and Standard Deviation
- How to Make a Probability Tree Diagram in Excel (3 Easy Methods)
- How to Calculate Probability of Exceedance in Excel
- Poisson Probability Distribution in Excel (4 Practical Examples)
thank you sir , nice post.
need to ask about the “current volatility” means realised volatility or implied volatility ?
I appreciate your question. I’d like to clarify that in this article, the expression “Current Volatility” refers specifically to Implied Volatility, rather than Realised Volatility. When working with Option Probability, it’s generally more advantageous to use Implied Volatility rather than Realised Volatility since it enables us to make more precise predictions about the projected price range of a stock in the future.
I hope this answers your question. If you have any more queries, please let us know.
not a finance person but as i understand it so far , the realized volatility will generate a probability based on the past and implied Vol on what the market thinks and is betting on the probability to be for the contract end. the difference between the 2 is a regular trade strategy is referred to as “variance risk premium” .
and again i love the detailed post of yours.