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 an equity. 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 Rajesh and Amit’s examples and outlined the steps for calculation:

Compute the average

Compute the deviation by subtracting the mean from the actual observation

Square each deviation and sum them up, this is referred to as variance

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 equity 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: Utilise 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 Tech Mahindra. To retrieve the historical closing prices, you can visit the website http://www.nseindia.com/products/content/equities/equities/equities.html and click on the ‘historical data’ section. From there, select the desired search option to access the necessary data.

This is a photo I have marked the search option on.

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 the appropriate date range.

Once ‘get data’ has been clicked, NSE’s website will search for your desired information and present it to you. Upon completion of the query, this should be the displayed screen.

Once you have this, simply click on the green box which says ‘Download file in CSV format’, and you’re done.

You now possess the requisite data in Excel. I’m usually a fan of discarding any extra superfluous information, just maintaining the date and closing prices. This practice presents the sheet in a much tidier, organised fashion.

Here’s the screenshot:

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 Tech Mahindra.

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

Step 3 – Use the STDEV Function

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

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

Select an empty cell

Type ‘=’ to begin a formula

Enter the function syntax, which is STDEV, followed by an opening bracket

The empty cell would look like =STDEV(

After opening the bracket, choose all the data points for daily returns and then close the bracket

Press enter

Here’s a screenshot:

Once this is completed, Excel will quickly calculate the daily standard deviation for Tech Mahindra. My response was 0.0152 giving a percentage of 1.52%.

The daily fluctuation rate of Tech Mahindra is 1.52%.

We have determined Tech Mahindra’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 Tech Mahindra and now we must calculate the annual volatility. Here, we will do the same.

Daily Volatility = 1.52%

Time = 252

Annual Volatility = 1.52% × SQRT (252)

= 24.13%

Here’s the calculation on the excel sheet:

From this data, we can see that Tech Mahindra has a daily variance of 1.52% and an annual volatility of approximately 24%.

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 equities, here is a preview of that data.

Our figures are similar to the ones calculated by NSE. Daily Volatility of Tech Mahindra is estimated at 1.45%, with an Annualised Volatility around 25.8%.

So why is there a slight discrepancy between our calculation and NSE’s? A possible explanation may be that we are using spot value whilst NSE is utilising 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 Tech Mahindra’s annual volatility is determined as 25.8%, 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.8% / SQRT (252)

= 1.63%

We have an understanding of 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.

For those exploring equity investment opportunities through a stock broker or consulting with a financial advisor, understanding how to calculate volatility proves essential when navigating the stock market. Whether evaluating trading calls or utilising a stock screener to identify opportunities, comprehending volatility calculation methods enables more accurate options pricing analysis and risk assessment.