A stock's volatility is the variation in its price over a period of time. For example, one stock may have a tendency to swing wildly higher and lower, while another stock may move in much steadier, less turbulent way. Both stocks may end up at the same price at the end of day, but their path to that point can vary wildly.
With the help of an Excel spreadsheet, calculating volatility is a fairly straightforward process, as is turning that volatility into an annualized format.
Step 1: Calculating a stock's volatility
To calculate volatility, we'll need historical prices for the given stock. In this example, we'll use the S&P 500's pricing data from August 2015. This example uses just one month, but it is equally applicable to any other range of time.
In the screengrab below, you'll see that we have a column for the date, a column with the S&P 500's closing prices, and a column that shows the daily percentage change in closing price.
The percentage change in closing price is calculated by subtracting the prior day's price from the current price, and then dividing by the prior day's price.
With this information, we can now calculate the daily volatility of the S&P 500 over this time period. We will use the standard deviation formula in Excel to make this process easy. Standard deviation is the degree to which the prices vary from their average over the given period of time. In Excel, the formula for standard deviation is =STDVA(), and we will use the values in the percentage daily change column of our spreadsheet.
In this example, our daily standard deviation is 1.73%. This represents the S&P 500's daily volatility for August 2015.
Annualizing volatility
To present this volatility in annualized terms, we simply need to multiply our daily standard deviation by the square root of 252. This assumes there are 252 trading days in a given year. The formula for square root in Excel is =SQRT().
In our example, 1.73% times the square root of 252 is 27.4%. Therefore, based on the daily price movements in August 2015, the S&P 500's annualized volatility is 27.4%.
With some small tweaks, this process works for any time period. For example, instead of annualized volatility, you could calculate the monthly volatility by multiplying the daily volatility by the square root of 21. We use 21 because there were 21 trading days in August 2015.
Likewise, if you chose to use weekly data, you could calculate the weekly volatility in the exact same way as we calculated the daily volatility. Just calcluate the weekly percentage change and take the standard deviation of that data. To annualize the weekly volatility, you'd just need to multiply by the square root of 52, because there are 52 weeks in a year.
Volatility can seem highly complex and hard to understand. But, as you've learned here, there is no reason to fear this metric. All it takes is a spreadsheet, a little data, and a couple of simple formulas.
If you're an investor always seeking the best path for your investments, visit our broker center.
This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general or this page in particular. Your input will help us help the world invest, better! Email us at [email protected]. Thanks -- and Fool on!
