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.

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.