Historic Vol. in Excel

A

archived_user

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
Assume my log normal returns are in column 1 and I am trying to enter my historic vol. calculation for 30 days, is this the right equation:
=STDEV(A2:A31)*SQRT(252)
 
3rd & Long Wrote:
——————————————————-
> I thought the SQRT(252) annualizes it?
1)you are using 30 days data, so you will calculate monthly volatility not the daily volatility
2)to calculate daily volatility you will have to divide the monthly volatility by sqrt(22) [22
business days in a month]
3)if you want annualized volatility from month volatility you will have to multiply by sqrt(12) , not by sqrt(252)
 
edgeraz Wrote:
——————————————————-
>
> 1)you are using 30 days data, so you will
> calculate monthly volatility not the daily
> volatility
the daily refers to “daily returns”. monthly volatility will be if he was using stock price observations every month, and calculating the return on monthly stock prices. the fact that the length of the period that he chose happens to be 30-days doesn’t have anything to do with whether vol is daily or monthly - he can use 45-days, 63-days or whatever he needs/wants
multiplying daily returns by Sqrt(252) annualizes the daily volatility, he is right
 
if he is using 30 days data he will calculate 30 day volatility
to calculate the daily volatility he will have to divide that by sqrt(30) or sqrt(22) depending upon whatever his time frame is
sqrt(252) is used to annualize the volatility if we are using daily volatilities
Mobius Striptease Wrote:
——————————————————-
> edgeraz Wrote:
> ————————————————–
> —–
>
> >
> > 1)you are using 30 days data, so you will
> > calculate monthly volatility not the daily
> > volatility
>
>
> the daily refers to “daily returns”. monthly
> volatility will be if he was using stock price
> observations every month, and calculating the
> return on monthly stock prices. the fact that the
> length of the period that he chose happens to be
> 30-days doesn’t have anything to do with whether
> vol is daily or monthly - he can use 45-days,
> 63-days or whatever he needs/wants
>
> multiplying daily returns by Sqrt(252) annualizes
> the daily volatility, he is right
 
edgeraz Wrote:
——————————————————-
> if he is using 30 days data he will calculate 30
> day volatility
> to calculate the daily volatility he will have to
> divide that by sqrt(30) or sqrt(22) depending upon
> whatever his time frame is
>
> sqrt(252) is used to annualize the volatility if
> we are using daily volatilities
>
that is not true. you are confusing the length of the measurement period (30 days) with the frequency of observation.
if you look at stock prices every day, calculate the daily continuously-compounded return (the log-return), and then take the standard deviation of the log-return, you need to multiply by the sqrt(252) in order to get the “annualized” volatility. it does not matter whether the period of observation was 30 days long, 50 days long, or 10 years long.
 
for finance related (black scholes) calculations volatility is the standard deviation of the periodic percent change in prices, divided by the square root of time.
i am not sure why is that hard for you to understand, I guess you are just thinking about plain standard deviations
Mobius Striptease Wrote:
——————————————————-
> edgeraz Wrote:
> ————————————————–
> —–
> > if he is using 30 days data he will calculate
> 30
> > day volatility
> > to calculate the daily volatility he will have
> to
> > divide that by sqrt(30) or sqrt(22) depending
> upon
> > whatever his time frame is
> >
> > sqrt(252) is used to annualize the volatility
> if
> > we are using daily volatilities
> >
>
>
> that is not true. you are confusing the length of
> the measurement period (30 days) with the
> frequency of observation.
>
> if you look at stock prices every day, calculate
> the daily continuously-compounded return (the
> log-return), and then take the standard deviation
> of the log-return, you need to multiply by the
> sqrt(252) in order to get the “annualized”
> volatility. it does not matter whether the period
> of observation was 30 days long, 50 days long, or
> 10 years long.
 
everywhere in the BS formula, the term vol*Sqrt(T) appears. T is measured in years, “vol” is the annualized volatility. to calculate “vol” based on historical daily stock observations, you are going to take the standard deviation of the daily stock returns and multiply by sqrt(252). you disagree with that?
 
Mobius Striptease Wrote:
——————————————————-
> everywhere in the BS formula, the term vol*Sqrt(T)
> appears. T is measured in years, “vol” is the
> annualized volatility. to calculate “vol” based on
> historical daily stock observations, you are going
> to take the standard deviation of the daily stock
> returns and multiply by sqrt(252). you disagree
> with that?
the first part of the statement is right, in the second part you take the standard deviations , divide by sqrt of time period over which observations were taken, and then multiply by sqrt(252) to get to the annualized volatility.
which book did you take that from, Hull/Bjork and all Black Scholes book calculate the volatility this way only.
 
Mobius Striptease Wrote:
——————————————————-
> everywhere in the BS formula, the term vol*Sqrt(T)
> appears. T is measured in years, “vol” is the
> annualized volatility. to calculate “vol” based on
> historical daily stock observations, you are going
> to take the standard deviation of the daily stock
> returns and multiply by sqrt(252). you disagree
> with that?
and one more thing ‘T’ refers to the time period over which a derivative is being priced on, and ‘t’ refers to the number of observations used for calculating volatility.
[std/sqrt(t)] *sqrt(252)
 
