WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 250+ Courses All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Excel Modeling » Covariance Formula

Covariance Formula

What is Covariance?

Covariance is a statistical measure used to find the relationship between two assets and is calculated as the standard deviation of the return of the two assets multiplied by its correlation. If it gives a positive number then the assets are said to have positive covariance i.e. when the returns of one asset goes up, the return of second assets also goes up and vice versa for negative covariance.

In the financial parlance, the term “covariance” is primarily used in portfolio theory, and it refers to the measurement of the relationship between the returns of two stocks or other assets and can be calculated based on returns of both the stocks at different intervals and the sample size or the number of intervals.

Covariance Formula

Mathematically, it is represented as,

covariance formula 1

Covariance Formula

where

  • RAi =Return of stock A in the ith interval
  • RBi =Return of stock B in the ith interval
  • RA=Mean of the return of stock A
  • RB=Mean of the return of stock B
  • n = Sample size or the number of intervals

The calculation of covariance between stock A and stock B can also be derived by multiplying the standard deviation of returns of stock A, the standard deviation of returns of stock B, and the correlation between returns of stock A and stock B. Mathematically, it is represented as,

Cov (RA, RB) = ρ(A, B) * ơA * ơB

where ρ(A, B) = Correlation between returns of stock A and stock B

  •  ơA = Standard deviation of returns of stock A
  • ơB = Standard deviation of returns of stock B

Explanation

The calculation of covariance between stock A and stock B can be derived by using the first method in the following steps:

  • Step 1: Firstly, determine the returns of stock A at different intervals, and they are denoted by RAi, which is the return in the ith interval, i.e., RA1, RA2, RA3,….., RAn are the returns for 1st, 2nd, 3rd,….. and nth interval.
  • Step 2: Next, determine the returns of stock B at the same intervals and they are denoted by RBi 
  • Step 3: Next, calculate the mean of the returns of stock A by adding all the returns of stock A and then dividing the result by the number of intervals. It is denoted by RA.

covariance formula 3

  • Step 4: Next, calculate the mean of the returns of stock B by adding all the returns of stock B and then dividing the result by the number of intervals. It is denoted by RB

 covariance formula 4

  • Step 5: Finally, the calculation of covariance is derived based on returns of both the stocks, their mean returns, and the number of intervals, as shown above.

The calculation of covariance between stock A and stock B can also be derived by using the second method in the following steps:

Popular Course in this category
Sale
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
4.9 (1,067 ratings)
250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Step 1: Firstly, determine the standard deviation of the returns of stock A based on the mean return, returns at each interval, and several intervals. It is denoted by ơA.
  • Step 2: Next, determine the standard deviation of the returns of stock B, and it is denoted by ơB.
  • Step 3: Next, determine the correlation between the returns of stock A and that of stock B by using statistical methods such as the Pearson R test. It is denoted by ρ(A, B).
  • Step 4: Finally, the calculation of covariance between stock A and stock B can be derived by multiplying the standard deviation of returns of stock A, the standard deviation of returns of stock B, and the correlation between returns of stock A and stock B as shown below.

Cov (RA, RB) = ρ(A, B) * ơA * ơ

Example

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

Let us take the example of stock A and stock B with the following daily returns for three days.

covariance example1

Determine the covariance between stock A and stock B.

Given, RA1 = 1.2%,RA2 = 0.5%,RA3 = 1.0%

RB1= 1.7%,RB2 = 0.6%,RB3 = 1.3%

Therefore, the calculation will be as follows,

Example1.1

Now, Mean Return of stock A,RA= (RA1 + RA2 + RA3 ) / n

  • RA= (1.2% + 0.5% + 1.0%) / 3
  • RA= 0.9%

Example1.2

Mean Return of Stock B, RB= (RB1 +RB2+ RB3 ) / n

  • RB= (1.7% + 0.6% + 1.3%) / 3
  • RB= 1.2%

Therefore, the covariance between stock A and stock B can be calculated as,

covariance formula 6

Example1.3

= [(1.2 – 0.9) * (1.7 – 1.2) + (0.5 – 0.9) * (0.6 – 1.2) + (1.0 – 0.9) * (1.3 – 1.2)] / (3 -1)

Covariance between Stock A and Stock B will be –

example1.4

  • Cov (RA, RB) = 0.200

Therefore, the correlation between stock A and stock B is 0.200 which is positive and as such it means that both returns move in the same direction i.e. either both have positive returns or both have negative returns.

Relevance and Uses

From the perspective of a portfolio analyst, it is vital to grasp the concept of covariance because it is primarily used in portfolio theory to decide which assets are to be included in the portfolio. It is a statistical tool to measure the directional relationship between the price movement of two assets, such as stocks. It can also be used to ascertain the movement of a stock vis-à-vis the benchmark index, i.e., whether the stock price goes up or goes down with the increase in the benchmark index or vice versa. This metric helps a portfolio analyst to reduce the overall risk for a portfolio. A positive value indicates that the assets move in the same direction, while a negative value indicates that the assets move in opposite directions.

Recommended Articles

This article has been a guide to Covariance and its definition. Here we discuss how to calculate covariance using its formula along with a practical example and downloadable excel template. You can learn more about financing from the following articles –

  • Population Variance Formula
  • Formula of Variance Analysis
  • Calculate Portfolio Variance
  • Correlation vs. Covariance
  • Histogram Examples
0 Shares
Share
Tweet
Share
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
  • 250+ Courses
  • 40+ Projects
  • 1000+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

Login

Forgot Password?

WallStreetMojo

Download Covariance Formula Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More