How to Easily Calculate Portfolio Variance for Multiple Securities in Excel

[@aliassaid6398]

You are a great teacher. Just by watching this video once I am able to replicate it to my own portfolio. Keep up the great content. Thank you!

[@MattMacarty]

Thank you. Glad it helped.

[@hendrickkone679]

Thank you, this is the quickest way of using Excel to calculate volatility of a portfolio of stocks.

[@MattMacarty]

Glad it helped

[@sjampatbeesie7982]

Great Video! Thank you. I was wondering if another solution would be to add a formula to the sheet with stock performances , will calculate the total actual $ win or loss for that day and then calculate the volatility of those outcomes

[@MattMacarty]

Glad it helped. I think you would end up normalizing for dollar amount and end up back at the same place, but sure you can try that

[@sebastienmarseille6720]

Thank you!

[@jkmsolutions]

so helpful!

[@MattMacarty]

Glad it helped.

[@aaronsarmiento9595]

I'm going to get my degree in cis and finance and your videos are OMG so helpful thx :)

[@MattMacarty]

Thanks. Glad it's helping

[@jamesliu790]

Is this exactly what i needed. Bless

[@MattMacarty]

Glad it helped

[@danishhussain7127]

Hello Matt, Just one question, why did you multiply by 252 days while calculating portfolio SD because you has already done that when calculating Individual security SD?

[@MattMacarty]

The first measure is the variance, not SD

[@niekvogel]

Hi Matt, thank you for this clear and helpful video! I have 2 questions you can hopefully help me out with: 1. Can you please explain why you use instantaneous returns and not simple daily returns ((p1/p0)-1)? 2. And how would you calculate the annual average returns? I ask because I would like to compare annual average returns and volatility for different portfolios (by changing the weights). Hope to hear from you, thank you!

[@MattMacarty]

This is typically how finance applications formulate returns. it has the benefit of being summable. The standard percent change over estimates returns. I would use instantaneous for annualized returns too.

[@TempterPS]

Thank you for the video. Please show how did you got the numbers on the Data tab?

[@MattMacarty]

I downloaded historical data and then calculated instantaneous rate of return

[@TempterPS]

@@MattMacarty thank you. Do you have a lolink where to get it?

[@davidbellman7743]

What if there are a few short stocks in the portfolio? I presume cant have negative weights...but need to add to 1?

[@MattMacarty]

Yes that's right you can have negative weights, but if technically you can exceed 100% when using shorts. In the two security version you can see how I handled.

[@glluga1991]

Many thanks Matt.. Really help me a lot in calculating portfolio volatility with 11 stocks. Could you please also making video to calculate sharpe, jensen, and treynor ratio with portfolio that is consist with more than 3 stocks?

[@MattMacarty]

I have a portfolio optimization tutorial with Sharpe ratio: kzbin.info/www/bejne/qXqYiWWhf6aSas0

[@kingant84]

Hi Matt, thanks for video. at 6:29 minute of the video, for the mmult excel formula, do you have to mutiply by "Square Root 252" to annualise it? instead of just by 252? Since it is volatility parameters?

[@MattMacarty]

For the variance you do not use the square root.

[@kingant84]

Thank you ​@@MattMacarty

[@Camodicko]

Hi Matt, how would I go about finding the correlation coefficient between two portfolios with 7+ assets between them, given that I have annual return data? Thanks for your help.

[@MattMacarty]

You mean you want to find the correlation between assets in the different portfolios? You would probably want monthly return data or even daily. See kzbin.info/www/bejne/eqrah52JnNB0iJY

[@shonitsinghal]

That is sooo helpful keep up the good work sir. Thank you

[@MattMacarty]

Thanks. Glad it helped.

[@Matthias1399]

Thanks for the video, but I have one question: why do use the sqrt() function in the end in order to get the variance?

[@MattMacarty]

So I start by calculating the variance, then I convert to Std Dev for volatility

[@bananabill2390]

Hi Matt, following up on this, is there a reason you used that combined function to calculate stdev instead of the stdev.s function?

