Undoubtedly, Excel’s ability to crunch numbers and plot three-dimensional graphs can help us better understand and represent the volatility of stocks. With this in mind, this article shows the process of how to generate volatility surface in Excel in detail.

**Table of Contents**hide

## What Is Volatility Surface?

In a nutshell, the Volatility Surface is a three-dimensional representation of *“Time”* to maturity, the *“Strike”* price, and the *“Implied Volatility”.* Ideally, the implied volatility surface should be plain, however, in practice, it is usually curved due to limitations in the *Black-Scholes model*.

As a note, we’ll use the *Dumas, Fleming, and Whaley* specifications to model *Implied Volatility *using the formula given below.

where,

- K is the Strike price
- T is the Time to maturity in Years
- a
_{0}, a_{1}, a_{2}, a_{3}, a_{4}, and a_{5}are the Coefficients

## How to Generate Volatility Surface in Excel: with Detailed Steps

First of all, let’s suppose the *Stock Calls Information of Amazon* shown in the **B4:F19** cells. Here, the dataset depicts the *“Strike”* price, *“Bid”* and *“Ask”* prices, *“Start Date”*, and *“End Date”* columns. In addition, we have the *“Stock Price”*, *“Risk-Free Rate”*, and *“Div & Yield”* percentages. So, without further delay, let’s see each step on how to generate a volatility surface in Excel with the necessary illustrations.

Here, we have used the *Microsoft 365* version; you may use any other version according to your convenience.

### 📌 Step 1: Calculate Time to Maturity

- First, go to the
**H5**cell >> subtract the value in the**F5**cell from the value in the**G5**cell.

`=G5-F5`

In this case, the **F5** and **G5** cells indicate the *“Start”* and *“End Dates”* respectively.

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

### 📌 Step 2: Obtain Implied Volatility with VBA Code

- Second, move to the
**Developer**tab >> click the**Visual Basic**button.

This now launches the **Visual Basic Editor** in a new window.

- Next, press the
**Insert**tab >> select**Module**.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

```
Function Black_Scholes(Stock_price, Strike, Risk_free_rate, Div_Yield, Sigma_val, Time)
Dim D_1, D_2, n_D_1, n_D_2
D_1 = (Log(Stock_price / Strike) + (Risk_free_rate - Div_Yield + 0.5 * Sigma_val ^ 2) * Time) / (Sigma_val * Sqr(Time))
D_2 = D_1 - Sigma_val * Sqr(Time)
n_D_1 = Application.NormSDist(D_1)
n_D_2 = Application.NormSDist(D_2)
Black_Scholes = Exp(-Div_Yield * Time) * Stock_price * n_D_1 - Exp(-Risk_free_rate * Time) * Strike * n_D_2
End Function
Function Bisection_method(Stock_price, Strike, Risk_free_rate, Div_Yield, Time, callmktprice)
x = 5
y = 0
Do While (x - y) > 0.00000001
If Black_Scholes(Stock_price, Strike, Risk_free_rate, Div_Yield, (x + y) / 2, Time) > callmktprice Then
x = (x + y) / 2
Else: y = (x + y) / 2
End If
Loop
Bisection_method = (x + y) / 2
End Function
```

**Code Breakdown**

At this point, we’ll explain the **VBA** code to compute the implied volatility.

- In the first portion, the function is given a name, here it is
**Black_Scholes()**and takes the arguments*“Stock_price”*,*“Strike”*,*“Risk_free_rate”*,*“Dive_Yield”*,*“Sigma_val”*, and*“Time”*to expiry. - Next, define the variables
**D_1**,**D_2**,**n_D_1**, and**n_D_2**. - Then, assign the formulas shown below to the
**D_1**and**D_2**variables and use the**NormSDist function**to the**n_D_1**, and**n_D_2**variables. - Lastly, enter the expression into the
**Black_Scholes**variable. - In the later portion, name the function
**Bisection_method**and assign the values**1**and**0**to**x**and**y**. - Following this, combine
**Do While**and**If**statements to obtain the implied volatility.

- Not long after, close the
**VBA**window >> call the**Bisection_method**function to return the*“Implied Volatility”*>> use the**Fill Handle**tool to drag the formula to the cells below.

`=Bisection_method(B5,C5,0.02,0.018,H5/365,(D5+E5)/2)`

