How to Calculate Historical Volatility in Excel (with Easy Steps)

Historical volatility is a statistical measurement in a business model for getting dispersion of returns. As volatility is associated with risk factors in business models, it is a must for the management committee to have insight into volatility in a business model. Today we are going to learn about historical volatility and its impacts on any business model. This tutorial will show you how to calculate historical volatility in Excel.

What Is Historical Volatility?

In business aspects, the monetary value of different financial factors (i.e. stocks & currency) keeps fluctuating because of ins and outs of assets by the traders in the market. This variation over a certain period of time is defined as “Volatility”. Volatility is also an indicator of risk associated with the business.

There are several ways to calculate volatility. But most often, business analysts prefer “Historical Volatility”. Historical Volatility emphasizes previous performances. It provides long terms assessment of risk associated with the concerning business model. This is widely used for investment strategies by the management authority. Higher historical volatility is an indicator of greater risk. Even then, some investors look for higher volatility for better profit opportunities.

How to Calculate Historical Volatility in Excel: with Easy Steps

Four consecutive steps should be followed in order to calculate historical volatility.

Putting the historical data in the worksheet
Finding the logarithmic change from the input data
Calculating standard deviation from the change value
Annualizing the daily volatility into annual volatility

Let’s discuss the steps with proper illustrations.

Step 1: Input Historical Data

The first and foremost task is to input the historical data in the Excel worksheet. The daily historical closing price and time span (i.e. Date) are used to calculate the daily historical volatility. So, I have inserted the historical data in column A and column B of the Excel worksheet.

Volatility is a kind of time-bound measurement. It fluctuates over a certain period of time. The timeframe is used as the Date. Notice that we have inserted the Date reversely. The Close Price is used in Currency Format. As we have input the corresponding historical data, we can now use it for the next steps.

Step 2: Find Logarithmic Return

The Historical Volatility is measured as the standard deviation of the natural Logarithmic returns. After inserting the historical data, now we need to find out the logarithmic returns from the data. We will calculate the natural logarithm of a number using the LN function

Here, we have inserted the date in reverse order. So type the following formula in cell D5:

=LN(C5/C6)

Here,

C5 = Closing Price of a particular Date
C6 = Closing Price of the Date before the mentioned date

Then, press ENTER and the cell will calculate the log-returns of the dividend of the close price of the date and its previous date.

Now, drag the Fill Handle tool downward to Autofill the formula for the other timeframes.

Hence, you will find the logarithmic return for all dates.

Here, you can show the change of log return in percentage. Just create another column, use the return and click the percentage icon from the toolbar.

Step 3: Calculate Standard Deviation

Now it’s time to calculate the standard deviation from the logarithmic return. Select the range from your dataset from which you want to calculate the standard deviation. Here, I have shown you a smaller range. In a practical scenario, you might face a larger data set. You might be roaming around which timespan you should follow. In this case, you might use the trading days of 1 month (21 days) / 3 months (63 days) / 1 year (252 days). In order to calculate the standard deviation, we will use the STDEV.S function. This function is used to estimate standard deviation on the basis of a sample ignoring the logical values and texts in the sample.

Here, select a cell and apply the following formula in that cell.

=STDEV.S(D5:D21)

Here, D5:D21 is the range of numbers of log-returns.

Then, press ENTER and you will get the standard deviation. Standard deviation indicates the daily historical volatility.

Step 4: Get Annual Historical Volatility

We have calculated the daily Historical Volatility (i.e. Standard Deviation). Now it needs to be annualized to get Historical Volatility over the trading year.

Here, select a cell and type the following formula in the selected cell. Use the SQRT function for this purpose.

=F5*SQRT(252)

Here,

F5 = Standard Deviation
252 = Trading Days in a year

Then, press ENTER, and your task will be done.

So, these are the steps you can follow to calculate historical volatility in Excel.

Things to Remember

Historical volatility gives an idea about the previous performance of a business model.
The risk associated with the security increases with the historical volatility.
Sometimes, higher volatility is sought out by some traders for greater profit and investment opportunities.

Practice Section

Here, I am providing a practice worksheet so that you can practice yourself.

📁 Download Practice Workbook

You can download the practice book from the link below.

Calculating Historical Volatility.xlsx

Conclusion

In this article, I have tried to show you some basic steps to calculate historical volatility in Excel. Hope the article helped you. If you have any questions, or feedback regarding this article, please don’t forget to share them in the comment box. We are always responsive to your concerns. Happy Excelling!

<< Go Back to Volatility In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!