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.

  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

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:



  • 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

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

Read More: How to Calculate Implied Volatility in Excel

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

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

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.


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!

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF