Sometimes you may need to **calculate volatility** using the **Black-Scholes** formula in Excel. So, if you are looking for how to calculate **volatility** for the **Black-Scholes** in Excel, then you have come to the right place. Today, in this article, I’m going to compute the **volatility** using the **Black-Scholes** model in Excel.

Furthermore, for conducting the session, I will use **Microsoft 365 version**.

## Download Practice Workbook

You can download the practice workbook from here:

## 2 Methods to Calculate Volatility for Black Scholes in Excel

Here, I will describe **2 **suitable methods for calculating **volatility** for **Black-Scholes** in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains **two** columns. These columns represent some monetary information about future market value. The dataset is given below.

### 1. Applying Trial and Error Process for Calculating Volatility in Excel

Here, you can use some formulas for doing a **Trial and Error** process to compute **volatility** for **Black Scholes** in Excel. Additionally, I’m going to use some Excel functions like **LN**, **NORMDIST**, and **EXP**. Now, let’s see the steps.

**Steps:**

- Firstly, assume a
**volatility percentage**in the**C8**cell. Here, I have assumed**30%**. - Secondly, in the
**F6**cell use the following formula to find out the**d1**value.

`=(LN(C6/C7)+(C9-C10+(C8*C8/2))*C11)/(C8*(C11^0.5))`

Actually, this is the formula from the **Black Scholes** model. Where the **LN** function will return the natural logarithm of the ratio of the underlying price and strike price.

- Thirdly, press
**ENTER**to get the**d1**value.

- Similarly, in the
**F7**cell use the following formula to find out the**d2**value.

`=F6-(C8*C11^0.5)`

Actually, this is another formula from the **Black-Scholes** model for finding the value of **d2**.

- Consequently, press
**ENTER**to get the**d2**value.

- After that, use the following formula in the
**F8**cell to find out the**N(d1)**value.

`=NORMDIST(F6,0,1,TRUE)`

- Subsequently, press
**ENTER**to get the**N(d1)**value.

**🔎**** Formula Breakdown:**

- Here, in this formula,
**the NORMDIST function**will return the**normal distribution**from the specified mean and standard deviation. - Then,
**F6**is the supplied value to calculate the distribution. - Furthermore,
**0**is the**mean**value. - Additionally,
**1**is the**standard deviation**. - Lastly,
**TRUE**denotes**cumulative_distribution_function**.

- In the same way, use the corresponding formula in the
**F9**cell to find out the**N(d2)**value.

`=NORMDIST(F7,0,1,TRUE)`

- Then, press
**ENTER**to get the**N(d2)**value.

- Lastly, use the formula given below in the
**F10**cell to find out the**call price**.

`=(C6*EXP(-C10*C11))*F8-C7*EXP(-C9*C11)*F9`

- After that, press
**ENTER**.

**🔎**** Formula Breakdown:**

- Here, in this formula,
**the EXP function**will return the value of the constant**e**raised to the power of a given number. - So,
**EXP(-C9*C11)**gives**0.941764533584249**. - Similarly,
**EXP(-C10*C11)**turns**1**. - Lastly, the rest are general calculations of multiplication and subtraction.

Here, if you notice then you will see I need to compute the volatility for the call price of **$24**. But here, I have found **$18** with a volatility of **30%**.

So, let’s find the required volatility to keep the call price at **$24**.

- Now, at this stage just change the
**volatility percentage**manually in the**C8**cell and notice the**call price**of the**F10**cell. When you get a relevant or the nearest**call price**value compared to a**C4**cell then you should consider that particular**volatility percentage**.

As an example, I have considered the **volatility percentage** of **40%** as it gives the** call price** of **$23.14** which is very close to **$24**.

- So, write that volatility in the
**B14**cell. - Then, write the corresponding
in the*call price***C14**cell.

However, you can use **Excel keyboard shortcuts** **Ctrl+C **and **Ctrl+V** to **copy** and **paste** both values.

- Similarly, choose another
**volatility**in the**C8**cell to find another relevant**call price**. - Then, write that
**volatility**in the**B15**cell. - After that, write the corresponding
in the*call price***C15**cell.

- Finally, use the formula given below in the
**C16**cell to find out the**final volatility**for the**given call price**.

`=B14+(C4-C14)/(C15-C14)*(B15-B14)`

- After that, press
**ENTER**.

Hence, you will get certain** volatility**.

Here, I have shown you another example of this **Trial and Error process** in another worksheet named **Trial Process- Exm 2**. Where the final ** volatility** is

**39.59%**for a

**of**

*call price***$4.23**.

**Read More: ****How to Calculate Implied Volatility in Excel (2 Simple Methods)**

### 2. Use of Goal Seek Feature to Compute Volatility for Black Scholes

Here, you can employ the** Goal Seek **feature under the **Data** tab to compute **volatility** for **Block Scholes** in Excel. Now, let’s see the steps.

**Steps:**

- Firstly, assume a
**volatility percentage**in the**C8**cell. Here, I have assumed**30%**.

- Then, follow the previous method to use the
**Black Scholes**formulas to get the values of**d1**,**d2**,**N(d1)**,**N(d2)**, and**call price**.

- After that, select the
**F10**cell. - Then, from the
**Data**tab >> go to**Forecast**>> then from**What-If Analysis**>> choose**Goal Seek**.

As a result, a new dialog box named** Goal Seek** will appear.

- Firstly, make sure that the
**F10**cell reference is in the**Set cell**box. - Secondly, write
**24**in the**To value**box.*Here, you must write that particular value which is the given***call price**. - Thirdly, select the
**C8**cell in the**By changing cell**box.*Here, you must select that particular cell where the***volatility percentage**was situated. - Lastly, press
**OK**.

Subsequently, another dialog box named** Goal Seek Status** will pop up.

- Here, press
**OK**.

As a result, you will see the correct **volatility percentage** in that** C8** cell for the **call price **of **$24**.

**Read More: ****How to Generate Volatility Surface in Excel (with Detailed Steps)**

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

I hope you found this article helpful. Here, I have described **2** suitable methods **to calculate Volatility for Black Scholes in Excel**. You can visit our website **Exceldemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.