EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Finance Finance Resources Finance Formula Correlation Coefficient Formula

Correlation Coefficient Formula

Madhuri Thakur
Article byMadhuri Thakur

Updated June 3, 2023

Correlation Coefficient Formula

Correlation Coefficient Formula (Table of Contents)

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

  • Formula
  • Examples

What is Correlation Coefficient Formula?

In statistics, specific outcomes directly relate to other situations or variables, and the correlation coefficient measures the direct association of two variables or situations. These variables exhibit a positive correlation coefficient when they simultaneously move in the same direction. If they move in opposite directions, a negative correlation coefficient is present. For example, if the market’s interest rate falls, corporate loans will be cheaper and boost the economy. So the interest rate and growth of the economy have a positive correlation coefficient. The value of the correlation coefficient defines the strength of the relationship between variables. The maximum value of the correlation coefficient varied from +1 to -1. If the correlation coefficient is +1, the variables are positively correlated; if that value is -1, it is called perfectly negatively correlated.

Suppose we have 2 sets of data given by X (X1, X2 … Xn) and Y (Y1, Y2 … Yn).

The formula For the Correlation Coefficient is given:

Correlation Coefficient = Σ [(X – Xm) * (Y – Ym)] / √ [Σ (X – Xm)2 * Σ (Y – Ym)2]

Where:

  • X – Data points in Data set X
  • Y – Data points in Data set Y
  • Xm – Mean of Data set X
  • Ym – Mean of Data set Y

This formula seems to be very time consuming and confusing at first.

Another way to calculate the correlation coefficient is using the CORREL () function in Excel. Finally, I will explain both Correlation Coefficient formulas by using examples.

Examples of Correlation Coefficient Formula (With Excel Template)

Let’s take an example to understand the calculation of the Correlation Coefficient in a better manner.

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

Correlation Coefficient Formula – Example #1

Let’s say we have two data sets, X & Y, each containing 20 random data points. First, calculate the Correlation Coefficient for the data set X & Y.

Correlation Coefficient Formula Example 1-1

Solution:

The mean is calculated as follows:

Correlation Coefficient Formula Example 1-2

  • Mean of Data Set X = 15.6
  • Mean of Data Set Y = 13.8

We must calculate the difference between the data points and the mean value.

Correlation Coefficient Formula Example 1-3

Similarly, calculate for all values of the data set X.

Correlation Coefficient Formula Example 1-4

Similarly, calculate all values of the data set Y.

Correlation Coefficient Formula Example 1-5

Calculate the square of the difference for both the data sets, X and Y.

Square of the Differences Example 1-6

Multiply the difference in X with Y.

Multiply the difference Example 1-7

The formula given below calculates the correlation coefficient:

Correlation Coefficient = Σ [(X – Xm) * (Y – Ym)] / √ [Σ (X – Xm)2 * Σ (Y – Ym)2]

Correlation Coefficient Formula Example 1-8

Correlation Coefficient = 0.343264

So it means that both the data sets have a positive correlation and are given by 0.343264.

Correlation Coefficient Formula – Example #2

Let’s say you are looking to invest in the stock market and invest in 2 stocks and want to choose those stocks so that your portfolio is diversified. It means that if one gives you a negative return, others will help you to get a positive return and vice versa. So basically, you want to invest in stocks with a negative correlation. You have 2 stocks and have got information on their historical returns for the last 15 years.

Example 2-1

Solution:

The correlation coefficient is calculated using the Excel formula.

Correlation Coefficient Formula Example 2-2

Correlation Coefficient = -0.45986

We have used the CORREL() function of Excel to see the correlation coefficient for the 2 stocks. You see that the correlation function is negative in value, which means that both stocks have a negative correlation. So your choice is apt as per your requirements.

Explanation

We know and discuss that the correlation coefficient measures the extent of the relation between two variables. Still, the catch here is that it can only measure the relationship, which is linear. This tool is not efficient in capturing nonlinear relationships.

Also, there are a few other properties of the correlation coefficient:

  • A correlation coefficient is a unit-less tool. This property is beneficial since it allows you to compare data with different units. For example, Stock prices depend on various parameters like inflation, interest rates, etc. So we can use public information to determine the correlation between them.
  • As discussed above, its value lies between + 1 to -1. So +1 is perfectly positively correlated, and -1 is perfectly negatively correlated.

Relevance and Uses of Correlation Coefficient Formula

The correlation coefficient helps us better understand the data sets and their relationship and has many applications in finance and economics. Financial institutes, banks, companies, and even governments use correlation coefficients to track historical data, extract meaningful information, and predict market trends efficiently. While the correlation coefficient is a powerful tool, one should not use it in isolation but rather apply it in conjunction with other tools and methodologies. The reason for that is simple; we cannot rely on data, which sometimes gives us insignificant complete information.

For example: If you have collected information and you have got to know that there is a positive correlation between rain and the death of dogs. It means that in the year when the rain was more, a number of dogs died. However, there is a correlation that is not meaningful at all. That is called a spurious correlation. So be very careful while making decisions only based on data.

Recommended Articles

This has been a guide to Correlation Coefficient Formula. Here we discuss calculating the Correlation Coefficient using a formula, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more –

  1. Guide To Coefficient of Determination Formula
  2. Formula For Calculating Adjusted R Squared
  3. How To Calculate Covariance Using Formula?
  4. Examples of Correlation Formula with Excel Template
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Investment Banking Jobs Offer
  • Finance Formula
  • All Tutorials
Certification Courses
  • All Courses
  • Financial Analyst All in One Bundle
  • Investment Banking Training
  • Financial Modeling Course
  • Equity Research Course
  • Private Equity Training Course
  • Business Valuation Course
  • Mergers and Acquisitions Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Correlation Coefficient Formula Excel Template

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Investment Banking Course

Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

डाउनलोड Correlation Coefficient Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW