Implied volatility is a widely used parameter for the market price of any commodity. In this article, we will discuss about implied volatility and show 2 simple methods to calculate implied volatility in Excel.
Download Practice Workbook
You can download the practice workbook from here.
What Is Implied Volatility?
Implied volatility is the probability of market price fluctuation for a commodity. It is frequently used to price option contracts where high implied volatility refers to the premium price and vice versa. It depends on supply, demand and time value mainly. We can determine the option price by using Black Scholes Model which will utilize the implied volatility, current stock price, strike price, and time until expiration. And we can back-calculate the implied volatility for a fixed option price using the Black Scholes Model.
The formula used in the model is:
- C is the Option Premium.
- S is the Price of the stock.
- K is the Strike Price.
- r is the Risk-free Rate.
- t is the Time to Maturity.
- e is the exponential term.
- d1 is the Conditional Probability, d2 is the probability for the option to expire on money.
- N(d1) and N(d2) are the statistical measures (normal distribution) for the values of d1 and d2 respectively.
2 Simple Methods to Calculate Implied Volatility in Excel
In this section, we will use the Black Scholes Model formula to determine the implied volatility for a fixed option price with 2 simple methods. In the first method, we will iterate manually and use the Goal Seek feature of Excel in the second method.
1. Calculate Implied Volatility for Specific Call Option Price by Iteration
We can calculate the call option price using the Black Scholes Model formula. Later on, we will change the implied volatility until the Option Price matches our expected value. Follow the stepwise procedures given below for this method.
- Firstly, include the data for the Underlying Price, Strike Price, Volatility, Time to Maturity, and Risk-free Rate for 2 cases.
- Also, in the second case, the Volatility should be slightly different for calculating Implied Volatility for a specific Target Call Option Price.
- Secondly, calculate the conditional probability d1 by using the following formula in Cell C11.
Note: The formula is derived from Black Scholes Model.
- Then, apply the following formula in Cell C12 to determine the probability for the option to expire on money (d2).
Here, we have used the SQRT function to determine the square root of Cell C8.
- Afterward, calculate the statistical measures (normal distribution) N(d1) in Cell C13 by writing the following formula there.
Here, we have used the NORM.S.DIST function to calculate the normal distribution in Excel.
- Further, write the following formula in Cell C14 to get the statistical measures (normal distribution) N(d2).
- Afterward, let’s calculate the Call Option Price in Cell C15 by writing the following formula there.
- Later on, calculate the same parameters for the second case like in the picture below.
- We can see the Call Option Price for both cases is a bit different than Target Call Option Price.
- Now, let’s use the Call Option Prices to determine our Implied Volatility for Target Call Option Price.
- Finally, write the following formula in Cell F17 to calculate the approximate Implied Volatility there.
Hurrah! We have successfully determined the Implied Volatility for Target Call Option Price.
Read More: How to Calculate Realized Volatility in Excel (with Easy Steps)
2. Use Goal Seek Feature to Calculate Implied Volatility in Excel
Instead of calculating the implied volatility by manual iteration, we can use the Goal Seek feature of Excel to do the same task. Also, this method is much simpler and more accurate. Let’s follow the stepwise procedures given below for this method.
- Primarily, fill up some input data for Underlying Price, Strike Price, Implied Volatility (arbitrary), Time to Maturity (in years), and Risk-free Rate.
- Afterward, calculate the data for d1, d2, N(d1), N(d2) and Call Option Price following any case from Method 1.
- Then, we are ready to use the Goal Seek feature.
- Further, go to the Data tab and select What-If Analysis > Goal Seek.
- Consecutively, a Goal Seek window will appear.
- Afterward, write the cell reference $C$15 (Target Call Option Price) in the Set to section.
- Also, you can select the cell by clicking on the upload icon beside Set to.
- Then, give the Target Call Option Value (we set 50) in the To Value section.
- Again, write the cell reference $C$7 (Implied Volatility) in By changing cell section.
- Also, press OK.
- Finally, we will see the adjusted Implied Volatility for our Target Call Option Price.
Read More: How to Calculate Historical Volatility in Excel (with Easy Steps)
Implied volatility is widely used for forecasting the future price of a commodity. In this article, we have shown 2 simple methods to calculate implied volatility in Excel. If you have any queries or suggestions, let us know in the comment section. Visit our ExcelDemy Website for similar articles regarding Excel.