I would like a quick assistance on a very basic question on the use of STDEV function in excel.
In order to calculate your standard deviation of a stock, you’d get the daily closing price then use the function =STDEV on all the daily variation.
The resultat is a daily standard deviation , am I correct?
If I wish to elevate my result yearly, weekly, monthly…etc I would multiply it by the square root of “T” with “T” being the number of points in my period.
So now, my issue derivate from that. I used an old model at my work using weekly NAV of a fund. The model uses the STDEV function of the weekly variations, consequently I though that the result was sufficient and was the STDEV weekly… but it also multiplies the result by the square root of 52.
In order to get the correct standard deviation, could you tell me what I should do? I need this information to calculate my sharpe ratio, but I am not confident about doing this with weekly data, as I believe that you would want to take the longest measurement interval in order to get a “good” sharpe ratio (right now its extremely low).
Thanks a lot for your help guys! by the way, happy to be there it seems like a great forum!
If your question is do you need to multiply by the square root of 52 to get the annualized standard deviation, the answer is yes. If you just want the weekly standard deviation, then you’re good to go without it. Not sure what you would want weekly for though. Also, when people talk about the standard deviation of a stock, they are generally referring to the standard deviation of returns, not the price, so you most likely should be measuring daily or weekly returns.
“I am not confident about doing this with weekly data, as I believe that you would want to take the longest measurement interval in order to get a “good” sharpe ratio”
I think the question is: should he use weekly SD and multiply by sqrt(52) or should he use a “longer measurement interval”, i.e. 1 year? Generally, you should try to use the data with the most information - weekly data in this case. Taking out data points could distort the results. For instance, if the price is volatile intrayear but returns to 100 at December 31 every year, you will see zero volatility with the yearly data.
You’re assuming that he wants the standard deviation of the returns.
Perhaps he really wants the standard deviation of the prices.
(I don’t know why he would want the standard deviation of the prices – most finance people want the standard deviation of the returns, even though they (sloppily) only say “standard deviation” (or, worse, “standard deviation of stock XYZ”) – but maybe this guy’s different.)
For most purposes what PalacioHill says is good enough. If your boss asked you for the information, that’s most likely what they want.
However, if you’re trying to answer the question if what the variance will be if you hold the stock for a year, it’s not really the right answer (even under simple assumptions, such as that log returns are normally distributed with a constant mean and variance). The more complicated but correct approach is to take log returns, calculate the mean and variance, multiply each by the appropriate frequency, then convert the mean and variances to the means and variances of the log normal distribution (see wikipedia’s log normal distribution page for the formula). This converts the log returns (which can be assumed to be normally distributed and thus easily projected to the future) to arithmetic returns (which are log normally distributed and more difficult to project) that the investor will actually experience.
Edit: S2000magician has a good point that I did not initially see. If you want the standard deviation of the prices in the future, then all you would need to do is calculate the standard deviation of the returns as I suggest and multiply by the most recent price.
This will give you the standard deviation of (a sample of) the stock’s price. It’s likely that you really want the standard deviation of (a sample of) the stock’s returns.
You are correct: it will give you the daily standard deviation of the stock’s price.
Also correct.
However, daily data have a lot of noise in them. If you want a weekly or monthly standard deviation, you’re better off using weekly or monthly prices, removing the noise from daily data. In my experience, weekly or monthly price data have the highest signal-to-noise ratio.
By multiplying by the square root of 52 they’re converting the weekly standard deviation to an annual standard deviation.
If you want to calculate a Sharpe ratio, you definitely need return data, not merely data, and you should use annual data (as you’ll likely have an annual risk-free rate, and the common period of time for a Sharpe ratio is one year).
You’re quite welcome. You’re correct: we have some very good people here.
Sorry I wasn’t clear enough The only data available I have are weekly NAVs. I could not agree more that the more data points the more precise the result is. The fund is quite young (I only have 31 weeks of NAVs), so I am using the STDEV of the return (and not the price, as higgmond suggested) that I divided by my average return and the result is quite poor.
@PalacioHill, thank you but I only have monthly returns.
Let’s take the problem an other way:
I tried to do some kind of reverse engeneering to find how to get a good sharp ratio from other data, I took the factsheet of an index which mentioned its Sharp ratio since 3 years ( http://www.lpx-group.com/lpx/uploads/media/Newsletter_MM_30092013.pdf ). So I extracted the TR monthly from 09/30/2010 to 09/30/2013, I also got the overnight EONIA, calculated the LN(Pt/Pt-1) for both, substracted the eonia from my TR to get the excess returns.
Having these infos, I simply calculated my average excess return that I divided over the STDEV of the excess returns. My result is 0.14, huge delta compared to 0.96 that I should get.
If you guys could tell me what I got wrong, understand this and getting the right number would allow me to apply this system with my weelky NAVs in order to try to end up with the same result.
I noticed on the fact sheet that my STDEV is close to their ( over 17% ) but my average return is really low, I used the function AVERAGE in excel while I think they used 17.26% that seem like an IRR.
^Average in excel is not annualized but theirs is (and if you’re calculating an annualized Sharpe you have to annualize both). From arithmetic returns, I use something like =product(1+A1:A120)^(12/COUNT(A1:A120))-1 entered as an array formula (control + shift + enter) to get the CAGR. From log returns, it would be something like =exp(12*average(A1:A120))-1.
Just remember that when you are scaling standard deviation (or sharpe ratios) by the square root of time, you are implicitly assuming that returns are not serially correlated. That’s not always a good assumption. People make it anyway because it’s a PITA to do the correction in excel, but if you have a reason to believe that returns have momentum or mean reversion, then your estimation will be biased one way or the other.
Thanks so much for your advices, it helped me very much
I would like to take advantage of your wisedom one last time regarding the sharpe ratio.
We all know that the sharpe ratio equal : average return - average risk free (both over the same periode of time) / standard deviation.
I now have the denominator right yet regarding the numerator I was suprise to see that some people ( http://www.lpx-group.com/lpx/uploads/media/Newsletter_MM_30092013.pdf ) uses the IRR - Rf / STDEV. I always though it was a simple AVERAGE of the returns and I am also quite sure that it is what I was tought in school. Can you tell me which one is right?
Thanks again guys, keep up with the great work on this forum!
