In Excel, what formula do you use to measure the volatility of a stock’s returns (as it applies to the BS model) if you have the historical prices? is it just =stdev(XXX)? Specifically, I’m looking for the 12, 6, and 3 month volatility of a stock, but I’m not sure if its simply =stdev(XXX) over those time periods, or if I need to do the following: for 12 month: divide =stdev(xxx) by the square root of (1/252) for 6 months: divide =stdev(xxx) by the square root of (1/126) for 3 months: divide =stdev(xxx) by the square root of (1/63) I’m a little lost since the volatility I’m getting by doing =stdev(xxx) is significantly less than what the company has on its financials. Any help would be appreciated.

Right, also you can just multiply by square root of x instead of dividing by square root of 1 / x

as it applies to the BS model, it depends on how your formula is coded in Excel. the term of the option T is an input in the model, and so is the *annualized* volatility v, so the compounding v*sqrt(T) will be done by the formula within the model. The companies’ financials will also typically report the annualized volatility v, rather than the cumulative volatility over the option term v*sqrt(T)

I’m doing some more reading on the subject, and it seems that regardless of how many days worth of returns I use, to annualize it, I have to divide the stdev by sqrt(1/252)… or am I mistaken here and the 252 depends on how many days I use…? Also, it further says that instead of just taking the stdev of the stock prices, I have to take the stdev of the ln(day x/day x+1) of the stock prices. Any comments on this? And Mobius, can you clarify your comments with an example? Thanks all!

- you must take the standard deviation of the continuously-compounded stock price returns, not the stock prices. that means log(St/St-1) 2) to get to the *annualized* volatility v, take the standard deviation of the DAILY log-returns and multiply by the sqrt(252) (or divide by sqrt(1/252), same deal) 3) this is most likely the volatility reported in financial statements, an annualized volatility in the Black-Scholes formula, the annualized volatility v will be multipled by sqrt(T), T in years, in order to get to the cumulative volatility over the life of the option. that should be done within the formula so you probably don’t need to do anything there

I’m curious… what’s the normal practice on using standard deviation of returns vs standard deviation of log returns here?

I’ve always used standard deviation of log returns, as have a couple of external advisors that we’ve engaged on stock option valuation projects (both Big 4 Accounting firms).

The normal practice, indeed, is the assumption of normal distribution of returns And the returns have to be continuously compounded if you want them to take any value from negative to positive infinity as a gaussian… An arithmetic return can’t be less than -1 of course

I tend to use log returns for regression modeling, because it meets the normality assumptions better. But I convert to normal returns for things like Sharpe ratios. Attilio Meucci’s course said to use log returns for model estimation and forecasting but to use ordinary returns for optimization. However, he glossed over what that meant for calculating variances and covariances (calculate covariances and then map those numbers to ordinary returns, or map to ordinary returns and then calculate covariances).

Mobius Striptease Wrote: ------------------------------------------------------- > 1) you must take the standard deviation of the > continuously-compounded stock price returns, not > the stock prices. that means log(St/St-1) > 2) to get to the *annualized* volatility v, take > the standard deviation of the DAILY log-returns > and multiply by the sqrt(252) (or divide by > sqrt(1/252), same deal) > 3) this is most likely the volatility reported in > financial statements, an annualized volatility > > > in the Black-Scholes formula, the annualized > volatility v will be multipled by sqrt(T), T in > years, in order to get to the cumulative > volatility over the life of the option. that > should be done within the formula so you probably > don’t need to do anything there +1

that’s a good point bchad, i was recently reading up a bit on the use of log-returns (say in volatility calculations as above) vs. arithmetic returns (for instance when you calculate beta for CAPM, etc.) and his Quant Nugget 1-6 series were suggested by someone on AF, very succinct and clear overview of the approaches http://papers.ssrn.com/sol3/cf_dev/AbsByAuth.cfm?per_id=403805

Are you trying to price some sort of option? If so, you might want to consider looking at implied volatility, in addition to historical volatility. The prices can be quite different.

ohai Wrote: ------------------------------------------------------- > Are you trying to price some sort of option? If > so, you might want to consider looking at implied > volatility, in addition to historical volatility. > The prices can be quite different. How would you calculate implied volatility without price?

justin88 Wrote: ------------------------------------------------------- > ohai Wrote: > -------------------------------------------------- > ----- > > Are you trying to price some sort of option? If > > so, you might want to consider looking at > implied > > volatility, in addition to historical > volatility. > > The prices can be quite different. > > How would you calculate implied volatility without > price? We look at the implied volatilities of “similar” options.

higgmond Wrote: ------------------------------------------------------- > Right. That’s what people generally do, but they might bump the surface up or down, or do things with the skew. Historical vol is pretty disconnected from market vol prices, since it’s backwards looking; if North Korea fires nukes today, option prices will go up a lot, but historical vol won’t really change.

What’s the standard way to use GARCH estimates of volatility with option pricing models? For instance, do people just estimate a GARCH model and find the estimated variance at time t, or do they simulate out returns from t to T and calculate the expected variance over this period?

that’s a great question and i’ll be interested in knowing more about this too… it would seem that the GARCH vol estimate would be different under real vs risk-neutral measure, which would make things messier when you use it for option pricing as opposed to risk management

Mobius Striptease Wrote: ------------------------------------------------------- > that’s a great question and i’ll be interested in > knowing more about this too… it would seem that > the GARCH vol estimate would be different under > real vs risk-neutral measure, which would make > things messier when you use it for option pricing > as opposed to risk management What do you mean by estimating volatility under risk-neutral measure?

higgmond Wrote: ------------------------------------------------------- > We look at the implied volatilities of “similar” > options. Fair enough, I suppose, but not exactly a reassuring way to come up with a number.

justin88 Wrote: ------------------------------------------------------- > higgmond Wrote: > -------------------------------------------------- > ----- > > We look at the implied volatilities of > “similar” > > options. > > Fair enough, I suppose, but not exactly a > reassuring way to come up with a number. Depends on how “similar” the other options are to your options.