[@Koozi17]

Can anyone explain what is the 252 being multiplied at that end is?

[@MattMacarty]

This is the typical number of trading days in a year. Since I am calculating daily values this is needed to annualize the statistic.

[@jatot1-ljl]

Hi Matt, how does the formula change when accounting for monthly returns rather than daily returns? Thanks

[@MattMacarty]

Hi. The formula isn't really differnt, just the volatility calculation will change. Id you have monthly data you can take the variance and multiply by 12 (or st dev * sqrt(12))

[@huanchungkhoan]

Thank you for the wonderful video. I just wonder if it is possible to graph the multiple securities and the capital market line together like you would do with two securities?

[@MattMacarty]

You can do this, but not very simply so I left it out to keep the video shorter. As you might expect, the shape of the graph is pretty much the same. I will take a look at making a dedicated video.

[@shes.an.angler]

Matt, thanks for posting the video! I'm a beginner finance student. How did you calculate the instantaneous rate of return for the securities?

[@MattMacarty]

Glad it helped. It's =LN(today / yesterday)

[@jonathancreative5779]

Hi Matt, I was wondering how I could create the correlation matrix if i manually input the standard deviations that i've put into a column. The reason im doing it this way is because some assets have longer historical data i could pull from, while others do not.

[@MattMacarty]

So you will want to correlate data of the same dimensions since it's is essentially a measurement of two variables to move together. You really need pairwise data to calculate.

[@kimyungyap1968]

Thanks for ur sharing! Can I just remove the sqrt() if I want to calculate variance rather than volatility?

[@MattMacarty]

Glad it helped. Yes, leave out sqrt for variance.

[@imnitin97]

Hi , I have a doubt about the calculation, The covar. Between SPY and SPY should be 1 but why we have such a less number in the calculation

[@MattMacarty]

The COVAR of SPY vs SPY is the variance

[@yanghong2437]

It is so helpful and easily understands! Thank you

[@MattMacarty]

Glad it helped

[@danielcoplon9437]

Thanks for the great video, Matt! I see that you're using VAR.S to get volatility (appropriate since we're dealing with a sample of actual returns). However, on the Variance-Covariance Matrix, Excel uses VAR.P and COVAR.P by default. Does this throw off the end result of portfolio volatility when you run the MMULT on the matrix?

[@MattMacarty]

Yeah sadly, Excel is not very consistent here. Not sure why they did that but yes there will be a difference if we are able to correct for sample bias. It shouldn't be very large with the sample size we are using.

[@moreno3461]

bravo! the majority of websites and channesl stop the analysis to two or three stocks!

[@MattMacarty]

Thanks. Glad it helped. Yes that's because it's too difficult to deal with all the covariances unless you use matrix multiplication, no one want to do that by hand ;).

[@teo4078]

Really thank you! I have a question, if I need daily volatility, I just do not need to multiply the 252, is it?

[@teo4078]

When i calculate volatility in the ways in this video for 2 stock, kzbin.info/www/bejne/fqvPpWuGrpKNnaM,the volatility is different, where is the wrong?

[@MattMacarty]

We have daily volatility and I convert to an annual number.

[@MattMacarty]

@@teo4078 On a guess, I would say that the covariance of the stocks is not being accounted for.

[@teo4078]

@@MattMacarty ok, thank you!!

[@zechengfu6308]

Thank you so much for your video, that's nice. Could you tell me how to calculate a portfolio's VaR including US treasury bill and where could I find the historical price of that bill> Looking forward your reply

[@MattMacarty]

Try this: kzbin.info/www/bejne/fqvPpWuGrpKNnaM and kzbin.info/www/bejne/sKPUemCEl7-GoJI

[@zechengfu6308]

@@MattMacarty thanks!

[@zechengfu6308]

@@MattMacarty another question: DO I have to use ln return to calculate the variance-covariance matrix?\

[@JJMPH]

