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_{1 }and d_{2 }first. We can calculate d_{1 }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.

**Read More:Â **How to Make a Probability Tree Diagram in Excel

### 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.

**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.

`=(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.

**Read More:Â **How to Calculate Empirical Probability with Excel Formula

**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.

## 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.

thank you sir , nice post.

need to ask about the “current volatility” means realised volatility or implied volatility ?

Hello AMIT,

I appreciate your question. I’d like to clarify that in this article, the expression “

Current Volatility” refers specifically toImplied Volatility, rather thanRealised Volatility. When working withOption Probability, it’s generally more advantageous to useImplied Volatilityrather thanRealised Volatilitysince 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

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