## 17.1 – Background

In the earlier chapter we had this discussion about the range within which Nifty is likely to trade given that we know its annualized volatility. We arrived at an upper and lower end range for Nifty and even concluded that Nifty is likely to trade within the calculated range.

Fair enough, but how sure are we about this? Is there a possibility that Nifty would trade outside this range? If yes, what is the probability that it will trade outside the range and what is the probability that Nifty will trade within the range? If there is an outside range, then what are its values?

Finding answers to these questions are very important for several reasons. If not for anything it will lay down a very basic foundation to a quantitative approach to markets, which is very different from the regular fundamental and technical analysis thought process.

So let us dig a bit deeper and get our answers.

## 17.2 – Random Walk

The discussion we are about to have is extremely important and highly relevant to the topic at hand, and of course very interesting as well.

Have a look at the image below –

What you see is called a ‘Galton Board’. A Galton Board has pins stuck to a board. Collecting bins are placed right below these pins.

The idea is to drop a small ball from above the pins. Moment you drop the ball, it encounters the first pin after which the ball can either turn left or turn right before it encounters another pin. The same procedure repeats until the ball trickles down and falls into one of the bins below.

Do note, once you drop the ball from top, you cannot do anything to artificially control the path that the ball takes before it finally rests in one of the bins. The path that the ball takes is completely natural and is not predefined or controlled. For this particular reason, the path that the ball takes is called the ‘**Random Walk**’.

Now, can you imagine what would happen if you were to drop several such balls one after the other? Obviously each ball will take a random walk before it falls into one of the bins. However what do you think about the distribution of these balls in the bins?.

- Will they all fall in the same bin? or
- Will they all get distributed equally across the bins? or
- Will they randomly fall across the various bins?

I’m sure people not familiar with this experiment would be tempted to think that the balls would fall randomly across various bins and does not really follow any particular pattern. But this does not happen, there seems to be an order here.

Have a look at the image below –

It appears that when you drop several balls on the Galton Board, with each ball taking a random walk, they all get distributed in a particular way –

- Most of the balls tend to fall in the central bin
- As you move further away from the central bin (either to the left or right), there are fewer balls
- The bins at extreme ends have very few balls

A distribution of this sort is called the “**Normal Distribution**”. You may have heard of the bell curve from your school days, bell curve is nothing but the normal distribution. Now here is the best part, irrespective of how many times you repeat this experiment, the balls always get distributed to form a normal distribution.

This is a very popular experiment called the Galton Board experiment; I would strongly recommend you to watch this beautiful video to understand this discussion better –

So why do you think we are discussing the Galton Board experiment and the Normal Distribution?

Well many things in real life follow this natural order. For example –

- Gather a bunch of adults and measure their weights – segregate the weights across bins (call them the weight bins) like 40kgs to 50kgs, 50kgs to 60kgs, 60kgs to 70kgs etc. Count the number of people across each bin and you end up getting a normal distribution
- Conduct the same experiment with people’s height and you will end up getting a normal distribution
- You will get a Normal Distribution with people’s shoe size
- Weight of fruits, vegetables
- Commute time on a given route
- Lifetime of batteries

This list can go on and on, however I would like to draw your attention to one more interesting variable that follows the normal distribution – the daily returns of a stock!

The daily returns of a stock or an index cannot be predicted – meaning if you were to ask me what will be return on TCS tomorrow I will not be able to tell you, this is more like the random walk that the ball takes. However if I collect the daily returns of the stock for a certain period and see the distribution of these returns – I get to see a normal distribution aka the bell curve!

To drive this point across I have plotted the distribution of the daily returns of the following stocks/indices –

- Nifty (index)
- Bank Nifty ( index)
- TCS (large cap)
- Cipla (large cap)
- Kitex Garments (small cap)
- Astral Poly (small cap)

As you can see the daily returns of the stocks and indices clearly follow a normal distribution.

Fair enough, but I guess by now you would be curious to know why is this important and how is it connected to Volatility? Bear with me for a little longer and you will know why I’m talking about this.

## 17.3 – Normal Distribution

I think the following discussion could be a bit overwhelming for a person exploring the concept of normal distribution for the first time. So here is what I will do – I will explain the concept of normal distribution, relate this concept to the Galton board experiment, and then extrapolate it to the stock markets. I hope this will help you grasp the gist better.

So besides the Normal Distribution there are other distributions across which data can be distributed. Different data sets are distributed in different statistical ways. Some of the other data distribution patterns are – binomial distribution, uniform distribution, poisson distribution, chi square distribution etc. However the normal distribution pattern is probably the most well understood and researched distribution amongst the other distributions.

The normal distribution has a set of characteristics that helps us develop insights into the data set. The normal distribution curve can be fully described by two numbers – the distribution’s mean (average) and standard deviation.

The mean is the central value where maximum values are concentrated. This is the average value of the distribution. For instance, in the Galton board experiment the mean is that bin which has the maximum numbers of balls in it.

So if I were to number the bins (starting from the left) as 1, 2, 3…all the way upto 9 (right most), then the 5^{th} bin (marked by a red arrow) is the ‘average’ bin. Keeping the average bin as a reference, the data is spread out on either sides of this average reference value. The way the data is spread out (dispersion as it is called) is quantified by the standard deviation (recollect this also happens to be the volatility in the stock market context).

Here is something you need to know – when someone says ‘Standard Deviation (SD)’ by default they are referring to the 1^{st} SD. Likewise there is 2^{nd} standard deviation (2SD), 3^{rd} standard deviation (SD) etc. So when I say SD, I’m referring to just the standard deviation value, 2SD would refer to 2 times the SD value, 3 SD would refer to 3 times the SD value so on and so forth.

For example assume in case of the Galton Board experiment the SD is 1 and average is 5. Then,

- 1 SD would encompass bins between 4
^{th}bin (5 – 1 ) and 6^{th}bin (5 + 1). This is 1 bin to the left and 1 bin to the right of the average bin - 2 SD would encompass bins between 3
^{rd}bin (5 – 2*1) and 7^{th}bin (5 + 2*1) - 3 SD would encompass bins between 2
^{nd}bin (5 – 3*1) and 8^{th}bin (5 + 3*1)

Now keeping the above in perspective, here is the general theory around the normal distribution which you should know –

- Within the 1
^{st}standard deviation one can observe 68% of the data - Within the 2
^{nd}standard deviation one can observe 95% of the data - Within the 3
^{rd}standard deviation one can observe 99.7% of the data

The following image should help you visualize the above –

Applying this to the Galton board experiment –

