R-squared is a statistical tool used to measure the degree of correlation between a portfolio (or a single stock) and the broader market (market index or other stock). Correlation analysis allows investors to make predictions about the growth or price direction of an asset by looking at how it correlates with other market variables. Specifically, the number R^2, is used as a metric to measure how well outcomes can be predicted.
Define your variables. Assume you are comparing two different assets, Asset 1 and Asset 2.
Create six columns of data in an Excel worksheet.
Name each column date, a, b, ab, a^2, b^2. The first column is the date. The second column is the price of Asset 1 (stock, property, mutual fund, etc.) by date. The third column is the price of the comparison asset (Asset 2) by date. If you are comparing Asset 1 against the market, use the price of the Dow Jones Industrial Average. The fourth column represents the price of Asset 1 (a) multiplied by Asset 2 (b). The fifth column is the price of the Asset 1 (a) raised to the second power (a^2) and the sixth column is the price of the Asset 2 (b) raised to the second power (b^2).
Insert your data in columns a and b and fill out the remaining columns. Assume you have prices for Asset 1 and Asset 2 for six dates. In other words, you have six data points to compare.
At the bottom of your chart, create a summation row to sum the data in each column.
Use the numbers in your Excel worksheet to calculate R^2 on paper or in another area of your worksheet. R = (6_(sum of ab column) - (sum of a column)(sum of b column)) / [sqrt((6(sum a^2 column) - (sum of a column)^2)_(6*(sum of b^2 column) - (sum of b column)^2)], where sqrt = square root and 6 is the number of data points (see Step 4). Take R and raise it to the second power. This is R^2 or the rate of correlation between the two assets.
Use the Excel function CORREL to calculate R^2 and validate your answer in Step 6. Excel refers to R^2 as the correlation coefficient. The function is CORREL(known_y's, known_x's), where y = b and x = a in our example. The answer should be the same as in Step 6.