How to Create Option Probability Calculator in Excel

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 d₁and d₂first. We can calculate d₁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.

=EXP(-C9*C9/100)*(NORM.DIST((((LN(C5/C6))+((C8-C9+(POWER(C7,2))/2)*(C10/365)/10000))/(C7*SQRT(C10/365)/100)),0,1,TRUE))

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.

=EXP(-C9*(C10/365)/100)*((NORM.DIST((((LN(C5/C6))+((C8-C9+(POWER(C7,2))/2)*(C10/365)/10000))/(C7*SQRT(C10/365)/100)),0,1,TRUE))-1)

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.

=F5*100

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.

=ABS(G5*100)

Finally, press Enter.

This way we can create the option probability calculator and calculate such 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.

=(NORMSDIST((LN(C5/C6))/(C7*SQRT((C10/365)/1000))))*100

Then press Enter.

Next, select cell G7 and write down the following formula.

=(1-(NORMSDIST((LN(C5/C6))/(C7*SQRT((C10/365)/1000)))))*100

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.

Option Probability Calculator.xlsx

Conclusion

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.

<< Go Back to Excel Probability | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

3 Comments

amit Mar 27, 2023 at 8:55 AM

thank you sir , nice post.
need to ask about the “current volatility” means realised volatility or implied volatility ?
- Reply
  Zahid Hasan Mar 27, 2023 at 3:29 PM
  
  Hello AMIT,
  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.
  
  Regards
  Zahid
  ExcelDemy
amit Mar 28, 2023 at 2:48 PM

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.

regards amit