Calculating Historical Volatility

Historical Volatility shows how volatile an asset has been. There are numerous ways of calculating it, and I will show the most simple one here.

Here’s how you calculate it using Excel or LibreOffice Calc if you so will.

* Create a column A with 30 prices
* Calculate the Daily Log Return into column B, using formula =LN(Ap/Ap-1) for every day.
* Calculate the average of that daily return (AVG_RETURN)
* In column C, calc the SQUARED DIFF from the AVG_RETURN for each day =POWER(Bx-AVG_RETURN, 2)
* Calculate the AVERAGE of column C (this is the VARIANCE)
* Calculate the SQRT() of the AVERAGE, this is the STDDEV.
* Verify that STDEV.P(column of daily returns) equals your calculated value

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.