Formula for Standard Deviation, Variance – and Calculate Volatility In Excel

Variance

In mathematics - probability theory and statistics, we learnt that variance is a description of how far values in a data sample lies from the mean, and that variance is one of the moments of a distribution

The standard deviation of a data set is the square root of its variance
The standard deviation of a data set is the square root of its variance | Source

Standard Deviation

We also learnt that Standard deviation is a measure of variability or diversity that shows how much variation there is from the mean. The standard deviation of a data set is the square root of its variance.

To the right is the formula for standard deviation.

Risk in an Investment

In finance, standard deviation is used in determining risk in an investment – that is, standard deviation provides investors with a mathematical basis for their investment decisions. If you would want to trade stocks, bonds, stock options, property, mutual funds, index mutual funds, and ETFs, when you get in to the market you will definitely be hearing much about standard deviation. Standard deviation in finance is known as volatility.

Volatility

In the stock market and financial markets, volatility refers to the standard deviation of a financial instrument within a given time frame. Volatility is thus used to quantify the risk of the financial instrument over the given time period. In finance, volatility is calculated over a given time period and then expressed in annualized terms as a percentage.

In mathematics we know that

volatility refers to the standard deviation of a financial instrument
volatility refers to the standard deviation of a financial instrument

log(10) = 1

And that log(10) = 1, we then break that formula a little bit more (outside the scope of this article) to get volatility as follows:

Let P = LN{(high price)/(low price)}²

where LN is log normal.

Volatility =√(P) * √(252) * 100

We use 252 to annualize because there are 252 trading days in a year and we multiply by 100 to have volatility as a percentage.

If we use the high and low prices for a stock in intraday trading, we get a different annualized volatility value than from when we use high and low prices for weekly or monthly data. So, when you calculate volatility, you must be very careful about this – A security can be very volatile on intraday trading but less volatile on end of day to end of day trading, and so on and so forth.

Excel Formula for Volatility

In MS excel, we can easily compute the volatility of a security or a stock as follows: in this case, we take daily volatility and average for say, the last ten trading days, before annualizing the same

  1. In column A1, B1, and C1, input the high, low and closing prices of a stock. The data can be obtained free from Yahoo Finance, or anywhere else from internet. Input data for several days; say for the last 100 trading days.
  2. Calculate P in column D1 by inputting the formula “={LN((A1)/(B1))}^2”
  3. Calculate Volatility in column E1 by inputting the formula “=SQRT(SUM(D1:D10)/10)*SQRT(252)*100”
  4. Notice we did not use C1 which is the closing price.

The above therefore is the Excel Formula for Volatility (which is also the excel formula for standard deviation).

A stock with a higher volatility has a higher return and a higher risk. The opposite is also true.

Implied Volatility

In stocks, we have stock options. You can read the options basics elsewhere. Ideally, a stock option should have volatility equal to that of the underlying stock. When the price of a stock option is calculated using the volatility of the underlying stock, we get the theoretical price of the option. But hardly do options have volatility equal to that of the stock. The volatility implied by stock options can be higher or lower depending on how the market views the options. The options volatility is therefore called implied volatility. When implied volatility of options is high we say options’ premium is high. The opposite is also true.

options trading strategies
options trading strategies

Options Traders

As much as 90% of options traders do not consider implied volatility when trading options and if they do they do not know how to trade it. Implied volatility is the single most important variable in the pricing of an option. Let’s consider the QQQQ trading at $50 in November 22. Lets then consider the call option with a strike price of  51 for the same QQQQ that will expire in January next year and compare prices at different implied volatility. Everything else will be retained the same:

  1. At implied volatility equals 20, the price of the option is $1.31
  2. At implied volatility equals 21, the price of the option is $1.40
  3. At implied volatility equals 25, the price of the option is $1.72
  4. At implied volatility equals 50, the price of the option is $3.77
  5. At implied volatility equals 75, the price of the option is $5.82
  6. At implied volatility equals 100, the price of the option is $7.85

Its quit normal for implied volatility to change from 20% to 21% and it happens almost everyday. If you had invested $131,000 in such options you will find your investment is now worth $140,000. The opposite is also true. If the implied volatility was to increase from 20% to 50%, your $131,000 will become $377,000. And that can happen in a day. The opposite is also true.

To try to trade uncovered options is very risky and is not advised for beginning traders. Keep off! This is as good as having a flame next to petrol and before you realize there is fire you are already burnt.

Best Trading Strategies for Beginning Traders

The best trading strategies for beginning traders in options are those that use a combination of long options and short options such that when the long option loses, the short option gains. A good example is the option strategy known as calendar spread or horizontal spread. A calendar spread is an option trading strategy where a trader simultaneously purchases options expiring in a particular month and sell equal number of options of the same stock expiring in another month. The difference between the legs of the spread is only the expiration month; the options are based on the same underlying market and strike price. Options strategies involving combination of long and short legs are safer and more traders should trade them than they are currently doing.

If you have liked this article, and you would want this page to keep up and improved, you can help by purchasing some great items from Amazon by following Amazon links and widgets on this page. A free way to help would be to link back to this webpage from your web page, blog, or discussion forums.

The Author’s page is designed to help beginners and average readers make some money as an extra income to supplement what they may be earning elsewhere - details of which you can find in My Page, if you will.

More by this Author


Comments 12 comments

Hello, hello, profile image

Hello, hello, 5 years ago from London, UK

Sorry but is something which is way above my head.


crystolite profile image

crystolite 5 years ago from Houston TX

Nice lecture,thanks for that and i guess you must be a good mathematician.


chamilj profile image

chamilj 5 years ago from Sri Lanka

I am not good with complicated calculations but thanks!


Jennie Demario profile image

Jennie Demario 5 years ago from Floating in the clouds

This was vicious! Thanks, now I need to go take a couple of advils lol.. I'm going to be having nightmares about the old days of math homework.


Julie Cole profile image

Julie Cole 4 years ago from Wilmington, Delaware

Wow this is really helpful! I was looking into learning more about options trading. Thanks for the info.


Knowing Truth profile image

Knowing Truth 4 years ago from Malaysia

Some ideas on annualized standard deviation calculation using excel formula, i.e.

1)list all daily/weekly/monthly closing security prices in a column

2) create a column (as ROI column) to calculate the rate of return between successive period of security prices change, e.g. (day t+1/day t)-1, e.g. column B.

3)select excel statistic formula to calculate standard deviation by using formula STDEV and select all numbers in the ROI column and multiple by either SQRT(225), SQRT(52) or SQRT(12) , depend on security prices used are daily, weekly or monthly price. (note: for yearly prices, SQRT is not required). Example to calculate annualized standard deviation using daily prices: =STDEV(B1:B225)*SQRT(225)


kripkrip420 profile image

kripkrip420 4 years ago from In relation to what?

Statistics. Gotta love it!


adrienne2 profile image

adrienne2 4 years ago from Atlanta

Oh lordy have mercy! This is one of the most technical hubs I have ever come across. Did I understand it? No. Was it impressive? Yes @Julie got and cograts on the perfect score!


sarasotadui profile image

sarasotadui 4 years ago from Florida, USA

Wow! I used to hate statistics. But I do love it this time. Thanks for your well-written hub.


Island Tropical profile image

Island Tropical 4 years ago

thanks, that is really helpful.


monicamelendez profile image

monicamelendez 4 years ago from Salt Lake City

Wow I honestly had no idea that Excel could even do stuff that technical.


ronypurba@gmail.com 3 years ago

Great explanation. but, how to apply the volatility formula to GDP?

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working