- Within the 1
^{st}standard deviation i.e between 4^{th}and 6^{th}bin we can observe that 68% of balls are collected - Within the 2
^{nd}standard deviation i.e between 3^{rd}and 7^{th}bin we can observe that 95% of balls are collected - Within the 3
^{rd}standard deviation i.e between 2^{nd}and 8^{th}bin we can observe that 99.7% of balls are collected

Keeping the above in perspective, let us assume you are about to drop a ball on the Galton board and before doing so we both engage in a conversation –

**You** – I’m about to drop a ball, can you guess which bin the ball will fall into?

**Me** – No, I cannot as each ball takes a random walk. However, I can predict the range of bins in which it may fall

**You** – Can you predict the range?

**Me** – Most probably the ball will fall between the 4^{th} and the 6^{th} bin

**You** – Well, how sure are you about this?

**Me** – I’m 68% confident that it would fall anywhere between the 4^{th} and the 6^{th} bin

**You** – Well, 68% is a bit low on accuracy, can you estimate the range with a greater accuracy?

**Me** – Sure, I can. The ball is likely to fall between the 3^{rd} and 7^{th} bin, and I’m 95% sure about this. If you want an even higher accuracy then I’d say that the ball is likely to fall between the 2^{nd} and 8^{th} bin and I’m 99.5% sure about this

**You** – Nice, does that mean there is no chance for the ball to fall in either the 1^{st} or 10^{th} bin?

**Me **– Well, there is certainly a chance for the ball to fall in one of the bins outside the 3^{rd} SD bins but the chance is very low

**You** – How low?

**Me **– The chance is as low as spotting a ‘**Black Swan**’ in a river. Probability wise, the chance is less than 0.5%

**You** – Tell me more about the Black Swan

**Me** – Black Swan ‘events’ as they are called, are events (like the ball falling in 1st or 10^{th} bin) that have a low probability of occurrence. But one should be aware that black swan events have a non-zero probability and it can certainly occur – when and how is hard to predict. In the picture below you can see the occurrence of a black swan event –

In the above picture there are so many balls that are dropped, but only a handful of them collect at the extreme ends.

**17.4 – Normal Distribution and stock returns**

Hopefully the above discussion should have given you a quick introduction to the normal distribution. The reason why we are talking about normal distribution is that the daily returns of the stock/indices also form a bell curve or a normal distribution. This implies that if we know the mean and standard deviation of the stock return, then we can develop a greater insight into the behavior of the stock’s returns or its dispersion. For sake of this discussion, let us take up the case of Nifty and do some analysis.

To begin with, here is the distribution of Nifty’s daily returns is –

As we can see the daily returns are clearly distributed normally. I’ve calculated the average and standard deviation for this distribution (in case you are wondering how to calculate the same, please do refer to the previous chapter). Remember to calculate these values we need to calculate the log daily returns.

- Daily Average / Mean = 0.04%
- Daily Standard Deviation / Volatility = 1.046%
- Current market price of Nifty = 8337

Do note, an average of 0.04% indicates that the daily returns of nifty are centered at 0.04%. Now keeping this information in perspective let us calculate the following things –

- The range within which Nifty is likely to trade in the next 1 year
- The range within which Nifty is likely to trade over the next 30 days.

For both the above calculations, we will use 1 and 2 standard deviation meaning with 68% and 95% confidence.

**Solution 1 – (Nifty’s range for next 1 year)**

Average = 0.04%

SD = 1.046%

Let us convert this to annualized numbers –

Average = 0.04*252 = 9.66%

SD = 1.046% * Sqrt (252) = 16.61%

So with 68% confidence I can say that the value of Nifty is likely to be in the range of –

= Average + 1 SD (Upper Range) and Average – 1 SD (Lower Range)

= 9.66% + 16.61% = **26.66%**

= 9.66% – 16.61% = **-6.95%**

Note these % are log percentages (as we have calculated this on log daily returns), so we need to convert these back to regular %, we can do that directly and get the range value (w.r.t to Nifty’s CMP of 8337) –

Upper Range

= 8337 *exponential (26.66%)

= **10841**

And for lower range –

= 8337 * exponential (-6.95%)

= **7777**

The above calculation suggests that Nifty is likely to trade somewhere between 7777 and 10841. How confident I am about this? – Well as you know I’m 68% confident about this.

Let us increase the confidence level to 95% or the 2^{nd} standard deviation and check what values we get –

Average + 2 SD (Upper Range) and Average – 2 SD (Lower Range)

= 9.66% + 2* 16.61% = **42.87%**

= 9.66% – 2* 16.61% = **-23.56%**

Hence the range works out to –

Upper Range

= 8337 *exponential (42.87%)

= **12800**

And for lower range –

= 8337 * exponential (-23.56%)

= **6587**

The above calculation suggests that with 95% confidence Nifty is likely to trade anywhere in the range of 6587 and 12800 over the next one year. Also as you can notice when we want higher accuracy, the range becomes much larger.

I would suggest you do the same exercise for 99.7% confidence or with 3SD and figure out what kind of range numbers you get.

Now, assume you do the range calculation of Nifty at 3SD level and get the lower range value of Nifty as 5000 (I’m just quoting this as a place holder number here), does this mean Nifty cannot go below 5000? Well it certainly can but the chance of going below 5000 is low, and if it really does go below 5000 then it can be termed as a black swan event. You can extend the same argument to the upper end range as well.

**Solution 2 – (Nifty’s range for next 30 days)**

We know the daily mean and SD –

Average = 0.04%

SD = 1.046%

Since we are interested in calculating the range for next 30 days, we need to convert the same for the desired time period –

Average = 0.04% * 30 = 1.15%

SD = 1.046% * sqrt (30) = 5.73%

So with 68% confidence I can say that, the value of Nifty over the next 30 days is likely to be in the range of –

= Average + 1 SD (Upper Range) and Average – 1 SD (Lower Range)

= 1.15% + 5.73% = **6.88%**

= 1.15% – 5.73% = **– 4.58%**

Note these % are log percentages, so we need to convert them back to regular %, we can do that directly and get the range value (w.r.t to Nifty’s CMP of 8337) –

= 8337 *exponential (6.88%)

= **8930**

And for lower range –

= 8337 * exponential (-4.58%)

= **7963**

The above calculation suggests that with 68% confidence level I can estimate Nifty to trade somewhere between 8930 and 7963 over the next 30 days.

Let us increase the confidence level to 95% or the 2^{nd} standard deviation and check what values we get –

Average + 2 SD (Upper Range) and Average – 2 SD (Lower Range)

