How to calculate the standard deviation of stock returns?

This link does it ok: http://investexcel.net/1979/calculate-historical-volatility-excel/

Basically, you calculate percentage return by doing stock price now / stock price before. You're not calculating the rate of return hence no subtraction of 100%. The standard is to do this on a daily basis: stock price today / stock price yesterday.

The most important and most misunderstood part is that you now have to analyze the data geometrically not arithmetically. To easily do this, convert all percentage returns with the natural log, ln().

Next, you take the standard deviation of all of those results, and apply exp(). This answers the title of your question.

For convenience's sake, it's best to annualize since volatility (implied or statistical) is now almost always quoted annualized. There are ~240 trading days each year. You multiply your stdev() result by (240 / # of trading days per return) ^ 0.5, so if you're doing this for daily returns, multiply the stdev() result by 240^0.5; if you were doing it weekly, you'd want to multiply by (240 / ~5)^0.5; etc. This is your number for sigma. This answers the intent of your question.

For black-scholes, you do not convert anything back with exp(); BS is already set up for geometric analysis, so you need to stay there.

The reason why analysis is done geometrically is because the distribution of stock returns is assumed to be lognormal (even though it's really more like logLaplace).

For implied volatility it is okay to use Black Scholes but what to do with the historical volatility which carries the effect of past prices as a predictor of future prices? And then precisely the conditional historical volatility. I suggest that you go with the process like, for stock returns:

1) download stock prices into an Excel spreadsheet

2) take the natural log of (P1/po)

3) calculate average of the sample

4) calculate square of (X-Xbar)

5) take the square root of this and you will get the standard deviation of your required data

Whenever you are computing volatility in Excel or using any other formula/tool that utilizes past historical data of stock price, you are always calculating the historical volatility.

To compute implied volatility, there is no explicit formula. In such cases we usually use a numerical method. In Excel whenever we use goal seek to minimize the difference between option market price and option Black Scholes price by changing volatility, the final volatility that we get is the implied volatility.

To compute historical volatility just take the price series data from Yahoo etc. Adjacent to the closing price column, add a new column containing daily % returns.

Daily % Return = 100 * (Today's closing Price - Last day closing price)/(Last day closing price)

In Excel then you can apply stdev.s formula to the daily % returns column. The answer that you will get is Avg. Daily standard deviation. You can convert it to an annual number by multiplying it to sqrt(252) as there are 252 trading days in a year.