How to calculate historical volatility in Excel for any asset

Professional investors need an indicator for stock price fluctuations in any financial market. Because stock prices vary on a daily basis, it is required to quantify these changes effectively. In this post, we’re going to see how we can calculate the historical volatility for any asset class publicly trading.

Basically, the two most important metrics affecting the value of a particular stock are supply and demand.

What is historical volatility?

The historical volatility simply refers to the measure of the past performance of any asset. This means, the price fluctuations a stock experiences on a daily, weekly, monthly, quarterly or even on an annualized basis.

In other words, it is a statistical indicator of the dispersion of returns for any asset in a certain period of time.

Note, the higher the historical volatility of a stock, the higher the risk involved in it and the higher the potential profits on trading the given security.

Computing historical volatility

First, let’s download the daily historical data for a stock or index. In this example, we’re going to use the DAX PERFORMANCE-INDEX.

To download the required data, simply visit a data provider such as Yahoo Finance or Stooq.

1. Search for your stock symbol or ticker; ^DAX (in this case).

2. Visit the historical data area.

3. Enter your preferred START DATE and END DATE & Click on Apply / Show.

4. Download data in CSV file and open with Excel.

5. Convert your spreadsheet into an Excel file format.

Now you should have something like this:

computing-historical-volatility-with-excel

Calculating historical volatility in Excel

Now that we have our data downloaded into an Excel spreadsheet, we can start working on it. Remember, keep it stupid simple (KISS principle). In order to follow this principle, we will get rid of useless data for our calculations.

  • Delete; Open, High, Low & Volume.
calculate-historical-volatility-dax-index

Simple Return and Log Return Formula

The first step towards finding out the historical volatility of our given security, is to compute both, the simple daily returns and the log daily returns.

Simple Returns

simple-returns-formula

Log Returns

log-returns-formula

Standard Deviation Calculations

In this step, we will use Excel to determine the standard deviation for different time periods.

For instance, we will use 21 days to calculate the standard deviation for one month, as there are 21 trading days in a month, on average. The same goes for a quarter, 63 trading days on average.

  • 1 month: 21 trading days.
  • 3 months: 63 trading days.
  • Semi-annual: 126 trading days.
  • Annual: 252 trading days.

Simply, use the Excel formula STDEV.P as follows:

standard-deviation-calculation-with-excel
standard-deviation-quarter

Historical Volatility

Now that we calculated the standard deviation, we can use these numbers to identify the historical volatility. Our recommendation is you create a table to calculate the historical volatility for any time period.

For example, we can annualize the 21 days standard deviation, simply by multiplying STDEV.P by the SQRT (square root) of 252 trading days.

  • STDEV.P (D2:D22) * SQRT (252) = HISTORICAL VOLATILITY
historical-volatility-annualized

If we would want to calculate the historical volatility for a smaller period of time, instead we could multiply by the square root of 21, or by the square root of 63 and get the monthly volatility.

Calculate stock price

It is our duty, not to share freely, such powerful information. However, we will proof to you, how this methodology accurately calculates the future value of any financial security. For more information on this topic, please contact us privately.

DAX-volatility-calculus

If you’re familiar with statistics and normal distributions, you would know it goes against zero to fall lower than 3 standard deviations ( = 0.1 % ) and ( = 0.00006 % ) to fall behind 4 standard deviations.

Based on our calculus, we would assume the value of the DAX on the given day wouldn’t fall lower than $12,174.06.

See how the market reacts when pointing at $12,174.22.

DAX-low

On the 26th of October of 2020, the DAX PERFORMANCE-INDEX falls down to $12,174.22 and immediately reacts back to the upside. The probability that the market falls lower would be of 0.1% or less.

historical-value-DAX

Need a more detailed calculation? Order our services to get a more accurate analysis.

Posted in Investing.

One Comment

Leave a Reply

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