what is the excel formula for market volatility?

  • Thread starter Thread starter Guest
  • Start date Start date
i am not quite sure what u mean
while there is no this type in the excel help,maybe u mean the statistic
formula ,such as Normsdist(),stdev() and so on.
BTW which one u choose is relate to your make volatility model.
HTH

Regards
Chelsea
 
Dyn said:
anybody knows the formula??
Click to expand...

You ask: "What is the excel formula for market volatility?".

(In the future, always put your question in the text body, even if it
duplicates the subject line.)

There is no Excel function for market volatility. In fact, there is no
single mathematical formula for market volatility.

To answer your question: decide on the mathematical formula that you want
to use for market volatility, then write the Excel formula that implements it.

Sometimes, market volatility is defined as the standard deviation (of
something; TBD depending on context). The Excel function is STDEV().
 
Hi

Not sure what you mean by market volatility. If it is to do with moevments
in share prices, I can't help you.

If you mean the Price Elasticity of Demand, then there is not an inbuilt
formula, but you can use Excel to calculate the value for you.

Price Elasticity of Demand = % change in demand / % change in price

To calculate the percent change in demand, find the difference between the
quantities purchased at each price and divide the difference by the average
of the two quantities:

Step 1 (Quantity2 - Quantity1) / ((Quantity1 + Quantity2)/2)

To calculate the percent change in price, find the difference between the
two product prices and divide the difference by the average of the two
prices:

Step 2 (Price2 - Price1) / ((Price1 + Price2)/2)

Dividing the percent change in demand (result of step 1) by the percent
change in price (result of step 2) provides the price elasticity of demand
for the product.
 
If you mean as defined athttp://en.wikipedia.org/wiki/Volatility_%28finance%29
you could use the array formula (committed with Ctrl-Shift-Enter)
=STDEV(LOG(data))*SQRT(tradingDaysPerYear)
to get annualized volatility.
Click to expand...

I notice that you (and the Wiki entry) do not convert that back to a
std dev of the returns (i.e. exp(stdev(ln(data))...)). Is that
conventional? If so, would you also leave the mean in terms of the
log return (e.g. average(log(data)))?

I also wonder what financial software really wants when it asks us to
input, variously, "volatility" or "std dev" along with the "mean".
 
I can't speak to financial convention, since I don't work in that area.

However, for most things that you would want to do with a standard
deviation, back-transforming the standard deviation itself would not be
useful. For instance, if you had to transform your data to achieve
approximate normality, you would calculate confidence intervals, etc on the
thransformed scale and back-transform those limits rather than the standard
deviation itself.

Jerry
 
I can't speak to financial convention, since I don't work in that area.
Click to expand...

Fair enough. I am in a similar situation.

However, for most things that you would want to do with a standard
deviation, back-transforming the standard deviation itself would not
be useful.
Click to expand...

Oh, I agree; that is, we would have to continually convert back and
forth. And I ass-u-me that financial software does the right thing.
I was just asking about the convention for the __input__, if there is
any.

I have used free online software (read: poorly documented) that asks
for "average return" and "volatility" or "standard deviation". I
would expect the two should be expressed in terms of the same "space",
be it the returns or the log returns.

And in the early days of my learning in this area (some years ago),
google searchs turned up lengthly explanations that converted the
average log return to the geometric mean of the return and similarly
for the standard deviation of the log returns.

(And of course, some software that I have used want the beta for
"volatility", which is something else entirely.)

If the world were a consistent place, the software would ask for
statistics about the "returns" or the "log returns" per se. That
would clear things up. But except for some educational web sites, I
have never seen the term "log return" used explicity -- for example,
never in sales literature that includes performance data.

Oh well, I suspect there is no canonical answer to my question. I
really just wanted to raise the visibility of the fact that the
definition "volalitity" as the sd of the log return, per se, is not
necessary so cut-and-dried. At least, that has been my experience.
 
I think you still have to learn about how to calculate the market volatility. The standard deviation is used to calculate the volatility of the market and the Newton Raphson method will calculate it. There is not any formula in excel to calculate market volatility.
Be precise in your question.
http://www.livevol.com/options-excel.html

anybody knows the formula??
On Tuesday, September 11, 2007 10:09 PM chelsea wrote:
i am not quite sure what u mean
while there is no this type in the excel help,maybe u mean the statistic
formula ,such as Normsdist(),stdev() and so on.
BTW which one u choose is relate to your make volatility model.
HTH

Regards
Chelsea

"Dyn" <[email protected]> ????????????:[email protected]...
Click to expand...
//en.wikipedia.org/wiki/Volatility_%28finance%29[/url]
you could use the array formula (committed with Ctrl-Shift-Enter)
=STDEV(LOG(data))*SQRT(tradingDaysPerYear)
to get annualized volatility.

Jerry

"Dyn" wrote:
Click to expand...
Click to expand...
Click to expand...
 
Back
Top