Calculating the Variance Covariance Matrix using stock Prices

Modeling - I take 12 years of annual data for 6 stocks and work out the variance covariance matrix.
Simon Benninga comprehensively develops Excel spreadsheets to explain advanced topics in finance. His approach is intuitive and simple yet succinct in explaining otherwise complex areas.

The Variance-Covariance Matrix: Cornerstone of Portfolio Analysis

In the realm of investment management, the variance-covariance matrix stands as a pivotal tool, providing deep insights into the dynamics of asset returns within a portfolio. This matrix is not just a mathematical construct but a practical guide to understanding and managing risk and return in a portfolio.

1. Understanding the Basics:

Variance: At its core, the variance measures the spread of a security's returns around its mean. It's a quantification of risk. For a portfolio, understanding each asset's variance is key to assessing its inherent risk.
Covariance: While variance focuses on individual assets, covariance deals with how pairs of securities move in relation to each other. Positive covariance indicates that assets tend to move in the same direction, whereas negative covariance suggests they move in opposite directions.

2. Building the Matrix:

The variance-covariance matrix is a symmetrical matrix where each element represents the covariance between a pair of assets. The diagonal elements are variances of individual assets, while off-diagonal elements are covariances.

3. Application in Portfolio Analysis:

Risk Assessment: By analyzing this matrix, investors can understand the combined risk of the portfolio, not just the risk of individual assets.
Diversification: A key strategy in portfolio management is diversification, which aims to reduce risk without sacrificing returns. The variance-covariance matrix helps in identifying assets that, when combined, reduce the overall portfolio risk due to diversification benefits.

[rogjerr]

Why use ln(4.15/2.36) instead of (4.15/2.36)-1 for returns?

56.44% vs 75.84%

[yutapark]

Omg，thank you every much！very very thank you ！🎉🎉🎉

[Michael-enrk]

5 years later and this is so help for my coursework. God bless you sir

[zoniatayba]

Do you have any idea HOW MUCH YOU HAVE HELPED ME?! Thank You So Much. Really! Splendid. I did it after practice! And one thing, CTRL+SHFT+return is actually CTRL+SHFT+Enter as well. Both are the same. They are used to create flower brackets

[aslivinschi]

Brian Many thanks for your video! would like to ask how would you deal in case not all of your assets have same data available. Let's suppose that there are hour bonds and for three bonds you have data for the last 250days however for the forth bond you have data just for the last 100 days (as is a new bond)? Much appreciated your reply.

[mr.richard]

You're a life saver, sir. Thank you so much!!

[romans.]

Why are you calculation the excess return by return minus average return. I think instead of average return the risk free rate has to substracted?

[tjayratnawardana]

If we have monthly returns and we are to compute an annualised covariance matrix in excel, how do we do that? I know how to generate the covariance matrix using the 'Data Analysis' function. However, how do I convert it to yearly figures as the returns are all monthly??

[sulfursalt]

You saved my life, sir! Wish you health and success!

[marto]

Hi Brian, very useful video. Thank you. I have a further question...given that you have a time series of stock prices and weights, could one calculate an index value for each period which is the sum product of the weights and stock returns in each period and then calculate the standard deviation of that index series? Would that be the same value as when we calculate the standard deviation of the portfolio via matrix multiplication of the variance-covariance matrix and weights?

[MuhammadAsif]

Please guide me, You take an average of return, why not you take Geometric mean of return.

[connos]

Great video, very helpful. Thank you!

[fksons]

Highly simplified and impactful.Thank you

[sayednab]

Hello there, thanks for the upload. I have a couple of questions. 1. why there is a slight deviation when I use this formula to solve for the returns. stock close/stock open-1instead of ln. last q I have is regard to the matrice. why you don't subtract -1 at the end. thx

[finaakutty]

Indeed the basics really important. Just nice. Thank you so much.

[mahanani]

this video very very very helpful, thank you sir

[ianduty-dean]

This was so helpful! I kept making the dumbest mistake. I forgot to hit control+shift while hitting the return key. For a solid five minutes I thought I was going to lose my mind.

[titushui]

Using ln(This Yr Price/Previous Yr Price) as Annual Rate of Return is something fresh and wonderful.

[王王-kv]

Thank you for this helpful video. But could you show how to calculate VAR for a simple stock index by variance covariance simulation?

[AK.]

Hi Brian, Thanks for the video. I have the book, but I am unable to find where it shows how to calculate the matrix. Can you kindly tell me where it is?