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

Calculate the call option price using the Black Scholes Model formula. Change the implied volatility until the *Option Price* matches our expected value.Â

- Include the data for the
*Underlying Price, Strike Price, Volatility, Time to Maturity, and Risk-free Rate*for 2 cases. - The Volatility should be slightly different when calculating Implied Volatility for a specific
*Target Call Option Price*.

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

- Apply the following formula in
**cell C12**to determine the probability for the option to expire on money (**d2**).

`=C11-C7*SQRT(C8)`

We used **the SQRT function **to determine the square root of **cell C8**.

- Calculate the statistical measures (normal distribution)
**N(d1)**in**cell C13**by writing the following formula.

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

We used the** NORM.S.DIST** function to calculate the normal distribution in Excel.

- Write the following formula in
**cell C14**to get the statistical measures (normal distribution)**N(d2)**.

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

- Calculate the
*Call Option Price*in**cell C15**by writing the following formula.

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

- Calculate the same parameters for the second case, like in the picture below.

- The
*Call Option Price*for both cases is a bit different than*Target Call Option Price*. - Use the
*Call Option Prices*to determine our*Implied Volatility*for*Target Call Option Price.* - Write the following formula in
**cell F17**to calculate the approximate*Implied Volatility*.

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

**Method 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. This method is much simpler and more accurate. Follow the stepwise procedures given below for this method.

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

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

- Use the
**Goal Seek**feature. - Go to the
**Data**tab and select**What-If Analysis**>**Goal Seek**.

- A
**Goal Seek**window will appear. - Write the cell reference
**$C$15**(**Target Call Option Price**) in the**Set to**section. - Select the cell by clicking on the upload icon beside
**Set to**. - Give the
**Target Call Option Value**(we set**50**) in the**To Value**section. - Write the cell reference
**$C$7**(**Implied Volatility**)**by changing cell**section. - Press
**OK**.

- See the adjusted
**Implied Volatility**for our**Target Call Option Price**.

**Download Practice Workbook**

You can download the practice workbook from here.

## Related Articles

- How to Generate Volatility Surface in Excel
- How to Calculate Annualized Volatility in Excel
- How to Calculate Daily Volatility in Excel
- How to Calculate Historical Volatility in Excel

**<< Go Back to ****Volatility In Excel ****|** **Excel for Finance**** | ****Learn Excel**