= 1.15% + 2* 5.73% = 12.61%

= 1.15% – 2* 5.73% = -10.31%

Hence the range works out to –

= 8337 *exponential (12.61%)

= **9457 **(Upper Range)

And for lower range –

= 8337 * exponential (-10.31%)

= **7520**

I hope the above calculations are clear to you. You can also **download** the MS excel that I’ve used to make these calculations.

Of course you may have a very valid point at this stage – normal distribution is fine, but how do I get to use the information to trade? I guess as such this chapter is quite long enough to accommodate more concepts. Hence we will move the application part to the next chapter. In the next chapter we will explore the applications of standard deviation (volatility) and its relevance to trading. We will discuss two important topics in the next chapter (1) How to select strikes that can be sold/written using normal distribution and (2) How to set up stoploss using volatility.

Of course, do remember eventually the idea is to discuss Vega and its effect on options premium.

### Key takeaways from this chapter

- The daily returns of the stock is a random walk, highly difficult to predict
- The returns of the stock is normally distributed or rather close to normal distribution
- In a normal distribution the data is centered around the mean and the dispersion is measured by the standard deviation
- Within 1 SD we can observe 68% of the data
- Within 2 SD we can observe 95% of the data
- Within 3 SD we can observe 99.5% of the data
- Events occurring outside the 3
^{rd}standard deviation are referred to as Black Swan events - Using the SD values we can calculate the upper and lower value of stocks/indices

Sir,

What a surprise journey. Pleasant surprise because maths use to be my favourite subject and i did not expect that I will get chance to use my skill in share market also. Now after this I (we) are more curious for the next chapter. I am eagerly waiting for it as you have said that this approach is different from the technical and fundamental approach.

Congratulation for again simple explanation and

Thanks for enlightening us.

R P HANS

Thank you! I hope you will like the upcoming chapter as well 🙂

So much clarity in your writeup. Pls share your twitter id 🙂

@karthikrangappa 🙂

wonderful explanation ….was worth the wait :-).

small correction, I couldn’t find the red arrow in the digram above the statement “marked by a red arrow)”. same for picture on Black Swan, also you have taken example of 1 to 10 slots but there are 13….may be pic needs to be changed….. ofcourse not a big thing…..

Yeah, i’ve noticed the mismatch but cant too late to get the illustrations changed 🙂

Sir, In the Nifty eg you have taken data from 10th March’11 onwards for the calculation. Obviously using more data for calculation wil provide the best result. But for precise caluclation how much data to be collected? Whether last 1year/2 year or anything? Kindly suggest..

In fact you can try this for any time frame…1 year , 2 year etc…you will end up with a normal distribution!

Sir, one more question: You have shown predicting the price movement for the year and month. But it will also be needed to calculate the range of price for a day. i.e. present day or tomorrow to plan a trade. Will you explain that also?

Will be talking about this in the next chapter 🙂

The way you simplifying the things which are complex to most of us, is fabulous. Many such things i never paid any heed till now. Thank you very much and keep going. we all are ready to grasp the knowledge you are sharing with us.

Thanks Shreya 🙂

We will discuss two important topics in the next chapter (1) How to select strikes that can be sold/written using normal distribution and (2) How to set up stoploss using volatility. This is what you say at the end of the chapter. What about selecting strikes for an option buyer using normal distribution? wont you be covering that? after all most of us would be buying options than selling them. appreciate your content and form of presentation. thanks.

Will cover that as well, please stay tuned 🙂

Sir,

You r a doing a wonderful job teaching us the ABC of stock market trading.The brokerage rates of zerodha r also very low.

But many other brokerages have started giving low brokerages & also provide very good intraday F&O tips and also long term investment ideas based on technical & fundamental research

So,its my request that zerodha too start giving very good intraday F&O tips & long term ideas.It would make zerodha even more successful than it is now……

Pankit – Giving tips is like feeding a hungry man with 1 meal. But we have a slightly different philosophy – we believe in empowering people so that they don’t go hungry again!

Hence we have all these educational initiatives like –

http://tradingqna.com/

http://zerodha.com/z-connect/

Wonderful. I agree.

Cheers!

Hello Mr Karthik,

I have one query…in the calculation of bin width, why did you divide difference of min and max by 50?

When I divide it over 50, the intention is to create 50 bins, I can in fact divide it by 100 or even 200. No restriction on that.

Adding to the above query..if i am adding data for the month of Aug in the Excel and trying to modify the data range in the frequency columns its not allowing…what i am doing wrong please help me with that.

Not sure Sharad, can you share a snapshot please?

Hi Karthik,

I’m also facing the same problem while trying to change the FREQ formula in H14. The current formula is =FREQUENCY(E9:E1096,G14:G64). Now my data range has changed in col E, so I need to change that. When I’m trying the same, it is giving the error message – “You can not change the part of an array”. Can you please look into this?

Thanks in advance.

Are you sure you are selecting the right date range? Also do pay attention to the Çtrl+shift+enter thing when using the frequency function.

Hello,

Fantastic Article, what a way to explain the things, simply superb!

All the initiatives taken by Zerodha (varsity, Taxation) are exceptionally good.

Best of luck to entire zerodha Team.

regards

Thank you so much Ajit!

Hi, in the above e.g of nifty how have you calculated the daily average/mean as 0.04%. ? the earlier chapter talks of calculating the daily return using the log return. do you add the daily returns and divide by the number of observations?

do you add the daily returns and divide by the number of observations?

Raj – Yes, that’s how average is calculated. However you can use excel function ‘=Average()’ to calculate the same in 1 shot.

How to convert log returns into simple returns.How to do that in PC.

Exponential of log should minus one should give you the simple return. Use can use excel for this.

which means karthik, as per the wipro excel sheet you provided us as a download (chapter 16) , in which you have calculated its daily return. the formula application would be =average(c3,c245). that gives us 1.77% as daily average/mean. this figure has to be used along with the standard deviation to solve the e.g you have provided at the end of this chapter. would that be a correct assessment ? thanks.

Yup, you can. In fact in the coming chapter will give you more clarity on this aspect.

Waiting for the next chapter.

By next week for sure 🙂

Sir,

As usual eagerly waiting for next chapters :0 …kindly update

By next week for sure 🙂

Next week.! Oh it’s too late sir, pls update this week only..

Will try our best 🙂

Am a Statistics graduate, I wish I have seen this post when I was in my college, amazing explanation. please include variance, regression, correlation as well in your coming chapters.

Thanks for the encouragement Karan. Will talk about these topics as and when its appropriate. Please stay tuned.

