How to make the variance-covariance matrix in Excel: Portfolio Models #1
HTML-код
- Опубликовано: 19 дек 2024
- A short video on how to make the variance-covariance matrix in Excel, which is a basic skill needed if you are going to optimize portfolios. If you just want to know how to do it and don't want any explanations, go to 3:30. Let me know if there is any finance/excel topics you want me to make a video on. Please like, comment and subscribe.
Best video on youtube!!
I'm glad it's helpful. You should type semicolon and not star in your formula to multiply the matrices. In your case it would be: =MMULT(TRANSPOSE(C68:J126);C68:J126)
Thanks man, so clear
Nice job thank you.
Why would you choose to use this method and not simply generate the var/covar matrix of asset returns using Excel's Data Analysis Covariance function?
Thanks and regards, Robert.
Can u help me please how to create this matrix using var/cov matrix using data analysis? thanks
Ronilda Shkalla
Sure.
Once you have converted all the asset prices into returns you can use Excel's Data Analysis Tool.
On the tool bar on top of the work sheet select 'DATA'. Then select 'Data Analysis'. A pop up will appear. Scroll thru until you see 'Covariance'. Click on 'Covariance' and then click on 'OK'. A dialog box will appear requesting information. The 'input range' is the columns of return data you require the var/covar matrix for. Make sure you include the asset labels and click on the 'Labels in first row' box. Select a position on your work sheet that you want the matrix to appear; select 'output range' and click on the desired cell in the worksheet.
All done... so click on 'okay'. The matrix will appear. With Excel only one side of the matrix will be populated - this is a giant pain in the butt especially if you have a lot of assets. You will need to populate the other side of the matrix using the 'Transpose' function.
Hope this helps.
PS you can also construct a 'Correlation' matrix using Excels Data Analysis...just look for Correlation in the dialog box as described above.
If you are not sure there are a lot of good youtube videos on how to do this.
Good luck.
R.
Robert Brady thanks e loot :)
Robert Brady THanks a lot. I have created the matrix at a certain point,but i didint finished it because i dont know how to use the transpose function. I searched for a you tube video to show me that but i dont know whay i couldnt find it. I have created the correlation matrix watching a you tube video,thanks a lot for the suggestion. If its not a problem for you please can you send me an url video that show me how to use the transpose function? Thanks a loot :)
Ronilda Shkalla
Hello.
I will try and find a good video for you.
You need to remember that the other (missing) side of the matrix is a mirror image of what you can see. The diagonal values running from the top left position to the bottom right position in the matrix are the Variances of each asset (if this was a correlation matrix the values would be 1). All other values are covariances.
You can insert the 'missing' values manually and this may be a good exercise for you to understand how the matrix is constructed. The 'Transpose' function is simply a faster way to insert this missing information. My suggestion is to start small, say with only 3 assets/ securities and construct your Var/ Covar matrix to get the hang of it.
If I can't find anything of value I will send you a small spread sheet to take a look at.
Regards, Robert.
I am repeatedly told too few arguments when I type the formula at 2:29. Getting serious excel rage, could you please advise me on how to deal with it?
Hmm.. Are you sure you have typed the formula exactly the same way as I did in the video? Have you tried to see if the formula that I am using at 3:40 is working?
Hey, I am getting a formula error when I enter it this way: {=MMULT(TRANSPOSE(A1:B210-A211:B211);A1:B210-A211:B211)/210}. Could you please let me know what's the mistake. Thanks.
Hi Gautam .... You can't write it like that .... You've to write the formula without {} and then press ctrl+shift+enter
Super clear!
that's great thanks
but i have solved covariance variance matrix for 10 stocks in two ways : first- by mmult and second- by covariance.s and I've got different answers
would you please show to solve by covariance.s
also, even if you wrote 3 functions giving same results, why only used mmult?
I have 4 industry returns data for 13 years and somehow the Var Covar matrix is generated only for the first 4 years and not the the rest, even though the whole area 4 by 13 is selected. Do you have any idea what may be wrong? The error I get for Year 5 to 13 is N/A in the cells of the matrix, formula as follows: =MMULT(TRANSPOSE(K4:N16-K17:N17),(K4:N16-K17:N17)/12)
ok, I got it! Var Covar matrix will be between industries, so I only have to select 4 x 4 area - and it generates values for those :)
Thanks. I used both methods (step-by-step and the shorter one at the end), but I'm getting different results. I checked the formula over and over. Btw, I have predefined expected values (not average). What's wrong?
n is the number of periods/observations
I think you have an error in your data. The Variance for Hydro should be 0.0035 (0.35%) instead of 0.00035. The same may apply to the other variance and covariance values.
what is the n, the weeks or the assets?
n = weeks
Could you please set link to download excels ready built ?
Getting an error with the following formula. Any idea why?
=MMULT(TRANSPOSE(J4:P63-S4-Y4);J4:P63-S4:Y4)/59
Try =MMULT(TRANSPOSE(J4:P63-S4:Y4);J4:P63-S4:Y4)/59
Seems like you have a mistake with the S4:Y4 inside the transpose parentheses.
Thank you!
the video is really helpful, but can u tell me what is wrong with my formula: =MMULT(TRANSPOSE(C68:J126)*(C68:J126))
it always gives me error. your feedback is highly appreciated
you dont put star but this ; and divide it by n-1
thanks, nice jobs bro
*Excellent video* TᕼᗩᑎK YOᑌ
_Have u done any videos recently?_
Hello and
Thank you :)
thanks, it helps.
Thank u sooooooooooooooooooo much
thank you!