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.

📁 Download Practice Workbook

You can download the practice book from the link below.

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.

Step-by-Step Procedure to Calculate Historical Volatility in Excel

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

  1. Putting the historical data in the worksheet
  2. Finding the logarithmic change from the input data
  3. Calculating standard deviation from the change value
  4. 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.

Historical Data to Calculate Historical Volatility in Excel

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.

Read More: How to Calculate Daily Volatility in Excel (2 Easy Ways)

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 use the LN function for this purpose. This function returns the natural logarithm of a number in Excel.

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



  • 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.

Find Log Return to Calculate Historical Volatility in Excel

  • 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.

Read More: How to Calculate Implied Volatility in Excel (2 Simple Methods)

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.


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

How to Calculate Historical Volatility in Excel

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

How to Calculate Historical Volatility in Excel

Read More: How to Calculate Volatility for Black Scholes in Excel (2 Methods)

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 = Standard Deviation
  • 252 = Trading Days in a year

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

How to Calculate Historical Volatility in Excel

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

Read More: How to Calculate Annualized Volatility in Excel (with Quick Steps)

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.


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. For more related articles, kindly visit our website. Happy Excelling!

Related Article

Rafiul Hasan

Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features