如何用Excel计算投资组合的历史波动率

Calculate Historical Volatility in Excel

This spreadsheet calculates the historical volatility of a stock. It uses returns data automatically downloaded from Yahoo.

Historical volatility is the standard deviation of an asset’s
historical returns. The standard deviation is calculated over a moving
time window.
历史波动率是一项资产历史回报率的标准差，而标准差会随着统计期间的变化而变化。

The historical volatility of a stock is distinct from implied volatility of an option.
The former represents past movements in price. The latter represents
future expectations about price movements, and is calculated from the
option price.
股票的历史波动率与以该股票为标的资产的股票期权的隐含波动率不是一回事，历史波动率反应的是股票成交价的历史走势，而隐含波动率反应的是市场对股票未来走势的预测，是根据期权的报价倒推出来的。
By comparing the historical volatility of the underlying to the
implied volatility of the option, investors can judge if the option is
cheap or expensive. If the implied volatility is high, then selling the
option is sensible. However, if the implied volatility is low, the
option is a good buy.
通过对比一项资产的历史波动率和其期权的隐含波动率，投资者可以判断期权的报价是高还是低。如果比较之下隐含波动率的水平更高，那么卖期权的做法就是靠谱的；如果隐含波动率的水平低很多的话，买期权就应该成为不二选择。

[h1]How to Calculate Historical Volatility[/h1]

Calculate the natural log of the current stock price to yesterday’s stock price. This is the continuously compounded return.
Calculate the average return over a moving time window of n days. A
value of n = 21 represent the typical number of trading days in a month,
and is often used. Values lower than this tend to produce lots of noise
in the results. The greater the time window, the smoother the results.
Calculate the standard deviation of the returns over the moving time window.
Annualize the daily standard deviation by multiplying by the square
root of the number of days in a year. The average number of trading days
in a year is 252.

[h1]
[/h1]如何计算历史波动率

先计算本交易日收盘价环比上一交易日收盘价的涨跌幅度的自然对数，公式：
，其中S_t为本交易日收盘价，S_t-1为前交易日收盘价，R_t为自然对数计算结果；[sub][/sub]
计算某一统计周期的平均回报率，通常一个日历月份里有21个交易日，如果交易日数量明显少于21，计算结果的波动情况就会很大，周期内天数越多，结果的波幅就会越平坦，每个统计周期的天数均为21天，但随着交易日的变化而依次下移，公式为：
，其中n为统计周期的交易日天数，收盘价，S_t-1为前交易日收盘价，R_t为自然对数计算结果；t为交易日的天数，m为最新的统计日
计算各统计周期平均回报率的标准差，公式为
截止到目前为止，得出的结果为资产日均收益率的标准差，下面需要对结果进行年化处理，即乘以每年交易日天数的平方根，年交易日天数一般为252

[h1]
[/h1][h1]Calculate Historical Volatility in Excel[/h1]

The spreadsheet automates the steps described above, and is simple to
use. Simply enter the stock ticker, the start and end dates, and the
volatility window (i.e. the number of days over which the volatility is
calculated).

The end date is set to NOW() by default, which gives the current date.
After clicking the button, the spreadsheet downloads returns data
from Yahoo using VBA. Then, the chart will plot the historical
volatility (based on the daily adjusted close)
计算示例：以谷歌公司GOOG的股价为例，计算2009-1-2至2012-3-17期间的投资波动率，假设一年有365天，波动率统计周期的天数为30天，结果见下图

如果将一年设定为252天，波动率统计周期的天数为20天，结果见下图