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.
(Values in boldface are required; others are
Dividends, DataInterval, AnnualTradingDays)
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.
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,
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
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.
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
Function Setup" page.
To copy the contents to your
clipboard, place your cursor inside the window below, then
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.