Standard Deviation Calculation Help
Standard Deviation Calculation Help
Hello,
I'm composing my thesis on investment management (though my background is not in finance) and am failing to accurately calculate the standard deviation for a given data set: S&P 500 annual returns, 2003-2012.
Using a software program to create portfolios, the standard deviation of the S&P 500 from January 2003 to December 2012 is computed as 4.26%. My calculations are hovering between 17.38% (doing the calculation myself) and 18.32% (using =STDEV in Excel).
I've checked my work many times (I've spent all day on this), but can't figure out what I'm doing wrong. I know that I'm using the same data set as the software program database, because I am able to accurately calculate the same annualized returns as the software program (7.10%).
Here are the annual returns for 2003 onwards.
28.68%
10.88%
4.91%
15.79%
5.49%
-37.00%
26.46%
15.06%
2.11%
16.00%
What am I doing wrong?
Thanks in advance for your help,
J
I'm composing my thesis on investment management (though my background is not in finance) and am failing to accurately calculate the standard deviation for a given data set: S&P 500 annual returns, 2003-2012.
Using a software program to create portfolios, the standard deviation of the S&P 500 from January 2003 to December 2012 is computed as 4.26%. My calculations are hovering between 17.38% (doing the calculation myself) and 18.32% (using =STDEV in Excel).
I've checked my work many times (I've spent all day on this), but can't figure out what I'm doing wrong. I know that I'm using the same data set as the software program database, because I am able to accurately calculate the same annualized returns as the software program (7.10%).
Here are the annual returns for 2003 onwards.
28.68%
10.88%
4.91%
15.79%
5.49%
-37.00%
26.46%
15.06%
2.11%
16.00%
What am I doing wrong?
Thanks in advance for your help,
J
Re: Standard Deviation Calculation Help
To start with, an annual standard deviation of 4 is definitely not correct. Possibly the program is calculating a monthly standard deviation using monthly returns? In Excel, compare your results of STDEV vs. STDEVP.
Re: Standard Deviation Calculation Help
What happens if you express your numbers not as a percentage, but as ....
28.68 -> 1.2868
-37.00 -- 0.63
?
Are you trying to figure out the S.D. of the S&P500 or the S.D. of the returns of the S&P500? And are the "annual returns" with or without dividends?
28.68 -> 1.2868
-37.00 -- 0.63
?
Are you trying to figure out the S.D. of the S&P500 or the S.D. of the returns of the S&P500? And are the "annual returns" with or without dividends?
Re: Standard Deviation Calculation Help
To convert annual sd to monthly sd you divide by sqrt(12).
So 17.38/sqrt(12) = 5.02
Hope that helps.
So 17.38/sqrt(12) = 5.02
Hope that helps.
- nisiprius
- Advisory Board
- Posts: 52105
- Joined: Thu Jul 26, 2007 9:33 am
- Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry
Re: Standard Deviation Calculation Help
I suggest you post the actual source code. Can't hurt. Hopefully it will either be something some forum members understand, like one of the gazillion languages that all look like C, or it will be something fascinating and wonderful like LabView or SNOBOL and we will all learn something new about computer languages.
If it is a packaged program, you'd better at least tell us what its name is and what sequence of commands you are telling it to do. I wouldn't know anything about it but some people in this forum might.
By the way, be open to the possibility that you might actually have found a bug. Depending on how legitimate your use of the software is, and what your ability is to drill down through the protective shell of large organizations to locate people who are really willing to answer questions, maybe you should contact their support.
What are some intermediate results you are getting along the way to the final value?
Using Excel but not using any of their built-in functions, I find the following. All calculations are being done to Excel's full precision and then rounded for presentation.
The sum is 88.38%.
The mean is 88.38%/10 = 8.84%.
The differences from the mean are:
19.84%
2.04%
-3.93%
6.95%
-3.35%
-45.84%
17.62%
6.22%
-6.73%
7.16%
The squares of those differences are:
3.937%
0.042%
0.154%
0.483%
0.112%
21.011%
3.105%
0.387%
0.453%
0.513%
The sum of those squares is:
30.20%
The mean square, and thus the variance of values in the sample (not the "sample variance"), is 30.20%/10 =
3.02%.
The root mean square, and thus the standard deviation of values within the the sample, is
17.377%.
The sample variance, i.e. the estimate of the population variance obtained from the sample values after making the "Bessel correction," is 30.20%/(n-1) = 30.20%/9 =
3.355%
The sample standard deviation is sqrt(3.355%) =
18.317%
Excel's built-in functions:
STDEV.P(the ten original input numbers) =
17.377%
STDEV.S(the ten original input numbers) =
18.317%
[I edited this post to correct some mistakes I made in terminology, per PMs from bobcat2. The term "sample variance" is a technical term and is used to mean the underlying population variance as derived from a sample--not the variance of the numbers within the sample itself).
If it is a packaged program, you'd better at least tell us what its name is and what sequence of commands you are telling it to do. I wouldn't know anything about it but some people in this forum might.
By the way, be open to the possibility that you might actually have found a bug. Depending on how legitimate your use of the software is, and what your ability is to drill down through the protective shell of large organizations to locate people who are really willing to answer questions, maybe you should contact their support.
What are some intermediate results you are getting along the way to the final value?
Using Excel but not using any of their built-in functions, I find the following. All calculations are being done to Excel's full precision and then rounded for presentation.
The sum is 88.38%.
The mean is 88.38%/10 = 8.84%.
The differences from the mean are:
19.84%
2.04%
-3.93%
6.95%
-3.35%
-45.84%
17.62%
6.22%
-6.73%
7.16%
The squares of those differences are:
3.937%
0.042%
0.154%
0.483%
0.112%
21.011%
3.105%
0.387%
0.453%
0.513%
The sum of those squares is:
30.20%
The mean square, and thus the variance of values in the sample (not the "sample variance"), is 30.20%/10 =
3.02%.
The root mean square, and thus the standard deviation of values within the the sample, is
17.377%.
The sample variance, i.e. the estimate of the population variance obtained from the sample values after making the "Bessel correction," is 30.20%/(n-1) = 30.20%/9 =
3.355%
The sample standard deviation is sqrt(3.355%) =
18.317%
Excel's built-in functions:
STDEV.P(the ten original input numbers) =
17.377%
STDEV.S(the ten original input numbers) =
18.317%
[I edited this post to correct some mistakes I made in terminology, per PMs from bobcat2. The term "sample variance" is a technical term and is used to mean the underlying population variance as derived from a sample--not the variance of the numbers within the sample itself).
Last edited by nisiprius on Wed May 08, 2013 12:51 pm, edited 2 times in total.
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
Re: Standard Deviation Calculation Help
It's been over 40 years but as I recall you use
[ x(i) - x(avg)]^2 if there is no time trend in the data
but
[ x(I+1) - x(i)]^2 if there is a time trend in the data
this problem is mitigated if you use percent changes but I don't think it goes away completely.
You also have to correct for annual vs monthly data with the 12^.5 factor that was previously mentioned.
[ x(i) - x(avg)]^2 if there is no time trend in the data
but
[ x(I+1) - x(i)]^2 if there is a time trend in the data
this problem is mitigated if you use percent changes but I don't think it goes away completely.
You also have to correct for annual vs monthly data with the 12^.5 factor that was previously mentioned.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
- nisiprius
- Advisory Board
- Posts: 52105
- Joined: Thu Jul 26, 2007 9:33 am
- Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry
Re: Standard Deviation Calculation Help
I don't see how you can "correct" for monthly versus annual data. If you don't have the monthly data, you simply cannot calculate a monthly standard deviation--not without making a metric boatload of assumptions that we know for sure are not close to true for financial data.
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
Re: Standard Deviation Calculation Help
It's a matter of annualizing the SD calculated from monthly data. "Correcting" was a misnomer.nisiprius wrote:I don't see how you can "correct" for monthly versus annual data. If you don't have the monthly data, you simply cannot calculate a monthly standard deviation--not without making a metric boatload of assumptions that we know for sure are not close to true for financial data.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.
Re: Standard Deviation Calculation Help
That cannot possibly be correct. It is way too low. However, it could be the variance of the returns (the square of the standard deviation). My bullshit detector goes off when the annual standard deviation of reported US stock market returns is not between 17% and 22%. That doesn't mean the computed SD is wrong - but it would be suspicious.I'm composing my thesis on investment management (though my background is not in finance) and am failing to accurately calculate the standard deviation for a given data set: S&P 500 annual returns, 2003-2012. Using a software program to create portfolios, the standard deviation of the S&P 500 from January 2003 to December 2012 is computed as 4.26%.
BobK
In finance risk is defined as uncertainty that is consequential (nontrivial). |
The two main methods of dealing with financial risk are the matching of assets to goals & diversifying.
Re: Standard Deviation Calculation Help
YES! The software program is calculating the ST DEV of monthly returns.stlutz wrote:To start with, an annual standard deviation of 4 is definitely not correct. Possibly the program is calculating a monthly standard deviation using monthly returns? In Excel, compare your results of STDEV vs. STDEVP.
Thank you!