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.
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.
How to Create an Option Probability Calculator in Excel: Step-by-Step Procedure
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.
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 the 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 Apply Weighted Probability in Excel
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.
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.
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.