The OI/Volume qty in Nifty 8350, 8450,8550…calls/put are less compare to 8300,8400,8500…calls/Puts qty. For eg If i short 8350 call/Put whether the full qty at limit price will get execute or any issue due to less liquidity? If yes whether can i able to buy back the qty once it reaches the target price as per the limit order?

Vasant, the OI information is on a daily basis. I’m certain the OI for the strikes you have mentioned is large enough to even accommodate institutional orders 🙂

Again kudos to you sir…. Your effort to explain complex concept in simple and effective ways are simply superb:) particularly the question and answer part…great job sir…

Thanks Raghu!

While reading the change in volatility concept in a book i have noticed in an options example that premium is trading 0.78 [email protected]% volatility. If the volatility increases from 14% to 18% the premium also increased from 0.78rupees to 3.05 rupees? I don’t know how they caluclated? Can you explain how the premiun is increasing if the volatility increase from 14 to 18%?

This is because of the effect of Vega. I will talk about it in detail very soon (maybe in chapter 20).

Hey, may I know the name of the book?

karthik,

when will we get the next chapters…..

This week Sarath.

Hi karthik, while converting daily volatility into annualized volatility of HINDPETRO there is a difference in the numbers published on NSE website and the one i calculated . also, the difference is significant if i take SQRT 252 ( difference 13 %) vis- a -vis SQRT 365 (difference 5%). does that mean the NSE is using 365 days to calculate the same?

Madhu – Yes NSE uses 365 days in their calculation.

Tumba tumba thnks Karthik for such simplified content 🙂

So what should we use, 365 or 252 for our calculation? And also for monthly calculation 30 or 20?

Namaskara 🙂

252 and 22 should do.

adds for every click !!??

As in? Sorry dint get that.

no need for you to sorry there was just a malware causing problems

Sure 🙂

sir, how to tackle with gap up / gap down opening of nifty while trading nifty intraday options..?

Dint get that Abhijit – you are talking about gap up/down opening at the open right? So it should not really bother intraday traders.

ok…actually I was thinking about short strangle or short straddle strategy and what would happen if I take position at 3:15 pm and carried over night to take advantage of gap up / gap down opening of index on next day…am I right?

Short straddle or strangle etc are all delta neutral strategies…so gaps don’t matter.

Dear Karthik,

Thanks very much for ur Varsity initiative. I have learned about Options from ur writings, which I was not able to understand from other people writings. I earnestly request u to provide separate day trading n position trading in options, where we can use Gann levels, Fibonacci, Open Interest in put and call to understand what boundaries (strike prices) will be maintained and when the strike levels will be crossed etc.

Prasad – the next module is all about Option Strategies!

hi karthik, the difference between the upper range and lower range using SD for 30 days for HINDPETRO is quite substantial. it obviously would increase further with 2SD and 3SD. within such wide range wont it be a tedious task to select the right strike price? i am assuming there is a way to break through the clutter and finding the appropriate strike price. cheers.

Madhu – I think the up coming chapter (Volatility Application) will clear your doubt. Request you to please stay tuned.

In solution 1) 0.04%*252 is 10.8% where as it is printed as 9.66%, am i missing something here?

Ah! Blame that Excel. Its rounded it off to 0.04%….its in fact 0.0383248%*252 = 9.6578548% !

Really awesome article Sir..

eagerly waiting for the option strategies chapters 🙂

Options strategies will start from next month…

Hai Karthik,

Excellent work on making trading concepts simpler…

In module 5, the daily returns of different stocks and indices which are given, can u please tell me what’s there on Y axis, as there are values

of daily returns on X axis…

Y-axis represents the number of days/value. For example (referring to the Nifty chart in section 17.4) – there are 90 days during which Nifty returned an average return of 0.26%.

Karthik sir!! many would have told you, but I would also like to tell u that you are great !! you are making tougher subjects easier and more easier. Thankyou so much!! :-),…

This is reassuring! Thank you so much:)

What’s wrong with calculations? Why it is showing Daily volatility 14% in excel sheet when it is 2.57% on NSE website? Please help me.

Is it because of Stock Split?If yes, What to do in such case? (I was talking about SBIN)

Ah, this could be because of stock split…besides I’m assuming you are doing the calculations right. You can probably check here for historical data – https://in.finance.yahoo.com/q/hp?s=SBIN.BO ….guess they may have adjusted the data for splits/bonus etc.

I would like to know how you calculated the Daily Mean. I noticed in the previous chapter you have mentioned the calculation from Standard Deviation but haven’t mentioned on how to calculate the mean. Even if I take the average like you did for stddev the figure that you show is no where near to what I get. Also in the previous chapter you mentioned the daily volatility is 1.47% and in this chapter you take the daily volatility as 1.046% , is that a mistake ?

Sorry , I understood my mistake. Please ignore my comment.

Sure 🙂

Dear Karthik,

Amazing chapter the narration was exceptionally lucid.

However, there is some doubt whether stock returns follow “normal distribution “. I believe stock returns are more accurately represented by Student’s -t distribution

Sunil – yeah, in a sense stock returns appear more ‘peaked’ hence Student’s – t may fit the bill….my guess is this is especially true for stocks that have been trending well – like Kitex. However the idea here is to introduce the concept of simple distribution in the first place!

Daily Average / Mean = 0.04%

Daily Standard Deviation / Volatility = 1.046%

Current market price of Nifty = 8337

you have multiplied .04*252=9.66%. Please let me know as how you arrived at 9.66%.Can’t it be 10.08%

Need to look into this, will get back shortly. Thanks.

Refer above and even you may lok at nifty range for next 30 days

Av.=0.04%*30=1.15. it should be 1.20% instead.

Thanks…will check this.

Sir, In the above example Nifty, How much period you have taken to calculate mean (o.o4%). i.e from which date to which date.

Regards

2011 to 2015…4 years I suppose. Please do check the excel.

How to convert log returns into simple returns.

How to do that in PC.

Karthik

While calculating nifty’s one year average & SD, you have taken 252 days(Solution 1), but while calculating for 1 month you took 30 days(Solution 2)… any specific reason?

This is the day count convention that NSE follows, hence sticking to the same.

Dear Karthik,

I have done a Nifty analysis according to the 1SD, 2 SD and 3 SD . But i need to check it with you whether all the calculations done by me are right. When i tried i found that its not attaching excel files. So can you give your email id so that i can send my attached working file to you.

Thanking You,

With Regards,

Sonjoe Joseph

Mob.No.94972-82865

Ah…can you please try and put up the numbers here itself?

