How to Calculate Volatility in Excel?

We know that the prices of different financial assets such as currencies and stocks are constantly fluctuating as traders buy and sell these assets. The variation in the prices over a period of time is called volatility. The volatility tells us about how turbulent the price is and is an indicator of the risk involved. A currency pair with high volatility involves high risk, but is also seen as an opportunity to make profits by the currency traders. If you trade in financial markets, then understanding volatility is important.

In this article, we will look at how the volatility can be calculated using excel. We will take the historical data for S&P 500 for the past three months and use the data to calculate the volatility.

Step 1: Get the Data

We have downloaded the price data for S&P500 in a spreadsheet. The data contains many things such as Close, Open, High, Low, and %change.

What we are interested in is the Closing price, and % change.

Step 2: Calculate Return Series

Note that % Change is calculated using each day’s closing price and represents the return series that is the % change in price from one day to another. Even if the % change is not already given, it can be easly calculated. For example, the closing price on Aug 01, is 1375.32 and on Aug 02, it is 1365. %change on Aug 02 will be 1365/1375.32-1 = -0.75% as shown in the table above.

Our next step is to calculate the standard deviation of the daily returns. In excel the Standard Deviation is calculated using the =StdDev(). This formula takes the range of data as its input such as the % change data. The standard deviation can be calculated for any period such as 10-days, 30-days, or for the entire price.

Our standard deviation for the 3-month data is:

= StdDev(Data range for % Change)

= 0.78%

This standard deviation represents the volatility.

Calculate Annualized Volatility

Note that in the above calculation, we have used the daily data to calculate the standard deviation. This will be the 1-day volatility. We need to convert this into Annualized Volatility. Assuming that there are 252 trading days, the volatility can be annualized using the square root rule, as follows:

Annualized Volatility = 1-day volatility *Sqrt(252)

= 0.78%*Sqrt(252) = 12.43%

Note that if we had used weekly data instead of daily data, we will use Sqrt(52) as there are 52 weeks in a year.

Download the sample excel sheet for calculating volatility.

Tags: ,

3 Responses to “How to Calculate Volatility in Excel?”

  1. Navnita Jayachandran May 9, 2013 at 3:09 am #

    Hello,
    thank u so much for the information. it was really helpful. though i found certain mistakes. I hope you can help me out with it.
    In the %change values, the values in G28, G49 and G50 are different from the actual calculation.

    You said that the %change can be calculated by taking the current day price and dividing it by the previous day price and -1. by doing so all d values are coming same except these 3blocks. the values in these blocks must be -2.92, -1.24 and 3.14 respectively. And if thats the case the1 day volatility becomes 0.92% and the annualized volatility comes as 14.61%.

    • Manish May 20, 2013 at 7:52 pm #

      Hi,

      The values you mentioned are indeed correct. I’ll update the excel sheet with this change. However, note that the application of formula is correct. Thanks!

    • Phil May 29, 2013 at 2:16 am #

      Hi, I have calculated the daily standard deviations for fund returns against its benchmark for 3 years. Am i correct in multiplying this figure by SQRT (252) to get an annualized tracking error?

      Thanks,
      PG

Leave a Reply