Does this calculate only the expected volatility or can this be used to calculate the realised volatilty of a time period as well? I calculated the standard deviations for multi asset portfolios with this, but the portfolio with much higher value changes got a lover standard deviation than the other that was more stable. Was also wondering can this be used to calculate the historical volatility since the weights of the stocks in the portfolio change quite a bit during the 5 year period because of the change in their values. Glad if you answer :)

[@MattMacarty]

This is really historical volatility based on a year's worth of data. So I downloaded price data, calculated log(change) for each day and then used that to calulate an annual variance. These variances were used to calcuate a portfolio volatility. You can download the spreadsheet I used and just update it with more recent or different data.

[@JJMPH]

Thank you for your reply! How should i deal with a situation where I want to calculate the volatility that the portfolio has faced during the time spread, when the weights of the different stocks has changed due to value change? At the beginning the portfolio was equally weighted but due to value change, at the end some stocks were weighted with over 30% and some were with under 2%?

[@JJMPH]

If it helps to understand the situation better, I’m doing risk adjusted portfolio performance evaluation, researching how the portfolios have performed for example with sharpe ratio, where I compare the excess return of the portfolio with the standard deviation, in this case, should I calculate the standard deviation of the portfolio with this covariance matrix or just with se standard deviation of the portfolio returns?

[@Flyingcabbage101]

@@MattMacarty Hi! I wondering if tehre is dividend included , how should I calculate the return ? Thank you so much for your help

[@adilsharafudheen3787]

How to find annual volatility from a dataset of 5 year prices?

[@MattMacarty]

You can use a similar methodology, but not many applications for 5 year volatility. I think beta is based on 60 months in some settings, i.e. Yahoo Finance

[@aristotelispapamentzelopou8231]

Good stuff!

[@MattMacarty]

Glad it helped

[@tonyy5542]

Hi, can you calculate a 5-year volitility by multiplying 1266 instead of 252?

[@MattMacarty]

I think you would be better off simulating returns out five years with a shorter duration volatility like annual or monthly.

[@charliegriffin3491]

@@MattMacarty if you were calculating using 5yrs of monthly returns would you simply multiply by 60?

[@edwinlauleejun5936]

Hi Matt, is this model-building approach or historical simulation approach?

[@MattMacarty]

Hi. I am using the traditional matrix method of calculating a portfolio using historical returns.

[@techknowledgestuff]

how do i extend this to a portfolio in which the weights of individual components change over time due to further portfolio inflows during the holding period?

[@MattMacarty]

Probably the easiest way will be to add the new securities by adding rows, update the volatility measurements and run the optimization again. The amount of money doesn't really matter here since the result gives you percentage allocations.

[@markfoster9394]

Hi Matt, I am getting a negative portfolio variance due to negative covariance values. How do I fix that? Thanks!

[@MattMacarty]

Hi covariance can be negative. It just means the securities tend to move in opposite directions. It would be rare to find a portfolio of stocks with all negative covariance, but you might something like gold that has slightly negative covariance with pretty much everything.

[@markfoster9394]

@@MattMacarty Thanks for your reply. Yes, I have gold in the portfolio that is probably the reason why I'm getting a negative portfolio variance. How would you state the risk of the portfolio if you cannot get the standard deviation due to a negative variance? Sorry this is part of my homework and I am so confused how to state the risk as my sample portfolio has a negative variance.

[@nikhilchowdhary8919]

I wanted to know more about ur python course. How should i contact you? I am interested in buying

[@MattMacarty]

I think you can send me email from within KZbin. The course covers an overview of general Python concepts. It then goes through numpy pandas and matplotlib the main workhorses for data analysis. Then we cover most of the mathematical concepts you would get in a college level finance course. Technical analysis of stocks is covered and we build a basic trading system. The course wraps up with Monte Carlo simulation of a portfolio and value at risk. Let me know if you have other questions

[@nikhilchowdhary8919]

@@MattMacarty I am a buyside analyst.. do u recommend?

[@Shubharwal]

Thankyou very much for this

[@MattMacarty]

Glad it helped

[@willfricker6319]

