How to Calculate R-Squared

Step 1

Define your variables. Assume you are comparing two different assets, Asset 1 and Asset 2.

Step 2

Create six columns of data in an Excel worksheet.

Step 3

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).

Step 4

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.

Step 5

At the bottom of your chart, create a summation row to sum the data in each column.

Step 6

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.

Step 7

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.