How to Create an Option Probability Calculator in Excel

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:

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:

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:

• 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:

• 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:

• 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:

• 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:

• Press Enter.

• Next, select cell G7 and enter this formula:

• 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