How to Calculate Volatility for Black Scholes in Excel (2 Methods)

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.

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.

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.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Calculating Volatility for Black Scholes.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!