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 the *Microsoft 365* version.

**Table of Contents**Expand

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

Here, I will describe 2 suitable methods to calculate 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
*call price*in the**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
*call price*in the**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 *call price* of $4.23.

**Read More: **How to Calculate Daily Volatility in Excel

### 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 Calculate Implied Volatility in Excel

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have described 2 suitable methods to calculate Volatility for Black Scholes in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

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

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