Let us first address Variance, which is a statistical measure, followed by Covariance.
The spread of stock yields is a way to evaluate the deviation from its daily regular returns. Computing the variance is very simple –
Where,
σ2 = Variance
X = Daily return
µ = Average of daily return
N = Total number of observation
Assume the daily return of the stock for 5 consecutive days are as follows:
Day 1 – + 0.75%
Day 2 – + 1.25%
Day 3 – -0.55%
Day 4 – -0.75%
Day 5 – +0.8%.
In this instance, we must determine the daily return’s deviation from its mean and then square it. The average return is +0.3%.
The dispersion squared is 0.0318000%, which when divided by five (N) gives us the variance.
0.0318000% / 5
0.0318000% / 5
This number gives us an idea of how the daily returns vary from their average expected returns. As an investor, you should analyze the variance to evaluate the riskiness of the investment. A large variance implies greater potential risk, while a smaller one indicates lower risk. Taking into account only 5 days of data, we can consider this variance to be high.
Here’s an interesting fact: Variance and standard deviation are linked by a straightforward equation. The relationship between them is easy to understand.
Square Root of Variance = Standard Deviation
We can apply this to the example above and calculate the 5-day standard deviation of the stock %
~ 0.8%
I now want to draw your attention to the Variance, and its significance. Ultimately, we will input both variance and covariance into the portfolio variance equation; this figure is also known as the standard deviation or the volatility of the stock over the previous 5 days.
– Covariance
Covariance is a measure of how two (or more) variables interact. It can indicate whether they move together with a positive covariance, or in opposition with a negative covariance. In relation to stock market prices, this can give us an idea of the likely trajectory of the prices: if they have a positive covariance, they will likely move in tandem; however, if the correlation is negative, then they are likely to go in opposite directions.
I understand that covariance and ‘correlation’ can be confused, but they are different. We will delve deeper into this topic in the chapter.
Calculating the covariance of two stocks may help us comprehend the concept more thoroughly. The formula is as follows:
Where,
Rt S1 = Daily stock return of stock 1
Avg Rt S1 = Average return of stock 1 over n period
Rt S2 = Daily stock return of stock 2
Avg Rt S2 = Average return of stock 2 over n period
n – The total number of days
You can figure out the correlation between two stocks by calculating the sum total of their differences in daily revenues compared to their respective average returns.
Sounds confusing?
Let us use an example to comprehend how we can work out the covariance between two stocks.
For this example, I have chosen two stocks – Cipla Limited and Idea Cellular Limited. To work out the covariance between them, we should use the formula above. Fortunately, excel can be utilised to put this equation into practice.
If you had to guess the correlation between Cipla and Idea, what would it be? These two large corporates have similar sizes, yet are in completely distinct industries. What deduction can you make?
This article will provide a detailed look at the steps needed to calculate covariance in Excel. Even though there is an existing function for this purpose, we will take the longer route just for the sake of clarity.
Step 1 – Obtain the daily stock prices. To demonstrate, I have pulled 6 months’ worth of data for both equities.
Where,
Rt S1 = Daily stock return of stock 1
Avg Rt S1 = Average return of stock 1 over n period
Rt S2 = Daily stock return of stock 2
Avg Rt S2 = Average return of stock 2 over n period
n – The total number of days
To find out the covariance between two stocks, you simply add up the product of the variance in the daily returns for each stock from its respective average return.
It’s confusing, isn’t it?
For instance, we can figure out the covariance between two stocks by using an example.
For the purpose of this example, I have selected Cipla Limited and Idea Cellular Limited as two stocks. The formula mentioned previously can be used to find the covariance between them. For working out the calculation, we will use Microsoft Excel.
What do you suppose the covariance between Cipla and Idea would be? Both corporations are of a similar size, but operate in two distinct industry sectors. Take a moment to guess.
Here’s how to calculate covariance in Excel: I’ll use the longer approach for clarity, even though there is a direct function for this purpose.
To begin this example, I have acquired six months of daily stock prices for both assets.
Step 2- To obtain daily returns for both stocks, simply divide the current day’s stock price by that of the prior day and subtract 1.
Step 3- work out the mean of the daily results.
Step 4- Once the average is computed, compare the daily return against it.
Step 5- take the two series you worked out in the preceding one and multiply them together.
Step 6 – Sum up the data you’ve worked with. To get a count of the number of points, use the count function in Excel and use any field as an input array – I used dates here.
Step 7- To calculate the covariance, divide the sum of 0.006642 by n-1, which in this case is 126. Thus, the covariance will be
= 0.006642/126
= 0.00005230
As you can observe, the covariance figure is quite low. However, we are only evaluating whether the stocks have a positive or negative covariance. As they have a positive covariance, this implies that returns of both stocks move in the same direction for a certain market situation. It needs to be mentioned though that covariance does not show us how much these stocks fluctuate – correlation measures that instead. The correlation between Idea and Cipla is 0.106 which denotes that these two stocks are not strictly related.
Here’s an interesting fact: the mathematical equation for correlating two stocks is this.
Where,
The covariance between the two stocks can be found by computing Cov (x,y).
σx = Standard deviation of stock x
σy = Standard deviation of stock y
Let us verify the accuracy of the correlation between Idea and Cipla, which have been determined using an Excel function. To do that, use the formula to calculate the standard deviation of each stock, which is equal to the square root of its variance. Good luck!
When it comes to constructing a stock portfolio, do you believe that having stocks with a positive covariance is beneficial or detrimental? Or rather, is it something that portfolio managers desire? Generally speaking, they prefer having stocks with a negative covariance. This helps to protect the portfolio from sizeable losses because if one stock decreases in value, the other will usually remain stable. Thus, this counterbalances the entire portfolio and lessens the overall risk.
One way to approach assessing a portfolio containing multiple stocks is to look at the covariance of each holding with the rest. To explain this further, consider a portfolio of four investments as an example. The list may read something like this:
ABB
Cipla
Idea
Wipro
In order to figure out the covariance, we have to work this out.
ABB, Cipla
ABB, Idea
ABB, Wipro
Cipla, Idea
Cipla, Wipro
Idea, Wipro
As you can observe, 4 stocks necessitate the calculation of 6 covariances, which refers to the covariance between stock 1 and stock 2 as well as stock 2 and stock 1. When we have more than two stocks in the portfolio, a ‘Variance – Covariance Matrix’ is used for working out and tabulating this covariance.
*All subscription pricing plans mentioned across the website are exclusive of 18% GST
By signing up, you agree to receive transactional messages on WhatsApp. You may also receive a call from a BP Wealth representative to help you with the account opening process
Disclosures and Disclaimer: Investment in securities markets are subject to market risks; please read all the related documents carefully before investing. The securities quoted are exemplary and are not recommendatory. Past performance is not indicative of future results. Details provided in the above newsletter are for educational purposes and should not be construed as investment advice by BP Equities Pvt. Ltd. Investors should consult their investment advisor before making any investment decision. BP Equities Pvt Ltd – SEBI Regn No: INZ000176539 (BSE/NSE), IN-DP-CDSL-183-2002 (CDSL), INH000000974 (Research Analyst), CIN: U45200MH1994PTC081564. Please ensure you carefully read the Risk Disclosure Document as prescribed by SEBI | ICF
Attention Investors
Issued in the interest of Investors
Communications: When You use the Website or send emails or other data, information or communication to us, You agree and understand that You are communicating with Us through electronic records and You consent to receive communications via electronic records from Us periodically and as and when required. We may communicate with you by email or by such other mode of communication, electronic or otherwise.
Investor Alert:
BP Equities Pvt Ltd – SEBI Regn No: INZ000176539 (BSE/NSE), INZ000030431 (MCX/NCDEX), IN-DP-CDSL-183-2002 (CDSL),
INH000000974 (Research Analyst) CIN: U45200MH1994PTC081564
BP Comtrade Pvt Ltd – SEBI Regn No: INZ000030431 CIN: U45200MH1994PTC081564
For complaints, send email on investor@bpwealth.com