Determine the Correlation between a Stock and the Index

With talks about possible market decline, are gold companies actually a good investment to buy into? I want to investigate Barrick Gold Crop, which is the second-largest gold mining company in the world.

A correlation determines the relationship between two things. For Barrick gold Crop, a strong negative correlation against the S&P index is what we want to see. This means the stock goes up when the S&P goes down. Is this what we will get?

I will show you how to determine the correlation below using Excel. You can do this to determine the relationship for anything, including stocks, digital assets, and real estate.

You can see the free Excel here.

Step 1: Download data and calculate the return

Download data for Gold and S&P 500 from Yahoo Finance! I only downloaded the data for the last 5 years. The symbol GOLD is for Barrick Gold Crop, the second-largest gold mining company in the world.

Once you have the data, you need to calculate the percentage return, which is:

Percentage Change = (New Price – Old Price / Old Price)

I then edited the data from most recent to less recent and applied the formula here into the Excel workbook.

Step 2: Get the Average return and calculate the Deviation from the Mean

 

You can use the Average formula in Excel to get the average return for Gold and the S&P 500. Then you want to subtract the average return from the actual returns for both Gold and S&P 500. For example, each GOLD return is going to be subtracted by the GOLD average.

Step 3: Find the product of the Deviations and calculate the Covariance

You then need to find the product of [GOLD minus average] and [S&P minus average] that we found in Step 2, which is going to equal our product of deviation.

Once you have the product of deviation for each return, add them together. The total is now multiplied by 1 / (n – 1). In this situation, n is the number of items we have. This will give us the covariance.

Step 4: Get the Standard Deviation for Gold and S&P

 

To get the Standard Deviation (SD), square the deviation from the mean for GOLD and S&P. Do this all the returns and sum the total together.

When we have all this information, then we can get the variance. The variance is just the sum of the standard deviation divided by n – 1. Finally, square root the variance, and we have our standard deviation.

Step 5: Get the correlation

We have all the pieces that we need. Thus, we can use the correlation formula:

Correlation = Covariance / [Standard Deviation (GOLD) * Standard Deviation (S&P500)]

The correlation we have is a -0.013

To interpret this data, we see that there is no relationship between GOLD and the S&P index. We only see a slight negative correlation, which means a small increase for GOLD when the S&P falls.

 

Bonus: Get the Beta

With all the information that we have, we can calculate the Beta. The Beta for Gold is:

= Correlation * (SD of Gold / SD of S&P)

The weak beta here implies that the company GOLD has almost zero correlation with the S&P index, which can be considered good for investors who want to diversify. 

Leave a Reply