Dear Karthik,

Please find the attached file were i have made my excel file in the png format. But i don’t think now you will be able to see my formulas since its a picture file. Issues i’m facing not able to apply the frequency formula and OED ie Option Expiry Date LL values are seen as NUM. Please also check all other values so that i can know whether i have applied the formula correctly. One suggestion in the Choose file also add other file formats then only we will be able to attach excel and word files.

Thanking You,

With Regards,

Sonjoe Joseph

Mob.No.94972-82865.

This link explains how to use the Frequency Fn – https://support.office.com/en-us/article/FREQUENCY-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9

Rest of the excel seems to be ok. Can you please apply the frequency fn and check once? Thanks.

Dear Karthik,

Attaching the file after applying the frequency but i think its wrong. Can you give yr email id so that you can check the formulas and can make any corrections if i have done anything wrong. This technique i will be applying in my trading so its better to correct any errors.

Regards,

Sonjoe Joseph.

Mob.No.94972-82865

Done.

Hi SONJOE JOSEPH,

I am also facing the same issue.

Please see the below link how to calculate the frequency. Go to you tube website and search as “frequency function in excel 2010” and please find the below screenshot also.

https://www.youtube.com/watch?v=IelRI5GD2Ik

Thanks for sharing the link Saravana !

Thanks saravana for the link.

Hi karthik , i calculated mean for nifty based on last one year data (08-12-2014 to 07-12-2015), i got mean as -0.03, should we consider negative value or absolute value while calculating upper and lower ranges???

Take the actual value and not really the absolute value.

Dear Kartik,

How can i find out the % winning probabilities of Nifty Call Option and Put Options by taking the reference Nifty future price. I know this can be done in excel by entering the Nifty future price, Nifty Call option Strike price, Nifty Put option Strike price, Call option implied volatility, Put option implied volatility , Days to expiration. In excel there is a function called normdist or normsdist through which it can be done. I tried doing it but not coming correct. Can you help me out if you know the excel function.

Not sure Sanjoe. The normdist function in excel is used in the context of normal distribution. What you are asking for may not be implementable using this (at least as far as i know).

Dear Kartik,

How can i find out the % winning probabilities of Nifty Call Option and Put Options by taking the reference Nifty future price. I know this can be done in excel by entering the Nifty future price, Nifty Call option Strike, Nifty Put option Strike , Call option implied volatility, Put option implied volatility , Days to expiration. In excel there is a function called normdist or normsdist through which it can be done. I tried doing it but not coming correct. Can you help me out if you know the excel function.

Replied earlier.

dear karthi,

i am confusing with sd and volatility both are same? please guide

Volatility is Risk as measured by Standard Deviation.

so if volatility of a stock is .80 then we can conclude that sd will .80 right? (

Yup, but do keep the time frame in perspective.

Sir, Fantastic reading material ……… I am reading it again and again to digest it fully …….. Some queries on the calculations of volatility. Will be grateful to you if you can attend the same …….

1. In your excel sheet, are the values displayed in columns G & H used only for plotting the graph of daily returns?

2. While arriving at the value of yearly average why have you multiplied the daily average by 252 & sq root of 252 when the actual no. of trading days from 29/07/2014 to 28/07/2015 are 244?

3. You have taken the sample data for 1089 days i.e. for 3 years approx. In Sharekhan’s TradeTiger the data is available right from 06/09/2010 So, should we restrict the import of data to 3-4 years only? What should be the optimum size of the sample data?

Please revert.

Thanks & Regards.

CA. Prakash Tanak

1) G & H columns are basically creating bins and plotting a frequency of their occurrence. Of course, this is based on daily returns and used for plotting the graphs.

2) 252 is by practice, yes taking 244 would be more precise.

3) For short term trading, 2 year look back is good. With this calculate 2 year, 1 year, and 6 months volatility to get a fair reference.

Thanks for your prompt reply.

Welcome!

if tatasteel daily volatility id 2.63 and i want to calculate 1sd then I should multiply 2.63 by square root of 30 or square root of working days in a month i.e22?

I feel that it should be sq root of 30 & not 22 because you are more concerned with the no. of days for which funds are locked if invested for 1 month. Am I right, Sir, Karthik Rangappa ?

30 is good I guess.

If you want the 30 day Vol, you will have to multiply with Square root of 30.

How did you get Daily Average / Mean = 0.04%

Sir, I got it from your excel sheet. No need to reply on this query

Thanks 🙂

Add up all the returns and divide it over the number of days you will get the average.

Sir, If I have to estimate the range for next 1 week, how many days data should I use to make these calcualations?

Awaiting your reply on my query of ” If I have to estimate the range for next 1 week, how many days data should I use to make these calcualations?”

5 days.

HI Karthik,

I was trying to calculate the strike of nifty which is worth writing in this expiry, i have followed your steps explained in next chapter. my query

a) Can average daily return be negative. i have observed this while calculating avg mean for teh period 25.03.2015 to 23.03.2016. Due to this negative value i am getting a wrong nifty range for next 8 days(days left for march expiry).

b) I am still confused about number of days to be considered for calculating annualized daily SD and Annualized daily avg , should it be 365 or 248 in my case.

thank you . please correct if i am wrong

1) Yes, averages can be negative.

2) Stick to 365.

Due to negative average i am getting wrong range in which nifty will be trading for next one year.

and is Annual avg mean+ annual SD same as Annual SD+ Annual AVG mean, i am clear, because these both make a lot of difference while average is negative.

Please throe some light on calculations when average is negative.

thanks in advance

Will try and do this sometime soon.Thanks.

*i am not clear

A similar Algorithm has been developed by us, which has given average returns of 70% per year, where all trades would be automatically placed into your own account. you can check http://www.returnwealth.com

Nice, btw have you checked this – https://developers.kite.trade/login , we basically give out APIs to trigger orders. You may find this helpful.

sir, how can we get Daily Average / Mean =???????

Calculate the daily return and find out the average of it : )

how can we calculate 252 daily returns? for one company, as it takes long time to do so

Use Excle, it just takes 30 secs to do this calculation 🙂

WHY BIN WIDTH IS 50 IN DENOMINATOR IN EXCEL SHEET

This is completely arbitrary. It depends on the number of bins you need…for example – if the data set is large, say 500 odd, I’d choose 50 bins.

Honestly, i owe you a drink. This is the way to explain boring concepts. Keep it up. Cheers

I prefer whiskey on the rocks please!

Cheers!

Dear sir,

