9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

MODULES

CHAPTER 16

SHARE

Volatility Calculation
(Historical)

16.1 Calculating Volatility on Excel

In the previous chapter, we introduced the concept of standard deviation and how it can be used
to evaluate Risk or Volatility of a stock. Before we move any further on this topic I would like to
discuss how one can calculate volatility. Volatility data is not easily available, hence its always
good to know how to calculate the same yourself.
Of course in the previous chapter we looked into this calculation (recall the Billy & Mike example),
we outlined the steps as follows
1. Calculate the average
[Link]

1/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

2. Calculate the deviation Subtract the average from the actual observation
3. Square and add up all deviations this is called variance
4. Calculate the square root of variance this is called standard deviation
The purpose of doing this in the previous chapter was to show you the mechanics behind the
standard deviation calculation. In my opinion it is important to know what really goes beyond a
formula, it only enhances your insights. In this chapter however, we will figure out an easier way
to calculate standard deviation or the volatility of a given stock using MS Excel. MS Excel uses the
exact same steps we outlined above, just that it happens at a click of a button.
Ill give you the border steps involved first and then elaborate on each step
1. Download the historical data of closing prices
2. Calculate the daily returns
3. Use the STDEV function
So let us get to work straight away.
Step 1 Download the historical closing prices
You can do this from any data source that you have. Some of the free and reliable data sources
are NSE India website and Yahoo Finance.
I will take the data from NSE India for now. At this point I must tell you that NSEs website is quite
resourceful, and in terms of information provided, I guess NSEs website is one of the best stock
exchange websites in the world.
Anyway, in this chapter let us calculate Wipros volatility. To download the historical closing
prices, visit [Link]
([Link] and click on
historical data and select the search option.
Here is a snapshot where I have highlighted the search option

[Link]

2/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
Once you hit search, a set of fields open up, filling them up is quite self explanatory just fill in the
required details and hit Get Data. Do make sure you get the data for the last 1 year. The dates
that I have selected here is from 22nd July 2014 to 21st July 2015.
Once you hit get data, NSEs website will query your request and fetch you the required data. At
this point you should see the following screen

[Link]

3/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
Once you get this, click on Download file in CSV format (highlighted in the green box), and thats
it.
You now have the required data on Excel. Of course along with the closing prices, you have tons of
other information as well. I usually like to delete all the other unwanted data and stick to just the
date and closing price. This makes the sheet look clutter free and crisp.
Here is a snapshot of how my excel sheet looks at this stage

[Link]

4/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
Do note, I have deleted all the unnecessary information. I have retained just the date and closing
prices.
Step 2 Calculate Daily Returns
We know that the daily returns can be calculated as
Return = (Ending Price / Beginning Price) 1
However for all practical purposes and ease of calculation, this equation can be approximated to:
Return = LN (Ending Price / Beginning Price), where LN denotesLogarithmto Base e, note this
is also called Log Returns.
Here is a snap shot showing you how Ive calculated the daily log returns of WIPRO

[Link]

5/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
I have used the Excel function LN to calculate the long returns.
Step 3 Use the STDEV Function
Once the daily returns are calculated, you can use an excel function called STDEV to calculate
the standard deviation of daily returns, which if you realize is the daily Volatility of WIPRO.
Note In order to use the STDEV function all you need to do is this
1. Take the cursor an empty cell
2. Press =
3. Follow the = sign by the function syntax i.e STDEV and open a bracket, hence the empty
cell would look like =STEDEV(
4. After the open bracket, select all the daily return data points and close the bracket
5. Press enter
Here is the snapshot which shows the same

[Link]

6/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
Once this is done, Excel will instantly calculate the daily standard deviation aka volatility of
WIPRO for you. I get the answer as 0.0147 which when converted to a percentage reads as 1.47%.
This means the daily volatility of WIPRO is 1.47% !
The value we have calculated is WIPROs daily volatility, but what about its annual volatility?
Now here is a very important convention you will have to remember in order to convert the daily
volatility to annual volatility just multiply the daily volatility number with the square root of time.
Likewise to convert the annual volatility to daily volatility, divide the annual volatility by square
root of time.
So in this case we have calculated the daily volatility, and we now need WIPROs annual volatility.
We will calculate the same here
Daily Volatility = 1.47%
Time = 365
Annual Volatility = 1.47% * SQRT (365)
= 28.08%
In fact I have calculated the same on excel, have a look at the image below

[Link]

7/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
So with this, we know WIPROs daily volatility is 1.47% and its annual volatility is about 28%.
Lets double check these numbers with what the NSE has published on their website. NSE
publishes these numbers only for F&O stocks and not other stocks. Here is the snapshot of the
same

[Link]

8/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]
Our calculation is pretty much close to what NSE has calculated as per NSEs calculation Wipros
daily volatility is about 1.34% and Annualized Volatility is about 25.5%.
So why is there a slight difference between our calculation and NSEs? One possible reason
could be that we are using spot price while NSE is using Futures price. However I really dont want
to get into investigating why this slight difference exists. The agenda here is to know how to
calculate the volatility of the security given its daily returns.
Before we wrap up this chapter, let us just do one more calculation. Assume we directly get the
annual volatility of WIPRO as 25.5%, how do we figure out its daily volatility?

[Link]

9/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Like I mentioned earlier, to convert annual volatility to daily volatility you simply have to divide
the annual volatility by the square root of time, hence in this particular case
= 25.5% / SQRT (365)
= 1.34%
So far we have understood what volatility is and how to calculate the same. In the next chapter
we will understand the practical application of volatility.
Do remember we are still in the process of understanding volatility; however the final objective is
to understand the option greek Vega and that really means. So please do not lose sight of our end
objective.
Please click here ([Link] to
download the excel sheet.

Key takeaways from this chapter

1.
2.
3.
4.
5.
6.
7.
8.

Standard Deviation represents volatility, which in turn represents risk

We can use NSE website to get the daily closing prices of securities
Daily return can be calculated as log returns
Log function in excel is LN
Daily return formula = LN (Todays Value / Yesterdays Value) expressed as a percentage
Excel function to calculate volatility is STDEV
Standard Deviation of daily return is equivalent of daily volatility
To convert daily volatility to annual volatility multiply the daily volatility by the square
root of time
9. Likewise to convert annual volatility to daily volatility, divide the annual volatility by the
square root of time
([Link]
([Link]

[Link]

10/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

53 Responses to Volatility Calculation (Historical)

Reply ([Link]

RAJ
July 24, 2015 at 9:32 am

Hi karthik, appreciate the excel calculations. makes it very simple for all of us. as far as options concerned , for e.g a put . the
stock could fall to a particular price at various times and the option premium could be different at those times. i bought a put
of lupin, and at one time the stock was at 1700 and the put was trading at 5. the stock went up to 1710 and when it re tested
1700 again the put was trading at 3 this time. is there a way to determine at what premium the option could trade at same
stock price points at different times of the day. will you be touching upon this? thanks a ton.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 25, 2015 at 1:28 pm

Raj, what you have quoted is a very practical situation. The main reason for this would be changes in volatility. As this
mini series on Volatility evolves, Im sure you will get the answers yourself. Please stay tuned till then
([Link]

Reply

WANNBETRADER
July 24, 2015 at 10:27 am

Fabulous :-) Wish I was so eager to learn during my school days ,lol.

([Link]
replytocom=16369#respond)

([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 25, 2015 at 1:29 pm
[Link]

calculation-historical/?replytocom=16404#respond)
11/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

I wish so myself, I was never a good student

([Link]

Reply ([Link]

RAJDEEP
July 25, 2015 at 5:32 pm

Hi Karthik,
I would like to congratulate you on your stellar effort in making such a complicated topic so easy to digest. If i ever succeed in
trading it will only be because of you, varsity and zerodha, i say it from my heart.
I am absolutely new to trading, i am having a confusion regarding options. What i understand is the option greeks are
responsible for the change in the option prices, but there is also a separate demand-supply of each option which is created by
the writers and the buyers of that particular option, so what moves the option premiums the demand supply interaction like in
spot market/futures or the greeks alone(the individual options demad suppy has no role to play in setting the price) or both
A 2nd question, from the current nifty option chain i see that the 8400 slightly OTM puts price have appreciated but the slightly
ITM 8400 calls have fallen, does it mean the market is getting more comfortable with the idea of nifty falling below 8400 in the
next 3 days, is that a case for buying slightly OTM puts, i dont have the confidence/conviction to trade, i am trying to confirm
my understanding by thinking in terms of a trade. Cannot thank you enough for what you have done for me.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 27, 2015 at 5:11 am

Happy to read this Rajdeepbtw, if you succeed in trading (I hope you do), then its because of your heard work and
nothing else. Coming to your query
Option premium are a function of many factors, the essence of which are captured by the Greeks. For example the
demand supply situation causes directional movement and thats captured by Delta. Effect of time is captured by
Theta, volatility by vega etc. So Greeks capture all the necessary price variable
Regarding the 2nd question Puts have appreciated due to two factors Markets and fallen and volatility also has
increased. You will understand and appreciate the effects of volatility over the next few chapter. Please stay tuned.
([Link]

Reply ([Link]

RAJDEEP
July 26, 2015 at 7:55 am
[Link]

12/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Infact as i was thinking about it, i have this question for futures too, derivatives as per definition move as per the movement of
the underlying, but the derivative itself is also being traded, so what determines the price of the derivative, the derivatives
supply demand dynamics or the movement of the derivatives underlying or both? e.g. nifty futures is a heavily traded
derivative but is the spot market single handedly determining its price ?
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 27, 2015 at 5:22 am

A derivative derives its value from its respective underlying. Its unusual for the derivative to influence the price of the
spot.
([Link]

Reply ([Link]

PANKIT SHAH
July 27, 2015 at 5:17 am

Hi Nitinji,
I have a [Link] now the market is trading sideways,1 day it increases & few days it [Link] my query is what
option strategies should i employ for trading nifty options in such a volatile marketits very difficult to make money in such a
market.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 27, 2015 at 5:58 am

Any strategy that involves a credit and benefiting from time decay would be a good I suppose. Example short
strangle, straddle etc.
([Link]

Reply ([Link]

WANNBETRADER
[Link]

13/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

July 27, 2015 at 9:00 am

I know there is chapter coming on option strategies, but what I read so far had following thought, can you verify if it make
sense?
Using the daily volatility we can predict how much can given stock move in next few days, so using this info couple of days
before expiry can give us high probability trades for example, Syndicate Bank has volatility of 2.5% with CMP of 99.7, if it
continues downward move its closing price on Thursday (expiry) should be minimum [Link], which means PUT option of SP 90
will be OTM and end up expiring worthless that means if we short it today at CMP of .40, we have high chances of earning .40 *
2000 800 RS in 4 days. I was tempted to say we have sure shot chance , but then nothing is sure in Market
Makes sense?
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 27, 2015 at 11:03 am

That part nothing is sure in markets certainly makes sense

Btw, yes you are more or less heading the right way, its just that we need to be aware of certain probabilities when it
comes to such trades. Im writing about this in the upcoming chapter. Please stay tuned.
([Link]

Reply ([Link]

WANNBETRADER
July 27, 2015 at 11:05 am

(y) yep, eagerly waiting for next chapters

calculation-historical/?
replytocom=16491#respond)

([Link]

Reply

KARTHIK RANGAPPA
July 29, 2015 at 5:52 am

The next chapter is taking a bit longerbut Im hoping it will be worth the wait
([Link]
([Link]
replytocom=16566#respond)

WANNBETRADER
July 29, 2015 at 8:27 am

no worries, going thru TA section till then.

([Link]
[Link]

14/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]

KARTHIK RANGAPPA
July 30, 2015 at 5:22 am

Awesome!
([Link]

Reply ([Link]
replytocom=16782#respond)
SHREYADR
August 4, 2015 at 2:10 am

What will be the monetary consequences, if i leave this to settle by exchange in respect of
brokerage+taxes+Short term capital gain? ( talking about the example given by WANNBETRADER)
([Link]

Reply ([Link]
replytocom=16790#respond)
KARTHIK RANGAPPA
August 4, 2015 at 6:46 am

It is always advisable to close ITM options before expiry to avoid the STT trap. We have a nice article
explaining this, check this [Link] ([Link]
([Link]

Reply ([Link]

WANNBETRADER
July 28, 2015 at 3:35 pm

and marked proved again that nothing is sure in market :) it fell more than 9% today so the OTM put is not ITM with
value from .40 to 1.40 :)
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 29, 2015 at 6:11 am
[Link]

calculation-historical/?
replytocom=16571#respond)
15/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]

Reply

NARSIMHA
July 28, 2015 at 12:11 pm

sir,ofcourse its good but it will be applicable in reality,ithink the simpler ur the better,correct me if iam wrong
([Link]
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 29, 2015 at 6:08 am

calculation-historical/?replytocom=16569#respond)

Simplicity is relative
([Link]

Reply ([Link]

SARATH LAL
July 29, 2015 at 2:41 pm

historical/?replytocom=16584#respond)

hi kartik,
i have a doubt what is liquidity?
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 30, 2015 at 5:35 am

Liquidity is the ease at which you can buy or sell shares from the market you can read more about it here
[Link] ([Link] (refer section
9.2).
([Link]

Reply ([Link]

VASANTH
[Link]

16/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

July 31, 2015 at 2:19 am

Is there any resource readily available to project the Greeks value during market hours depending upon the movement?
Calculating Greeks during market hours is difficult in intraday trading
([Link]

Reply ([Link]

KARTHIK RANGAPPA
July 31, 2015 at 6:50 am

Not that i know ofbut my experience tells me that over a period of time you will develop a sense for this and kind of
start approximating the values in your mind
([Link]

Reply

SHANKAR
August 1, 2015 at 6:58 am

Hi Karthik
Using the daily/annual volatility, can we also calculate the range of the stock/index for the next day?
Shankar
([Link]
([Link]

Reply

KARTHIK RANGAPPA
August 2, 2015 at 5:45 am

Yes, we can do thatin fact chapter 17 (will be uploaded next week) will discuss this.
([Link]
([Link]

Reply ([Link]

KRISH
August 2, 2015 at 2:59 pm

1. How to determine whether volatility is high or low for a particular stock option? For certain stocks the number can be
interpreted as high whereas the same number can represent low for others. So what is the indicator based on which we can say
its high or low for a particular stock? Is it by comparing the annualised historical volatility number with current IV number?
2. What is the expected time for an option volatility to raise before an event ? is it one day or five days or more than that?
([Link]
[Link]

17/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 3, 2015 at 5:21 am

Krish super valid questions.

1) Yup you are right here. To evaluate if todays volatility is high or not wrt to historical volone of the methods is to
compare todays implied vol with historical vol. More on this topic in the coming chapters.
2) Cant put a number for this but usually about 1 trading week before the event vol starts to increase. Disclaimer
This is purely from my personal observation, could be wrong here.
([Link]

Reply ([Link]

SARATH
August 3, 2015 at 3:54 am

calculation-historical/?replytocom=16756#respond)

karthik,
how the volatility effect the option premium like delta ,theta .
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 3, 2015 at 5:26 am

We are getting there sarath

The idea is to understand volatility completely before understanding its effect on

Options premium.
([Link]

Reply ([Link]

SHREYADR
August 4, 2015 at 4:39 am

also wanted to ask, if annual volatility can be calculated with the help of a formula from a daily volatility by taking 365 days.
how to calculate monthly volatility? by taking no. of days in a month i.e 30 or 31? or 20/22 i.e. actual no. of trading sessions?
([Link]

[Link]

18/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Reply ([Link]

KARTHIK RANGAPPA
August 4, 2015 at 6:47 am

Since we are taking the full calender of 365 days, it makes sense to take the actual number of days in the month i.e 30
or 31.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 4, 2015 at 6:48 am

Since we are taking the full calendar days i.e 365, it makes sense to take the actual number of days in the month i.e 30
or 31.
([Link]

Reply ([Link]

VASANTH
August 4, 2015 at 5:27 pm

Today nifty index suddenly showed a huge spike at 11Hrs which may be due to the no change in rate cut/behaved due to some
other issues. How the programmed index which is running through the market partcipants immediately behaving according to
the current scenarios? Is there any manual feed/input to the programmed exchange? If yes, then insiders are the players whose
odds are high compare to the normal traders.. can you clarify.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 5, 2015 at 7:34 am

No Vasanth nothing like that happens. Index is like a car which can go at a speed dependent of the accelerating
capacity of the [Link] the index derives its values which is dependent on the underlying stocks. No one can
manipulate this. Suggest you read this chapter [Link]
([Link]
([Link]
[Link]

19/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Reply ([Link]

ABHIJIT HAWARE
August 5, 2015 at 7:24 am

calculation-historical/?replytocom=17148#respond)

Hello Mr Karthik,
Eagerly waiting for next [Link] update the same
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 5, 2015 at 7:35 am

calculation-historical/?replytocom=17154#respond)

In a day or 2
([Link]

Reply ([Link]
replytocom=17158#respond)
ABHIJIT HAWARE
August 5, 2015 at 8:07 am

Thanks for update

I know that you would update chapters in strategies in coming days but I want to know are there any strategies
for intraday option trading? and what are those?
([Link]

Reply ([Link]
replytocom=17175#respond)
KARTHIK RANGAPPA
August 6, 2015 at 6:10 am

Abhijit this is a hard query for me :). At the moment I wont be able to comment on this, so please do
bear with me.
([Link]

Reply ([Link]

SHIBASHIS
August 7, 2015 at 8:33 pm
[Link]

20/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Hi Karthik,
One question regarding daily return calculation.
By using ((570.9-558.75)/570.9)*100 this formula daily return is :2.12%. But we are getting 2.15% using log base [Link] tell me,
Why you use log base e over linear return or even log base 10.
Thanks in advance.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 9, 2015 at 3:26 am

Check this [Link]

([Link]
([Link]

Reply ([Link]

RAJ
August 13, 2015 at 3:42 pm

Karthik, while calculating daily return for hindustan petroleum using =LN() , the same is displayed as 0.061547 and, not in
percentage points. how do i convert the same into %age? secondly, do we have to individually calculate for all the dates i.e the
whole year one at a time using the above formula? or is there a formula to do the same in one go for all 365 days? calculating
daily returns for all the
respective 365 days individually seems to be a humongous task. do clarify. thanks.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 14, 2015 at 4:25 pm

Raj keep the cursor on the cell which contains 0.061547 and then select the % symbol that you can see in the Excel
window. This button is placed right below the word General which is somewhere in the top middle. Once you do this
the figure should change to 6.1547%. Also you need not have to do this individually for all 365 day, just drag and drop
the formula and it will automatically get calculated for all days.
([Link]

[Link]

21/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Reply

RAJ
August 15, 2015 at 7:03 am

thanks karthik, how do i drop and drag the formula? i am not proficient with the usage of excel.
([Link]
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 16, 2015 at 12:47 pm

You can check the excel tutorials here [Link]

([Link]
([Link]

Reply

AMITVIKRAM
August 20, 2015 at 1:31 pm

How can i get the NIFTY data the way you got it for WIPRO, am typing NIFTY, but no records are shown!!
([Link]
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 21, 2015 at 6:57 am

Try this link [Link]

([Link]
([Link]

Reply ([Link]

ARUN
August 20, 2015 at 5:29 pm

[Link]

22/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

How often do you calculate and update daily returns for use in trading? As these values are used for calculations in chapter
after this, do we need to calculate it with new data every day
([Link]

Reply

KARTHIK RANGAPPA
August 21, 2015 at 7:04 am

If you are writing options once a month it makes sense to do the calculation once that month.
([Link]
([Link]

Reply ([Link]

RAJ
August 25, 2015 at 6:03 pm

karthik, i just calculated the daily volatility of glenmark. i found that there is a difference of 1 % between my calculation and
that published on NSEcould this be because i extracted data for the last 6 months(closing price) and the NSE probably does
it on a 1 year data? i used the =STDEV() for the same. thanks.
([Link]

Reply ([Link]

KARTHIK RANGAPPA
August 27, 2015 at 5:24 am

calculation-historical/?replytocom=18139#respond)

Yes, that does make a difference.

([Link]

Logged in as mpsravitej ([Link] Log out

([Link]
action=logout&redirect_to=http%3A%2F%[Link]%2Fvarsity%2Fchapter%2Fvolatility-calculationhistorical%2F&_wpnonce=d5ad7bfee0)
Comment*

Submit
[Link]

23/24

9/5/2015

VolatilityCalculation(Historical)ZerodhaVarsity

Select an image for your comment (GIF, PNG, JPG, JPEG):

ChooseFile No file chosen
Notify me of follow-up comments by email.

Copyright 2014 Varsity@Zerodha

Chapter 16
Option Theory
([Link]

[Link]

24/24