Standard Deviation Calculation Help

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
JonLuskin
Posts: 8
Joined: Mon Apr 22, 2013 1:55 pm
Location: Los Angeles, CA

Standard Deviation Calculation Help

Post by JonLuskin »

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
stlutz
Posts: 5585
Joined: Fri Jan 02, 2009 12:08 am

Re: Standard Deviation Calculation Help

Post by stlutz »

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.
livesoft
Posts: 85971
Joined: Thu Mar 01, 2007 7:00 pm

Re: Standard Deviation Calculation Help

Post by livesoft »

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?
Wiki This signature message sponsored by sscritic: Learn to fish.
Verde
Posts: 349
Joined: Mon Dec 31, 2007 3:47 am
Location: South Africa

Re: Standard Deviation Calculation Help

Post by Verde »

To convert annual sd to monthly sd you divide by sqrt(12).
So 17.38/sqrt(12) = 5.02

Hope that helps.
User avatar
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

Post by nisiprius »

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).
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.
User avatar
Doc
Posts: 10606
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: Standard Deviation Calculation Help

Post by Doc »

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.
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.
User avatar
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

Post by nisiprius »

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.
User avatar
Doc
Posts: 10606
Joined: Sat Feb 24, 2007 12:10 pm
Location: Two left turns from Larry

Re: Standard Deviation Calculation Help

Post by Doc »

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.
It's a matter of annualizing the SD calculated from monthly data. "Correcting" was a misnomer.
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.
User avatar
bobcat2
Posts: 6074
Joined: Tue Feb 20, 2007 2:27 pm
Location: just barely Outside the Beltway

Re: Standard Deviation Calculation Help

Post by bobcat2 »

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%.
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.

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.
Topic Author
JonLuskin
Posts: 8
Joined: Mon Apr 22, 2013 1:55 pm
Location: Los Angeles, CA

Re: Standard Deviation Calculation Help

Post by JonLuskin »

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.
YES! The software program is calculating the ST DEV of monthly returns.

Thank you!
Post Reply