matlab 历史波动率,如何用Excel统计历史波动率

How Do You Calculate Volatility In Excel?

by AdamHGrimes

I received a question from a reader who asked, “Can you calculate volatility in Excel?” The answer is, yes you can, but there are a few things you need to know. Without going into too much detail here, there are many ways to calculate volatility. Two of the most common measures are implied and historical (also called realized or statistical) volatility. It is fairly simple to calculate historical volatility in excel, and I will show you how in this post. Calculating implied is quite a bit more complicated. You technically can do it in excel, but you have to impute it from an option price. In addition, there’s actually a volatility surface, or different values of implieds for different strike prices and maturities. That’s a topic for another day; today let’s just look at how to calculate a simple historical volatility in Excel.

有位读者问我“能用Excel计算历史波动率吗？”答案是可以，当然没问题，但有几件事需要了解一下。计算波动率的方法有很多，在此不一一赘述。波动率中最主要的是隐含波动率和历史波动率，历史波动率也被称为实际波动率或统计波动率。今天我们要做的就是探究如何用Excel计算历史波动率。用Excel计算历史波动率很简单，本文将展示如何来做。隐含波动率的计算过程有点复杂。理论上也可以在Excel上算出来，但必须通过期权的价格进行推算。此外，实际上还有波动率曲面的存在，也就是按照不同的期权行权价和不同的期限展示出来的隐含波动率水平。但那是另一个话题，今天我们要探讨的就是如何用Excel计算比较简单的历史波动率。

1.Collect your raw data, in the form of a closing price for each time period