It may sound stupid but I am not able to resist myself asking this. Why are we calculating daily returns, instead we can find out the SD, mean directly on the daily closing prices itself. This would give us the range of closing prices for 1SD/2SD/3SD directly. I tried doing so but somewhere i am missing something. Not able to find out the mistake. Please clarify.

You can calculate SD and other things on the prices – this gives you how your capital will fluctuate but what really matters is how your returns fluctuate, after all your P&L is a function of the variation in returns. Hence the preference for calculating these on returns and not really prices.

Dear Sir,

I am lil bit confused,Can you tell me

what does dialy volatility in NSE website denotes?

Does it denotes Daily Average or standard Deviation?

is there any relation ship between Average and Standard Deviation?

Daily Volatility represents the daily standard deviation. Daily standard deviation gives you a sense of how much the stock/index can vary on any given day, which in other words is the volatility of the stock/instrument. Higher the daily standard deviation, higher is the volatility of the stock/index.

Thanks Karthik for simplifying things for us!! I have few doubts related to volatility chapters. Could you pls share few mins to resolve.

1- In chapter-15, we simply calculated the yearly range of Nifty, with spot price and annual volatility. But, included daily avg/mean in the following chapter-17 for the same. Pls elaborate.

2- Why do we use log return instead of simple return? And why did we again converted log percentage into regular% to get the range value.

Thanks in advance

Aditya

If you need a yearly range, you need to take the yearly number. If I have used daily numbers, I may have done a mistake. I need to re check this.

For log returns, please check this – http://tradingqna.com/3574/why-stock-returns-are-calculated-in-log-scale?show=3574#q3574

Thank you sir. My question: If I sell 420 CE at 1.00 for a lot size of 1500 (at this time the spot price is 360). So I get the premium of 1500. On the expiry day the Spot price is below 420. So I do NOT square off and allow the trade to expire on its own so that I keep the premium received. Now how will the tax and other charges be calculated in this case. can you please explain with this example. Thanks.

No tax implication here as you are a seller of the option. However, if you had bought options and let them expire in the money then you will have to pay a hefty STT, check this post – http://zerodha.com/z-connect/queries/stock-and-fo-queries/stt-options-nse-bse-mcx-sx

kindly show the calculation ,if i know max,min and average volatility then how to calculate standard deviation.if i know only three thing max,min,average volatility value.

The SD calculation on excel is explained in the chapter. Request you to kindly go through the same.

Dear Karthik

Wonderful explanation and a common man also can understand the way you have explained it. One question is still unanswered which is on look back period. There must some rule for look back period for a given future time horizon. Say I wish to get 99.7% confidence for next one week range prediction, what should be look back period for SD, Similarly what should be look back period for SD for next one month range prediction. If I choose any number of look back period, the SD and average return will vary and output range will also vary, may not be to a great extent but still theoretically we should plug the question to be more precise.

The higher the Standard Deviation, the higher is the accuracy. However, with higher accuracy the range also increases. For example 3SD is more accurate than 2SD, which is more accurate than 1SD. Accuracy in this context is simply because of the virtue of it including all possible data points.

Thanks for your reply…… my question was on look back period, should we always take last one year data or we should change the range? Apart from this, you have suggested to add last one years average gain to find out future range = spot price ×(average return+volatility %)for upper limit. Will it not be wrong when market goes in apposite direction. May be the past return was calculated in uptrend and now there is downtrend.

The look back period depends on the extent to which you intent to predict the raneg. If you are trying to forecast the range for the next one year then clearly look back period of 1 year is required.

Also, we consider both the upper end and lower end of the range…so this kind of takes care of down side.

Dear Karthik, thank you for the wonderful article.

I have a question on calculating yearly and 30 days SD from the NiftyExample.Xls. while calculating yearly SD we use Sqrt.252 which is the # of trading days and Sqrt.30 while calculating monthly SD which the total number of days.

From NSE website the way they have calculated ‘Annual volatility’ is daily volatility*sqrt 365 [ for example for nifty ]

Can you please help me in understanding what is the best way to derive Annual volatily and X-period volatility from daily volatility

Thanks and Regards

Sekhar

I’d suggest you stick to the time count convention used by NSE i.e 365 as you would be more aligned to market prices.

Karthik, Thank you for your prompt reply. Similarly, Can I go with actual number of days[ ex: 7 for a week, 30 for a month] in calculating SD for any period?

Regards

Sekhar

You can adopt any time count convention as long it is aligns with market rates.

Karthik, Yeah, got it, thanks a lot Karthik

Regards

Sekhar

Welcome!

Dear Karthik,

Thank you for the wonderful article.

I wonder is there any web site who can provide the high/low volatile stocks compared to their 30-day,180-Day, 1 year averages?

What is the best time period to compare to understand if the stock is high/low volatile compared to historocal volatility – i.e 30-day,180-day,1 yr?

I am assuming by knowing high/low volatile stocks compared to their historical volatility one can understand the options on that underlying stock are having high/Low IV index – is this assumption correct?

Could you please help me in understanding the above

Regards

Sekhar

Unfortunately, I’m not aware of any site which would give this information (accurately). One can certainly develop a deeper insight into trading options by looking into Volatility.

Karthik, Thank you for the reply.

I have a question on NSE web site’s Daily and Annualized Volatilizes and options IV an don Vega values

1.Does it make any sense to compare annualized volatility with options IV [ from options chain ] ?

or Do we need to theoretically compute option prize every time to understand if the option premium is over priced or under priced?

2.Is there a way to quickly understand if the option premium is over/under priced and the IV is high/low?

3. can we derive some useful information on IV from options Vega values [ assuming if we have Vega live data ], meaning if Vega is above value X the IV is high and vice versa

Appreciate your help,

Regards

Sekhar

Regards

Sekhar

1) No, but lots of traders use this from a ‘quick and dirty’ perspective to get a sense of how cheap or expensive the option really is

2) Yes, most easiest method is by comparing IVs with historical volatility, also I’d suggest you read the note on ‘Volatility Cone’.

3) Yes, again I’d suggest you read the note on Volatility cone.

Thanks.

Karthik, Thank you for your prompt reply.

Sure, I will revisit the Volatility Cone section

Regards

Sekhar

Cheers.

Hi Karthik,

I am trying to link between previous chapter and the beginning of this chapter. Not sure whether I am missing any thing here.

In the previous chapter(16) I learned about the Daily Returns, Daily Volatility and Annual volatility.

However in the beginning of this chapter(17) you have mentioned that,

“In the earlier chapter we had this discussion about the range within which Nifty is likely to trade given that we know its annualized volatility. We arrived at an upper and lower end range for Nifty and even concluded that Nifty is likely to trade within the calculated range.”

