How to Calculate the Volatility on XLS

The volatility of a financial instrument is defined as the standard deviation of its closing prices for a given period of time. Most often, the volatility is calculated over the previous 20 periods. A period in this case will be one day. Standard deviation is a measure of how much individual values vary from the average of all the values, and it can be calculated using an Excel spreadsheet (XLS).

Things You'll Need

• Microsoft Excel

Instructions

1. Choose a stock for which you would like to calculate the volatility. There are several financial websites where you can find stocks and the previous closing prices for them. For instance, at the official Nasdaq website you can find up to 10 years of historical daily closing prices for any stock.

2. Create a new Microsoft Excel spreadsheet.

3. Input the previous 20 closing prices for your stock into Column A of the XLS.

4. Highlight the cell directly below the data, then go to the top of the window and click "Insert," then "Function." Select the function called "STDEV" from the list of options and click "OK."

5. With the STDEV cell still active, highlight your 20 closing prices to direct the formula to calculate from those cells. Press the "Enter" key and the standard deviation--and thus the volatility--for those 20 closing prices will be calculated.

References

• Stockcharts: Volatility

Resources

• Nasdaq Website