How to Calculate the Asset Correlations in Excel

The correlation between two or more assets is the measure of how much their performances are aligned. If the performance of two assets is similar, then it can be said that they have some degree of correlation. This is especially true with stocks in the same sector, such as oil stocks. In modern portfolio theory, and specifically the capital asset pricing model, the correlations between two assets is measured by r-squared, which is calculated using the covariance and standard deviation between both assets. This calculation is however simplified using Excel.

Gather all the data needed for your correlation analysis. You will need data regarding the performance of both assets. List the performance of one asset in one column, and the performance of the other in the second column. For example, each column can list the daily performance of one stock, in terms of price, with each row representing a certain date. The rows for both columns should correspond to the same date.

Select the "Tools" menu, and then select "Data Analysis." From the data analysis dialog box, select "Regression" and click "OK." If you do not have data analysis in the tools menu, you may have to install it by selecting "Add-Ins" from the tools menu and selecting "Analysis Toolpak" from the dialog box.

Select the icon that appears, in the Regression dialog box, as an upward-pointing red arrow in the "Input Y Range" box. You will be taken back to your spreadsheet, where you should highlight the column that represents one of your assets. Click the downward-pointing red arrow icon and you will be returned to the regression dialog box.

Select the icon that appears as a upward-pointing red arrow in the "Input X Range" box and select the column that represents the other asset's performance. Click the downward-pointing red arrow icon to return to the regression dialog box. Select "OK." Your regression results will appear in a new worksheet within the existing spreadsheet.

Note the "R Square" result in the regression statistics section of the regression analysis. This represents r-squared, which may take a value between 0 and 1. A result of 1 means that the two assets are 100 percent correlated, and a result of 0 would mean that they are completely uncorrelated. Thus, the higher r-squared is, the more the two assets are correlated.