How to Calculate Correlation of Investment Returns

You've crossed all the T's and dotted all the I's in your beginning investment portfolio. You set aside a little money every month for mutual funds, you've maxed out your retirement account contributions, you've set up a Roth IRA and you have already begun to diversify.

Now that you have a solid portfolio, it's time to deepen your understanding not just of how everything works but how everything works together. Calculating the correlation of your investments' returns will help you do just that. In order to calculate the correlation, you will need the closing prices or non-stock asset values over the duration of time you intend to analyze.

Essentially, correlation gauges the relationship or lack thereof between the returns of two different investments. It's measured by a range of -1.0 to +1.0, where the latter indicates a perfect correlation, and the former indicates a perfect negative correlation. In a perfect correlation, two investments will always change value in unison; in a perfect negative correlation, they'll always change value in opposite directions. Of course, perfect correlations of either type are virtually impossible, but the scale helps you get a read on relationships.

The utility of knowing correlation boils down to one word: diversification. The less correlated your investments are, the less chance of your entire portfolio suffering at the mercy of fickle market trends.

Unless you're an aspiring mathematician, the most accessible way to input the formula for asset correlation is via a spreadsheet program, such as Microsoft Excel.

You'll first need to gather the daily closing prices or the value of non-stock assets rated over a regular period of time for the two assets you wish to explore. For stock, you can grab these from free sites, such as Morningstar or Google Finance. No matter what type of investment you have, the greater the amount of historic values you can gather, the more accurate your result will be.

Make an Excel spreadsheet for each investment with one column for the closing date and another for the corresponding value, listed in descending order. In the cell to the right of the first value, type "=ln(" and then click on the value, which will appear in the open parenthesis. Enter "/" to divide that value and then click on the next most recent value. Click on the bottom right corner of that cell and drag down to the bottom of your document, which will automatically calculate the periodic daily returns for each row.

Now it's time for another spreadsheet. Once you've got as many periodic daily returns for each investment as possible, list them in separate columns for each asset. In an empty cell, type "=CORREL(" and then select one entire column and add a comma to the cell. Select the other column, add a ")" to close the parenthesis and hit "Enter." This should produce the correlation coefficient between the two investments, a figure ranging from -1.0 to 1.0.

If you're not super into math or don't have Excel, worry not. Portfolio Visualizer offers a free online asset correlation calculator for stock, exchange-traded funds and mutual funds.

Just head over to their Asset Correlations calculator and enter your ticker symbols and the start and end dates of the period about which you're curious. From the drop-down menus, choose the correlation basis (from options including daily, monthly and annual returns) and the rolling correlation (from 20 to 120 trading days). Then, click the "View Correlation" button and you're off to the races – or the New York Stock Exchange.