How To Calculate Volatility on Excel

Calculating Volatility on Excel

In the earlier chapter, we introduced standard deviation and its role in assessing ‘Risk or Volatility’ for a stock. Before progressing further into this topic, we must understand how to find volatility data. It is not accessible readily so it is most beneficial to know how to calculate it personally.

In the previous chapter, we analysed Billy and Mike’s examples and outlined the steps for calculation.

1. Compute the average
2. Compute the deviation by subtracting the mean from the actual observation
3. Square each deviation and sum them up – this is referred to as variance
4. Calculate the square root of the variance – known as the standard deviation

In the previous chapter, we demonstrated the mechanics of how to calculate the standard deviation. I believe it is essential to understand what lies beneath a formula, as it can only help to expand your understanding. This chapter will teach us a much easier way to compute standard deviation or the volatility of a given stock using MS Excel. As always, Excel does all the hard work for us- all we need to do is press a button!

Let me outline the overall steps involved and then provide further details for each step:

Step 1: Obtain the historical closing prices dataset.

Step 2: Calculate the daily returns.

Step 3: Utilize the STDEV function.

Now, let’s delve into the first step:

Step 1: Acquire the historical closing prices by downloading the relevant dataset.

You can retrieve data from various sources – some of the reliable, free ones include the NSE India website and Yahoo Finance.

I will collect my data from the National Stock Exchange of India. NSE’s website is incredibly useful and provides a wealth of information, making it one of the finest stock exchange websites globally.

In this chapter, we will determine the volatility of Wipro. To retrieve the historical closing prices, you can visit the website http://www.nseindia.com/products/content/equities/equities/equities.htm and click on the ‘historical data’ section. From there, select the desired search option to access the necessary data.

Once you search, a series of fields will open up for you to fill in; the process is easy, simply enter the required information and click ‘Get Data’. Ensure that you are selecting data from the past year, starting with 22nd July 2014 and ending with 21st July 2015.

Once ‘get data’ has been clicked, NSE’s website will search for your desired information and present it to you.

Note that I have eliminated any superfluous data, keeping only the date and end of day pricing.

Step 2 – Calculate Daily Returns

Daily returns can be ascertained by subtracting the previous day’s closing price from the current one.

The return can be calculated using the formula (Ending Price / Beginning Price) – 1. However, for practicality and ease of calculation, this equation can be approximated to the natural logarithm of the ratio of the ending price to the beginning price, denoted as LN (Ending Price / Beginning Price). 

This approximation is commonly referred to as “Log Returns.”

This is a snapshot of the calculation I have done to determine the daily log returns of WIPRO.

I have used the Excel function ‘LN’ to calculate the long returns.

Step 3 – Use the STDEV Function

After computing the daily returns, you can use an excel function ‘STDEV’ to calculate WIPRO’s daily Volatility.

Note: To use the STDEV function, follow these simple steps:

1. Select an empty cell.
2. Type ‘=’ to begin a formula.
3. Enter the function syntax, which is STDEV, followed by an opening bracket.
4. the empty cell would look like =STEDEV(
5. After opening the bracket, choose all the data points for daily returns and then close the bracket.
6. Press enter

Once this is completed, Excel will quickly calculate the daily standard deviation for WIPRO. My response was 0.0147 giving a percentage of 1.47%.

The daily fluctuation rate of WIPRO is 1.47%.

We have determined WIPRO’s daily volatility, so what is its annual volatility?

It’s vital to remember that to establish the annual volatility, we must multiply daily volatility by the square root of time.

To convert annual volatility into its daily equivalent, simply divide the value by the square root of the time period.

We have determined the daily volatility of WIPRO and now we must calculate the annual volatility. Here, we will do the same.

• Daily Volatility = 1.47%

• Time = 252

• Annual Volatility = 1.47% * SQRT (252)

• = 23.33%

From this data, we can see that WIPRO has a daily variance of 1.47% and an annual volatility of approximately 23%.

Let’s cross-reference these figures with what the National Stock Exchange (NSE) has posted on their website. The NSE only gives information about Futures and Options stocks, here is a preview of that data.

Our figures are similar to the ones calculated by NSE – Daily Volatility of Wipro is estimated at 1.34%, with an Annualized Volatility around 25.5%.

So why is there a slight discrepancy between our calculation and NSE’s? A possible explanation may be that we are using spot value while NSE is utilizing Futures pricing. However, I don’t find it necessary to delve into the cause of this divergence. What needs to be done here is finding out how to evaluate the volatility of a security based on its day-to-day returns.

Finally, to conclude this chapter, let’s work out one more calculation. Suppose WIPRO’s annual volatility is determined as 25.5%, how can we calculate its daily volatility?

To convert annual volatility to daily volatility, one just needs to divide the annual figure by the square root of time; consequently, in this case –

= 25.5% / SQRT (252)

= 1.60%

We understand volatility and the way to calculate it. Moving on, in the following chapter we’ll explore its practical application.

We are still working on understanding volatility, but our final goal is to comprehend Vega and what it implies. Let’s not forget this aim.