**Implied volatility** is a widely used parameter for the market price of any commodity. In this article, we will discuss about implied volatility and show **2** simple methods to **calculate implied volatility in Excel**.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from here.

**What Is Implied Volatility?**

Implied volatility is the probability of market price fluctuation for a commodity. It is frequently used to price option contracts where high implied volatility refers to the premium price and vice versa. It depends on supply, demand and time value mainly. We can determine the option price by using **Black Scholes Model** which will utilize the implied volatility, current stock price, strike price, and time until expiration. And we can back-calculate the implied volatility for a fixed option price using the **Black Scholes Model**.

The formula used in the model is:

**C = SN (d1) – N (d2) Ke^(-rt)**

Here,

**C**is the**Option Premium**.**S**is the**Price**of the stock.**K**is the**Strike Price**.**r**is the**Risk-free Rate**.**t**is the**Time to Maturity**.**e**is the exponential term.**d1**is the**Conditional Probability**,**d2**is the probability for the option to expire on money.**N(d1)**and**N(d2)**are the statistical measures (normal distribution) for the values of**d1**and**d2**respectively.

**2 Simple Methods to Calculate Implied Volatility in Excel**

In this section, we will use the **Black Scholes Model** formula to determine the implied volatility for a fixed option price with **2 **simple methods. In the first method, we will iterate manually and use the **Goal Seek** feature of **Excel** in the second method.

**1. Calculate Implied Volatility for Specific Call Option Price by Iteration**

We can calculate the call option price using the **Black Scholes Model** formula. Later on, we will change the **implied volatility** until the **Option Price** matches our expected value. Follow the stepwise procedures given below for this method.

- Firstly, include the data for the
**Underlying Price**,**Strike Price**,**Volatility**,**Time to Maturity**, and**Risk-free Rate**for**2**cases. - Also, in the second case, the
**Volatility**should be slightly different for calculating**Implied Volatility**for a specific**Target Call Option Price**.

- Secondly, calculate the conditional probability
**d1**by using the following formula in**Cell C11**.

`=(LN(C5/C6)+(C9+0.5*C7^2)*C8)/(C7*SQRT(C8))`

**Note: **The formula is derived from **Black Scholes Model**.

- Then, apply the following formula in
**Cell C12**to determine the probability for the option to expire on money (**d2**).

`=C11-C7*SQRT(C8)`

Here, we have used **the SQRT function **to determine the square root of **Cell C8**.

- Afterward, calculate the statistical measures (normal distribution)
**N(d1)**in**Cell C13**by writing the following formula there.

`=NORM.S.DIST(C11,1)`

Here, we have used **the NORM.S.DIST function **to calculate the normal distribution in Excel.

- Further, write the following formula in
**Cell C14**to get the statistical measures (normal distribution)**N(d2)**.

`=NORM.S.DIST(C12,1)`

- Afterward, let’s calculate the
**Call Option Price**in**Cell C15**by writing the following formula there.

`=C5*C13-C6*EXP(-C9*C8)*C14`

- Later on, calculate the same parameters for the second case like in the picture below.

- We can see the
**Call Option Price**for both cases is a bit different than**Target Call Option Price**. - Now, let’s use the
**Call Option Prices**to determine our**Implied Volatility**for**Target Call Option Price**. - Finally, write the following formula in
**Cell F17**to calculate the approximate**Implied Volatility**there.

`=C7+(C17-C15)/(F15-C15)*(F7-C7)`

Hurrah! We have successfully determined the **Implied Volatility** for **Target Call Option Price**.

**Read More: How to Calculate Realized Volatility in Excel (with Easy Steps)**

**2. Use Goal Seek Feature to Calculate Implied Volatility in Excel**

Instead of calculating the implied volatility by manual iteration, we can use the **Goal Seek** feature of **Excel** to do the same task. Also, this method is much simpler and more accurate. Let’s follow the stepwise procedures given below for this method.

- Primarily, fill up some input data for
**Underlying Price**,**Strike Price**,**Implied Volatility**(arbitrary),**Time to Maturity (in years)**, and**Risk-free Rate**.

- Afterward, calculate the data for
**d1**,**d2**,**N(d1)**,**N(d2)**and**Call Option Price**following any case from**Method 1**.

- Then, we are ready to use the
**Goal Seek**feature. - Further, go to the
**Data**tab and select**What-If Analysis**>**Goal Seek**.

- Consecutively, a
**Goal Seek**window will appear. - Afterward, write the cell reference
**$C$15**(**Target Call Option Price**) in the**Set to**section. - Also, you can select the cell by clicking on the upload icon beside
**Set to**. - Then, give the
**Target Call Option Value**(we set**50**) in the**To Value**section. - Again, write the cell reference
**$C$7**(**Implied Volatility**) in**By changing cell**section. - Also, press
**OK**.

- Finally, we will see the adjusted
**Implied Volatility**for our**Target Call Option Price**.

**Read More: ****How to Calculate Historical Volatility in Excel (with Easy Steps)**

**Conclusion**

Implied volatility is widely used for forecasting the future price of a commodity. In this article, we have shown **2** simple methods to calculate implied volatility in **Excel**. If you have any queries or suggestions, let us know in the comment section. Visit our **ExcelDemy Website **for similar articles regarding **Excel**.