16.1 -  Volatility Calculator based on Excel

We have already discussed the concept of standard deviation, and how it can help to assess 'Risk or volatility' in a stock. Before we get into this topic, I want to talk about how volatility can be calculated. Volatility data isn't always readily available so it's important to be able to calculate it yourself.

We covered this calculation in the previous chapter (remember the Billy & Mike example). Here are the steps:

1. Calculate the average
2. Calculate the deviation and subtract the average from the observation.
3. Add all deviations together and square them - this is variance
4. The square root of variance is what we call standard deviation.

This chapter was written to explain the principles behind standard deviation calculations. It is essential to understand what goes beyond the formula. This will enhance your understanding. We will be using MS Excel to find a simpler way to calculate volatility or standard deviation for a stock. MS Excel follows the exact same steps as above but it is done with a single click.

First, I will outline the steps involved and then go into detail about each step.

1. Get the historical data on closing prices
2. Calculate the daily returns
3. Use the STDEV function

Let's get to work immediately.

Step 1 Download the historical closing price

This can be done from any data source you have. Yahoo Finance and NSE India are two reliable and free data sources.

For now, I will use the data provided by NSE India. NSE India's website has a lot of resources. I think NSE India's website provides the most accurate information.

Let's now calculate Wipro’s volatility.

Here's a quick snapshot of the search option I highlighted (image 1)
 

After hitting search, you will see a list of fields. Filling these up is easy. Just fill in the details and hit "Get Data". Make sure to get the data for the past 1 year. These dates are from 22 nd Jul 2014 to 21 _ July 2015.

After you click "get data", NSE's website will query and retrieve the data you need. The following screen should appear:
(image 2
 

Once you have this information, click on "Download file in CSV format" (highlighted in green box). That's it.

Now you have all the data in Excel. You can also have many other data. I prefer to remove all unnecessary data and keep to the closing prices and the date. This keeps the sheet clean and neat.

This is how my Excel sheet looks at this stage.
(image 3)
 

Please note that I have removed all unnecessary information. I have kept only the closing prices and date.

Step 2 Calculate Daily Returns

We know that daily returns can be calculated at -

Return = (Ending price / Beginning price) - 1.

This equation can however be approximated for practical purposes and calculation ease:

Return = (Ending price / Beginning price) . LN denotes Logarithm To Base 'e'. This is also known as 'Log Returns'.

Here's a quick shot of how I calculated WIPRO's daily log returns.
(image 4)
 

To calculate long returns, I used the Excel function "LN"

Step 3 - Use the STDEV Function

After the daily returns have been calculated, you can use the excel function 'STDEV" to calculate the standard deviation of daily return. This is, if you are aware, the daily Volatility for WIPRO.

Notice - To use the STDEV function, all you have to do is this:

1. Use the cursor to navigate an empty cell
2. Press "="
3. The = sign is next to the function syntax, i.e. STDEV. Open a bracket and the empty cell will look like =STEDEV(
4. Close the bracket after you have completed the open bracket.
5. Enter

This is the snapshot that shows the exact same.
(image 5)
 

Excel instantly calculates the daily standard deviation, or volatility of WIPRO. The answer is 0.0147, which can be converted into a percentage to read as 1.47%.

This means that WIPRO's daily volatility is 1.47%

We have calculated WIPRO's daily volatility. But what about its annual volatility.

Here's a crucial convention to keep in mind: To convert daily volatility to annual volatility, multiply the daily volatile number by the square root.

To convert annual volatility into daily volatility, divide it by the square root of the time.

In this example, we have already calculated daily volatility. Now we need WIPRO's annual volatility. The same will be done here.

• Daily Volatility = 1.47 %
• Time = 252
• Annual Volatility =
  1.47 % * SQRT (252
• = 23.33%

Actually, I calculated the same using excel. Take a look at this image.
(image 7)
 

This means that WIPRO's daily volatility of 1.47% is known and its annual volatility of around 23%.

Let's compare these numbers to what the NSE has posted on their website. These numbers are only available for F&O stocks. The same snapshot is here -
(image 8).
 

Our calculations are very similar to NSE's. According to NSE's calculations, Wipro's daily volatility is approximately 1.34% while Annualized Volatility at Wipro is around 25.5%.

So why is our calculation slightly different from NSE's? One reason is that we use spot price, while NSE uses Futures price. But I don't really want to go into the details of why this slight deviation exists. This is about how to calculate volatility given daily returns.

Let's do one more calculation before we close this chapter. Let's say that we have the annual volatility for WIPRO at 25.5%. How can we calculate its daily volatility?

As I said earlier, you can convert annual volatility into daily volatility by simply dividing the annual volatility by a square root of the time. In this case, -

= 25.5%/SQRT (252)

= 1.60%

We have so far understood volatility and how it can be calculated. The next chapter will explain how volatility can be applied in practice.

Remember that we are still learning about volatility. However, the ultimate goal is to understand what the options greek Vega really mean. Never lose sight of the end goal.

Keypoints

1. The Standard Deviation is a measure of volatility. This in turn indicates risk.
2. To get the closing prices for securities each day, you can access NSE website
3. Log returns can be used to calculate daily return
4. Excel log function is LN
5. Daily return formula = LN (Today’s Value / Yesterday’s Value), expressed in a percentage
6. Excel function STDEV to calculate volatility
7. Daily volatility is equal to the standard deviation of daily return
8. Divide daily volatility by the square root to convert it into annual volatility
9. To convert annual volatility into daily volatility, divide annual volatility by the square root time