I am confused that where did we discuss about the upper and lower range of Nifty. I see only for Wipro.

Also the below points from this chapter.

”

Daily Average / Mean = 0.04% —- How do calculate this?

Daily Standard Deviation / Volatility = 1.046% —— Is this daily volatility?

Current market price of Nifty = 8337″

I am sorry to bother you. I have been reading this Options module for last one week. I stuck here as I could not find the link between end of the chapter 16 and the beginning of the chapter 17.

I guess the range calculation is mentioned in chapter 15, towards the end. We discuss the range for both TCS and Nifty.

Daily average is simple – select the daily returns and use the function ‘=Average()’ in excel. The same is explained in the excel.

Yes, that is daily volatility.

Good luck.

Hi Karthik, I could answer my above questions by visiting the last two chapters and the excel sheet which you shared. sorry for posting the basic questions above. Thanks for your detailed explanation.

Glad you did 🙂

Solution 1 – (Nifty’s range for next 1 year)

Average = 0.04%

SD = 1.046%

Let us convert this to annualized numbers –

Average = 0.04*252 = 9.66%

SD = 1.046% * Sqrt (252) = 16.61%

I have a confusion with the multiplication of figure 252 (i believe we should use 365 here instead of 252 ) in the above equation and siting example from the 30 day Mean and Std. dev numbers from 4-5 lines below., i also believe that in the above quoted equation ( the one i am citing in this example ) The average should (Annualized ) should be written as = 0.04%*365 & not Average = 0.04*252 = 9.66%

Please clarify my both doubts

Yes, you can use 365 days, no problem with it. In fact, I think NSE uses the same.

Hi

Would you please let me know, in distribution of Nifty’s daily returns graph (image with blue vertical lines), x-axis is % of Returns, what is the y-axis.

Thanks

Y-axis represents the number of times a certain return has been generated in the past. For example in Nifty, approximately 80 times a return of 0.2% has been generated.

Hi

Thanks for clarifying, now it makes sense.

Regards

Cheers!

Sir i have one doubt ! Why do we use log normal while calculating returns.. rather than simply multiplying with the no. Of days

Using log normalizes the numbers and brings it to the same plane.

Thank u sir!

Welcome!

In the previous chapter, you had used 365 as the base. However, you have used 252 as the base here. Why so ?

Ideally it should be 252.

Hi zerodha

when calculating L&T daily avg i am getting negative value, (i.e daily avg = -0.14%, so annual = -36.13%), But Std Dev annual = 28.07 is positive. because of which i cant able to get upper and lower range. eg ( upper range = avg+sd= -36.13+28.07=-7.06% and lower range = -36.13-28.07=-65.01) so upper range of LT 1467 and lower range 826. But current price is 1583..

So it is a 3 sigma event 🙂

hi karthik,

If you can explain how to calculate upper and lower value when daily average is Negative, with support of example it will really helpful.

Will try and do that sometime soon, Nakash.

Sir, you pls calculate exponential of the log to get the simple return. it is very confusing

Thats the math part 🙂

Sir how did you get these numbers 68%,95%,99.70% is these are natural

Check out section 17.2 – the Galton board experiment.

Really Appreciate all your efforts to write such a nice article/book. The articulation, language and the ease in explanation is really amazing. You are an Star Karthik!! Keep it up.

Thanks for the kind words, happy learning 🙂

how to calculate Daily Average / Mean = 0.04% of nifty.

Use the standard excel function ‘=AVERAGE()’ on the daily return time series.

Dear Sir, Thank you for such wonderfull writing, I have become a real fan of your articles. I was doing the calculations here for Federal bank and I dont get a normal distrubution, can you please help to check this and let me know if this is not in normal distrubution. I took the data for Federal bank for last year i.e 1.1.16 till 31.12.16. I really appreciate if you can help me understand if Federal bank is in normal distrubution or not.

Thanks for the kind words, Murali. Most stock returns are normally distributed. I will check this as soon as I can.

First I wanted to thank you for detailed and elaborate info.

I have few questions to ask

How do you convert log percentage to normal percentage? or am I missing something. what does exponential of a percentage signify. Please if you could explain this. just once

To convert a log number back to a normal percentage, we take the exponential.

Hey Karthik,

To calculate the SD, why do we consider the daily returns. Why can we not do it using the closing prices?

i.e. take the closing prices, calculate the mean, variance and then SD?

Thanks.

For all such calculation we consider the daily returns, and not closing prices. One of the main underlying principles is that the returns are normally distributed while the closing prices (or stock price in general) are not.

First I wanted to thank you for detailed and elaborate info.

i wanted to ask you one question

in the excel file why have you taken bin width as =(H8-H9)/50.. why 50?

thank you

50 is the number of bins. You can select this based on the number of data points you have. Higher the number of data points, larger is you bin size. No preset formula to decide this.

hi ,

How can i find probability of particular (given) value in next two month when the current value is given . pl guide me

If the given value stems out of series which is normally distributed, then you can extent this and assign a probability to it.

I calculated historical volatility using stdev.p function with simple return (B3/B2-1) data of index during one year. In order to have max & min value we need to have mean of the return.

How can we calculate mean value? Just using “mean” fn of excel, i think , is not sufficient. The mean should be calculated using normal distribution.

You can use the ‘=Average()’ fn in excel.

I tried deriving the daily average from daily volatility for nifty from the nseindia site

daily volatility is the exp( daily average)

(a)Current Nifty : 9615

(b) daily volatility(sd) : 0.49 – nseindia

(c) days to expiry : 17

(d) daily average : ln(b) = (-0.71)

(e) volatility for 17 days : (b) * sqrt(c) = 0.49 * sqrt(17) =2.02

(f) average for 17 days = 17 *(d) -0.71 =-12.12

when i add e and f for the upper range for 1 sd i get a negative number and hence resistance is coming lower than the current price.

For other stocks this method is working fine.

Daily average can be calculated by running a simple average function on stock return. Step (d) seems redundant.

I am trying to avoid the downloading of historical data .. as daily volayility is readily available.

When i tried the above for nifty, the 1sd upper range came in negative for nifty.

This worked for me till now but when the upper range is negative the resistance goes below the market price and hence wondering what am doing wrong.

I can send you the excel template on the mail which might help in understanding what and where am going wrong

Not sure if I can run through the excel, Varun. I think you are taking the wrong approach while calculating daily average.

