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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Calculate Volatility for Black Scholes in Excel


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo