power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Annualized Historical Volatility

Returns the annualized historical volatility of a set of price data.  This value is one of the key inputs into the Black-Scholes formula, and is a primary driver of the value of an option.


Syntax  (Values in boldface are required; others are optional)

AVol
(Prices, Dividends, DataInterval, AnnualTradingDays)
  • Prices:  An array or reference to cells that contain the set of periodic prices of the security whose volatility you wish to determine.  If no dividend data is supplied to the function, the prices are assumed to be adjusted for historical dividends.  May be daily, weekly, monthly, or any other regular data interval.  A minimum of three data points must be supplied for this argument. 
  • Dividends:  An array or reference to cells that contain dividend data, if any, corresponding to the price data.  The data range must match the range you supply for Prices in size; typically you would reference one column in a worksheet for Prices data, and an adjacent column containing Dividends data corresponding to the Prices.
  • DataInterval:  The number of trading days per interval in Prices.  For example, 1 indicates daily price data; 5 indicates weekly price data; (AnnualTradingDays/12) indicates monthly price data.  If omitted, assumed to be daily price data.  You may also use the following shorthand notation for this argument:
    • "A" for annual price data
    • "S" for semiannual
    • "Q" for quarterly
    • "M" for monthly
    • "B" for biweekly
    • "W" for weekly
    • "D" for daily

    When using this shorthand notation, be sure to include quotes around the value.

  • AnnualTradingDays:  The number of days per year in which the security is traded.  If omitted, assumed to be 252.  (This argument is generally be left to its default value, but may be useful for analyses such as determining whether markets are "volatile" on weekends and holidays when no actual trading occurs.) 

 

 

Examples

The first example demonstrates how the shorthand function argument "w" is used to indicate weekly price data.  Leaving the second argument blank lets the function know that prices have already been adjusted for dividends.

 

The second example shows calculation of volatility based on unadjusted daily prices, with dividend information supplied in the second argument.  Since the default "DAILY" data interval is used, the remaining function arguments can be omitted.

 

Notes

We have found a great deal of misinformation on the Internet as to what is the correct measure of volatility for the Black-Scholes formula.  Volatility is emphatically NOT the same as "beta", which measures the correlation of a security's price movements with those of the overall market.  Neither is volatility simply a measure of the standard deviation of a security's closing prices over time. 

For purposes of Black-Scholes calculations, volatility is the standard deviation of the periodic percent change in prices, divided by the square root of time.

The Black-Scholes formula is based upon assumptions about random movements of security prices over time.  The equations assume correlations among daily, weekly, monthly, and annual price movements.  Thus in theory the calculation of volatility would be more or less the same regardless of whether you used daily, weekly, or monthly closing prices as your data inputs.  In practice, however, there tends to be a good bit of variance in volatility calculations using different price intervals. 

Using the Excel function provided here, you can measure annualized historical volatility of a security using any interval of closing price data - daily, weekly, monthly, quarterly, etc.

You may supply price data to the function either "raw" or adjusted for dividends.  If you supply unadjusted data, then you should also supply dividend data (the second function argument) so that price movements attributable to dividend payments will not artificially inflate the volatility measure.

Popular historical quote download services such as Yahoo! Finance (http://finance.yahoo.com) usually provide adjusted closing price data as well as unadjusted with dividend data.  

 

Code
To install this function, copy the entire contents of the window below into your power4XL code module in Excel's Visual Basic editor.

For detailed instructions, see the "Individual Function Setup" page.

To copy the contents to your clipboard, place your cursor inside the window below, then press "CTRL+C".

NOTE:  By copying the code below, you are agreeing to the power4XL license terms.  You agree to use the code only on your personal and/or business computer(s) for your own personal use.  You agree not to distribute, publish, alter, or edit the code in any way.  You may freely refer others to this website if you wish to make the code available to them.

 

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.