Correlation Formula

Formula to Calculate Correlation

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

The value of correlation 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, then 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.

If we are having 2 variable x and y then 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)
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)

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

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

It is effortless to calculate the correlation in Excel. Syntax of the function used is as follows:

Correlation Coefficient = CORREL (array1, array2)

Example#1

Let’s take the same example that we have taken 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 to +1, hence x and y are highly positively correlated.

Example#2 

Correlation is mainly useful for analyzing the stock price of companies and creating a stock portfolio based on that.

Let us find out 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) refers to that business entity whose headquarter is in one country, and its branches or subsidiaries extend across the globe in two or more nations. The MNCs aim at maximum revenue generation by spreading business worldwide.read more which is specialized in IT products such as iPod, iPad, Mac, etc.

Solution:

Below is the monthly return of Apple and Nasdaq stocks for the last one 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 is positively correlated with Nasdaq.

Example#3

Let us now look upon the correlation between Walmart and Nasdaq index based on the last one-year stock performance. Walmart is a US-based company which is having a retail supermarket chain.

Solution:

Below is the monthly performance between Walmart and Nasdaq for the last one 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 also positively correlated but not as much compared to Apple correlation with Nasdaq.

Relevance and Use

A correlation coefficient is useful in establishing the linear relationship between two variables. It measures how a variable will move compared to the movement of another variable. The practical use of this coefficient is to find out the relationship between stock price movement with the overall market movement. Basis of this analysis, a stock analyst, will include the proportion of stocks to create an optimal portfolioOptimal PortfolioPortfolio optimization refers to the strategic process of making improvements or positive changes to the investor's portfolio containing various assets. The Sharpe ratio helps to analyze the returns from an optimal portfolio.read more with minimum risk. Also, it is useful in data science to find out the relationship between 2 variables.

Also, the correlation coefficient is used very highly for studying the construct validity of data in factor analysis. It is highly used in regression analysisRegression AnalysisRegression Analysis is a statistical approach for evaluating the relationship between 1 dependent variable & 1 or more independent variables. It is widely used in investing & financing sectors to improve the products & services further. read more to predict the values of dependent variables based on the relationship between dependent and independent variables. This equation is quite useful in quantitative analysis to get the nature of the relationship between various variables. The basis of this relationship, if a variable is unrelated to other variables, then it can be eliminated from the list.

Recommended Articles

This has been a guide to Correlation Formula. Here we discuss how to calculate correlation using its formula along with examples and a downloadable excel template. You can learn more about financing from the following articles –