How To Calculate Historical Volatility in Excel

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

Home

Stock Options

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.

Public Function AVol( _ Prices As Range, _ Optional Dividends, _ Optional DataInterval = 1, _ Optional AnnualTradingDays = 252 _ ) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. Dim MyVal As Variant Dim DailyReturns As Variant Dim DailyReturnsSquared As Variant ' Set the TimePeriod value to 1. '(This value is used differently in variants of this function.) TimePeriod = 1 'Validate data: 'Range must have at least 3 datapoints (= 2 intervals) for calculating standard deviation 'NOTE: DataCount is used later in the function as well. DataCount = Application.WorksheetFunction.Count(Prices) If DataCount < 3 Then AVol = CVErr(xlErrRef) Exit Function End If 'Data ranges must be only ONE column wide OR one row high If Prices.Rows.Count > 1 Then If Prices.Columns.Count > 1 Then AVol = CVErr(xlErrRef) Exit Function End If End If If Not IsMissing(Dividends) Then If Dividends.Rows.Count > 1 Then If Dividends.Columns.Count > 1 Then AVol = CVErr(xlErrRef) Exit Function End If End If End If 'DIV range must be same size as PRX range: PricesSize = 0 For Each MyVal In Prices PricesSize = PricesSize + 1 Next If Not IsMissing(Dividends) Then DivSize = 0 For Each MyVal In Dividends DivSize = DivSize + 1 Next If DivSize <> PricesSize Then AVol = CVErr(xlErrRef) Exit Function End If End If 'Deal with AnnualTradingDays. In the US public markets, 'a typical year has 252 trading days. Since price movement 'occurs only on trading days, a year is counted as the number 'of trading days rather than 365 days. ' 'Some markets or assets may experience price movements 'for a different number of days. This optional variable 'allows the user to select an alternative number of 'annual trading days. If no input is provided, the number 'is 252 by default. If Not IsMissing(AnnualTradingDays) Then If Not IsNumeric(AnnualTradingDays) Then AVol = CVErr(xlErrValue) Exit Function ElseIf AnnualTradingDays > 365 Then AVol = CVErr(xlErrValue) Exit Function End If End If 'Now adjust for the DATA INTERVAL passed to the function - 'i.e., does the Prices range contain daily, weekly, monthly, or other 'data? ' 'DATA INTERVAL can be passed to the function as a NUMBER, 'represented in NUMBER OF TRADING DAYS (i.e., for weekly volatility, pass "5" 'OR, may be passed with a one-character key as follows: ' A = ANNUAL ' S = SEMIANNUAL ' Q = QUARTERLY ' M = MONTHLY ' B = BIWEEKLY ' W = WEEKLY ' D = DAILY If Not IsMissing(DataInterval) Then If Not IsNumeric(DataInterval) Then If Not TypeName(DataInterval) = "String" Then AVol = CVErr(xlErrValue) Exit Function ElseIf Len(DataInterval) > 1 Then AVol = CVErr(xlErrValue) Exit Function Else DataInterval = UCase(DataInterval) Select Case DataInterval Case "A" DataInterval = AnnualTradingDays Case "S" DataInterval = 0.5 * AnnualTradingDays Case "Q" DataInterval = 0.25 * AnnualTradingDays Case "M" DataInterval = AnnualTradingDays / 12 Case "B" DataInterval = AnnualTradingDays / 26 Case "W" DataInterval = AnnualTradingDays / 52 Case "D" DataInterval = 1 Case Else AVol = CVErr(xlErrValue) Exit Function End Select End If End If End If 'Now prepare to do the math. DailyReturns = 0 DailyReturnsSquared = 0 'Find the first data within the Price range variable i = 1 Do Until Not IsEmpty(PrevVal) PrevVal = Prices(i) i = i + 1 Loop FirstData = i 'Now caculate daily returns TodaysDiv = 0 n = 1 For Each NewVal In Prices If n >= FirstData Then If Not IsEmpty(Prices(n)) Then If IsMissing(Dividends) Then TodaysDiv = 0 Else TodaysDiv = Dividends(n) End If ThisDailyReturn = Application.WorksheetFunction.Ln((NewVal + TodaysDiv) / PrevVal) DailyReturns = DailyReturns + ThisDailyReturn DailyReturnsSquared = DailyReturnsSquared + (ThisDailyReturn ^ 2) PrevVal = NewVal End If End If n = n + 1 Next Part1 = DailyReturnsSquared / (DataCount - 2) Part2 = DailyReturns ^ 2 / ((DataCount - 1) * (DataCount - 2)) AVol = ((Part1 - Part2) ^ 0.5) * ((TimePeriod * AnnualTradingDays / DataInterval) ^ 0.5) End Function

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.