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:
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.
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:
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.
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.
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:
This is the snapshot that shows the exact same.
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.
Actually, I calculated the same using excel. Take a look at this image.
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 -
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)
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.