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

Steps:

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

Applying Trial and Error Process to Calculate Volatility in Excel

=F6-(C8*C11^0.5)

Press ENTER.

=NORMDIST(F6,0,1,TRUE)

Formula Breakdown:

The NORMDIST function will return the normal distribution from the specified mean and standard deviation.
F6 is the supplied value to calculate the distribution.
0 is the mean value.
1 is the standard deviation.
TRUE denotes cumulative_distribution_function.

=NORMDIST(F7,0,1,TRUE)

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

Formula Breakdown:

The EXP function will return the value of the constant e raised to the power of a given number.
EXP(-C9*C11) gives 941764533584249.
EXP(-C10*C11) turns 1.
The remaining are general calculations of multiplication and subtraction.

Calculate Call Price Using Black Scholes Formula in Excel

You can see that we need to compute the volatility for the call price of $24. But we have found $18 with a volatility of 30%.

Let’s find the required volatility to keep the call price at $24.

Change the volatility percentage manually in cell C8 and notice the call price of cell F10. When you get a relevant or the nearest call price value compared to cell C4, 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

You can use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste both values.

Enter the following formula in cell C16 to find out the final volatility for the given call price.

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

How to Calculate Volatility for Black Scholes in Excel

The final volatility is 39.59% for a call price of $4.23.

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

Steps:

Assume Volatility for Using Black Scholes Formulas in Excel

Follow the steps in Method 1 to get the values of d1, d2, N(d1), N(d2) and call price.

Select cell F10.
From the Data tab >> go to Forecast >> from What-If Analysis >> choose Goal Seek.

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

A new dialog box named Goal Seek will open.

Select cell F10 in the Set cell box.
Enter 24 in the To value You must enter the value which is the given call price.
Select cell C8 in the By changing cell You must select the cell where the volatility percentage was situated.
Press OK.