ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on June 6, 2012


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.


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.


    0 of 8192 characters used
    Post Comment

    • profile image 

      5 years ago

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

    • monicamelendez profile image


      6 years ago from Salt Lake City

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

    • Island Tropical profile image

      Island Tropical 

      6 years ago

      thanks, that is really helpful.

    • sarasotadui profile image


      7 years ago from Florida, USA

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

    • adrienne2 profile image

      Adrienne F Manson 

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

    • kripkrip420 profile image


      7 years ago from In relation to what?

      Statistics. Gotta love it!

    • Knowing Truth profile image

      Knowing Truth 

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

    • Julie Cole profile image

      Julie Cole 

      7 years ago from Wilmington, Delaware

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

    • Jennie Demario profile image

      Venture Boyz 

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

    • chamilj profile image


      7 years ago from Sri Lanka

      I am not good with complicated calculations but thanks!

    • crystolite profile image


      7 years ago from Houston TX

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

    • Hello, hello, profile image

      Hello, hello, 

      7 years ago from London, UK

      Sorry but is something which is way above my head.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)