Hi Matt I am working on a set of data spanning5 years. It's been broken down into months resulting in 60 data points. What would I multiply it by to calculate the volatility? Would it be 252 trading days x 5 years? Would really appreciate your help,

[@MattMacarty]

One way would be to take a 12 month st deviation and then multiply that by the square root of 12.

[@loicdesroches9441]

Why do you multiply everything by 252 (n) ?

[@MattMacarty]

There are 252 trading days in a typical year. The variance is daily so multiplying by 252 gives an annual variance. Some people may use 365 or 360 instead of 252.

[@Penner-x6l]

You could simplify a bit by using monthly returns and basically get the same result.

[@MattMacarty]

For sure

[@StockSpotlightPodcast]

I re-ran this using my own data with 3 securities (40% individual equity, 15% VTV, 45% SWPPX) and calculated weekly returns. Individual volatility was between 90-140% when I multiplied standard deviations of 3.7%, 2.6% and 2.5% by 36 (trading weeks per year). Then, when I included the covariances table my volatility went down to 17%. Does this make sense to you? It just seems like my individual volatility is way higher than yours and I'm not sure why, although it drops significantly when doing the additional calculation. I do know that all three securities are highly correlated (.75 or higher).

[@MattMacarty]

Hi, yes it sounds like something is off. Make sure you are calculating covariance on raw data. Not sure what data you are using for volatility but it could be something there too.

[@nikolaslamothe2580]

Hey Matt could you help me please? My formula doesn’t work instead I’m writting the same as yours =mmult(mmult(transpose(B3:B6),C13:F22),B3:B6) Thanks for all

[@MattMacarty]

Difficult to diagnose... Is there data in those cell ranges? Maybe start by downloading my spreadsheet from the link in the description and see if you can replicate from my workbook.

[@ericobenaus9466]

Isn't it incorrect to sqrt everything at that end since all calculations are made with SD? Using the last sqrt would give the sqrt of SD

[@MattMacarty]

No. I am using variance all the way through until I calculate the portfolio volatility (SD)

[@MrZectbumo]

Hello Matt. Great video! I tried to recreate the whole model to really understand the reasoning/calculations and it works! Thanks a ton. Only caveat: I am not able to reproduce the daily rate of return data (which should be simple...). For example using Yahoo Finance I calculate a daily return of 0.000652 for June 27th, quite different from 0.0035397. Would you mind describing the values you input to calculate that return? (I used Day opening and Day close)

[@MattMacarty]

Thanks. My calculation is an instantaneous return: =LN(today/yesterday)

[@mikehaddad5235]

he uses a logarithmic return (assumes continuous reinvestment i believe) - natural logarithm to be precise

[@hamsterman844]

You are awesome!

[@MattMacarty]

Glad it helped

[@spartakgrigoryev9975]

Thanks a lot!

[@MattMacarty]

Glad it helped

[@sibaodin]

Thank you sir

[@MattMacarty]

Glad it helped

[@narutachi_]

Matt, really, thank you! do you mind letting me know how should I choose the size of the (covariances and variances) matrix? with the formula you showed it only depicts size of the matrix when it comes to only two assets in portfolio (I am using python for this)

[@MattMacarty]

It really depends on the number of securities you are dealing with. You can visually see how I am doing it in Excel. In Python your best bet is to use numpy.dot or numpy.matmul in place of MMULT in Excel. It's actually easier in Python!

[@stocksforinvesting]

Nice

[@MattMacarty]

Glad it helped

[@anuragpandey1164]

Bhai ye wali Excel Sheet bhj de ( PLZ Share the Excel Sheet!!)

[@MattMacarty]

alphabench.com/data/excel-portfolio-variance.html

[@chencharlie7045]

why multiple 252?

[@MattMacarty]

This is the number of trading days in a year.

[@josuecc7843]

Shouldn’t it be * SQRT(252) ?

[@rubene.7511]

And the S&P Has in fact not settle down :( to this day

[@MattMacarty]

Thanks

[@carllosangeles9586]

anyone else get too many arguements?

[@MattMacarty]

This is likely a missing misplaced or extra parentheses in your formula.