For instance, the **B5**, **C5**, **D5**, **E5**, and **H5** cells point to the *“Stock”*, *“Strike”*, *“Bid”*, *“Ask”*, and *“Time”* columns respectively.

**Read More: **How to Calculate Volatility for Black Scholes in Excel

### 📌 Step 3: Compute the Coefficients for Implied Volatility Fit

- Third, copy and paste the
*“Strike”*price in column**J**>> navigate to the**K5**cell >> obtain the square of the*“Strike”*price.

`=J5^2`

Here, the **J5** cell represents the *“Strike”* price of *“75”*.

- Now, insert a
*“Time”*column >> get the*“Time”*in years using the formula below.

`=H5/365`

In this situation, the **H5** cell refers to the *“Time”* in days, and the *“365” *is the number of days in a year.

- In a similar style, calculate the
*“Time”*squared value.

`=(H5/365)^2`

- Afterward, compute the product of the
*“Strike”*and*“Time”*in the**N5**cell.

`=J5*L5`

In this scenario, the **J5** and **L5** cells indicate the *“Strike”* and *“Time”*.

- Consequently, generate the values of the coefficients using
**the LINEST function**.

`=LINEST(I5:I19,J5:N19,TRUE,TRUE)`

**Formula Breakdown**

**LINEST(I5:I19,J5:N19,TRUE,TRUE)**→ returns statistics that describe a linear trend matching known data points, by fitting a straight line using the least squares method. Here, the**I5:I19**and**J5:N19**are theand*known_x*arguments that refer to the*known_y**“Implied Volatility”*column and the*“Strike”*,*“Strike Squared”*, Time,*“Time Squared”*, and*“Strike*Time”*columns respectively. Next,**TRUE**points to the optionalargument that calculates the constant value normally. Lastly,*const***TRUE**represents the optionalargument which returns additional regression statistics.*stat*

- Lastly, we can calculate the
*“Implied Volatility Fit”*using the Dumas, Fleming, and Whaley equation.

`=$G$23+$F$23*J5+$E$23*K5+$D$23*L5+$C$23*M5+$B$23*N5`

Now, in the above expression, the **G23**, **F23**, **E23**, **D23**, **C23**, **B23 **cells point to the coefficients* “a*_{0}*, a*_{1}*, a*_{2}*, a*_{3}*, a*_{4}*, and a*_{5}*”* respectively. Moreover, the **J5**, **K5**, **L5**, **M5**, and **N5 **cells represent the *“Strike”*, *“Strike Squared”*, Time, *“Time Squared”*, and *“Strike*Time”* columns, correspondingly.

📃 *Note:**Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

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

### 📌 Step 4: Generate the Table for Volatility Surface

- Fourth, copy and paste the Dumas, Fleming, and Whaley equation into the
**J22**cell >> construct a table with varying values of*“Strike price, K”*and*“Time, T”*.

- Following this, select the
**J22:O27**cells >> jump to the**Data**tab >> click**What-If Analysis**>> choose**Data Table**.

- Next, enter the
**J5**and**L5**cell references as the Row and Column input cells.

Eventually, the table should resemble the image shown below.

### 📌 Step 5: Plot Volatility Surface

- Fifth, copy the table >> paste it in the
**I31**cell as shown below >> remove the top value in the**J32**cell.

- In turn, choose the
**J32:O37**cells >> proceed to the**Insert**tab >> press**Recommended Charts**.

- At this point, select the
**All Charts**tab >> choose**3-D Surface**from the list >> hit the**OK**button.

- Then, select the chart >> choose a
**Chart Style**according to your preference.

Afterward, format the chart using the **Chart Elements** option.

- In addition to the default selection, you can enable the
**Axes Title**to provide axes names. Here, it is*“Strike”*,*“Time”*, and*“Implied Volatility”*. - Now, add the
**Chart Title**, for example,*“Volatility Surface”*. - Lastly, you can enable the
**Gridlines**option.

- Furthermore, right-click on the chart >> navigate to
**3-D Rotation**.

- Next, in the
**Format Chart Area**pane, enter the**X**and**Y****Rotations**and**Perspective**values as shown in the picture below.

Subsequently, the final output should appear in the figure shown below.

## Practice Section

We have provided a *Practice* section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

In essence, this article shows a step-by-step guide on how to generate a volatility surface in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful, and if you have any further queries or recommendations, please feel free to comment here.