Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Dataset to Calculate Volatility for Black Scholes in Excel


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.

Applying Trial and Error Process to Calculate Volatility in Excel

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

Calculate Call Price Using Black Scholes Formula in Excel

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.

Trial Analysis to Calculate Volatility for Black Scholes in Excel

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

How to Calculate Volatility for Black Scholes in Excel

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.

Calculate Volatility for Black Scholes in Excel with Trial-Error Way

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

Assume Volatility for Using Black Scholes Formulas in Excel

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

Use of Goal Seek Feature to Compute Volatility for Black Scholes in Excel

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.

Calculate Volatility for Black Scholes Using Excel Feature

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


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Calculate Volatility for Black Scholes in Excel


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.


Related Articles

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo