- HubPages
*»* - Personal Finance
*»* - Investing in Stocks, Bonds, Real Estate, More

# 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

## 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

## 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

- 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.
- Calculate P in column D1 by inputting the formula “={LN((A1)/(B1))}^2”
- Calculate
**Volatility**in column E1 by inputting the formula “=SQRT(SUM(D1:D10)/10)*SQRT(252)*100” - 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 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:

- At implied volatility equals 20, the price of the option is $1.31
- At implied volatility equals 21, the price of the option is $1.40
- At implied volatility equals 25, the price of the option is $1.72
- At implied volatility equals 50, the price of the option is $3.77
- At implied volatility equals 75, the price of the option is $5.82
- 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.}

## Comments

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

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

thanks, that is really helpful.

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

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!

Statistics. Gotta love it!

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)

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

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.

I am not good with complicated calculations but thanks!

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

Sorry but is something which is way above my head.

12