Take the 2-minute tour ×
Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It's 100% free, no registration required.

I'm trying to learn the the Black–Scholes option pricing formula and one of the elements of that formula (according to http://bradley.bradley.edu/~arr/bsm/pg04.html) is the "standard deviation of stock returns".

I know if I download a CSV file of historical prices from Yahoo! and open up Excel and execute STDDEV(column with prices), I can get the "standard deviation of stock PRICES". But that is not what I need. I need the "standard deviation of stock RETURNS".

Does anyone know how I can calculate this in Excel? Or even better yet, if someone can provide a implementation in code showing how to do it?

Some of the questions that came up in my head when thinking about how to approach this include "how much historical data to use? (how far back to we go when downloading the CSV file from Yahoo!)" and "what kind of stock returns are we supposed to be calculating? Annual stock returns? Daily returns?"

share|improve this question
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.