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

## What Is Historical Volatility?

In business aspects, the monetary value of different financial factors (i.e., stocks and 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 a long-term assessment of risk associated with the business model. This is widely used for investment strategies. A higher historical volatility is an indicator of greater risk. Even then, some investors look for higher volatility for better profit opportunities.

## How to Calculate the Historical Volatility in Excel: with Easy Steps

### Step 1 – Input Historical Data

The daily historical closing price and time span (i.e. Date) are used to calculate the daily historical volatility.

• We have inserted the historical data in column A and column B of the Excel worksheet. The values are sorted from earliest to oldest dates.

### Step 2 – Find the Logarithmic Return

• Use the following formula in cell D5:

`=LN(C5/C6)`

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

• Press Enter and the cell will calculate the log-returns of the dividend of the close price of the date and its previous date.

• Drag the Fill Handle tool down to Autofill the formula for the other timeframes.

• You will find the logarithmic return for all dates.

• You can show the change of log return in percentage. Create another column, use the return and click the percentage icon from the toolbar.

### Step 3 – Calculate the Standard Deviation

• Select a cell and apply the following formula in that cell.

`=STDEV.S(D5:D21)`

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

• Hit Enter.

### Step 4 – Get the Annual Historical Volatility

• Select a cell and use the following formula in the selected cell:

`=F5*SQRT(252)`

• F5 = Standard Deviation
• 252 = Trading Days in a year

• Press Enter.

## 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

We’re providing a practice worksheet so you can test the calculations.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF