Wayne A. Thorp, CFA is a vice president and senior financial analyst at AAII and editor of Computerized Investing. Follow him on Twitter at @AAII_CI.

Discussion

Blaine from Utah posted over 2 years ago:

How can one download this spreadsheet? I find it much easier to understand when looking at the spreadsheet as oppose to looking at formulas in the article.

Brent from New York posted over 2 years ago:

=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),(\$B\$33:\$K\$42),D49:D58))

This formula has an open parenthesis. There are 5 left parens and only 4 right parens. Anybody know if it's a matter of too many lefts or too few rights? I keep getting an error message in my spreadsheet with this formula. Thanks.

Wayne from Illinois posted over 2 years ago:

The formula should be:

=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),\$B\$33:\$K\$42),D49:D58))

No open paren before \$B\$33

Wayne A. Thorp, CFA, editor, Computerized Investing

Ed from California posted over 2 years ago:

How can a self covariance is not 1?

Daniel from Nevada posted over 2 years ago:

The covariance of a security's returns with itself equals the variance of that security's returns. It will rarely be exactly 1; the more volatile the security, the higher the variance. Every element on the diagonal of the covariance matrix is actually a security's variance.

Herb from Pennsylvania posted over 2 years ago:

Since few portfolios are likely to have exactly the same number of stocks as the sample, it would have been helpful to detail the changes needed to accommodate additional stocks, or a different number of years of data. Also, since the number of years is more likely to be limited than the number of stocks, wouldn't the spreadsheet have been more compact (fewer columns) if the stocks were listed in column A than across row 3?

Jerry from Arizona posted over 2 years ago:

What differences are there between the correl function and above? Does the covariance offer the same standard or solid answer as the correlation?

Ronald from Colorado posted over 2 years ago:

I repeat my earlier comment:

There is a simple way to calculate mean and variance without calculating covariances. Simply calculate the total return for your portfolio (of as many weighted items as you want) for each time period. Then use the AVG and VAR functions on the results. It can be shown mathematically that the mean and variance calulated this way are the same as using covariances (and much easier). You can still use SOLVER to optimize weights to maximum mean return with fixed variance or vice versa. I would be happy to send a spreadsheet to anyone interested for noncommercial purposes. nordron@umich.edu

John from Idaho posted over 2 years ago:

I loved the article but have a hard time getting the historical annual returns.
I have the following question: I used to use the "Risk Grades" web site to judge the risk of my portfolio and to see "what if" changes. The web site is closed now and I am looking for a way to see the "risk" in my portfolio. Will this spread sheet give me the approximate risk values ??
Second question: Where has the "Risk Grades" site moved to??
Thanks,
John

Michael from New York posted over 2 years ago:

Thanks for correcting the portfolio Std Dev formula.

Clifford from California posted over 2 years ago:

I've hit the wall in Cell D61.

I entered "=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),\$B\$33:\$K\$42),D49:D58))"; got "#VALUE"; entered the Solver Parameters including the Constraint that \$D\$59 = 1 & got the Solver Results "Solver could not find a feasible solution" with all cells in the range D49:D59 populated by 0% in spite of the constraint that \$D\$59 = 1.

Is my problem because I'm using Excel 2010??

Geoff from Texas posted over 2 years ago:

Cliff, did you try this ... copy-paste this value into cell D61
"=SQRT(MMULT(MMULT(TRANSPOSE(D49:D58),(\$B\$33:\$K\$42)),D49:D58))" (without the quotes) and then press Ctrl+Shift+Enter to enter this formula ... you should be able to copy-paste this formula into all other cells on row 61 .. just drag the formula across, no need to Ctrl+Shift+Enter when copying across cells

Geoffrey from Texas posted over 2 years ago:

also, when using any kind of solver, you can try preloading the cells with approximate values

Geoffrey from Texas posted over 2 years ago:

My friend works for MSCI, I'll ask her what happened to "Risk Grades" ... maybe they sold it to a hedge fund :)

Clifford from California posted over 2 years ago:

Thanks for all the help. I got it to work & now I'm trying to migrate this to a portfolio of 31 stocks.

Eric from California posted over 2 years ago:

When I select the range to fill in the top half of my 1st covar. matrix and then press Ctrl+Shift+Enter it simply puts the calculation to all the cells in my data range. What am I doing wrong.

Thanks,
Eric

Dennis from Idaho posted over 2 years ago:

This is great stuff. I have followed through your tutorial by creating my own 'proof of concept' Excel-2010 sheet (it runs satisfactorily). Applying this approach to the real world problem (eg., setting up a 30-stock portfolio) appears to be quite labor intensive however. Do you know of any downloadable Excel 2010 templates that are macro-enabled and capable of:
1) importing the historical data for a portfolio of stocks
2) doing the data massaging required in advance of using Solver

Short of citing an elegant rendition of the above is it possible that in the near future you'd be doing an article on importing data from the web to populate the historical-data table that your method uses?

Although I'm working on my Excel competencies I'm not power user yet and this kind of tutorial stuff is much appreciated. :)

James from Ohio posted over 2 years ago:

Jerry, here's the primary difference between correlation and covariance.

Correlation is a "relative" number. It is usually presented as a percentage and is always a number that ranges from -100% to +100%. Therefore, comparisons are more meaningful. If A and B are 90% correlated and C and D are 70% correlated, then it can be fairly said the A and B are more highly correlated.

Covariance is an "absolute" number and relates to the magnitude of the underlying data series upon which is is calculated. It has no limit. It could be -1 million or + 5 million. Hence, comparisons are not particularly relevant or effective.

I hope you feel this helps.

Jerry from Arizona posted about 1 year ago:

Eric,

Here is a website for excel sheets. Look for the multiple stock download sheet.

http://investexcel.net

Ted Nicholas from California posted about 1 year ago:

Given the continual marketing of AAII's various optional cost offerings, it would be very interesting to see what the mean variance optimization of, say, the stock superstars portfolio is. Always nice to see how well risk is optimized when paying for advice.

Dan from California posted 12 months ago:

This is a joke, right? Or a test for your readers? If not, you should crosscheck by some other method before you believe a tool's result! If I start \$100,000 in SNDK and leave it there for the eight years, I'll have \$490,757. Do the same with ARO, I'll have \$773,942. What is your definition of maximum return? If I rebalance yearly to 42.6% ARO, 17.8% GTIV and 39.6% SNDK, I'll end up with \$963,634. That's my definition of maximun return.

Wayne Thorp from Illinois posted 12 months ago:

@Dan, risk is in the eye of the beholder. You already know the returns so it makes the decision of placing your assets into three stocks very easy.

D Kane from California posted 12 months ago:

We're talking about maximizing return when risk is not a consideration. You said in that section of the article, "Since we are not concerned with risk, it makes sense that our long-only, return-maximizing portfolio in column I would invest solely in the stock with the highest average annual returnâ€”SanDisk Corporation (SNDK)." I could see that this is not the case by looking at ARO. Just how does your optimizer come up with SNDK? When I run solver to maximize the dollar value at the end of 8 years, I get the portfolio I mentioned.

George B from Ohio posted 9 months ago:

Is this spreadsheet available for download. My motto is why reinvent the wheel if you don't have to. If it is available for download where do I find it.

Thanks

George