The correlation of asset returns is important to investors because a portfolio comprised of assets with lower return correlations has less overall risk. Ignoring dividends, positive returns indicate that the stock price has increased, and negative returns indicate the stock price has decreased. The steps here illustrate how to calculate the correlation by hand. In a spreadsheet, such as Excel, this same result can be obtained in one step. For illustration purposes, consider two return series with only four observations. Assume annual returns from 2009 through 2012 of 0.05, 0.12, 0.20, -0.05 for the first stock, and -0.01, -0.05, 0.06, and 0.09 for the second stock. (Note that 0.05 represents a 5-percent return, 0.12 is a 12-percent return, and so forth. Also, negative numbers represent a loss.)
Add all of the returns for the first return series, and divide by the number of returns to obtain the average return for the first stock. Repeat this for the second series of returns. The sum of the first stocks’ returns is 0.05 + 0.12 + 0.2 - 0.05 = 0.32. Its average is 0.32/4 = 0.08. The average of the second stock’s return is 0.0225.
Subtract the average from each observed return. These are referred to as the deviations from the mean. The 2009 deviation from the mean for the first stock is 0.05 - 0.08 = -0.03. For 2010, it is 0.12 - 0.08 = 0.04. For 2011 and 2012, they are 0.12 and -0.13, respectively. Calculating deviations from its mean of 0.0225 for the second stock results in -0.0325, -0.0725, 0.0375, and 0.0675.
Multiply the deviations from the mean together for each observation. The product of the deviations for the first observation in 2009 is (-0.03)(-0.0325) = 0.000975. The products for 2010, 2011 and 2012 are -0.0029, 0.0045, and -0.008775.
Add the products obtained in step three together to find the sum of the products: 0.000975 - 0.0029 + 0.0045 - 0.0080775 = -0.0062.
Find the covariance by dividing the sum of the products by the number of observations minus 1. The covariance is -0.0062/(4-1) = -0.0030667.
Square the deviations from the mean for the first return series. For 2009, the squared deviation from the mean is (-0.03)(-0.03) = (-0.03)^2 = 0.0009. For 2010, the squared deviation from the mean is (0.04)^2 = 0.0016. For 2011 and 2012 they are 0.0144 and 0.0169, respectively.
Add all squared deviations from the mean to find the sum of the squared deviations from the mean: 0.0009 + 0.0016 + 0.0144 + 0.0169 = 0.0338.
Divide the sum in step 7 by the number of observations minus 1. This is the variance of the first return series: 0.0338 / (4-1) = 0.01126667.
Take the square root of the variance of first return series. This is the standard deviation of the first return series: 0.10614456.
Repeat steps 6 through 9 for the second return series. This is the standard deviation of the second return series. The sum of the squared deviations for the second return series is 0.00105625 + 0.00525625 + 0.00140625 + 0.00455625 = 0.012275. The variance is 0.012275/(4-1) = 0.00409167. The standard deviation is the square root of the variance: 0.06396614.
Multiply the results in steps 9 and 10. The product of the standard deviations for each return series is the denominator in the formula of the correlation: (0.10614456)(0.06396614) = 0.00678966.
Divide the result in step 5 by the result in step 11. This is the correlation of the two return series. It is defined as the covariance divided by the products of their standard deviations: -0.00207/0.00678966 = -0.3043845.
- The correlation coefficient is bounded by -1 and +1. If you get an answer outside of this range, you made a mistake.
- Positive correlation coefficients indicate that, on average, returns move together, whereas negative coefficients indicate that more often than not, they move in opposite directions. A correlation coefficient close to zero indicates there is no statistical relationship between the two series.
- In Excel, labels can be placed as “Date” in cell A1, “Stock 1” in cell B1, and “Stock 2” in cell C1. The years 2009 through 2012 can reside in cells A2, A3, A4 and A5 denoted as an array by A2:A5. The four returns for Stock 2 can reside in the array B2:B5 and the returns for Stock 2 can reside in the array C2:C5. Then in a blank nearby cell, enter the formula =CORREL(B2:B5,C2:C5). The result is -0.3043845.
- In practice, you should use at least 30 observations for reliable results.
- How to Find the Correlation of Two Stocks
- How to Figure Trailing Returns
- How do I Identify the Required Rate of Return on an Investment?
- How to Calculate Historical Variance & Return on a Stock
- How to Calculate the Annual Rate of Return on a Bond
- How to Calculate Abnormal Returns with Stock Prices and S&P; Information
- How to Calculate Maximum Theoretical Value of a Bond
- How to Calculate 15-Year Fixed Mortgage Payments