edgeraz Wrote:
——————————————————-
>
> the first part of the statement is right, in the
> second part you take the standard deviations ,
> divide by sqrt of time period over which
> observations were taken, and then multiply by
> sqrt(252) to get to the annualized volatility.
> which book did you take that from, Hull/Bjork and
> all Black Scholes book calculate the volatility
> this way only.
ok, let say i am using 252 historical stock price observations over the past one year. i calculate the log-returns and take the standard deviation. according to you, in order to get the “annualized volatility”, i will divide this std. dev by Sqrt(252) and multiply by Sqrt(252), which essentially cancels each other. so essentially the standard deviation of the 252-day stock return is the annualized volatility, i.e. the “sigma” in the Black-Scholes formula. yes?
 
exactly
Mobius Striptease Wrote:
——————————————————-
> edgeraz Wrote:
> ————————————————–
> —–
> >
> > the first part of the statement is right, in
> the
> > second part you take the standard deviations ,
> > divide by sqrt of time period over which
> > observations were taken, and then multiply by
> > sqrt(252) to get to the annualized volatility.
> > which book did you take that from, Hull/Bjork
> and
> > all Black Scholes book calculate the volatility
> > this way only.
>
> ok, let say i am using 252 historical stock price
> observations over the past one year. i calculate
> the log-returns and take the standard deviation.
> according to you, in order to get the “annualized
> volatility”, i will divide this std. dev by
> Sqrt(252) and multiply by Sqrt(252), which
> essentially cancels each other. so essentially the
> standard deviation of the 252-day stock return is
> the annualized volatility, i.e. the “sigma” in the
> Black-Scholes formula. yes?
 
edgeraz Wrote:
——————————————————-
> exactly
>
:) that is just wrong, bro. take any public company of your choice and do this calculation, then tell me what “sigma” you get. or better yet, plug it in Black-Scholes and price a call option with 1 year to maturity. you will get some funny results
i dont think you’ve used this in practice ever by the way, you are running on some theoretical knoweledge so far is my guess. am i right?
 
I have priced and calibrated hunderds of models and options, so I know I am right,
I think I know whats confusing you, I am talking about daily annualized volatility, I think you are talking about 30 day annualized volatility/10 day annualized volatility?
am pretty sure thats the case?
Mobius Striptease Wrote:
——————————————————-
> edgeraz Wrote:
> ————————————————–
> —–
> > exactly
> >
>
> :) that is just wrong, bro. take any public
> company of your choice and do this calculation,
> then tell me what “sigma” you get. or better yet,
> plug it in Black-Scholes and price a call option
> with 1 year to maturity. you will get some funny
> results
>
> i dont think you’ve used this in practice ever by
> the way, you are running on some theoretical
> knoweledge so far is my guess. am i right?
 
edgeraz Wrote:
——————————————————-
> I have priced and calibrated hunderds of models
> and options, so I know I am right,
> I think I know whats confusing you, I am talking
> about daily annualized volatility, I think you are
> talking about 30 day annualized volatility/10 day
> annualized volatility?
> am pretty sure thats the case?
>
perhaps we have a communication problem of some sort, and couldn’t really understand each other somehow.
to make it specific, say i wanna price at-the-money call, spot=strike=100. time to maturity is one year, so in B-S i will have T=1. risk-free rate 5%, dividend yield zero.
i wanna use historical volatility for my sigma. i download the 252 stock price observations from last year, and caclulate the standard deviation of their log-returns. you are saying this is my sigma that will go into the B-S?
 
nopes, in that case you would want to use 1-year annualized volatility
which would be the standard deviation of one year log returns multiplied by sqrt(252).
period.
btw, are you a fan of topology?
Mobius Striptease Wrote:
——————————————————-
> edgeraz Wrote:
> ————————————————–
> —–
> > I have priced and calibrated hunderds of models
> > and options, so I know I am right,
> > I think I know whats confusing you, I am
> talking
> > about daily annualized volatility, I think you
> are
> > talking about 30 day annualized volatility/10
> day
> > annualized volatility?
> > am pretty sure thats the case?
> >
>
> perhaps we have a communication problem of some
> sort, and couldn’t really understand each other
> somehow.
>
> to make it specific, say i wanna price
> at-the-money call, spot=strike=100. time to
> maturity is one year, so in B-S i will have T=1.
> risk-free rate 5%, dividend yield zero.
>
> i wanna use historical volatility for my sigma. i
> download the 252 stock price observations from
> last year, and caclulate the standard deviation of
> their log-returns. you are saying this is my sigma
> that will go into the B-S?
 
edgeraz Wrote:
——————————————————-
> nopes, in that you would want to use 1-year
> annualized volatility
> which would be the standard deviation of one year
> log returns multiplied by sqrt(252).
> period.
>
> btw, are you a fan of topology?
>
>
alright, 15 posts of arguments to find out we are talking about the same thing, AF style
i never really got into topology/geometry/abstract algebra, i’ve always been more of an analysis guy. are you topologist or whatever the word is :)
 
You must log in or register to reply here.
Back
Top