How to Calculate Volatility in Excel (2 Suitable Ways)

What Is Volatility?

Volatility is the measure of the fluctuations of return of any stock or security over a time period. It is calculated based on the standard deviation or variance of returns over a time period. In the financial market, when the stock index increases or decreases more than 1% over a limited time, that is called a volatile market.

Types of Volatility:

  • Historical Volatility:

This calculates volatility based on past information. It predicts the future by analyzing past values. It does not consider future trends, market conditions, or needs. But It is not forward-looking. It is also called historical volatility.

  • Implied Volatility:

The implied volatility is forward-looking. It does not care the past performance and considers future expectations. It is also called projected volatility.


How to Calculate Volatility in Excel? (Both Historical and Implied Volatility)

Method 1 – Calculating Historical Volatility in Excel

We will collect stock data from Yahoo Finance.

Steps:

  • We collected the following sample data.

  • We added a row on the right side.
  • Use the following formula on cell D6.
=C6/C5-1

Calculate Return for Volatility in Excel

We compared the price of the present day with the previous day and subtracted 1. We can also use the following formula based on the LN function.

=LN(C6/C5
  • Select range D6:D19.
  • Choose Percentage from the Number group of the Home tab.

  • Add two new columns to the dataset.

  • Put the following formula in cell F6 for standard deviation.
=STDEV.S(D6:D19) 

Calculate Standard deviation for Historical Volatility

  • Use the standard deviation to calculate volatility. Apply the following formula in cell G6.
=$F$6*SQRT(252) 

Calculate Historical Volatility in Excel

We calculated the historical volatility. This volatility is also called annualized volatility as we used 252 in the equation. Here, the value of standard deviation is also called daily volatility. If we want to get monthly volatility, we need to use 22. The formula will look like this:

=$F$6*SQRT(22) 

Method 2 – Calculating the Implied Volatility

To calculate implied volatility, we need to follow the Black Scholes Model:

V = SN (P1) – N (P2) Ke^(-rt)
  • V =Option Premium.
  • S = Price of the stock.
  • K = Strike Price.
  • r = Risk-free Rate.
  • t = Maturity Time.
  • e =Exponential term.
  • P1 =Conditional Probability,
  • P2= Probability of the option to expire on money.
  • N(P1), N(P2) = Normal distribution of P1 and P2 respectively.

Steps:

  • Insert data on Underlaying Price, Strike Price, Volatility, Maturity Time, and Risk-Free-Rate in the dataset for two cases.

  • The value of volatility is different in the two cases, and the rest are the same.
  • Calculate conditional probability P1 using the following formula.
  =(LN(C5/C6)+(C8+0.5*C7^2)*C9)/(C7*SQRT(C9))

=C10-C7*SQRT(C9) 

  • Calculate the normalized distribution on cells C12 and C13 using the following formulas:

For Cell C12:

  =NORM.S.DIST(C10,1)

For Cell C13:

  =NORM.S.DIST(C11,1)

Calculate PDF for Implied Volatility

  • Use the NORM.S.DIST function to calculate the normal distribution for N(P1) and N(P2).
  • Calculate the Call Option Price using this formula:
=C5*C12-C6*EXP(-C8*C9)*C13 

  • Repeat for Case 2.

  • Add two new rows and set the Target Call Option Price to $65.

  • Use the following formula to get the implied volatility based on the Black Scholes Model.
=C7+(C16-C14)/(F14-C14)*(F7-C7) 

Calculate Implied Volatility in Excel


How to Calculate the Volatility of a Portfolio in Excel

We will consider the closing stock price of Microsoft and Tata Motors for the last 10 days. The weight of Microsoft is 82%, and Tata Motors is 18%.

Steps:

  • Insert data from Microsoft and Tata Motors into the dataset.

  • Add two new columns in the dataset for calculating returns.
  • Apply the formulas based on the LN function to get a return.

For Cell D6:

=LN(C6/C5)

For Cell F6:

=LN(E6/E5)

Calculate return of portfolio Volatility

  • Calculate the weighted return with the following formula:
=82%*D6+18%*F6 

  • Add a new row 16 in the dataset for Portfolio Volatility.
  • Insert the following formula in cell D16.
=STDEV.S(G6:G14)

Calculate Portfolio Volatility

This is also called the daily portfolio volatility. If we want monthly or annualized volatility, we need to multiply by the square root of 22 and 252, respectively.


Download the Practice Workbook


Volatility In Excel: Knowledge Hub


<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo