## Overview of Option Probability

Probability represents the likelihood of an event occurring among all possible outcomes. In the context of options, probability refers to the chance that an option will be “in the money” (ITM) or “out of the money” (OTM) before its expiration.

- An option is considered ITM when its strike price is lower than the current stock market price.
- Conversely, an option is OTM when its strike price is higher than the current stock market price.
- The intrinsic value of an option is associated with being ITM or OTM.
- ITM options have positive intrinsic value and are more likely to be exercised (but cost more).
- OTM options have negative intrinsic value and are less likely to be exercised (with a lower premium).

### Option Probability Formula

The option probability can be calculated using the following formula:

Where:

**UP**: Underlying Price**SP**: Strike Price**V**: Current Volatility**t**: Time to expiration (as a percentage of the year)

### Call Probability

To find the call probability, use this formula:

### Put Probability

The put probability can be calculated as:

### Black-Scholes Approach

An alternative method involves the Black-Scholes theorem. First, calculate auxiliary parameters (d_1) and (d_2):

To calculate, we need to use the following formula.

Compute the call price using:

And the put price:

Where:

**Y**: Underlying Price**Z**: Strike Price**V**: Volatility**i**: Interest rate (as a percentage)**t**: Time to Expiration**(N())**represents the normal distribution of the parameter inside the parentheses.

## Creating the Excel Calculator

### Step 1 – Prepare the Spreadsheet

- Set up a spreadsheet with input cells on the left and output cells on the right.

- We’ll use these cells to input particulars and display results.

### Step 2- Insert Input Values

- Enter relevant values (such as underlying price, strike price, volatility, interest rate, and time to expiration) into the input cells.

This step helps avoid errors in the output cells.

### Step 3 – Calculate Delta Value for Call Option

- Select the cell where you want to store the delta value for the call option (e.g., cell
**F5**). - Enter the following formula:

`=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))`

- Press
**Enter**to calculate the delta value.

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’ll calculate the delta value for the put option using the input values. Follow these steps:

- Select cell
**G5**. - Enter 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)`

- Press
**Enter**to calculate the delta value.

### Step 5 – Calculate Probability from Delta Values

The delta values we’ve calculated represent probabilities relative to **1**. To convert them to percentages, follow these steps:

- Select cell
**F6**. - Enter the formula:

`=F5*100`

- Press
**Enter**. This is the probability from the delta for the call option.

- For the put option, select cell
**G6**and enter the following formula:

`=ABS(G5*100)`

- Press
**Enter**to calculate the put option probability.

**Read More: **How to Apply Weighted Probability in Excel

### Step 6 – Determine Probability from Black-Scholes Approximation

Although not strictly necessary, let’s calculate the call and put options using the Black-Scholes theorem for comparison. Follow these steps:

- Select cell
**F7**. - Enter the formula:

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

- Press
**Enter**.

- Next, select cell
**G7**and enter this formula:

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

- Press
**Enter**to find the probabilities from the**Black-Scholes**approach in the option probability calculator.

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 practice workbook from here:

**Related Articles**

- Probability Formula for Lottery in Excel
- How to Calculate Probability Density Function in Excel
- How to Get Simulation Probability in Excel

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

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