Monthly Stock Return Volatility

[Guest]

I have a time series data contains two columns. (example as below) It records
stocks' daily return for 6 year period. What I am interested is to find out
the monthly return volatility presented by standard deviation. Therefore, my
first monthly return volatility will be =stdev(B2:b21). I can do this
manually each month for 6 year (6*12=72 times) and repeat for another stock.
Can anyone help me to simplify the process?

Date Return
1/4/1999 0.010539721
1/5/1999 0.016203103
1/6/1999 0.021746115
1/7/1999 -0.000462872
1/8/1999 0.009811546
1/11/1999 -0.003875422
1/12/1999 -0.020677227
1/13/1999 -0.0102644
1/14/1999 -0.005702611
1/15/1999 0.025050688
1/18/1999 0.009379503
1/19/1999 -0.00936856
1/20/1999 -0.006888695
1/21/1999 -0.011408838
1/22/1999 -0.006306738
1/25/1999 0.003726166
1/26/1999 0.006794611
1/27/1999 -0.00176185
1/28/1999 0.002516646
1/29/1999 0.009164049

 

[Guest]

I have a time series data contains two columns.
[....] It records stocks' daily return for 6 year period.
What I am interested is to find out the monthly return
volatility presented by standard deviation. Therefore, my
first monthly return volatility will be =stdev(B2:b21).

First, that computes the __daily__ volatility based on one
month's data, not monthly volatility, since B2:B21 contains
daily returns.

Second, you should compute the stdev of the log returns,
since it is the log returns, not the simple returns, that some
believe are normally distrtibuted. I would compute the
following array formula (ctrl-shift-Enter):

=exp(stdev(ln(1+B2:B21)))-1

Some people actually use the stdev of the log returns for
"volatility". That is fine if they also use the mean of the
log returns for "average return". But most people use the
geometric mean of the simple returns for "average return".
In that case, I think "volatility" should be the exponential
of the stdev of the log returns, as I have done above, so
that "average return" and "volatility" are in the same domain.

I can do this manually each month for 6 year (6*12=72
times) and repeat for another stock. Can anyone help
me to simplify the process?

IMHO, monthly volatility should be computed base on monthly
returns, not daily returns. Typically, the closing price at the
end of each month is used. Off-hand, I cannot offer a formula
or VBA code that would extra the last monthly close from the
daily close. I'm sure that someone else can.

However, some financial analysts believe that daily volatility
can be converted to monthly volatility by multiplying by a
constant, namely the square root of the average number of
trade days in a month -- that is, sqrt(21). If you want to
understand the theory, google "square root of time" with
quotes in the search field.

Not everyone agrees with the theory. Some academics
believe there needs to be a correction factor. Some analysts
dismiss the theory altogether because of doubts about the
statistical presumption underlying the theory (i.e, so-called
Brownian motion of log returns). I am skeptical. But I must
admit that using more than 30 years of several sets of data,
I have found that the "square root of time" factor applied to
daily volatility does come close to matching the actual monthly
or annual volatility. That is an anecdotal observation, not a
statistical one.

If you accept the "square root of time" rule, the monthly
volatility is computed with the following array formula
(ctrl-shift-Enter):

=exp(sqrt(21)*stdev(ln(1+B1:B127))) - 1

where B1:B127 is your 6 years of daily data. Of course,
you could use more or less daily data.

 

[Biff]

Hi!

Try this:

Dates in column A, Return in column B.

Say, in D2, enter this formula: (I'm assuming your data starts in January
1999)

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

In E2 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=STDEV(IF(TEXT(A2:A500,"mmm yyyy")=D2,B2:B500))

Select both D2 and E2 and copy down as needed.

The results will look like this:

Jan 1999..........0.0117530886768349
Feb 1999.........0.0036587412554555
Mar 1999........0.0154788521445444
Apr 1999.........0.0054782145874155
etc
etc

Adjust the range in the Stdev function to suit.

Biff

 

[Biff]

=TEXT(DATE(1999,0+ROWS($1:1),1),"mmm yyyy")

Don't need the 0+. Don't know why I put that in there!

=TEXT(DATE(1999,ROWS($1:1),1),"mmm yyyy")

Biff

 

[Guest]

Biff and Joe,

Thanks for the help. I've got a question for Biff. I copy the exact formula
to cell D2 and E2 (the other setting is just as you assumed). I got different
answer as Jan 1999 = 0.013515868. But i know that 0.011753089 is the right
answer.

Any idea?

Frank

 

[Biff]

Hard to say why that happened.

As you can see, the result I posted for Jan is the same result that you say
you should have gotten. I tested based on a larger data sample with other
dates/values besides the sample you posted.

The other numbers I posted are just made up for purpose of the example.

Did you enter the Stdev formula as an array?

Select cell E2

Move the mouse cursor to the end of the formula in the formula bar.

Hold down both the CTRL key and the SHIFT key then hit the ENTER key.

If done properly Excel will place squiggly braces { } around the formula.
You can't type these in, you MUST use the key combination.

Did that fix it?

Biff

 

[Guest]

Thanks Biff, it works.

Hard to say why that happened.

As you can see, the result I posted for Jan is the same result that you say
you should have gotten. I tested based on a larger data sample with other
dates/values besides the sample you posted.

The other numbers I posted are just made up for purpose of the example.

Did you enter the Stdev formula as an array?

Select cell E2

Move the mouse cursor to the end of the formula in the formula bar.

Hold down both the CTRL key and the SHIFT key then hit the ENTER key.

If done properly Excel will place squiggly braces { } around the formula.
You can't type these in, you MUST use the key combination.

Did that fix it?

Biff