Correlation Formula

Edited byAnkush Jain
Reviewed byDheeraj Vaidya, CFA, FRM

Correlation Meaning

The correlation formula is applied to understand the movement of two data points in relation to each other. In the world of finance and investing, the two data points are usually securities. It is closely associated with the diversification of a portfolio, a method that is used to mitigate risk and spread investments across different asset classes.

Correlation-Coefficient--Formula

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Correlation Formula (wallstreetmojo.com)

The correlation formula statistics help investors and analysts compare a particular stock in relation to the index to see if the performance is correlated. However, it is important to understand that this metric does not show if A caused B or if it was influenced by an external factor. It is computed as a correlation coefficient that should range between -1.0 and +1.0.

Correlation Formula Explained

Correlation is a statistical measure between two variables. One may define it as the change of quantity in one variable corresponding to a change in another. One can calculate it by summation of the product of the sum of the first variable minus the mean of the first variable into a sum of the second variable minus the mean of the second variable divided by the whole under the root of the product of the square of the first variable minus the mean of the first variable into the sum of the square of second variable minus the mean of the second variable.

The value of a simple correlation formula is limited between -1 and +1 and can be interpreted as follows:

  • -1: If it is -1, then variables are known as perfectly negatively correlated. That means if one variable is moving in one direction, another is moving in the opposite direction.
  • 0: That means the variable is not having any correlation.
  • +1: If it is +1, then variables are known as perfectly positively correlated. Both variables are moving in positive directions.

A perfect correlation implies that the movement in one variable shall result in the exact same movement in the other variable too in the same direction. While there are various methods of calculating this metric, the most widely used method of calculation is the Pearson product-moment correlation.

However, in the modern day, investors, market experts, and analysts use the CORREL function in MS Excel to avoid doing complex calculations which can be time-consuming and have a high possibility of errors.

Financial Modeling & Valuation Courses Bundle (25+ Hours Video Series)

–>> If you want to learn Financial Modeling & Valuation professionally , then do check this ​Financial Modeling & Valuation Course Bundle​ (25+ hours of video tutorials with step by step McDonald’s Financial Model). Unlock the art of financial modeling and valuation with a comprehensive course covering McDonald’s forecast methodologies, advanced valuation techniques, and financial statements.

Formula

If we have two variables, x, and y, then the correlation coefficientCorrelation CoefficientCorrelation Coefficient, sometimes known as cross-correlation coefficient, is a statistical measure used to evaluate the strength of a relationship between 2 variables. Its values range from -1.0 (negative correlation) to +1.0 (positive correlation). read more between 2 variables can be found as:

Correlation Coefficient = ∑(x(i)- mean(x))*(y(i)-mean(y)) / √ (∑(x(i)-mean(x))2 * ∑(y(i)-mean(y))2)

Where,

  • x(i)= value of x in the sample
  • Mean(x) = mean of all values of x
  • y(i) = value of y in the sample
  • Mean(y) = mean of all values of y

Examples

Let us understand the correlation formula statistics with the help of a few examples as discussed below. These examples shall give us a practical overview of the concept and its related factors.

You can download this Correlation Formula Excel Template here – Correlation Formula Excel Template

It is effortless to calculate the correlation in ExcelCorrelation In ExcelCORREL function is a statistical function in Excel. The CORREL formula finds out the coefficient between two variables and returns the coefficient of array1 and array2. The correlation coefficient determines the relationship between the two properties.read more. The syntax of the function used is as follows:

Correlation Coefficient = CORREL (array1, array2)

Example#1

Let’s take the same example above for calculating correlation using Excel.

Solution:

Below are the values of x and y:

Correlation Formula Example 1

The calculation is as follows.

Example 1.1

Basis Excel formula = CORREL (array(x), array(y))

Example 1.2

Coefficient = +0.95

Since this coefficient is near +1, x and y are highly positively correlated.

Example#2 

Correlation is mainly useful for analyzing companies’ stock prices and creating a stock portfolio based on that.

Let us determine the correlation of Apple stock with the Nasdaq index based on the last one-year stock performance. Apple is a US-based multinational companyMultinational CompanyA multinational company (MNC) is defined as a business entity that operates in its country of origin and also has a branch abroad. The headquarter usually remains in one country, controlling and coordinating all the international branches. read more specializing in IT products such as iPod, iPad, Mac, etc.

Solution:

Below is the monthly return of Apple and Nasdaq stocks for the last year:

Correlation Formula Example 2

Let’s now input the values –

Correlation Coefficient = ∑(x(i)- mean(x)).(y(i)-mean(y))/√ ∑(x(i)-mean(x)) ^2 ∑(y(i)-mean(y))^2

Example 2.1

Correlation between Apple and Nasdaq= 0.039/ (√0.0039)

Example 2.2

Coefficient =0.62

Since the correlation between Apple and Nasdaq is positive. Hence, Apple positively correlates with Nasdaq

Example#3

Let us now examine the correlation between Walmart and the Nasdaq index based on last year’s stock performance. Walmart is a US-based company which has a retail supermarket chain.

Solution:

Below is the monthly performance between Walmart and Nasdaq for the last year:

Image 3

Let’s now input the values in the formula: –

Correlation Coefficient = ∑(x(i)- mean(x)).(y(i)-mean(y))/√ ∑(x(i)-mean(x)) ^2 ∑(y(i)-mean(y))^2

Therefore, the calculation is as follows:

Image 3-1

Correlation between Walmart and Nasdaq= 0.0032/ (√0.0346*0.0219 )

Eg 3.2

Coefficient =0.12

We can see that Walmart and Nasdaq are positively correlated but not as much compared to Apple’s correlation with Nasdaq.

Relevance and Use

Let us understand the relevance and uses of applying the simple correlation formula to security and gauging its movements in accordance with the other variable through the points below.

Correlation Vs Regression

While both correlation formula statistics and regression are widely followed numbers for their individual importance, it is important to understand the differences in their fundamentals and implications to completely understand the concept. Let us do so through the comparison below.

Correlation

  • It refers to the relationship between two or more variables.
  • X and Y are not fixed variables, they are random in nature.
  • The range of the correlation or relationship lies between -1.0 to +1.0.
  • This metric gives very limited information after the relationship between the variables is established.
  • Therefore, it is the study of the linear relationship of the variables.
  • Its objective is to find the numerical value that can be a representation of the relationship between the variables.

Regression

  • Regression measures the average relationship between two or more variables.
  • Here X is a random variable, whereas, Y is a fixed variable.
  • It is expressed as an absolute figure.
  • It is used to determine or predict the value of the other variable based on the value of the given variable.
  • It is the study of the linear and non-linear relationship between the variables.
  • Its objective is to find or predict the values of a random variable based on the value of the fixed variable.

Recommended Articles

This article is a guide to Correlation Formula. Here we explain how to calculate, examples, relevance, and uses, and compared it with regression. You can learn more about financing from the following articles: –