When I calculated the average daily returns for nifty for the last one year ( 15 jul 16 to 14 jul 17) the value turned out to be negative. And when I annualized the returns ( both volatility and average daily return) and added them it’s still a negative percentage for the upper limit at 1sd. How come this be true? Does that mean nifty is bearish?

Also in your calculation, you have taken the average of daily return for nifty over 4 years. What is the rationale behind that? How many years of data should one take to find the average daily returns and volatility if you are looking for a time span of 15-30 days vis-a-vis 3-6 months?

The daily volatility shown in nse website, is it calculated in ln scale?

Avg daily returns can be negative, however, this may not be the case with Nifty as the markets have been trending upwards. Ideally, for a 15-30 day period you should look at maybe 1-1.5 yrs of data…and for 3-6 months…at least 2 years.

I’m not really sure about NSE’s calculation.

In the example of volatility used to calculate the stoploss( airtel), The upper and lower range for 5 days is computed by using volatility only. Shouldn’t we take the avg. daily return of airtel, in this case, to calculate the range for 5 days as done is previous examples?

Also, I would like to thank you for the articles you have shared here. Great work.. Kudos.

We should be using the average daily return. Let me recheck this. Good luck and happy learning.

Why I can’t extend Freq column range in your formula.Please help me.I want to add more column data.As per your data range = FREQUENCY(E9:E1096, G14:G64). I can’t extend more than E1096 data range.What i want to do? Thanks for advance. I m trying to attach snap shot. But don’t have option.

You just have to extend your selections to accommodate more cells.

Thanks for your reply.But actually array formula can’t edit as usual.I find from the forum for edit. “After you edit, press CTRL+SHIFT+ENTER not just ENTER.”That’s all. I really need to lot of thanks such a wonderful describe about options. I like it so much the way you example and describe.You have a great talent.Hats off to you.

Yes, all array function requires you to key in Crtl+shift+enter.

Good luck and happy learning, Anantharajan 🙂

How did you calculate MEAN 0.04%?

Average of returns, use excel function ‘=averege()’.

Thanks @Karthik. Standard deviation for sample data you have taken is 1.046%. Can you specify the date range for which you have calculated? When I try to calculate it from (2014,7,22) to (2015,7,22), it is 0.896.

I’d request you to please check the excel 🙂

Hello,

I have one doubt here.

In above excel you calculate probable volatility. For that you have consider nifty from 2011 to 2015.

Now, suppose I have calculate volatility for net 10 or 15 days only so, for that how much past record should we consider.

Means, to calculate volatility for next 10 to 15 days should we consider the same past record for 10 to 15 days back.

or, is it a random selection…?

Not random, really 🙂

If you want to do this for 10-15 days, then you need to look at at least 6 months of past data.

1)When i calculate Daily volatility it comes 2.04 for tata motors for last 365 days.Am I right???Is my calculation correct ???On NSE website it show daily volatility has 1.65.

2)When i calculate average for tata motors for last 365 days its comes -.091% it it right value , could please advice.

Venu, try the same calculation for 252 trading days. Also, I guess NSE’s volatility calculation method is slightly different. Average could be correct. Hope you’ve used the excel function ‘=avergea()’, on the returns.

Hi Sir,

Can we do the 1SD and 2SD and 3SD calculation as simple like this …

we can get the Daily Volatility Data from NSE website on daily basis, “… In the NSE home page, click on ‘All Reports’ and select ‘Daily Volatility (CSV) … ” …

On Nov 21st, Jetairways Daily Volatality ( means 1 SD ) is 0.0368

If we want to know the next 15 days Volatility can we calculate like this ,

1SD : 0.0368 -> 0.0368 * 100 = 3.68%

1SD * Sqrt(15) = 14.25%

CMP : 663.50 ( close price )

14.25% ( 663.50) = 94.54

So the Upper range ( 663.50 + 94.54 : ) is 758.04 and Lower range ( 663.50 – 94.54 ) is 568.96 …

2SD Calculation ( 1SD * 2 ) is 94.54 * 2 = 189.08 …

Can we proceed like this …

Yes, this works!

Thank you Sir …

Cheers!

What about adding SD to Avg and then finding a range

You can use Bollinger bands for that!

If we want to calculate the Daily volatility. How many days shall we consider for calculating daily returns. I mean last one year, two years , four years(As per your excel) or more. And can we interpret that more number of days means more accurate results we will get

At least 1 or 1.5 years will help.

Note these % are log percentages (as we have calculated this on log daily returns), so we need to convert these back to regular %, we can do that directly and get the range value (w.r.t to Nifty’s CMP of 8337) –

Upper Range

= 8337 *exponential (26.66%)

= 10841

And for lower range –

= 8337 * exponential (-6.95%)

= 7777

how to calculate exponential what is formula plz help

You can you the ‘=exp()’ function in excel for this.

how to calculate exponential plz help

Can you please elaborate on how did you arrive at 1.046 ? Daily Standard Deviation / Volatility = 1.046%. I am stuck here and it will be very helpful. _/\_

Let me check this Rohit.

Awaiting for your reply.

I’d suggest you run the excel function ‘=stdev()’, on the daily returns of Nifty. Do this on the same set of data that we have used in this chapter.

Hi Karthik,

Instead of using Log Average Calculation, can we use the normal Calculation for Daily Returns like this,

“” (current close / previous close) – 1 “”. It gives same Results, still I need to cross check again …

Yes, you can use that calculation.

In the excel sheet downloaded, for “bin width”, you have calculated =(h8-h9)/50. How did you arrive at number 50? Also find a way to update website, so that we get email alerts, whenever you respond. It haappens in other websites.

50 is arbitrary and I assumed it based on the number of data points. It can be 25, 50, 100, or 200 based on the number of data points.

Hi Karthik. This is Omprakash. Article was really wonderful. I have downloaded the Nifty Example Sheet. I am little bit confused. Just tell me whether put the close price of any stock for 1 year I can get the data correctly means the range it will trade for the month If I am wrong please correct me.

You can try doing that, OM. Please check if the cells are properly linked to the formula.

Also whether i have to change anything in frequency ?

I have downloaded 1 year data of aurobindo pharma stock close price form nse since it allows for 365 days but nothing changed in excel sheet after filling in price column. can you please help me sir….

I have done all the calculations for NIFTY expiring 22nd feb and upper limit tends to be 10720 and suppose if we sell Call option 10750. How to calculate the stop loss referring to the above example? The OTM transits to ATM (10750)? Thank you.

In this case, you really need to have a tight SL as you’ve already have buffered enough safety. For example, if I have written the option for 10, I’d start feeling uncomfortable the monment it goes above 12 or 13.