Markowitz Portfolio Optimization

in this video I'm going to demonstrate how to calculate the optimal portfolio of risky assets using Excel and then also using Excel calculate the proportion of an individual investors portfolio that should be invested in the risky asset portfolio and the risk-free asset so we're going to do this using the Markowitz portfolio optimization model within that model there's a minimum variance frontier and that's the lowest possible variants that can be attained for any given level of expected return the global minimum variance portfolio is the portfolio of risky assets that has the lowest variance of all risky asset portfolios and finally the fishing frontier is the range of all investments that are within the minimum variance frontier and our above or higher have a higher return than the global minimum variance portfolio what you should consider is that we're going to look for the optimal portfolio which gives us the best risk return trade-off and that would lie along this minimum variance frontier within a set of a given risky asset in this video I'm going to look at four different stocks and we're going to combine those in the best weights to give us that that risk return trade-off that is the optimal outcome so the expected return for a portfolio is calculated as the weights of the assets within the portfolio multiplied by their expected returns the variance of a two asset portfolio and the two risky assets in this case are x and y so think of those as two different stocks it's calculated as the weight of x squared multiplied by the variance of X plus the weight of Y squared multiplied by the variance of y plus 2 times the weight of X multiplied by the weight of Y multiplied by the covariance of x and y or how they move together so if we generalize this out to more than two assets we come up with the following equation which is the sum of the sum of the weights multiplied together for each asset I and J and so what we have here is if if we have just X so it would be the weight of X multiplied by the weight of X so it gives us x squared multiplied by the variance of X which is the covariance of it of an asset a single asset is its variance and so this is the generalized equation for this specific equation when we take it to more than two assets after we move past a two asset portfolio it's necessary to use matrix multiplication to determining optimal asset weights in the portfolio so using a matrix multiplication notation this is what our equation would look like we have the weights of the portfolio of the assets of the portfolio transposed multiplied by their expected returns and you can see this in the document that accompanies this video and Excel the formula is going to look like this equals M emiltt so that stands for matrix multiplication transpose so we're going to transpose the column of weights multiplied by the column of expected returns when making calculations with arrays in Excel type in the formula but don't press ENTER instead hold down Shift + ctrl and holding those down press ENTER this tells Excel that you are making a calculation with an array otherwise you're going to get an error and then puts the curly parenthesis around the formula you'll see that in the equation bar in Excel the variance of the portfolio is calculated as the weights of each asset the portfolio transposed multiplied by s s is the variance covariance matrix and I'll demonstrate how to calculate the variance covariance matrix in Excel and then multiply it again by the weights of the assets in the portfolio the standard deviation of the portfolio is calculated as the square root of the variance of the portfolio and in matrix notation looks like this s is the variance covariance matrix as I mentioned before and our equation like this equals square root mm old mm old okay so we have two products here so that's where we're using 2 mm bolts and then we transpose the weight so we do that there so we're making this multiplication and then we're making this multiplication and then again we press control-shift hold those down press Enter to get the calculation for the portfolio variance so the optimal weights for assets in a portfolio are the ones that maximize the value of the Sharpe ratio for the portfolio the Sharpe ratio for the portfolio is the expected return for the portfolio minus the risk-free rate divided by the standard deviation of the portfolio and I'll demonstrate how to use solver in Excel to do this finally we want to say okay we've got this optimal portfolio of risky assets now we want to look at the individual investor and based on their level of risk aversion how much of their well should they put in that risky portfolio and how much should they set aside and put in a risk-free asset to do that we're going to use this equation which says that the proportion of the portfolio invested in the reciept portfolio y asterisk is equal to the expected return for the portfolio minus the risk-free rate divided by a so a is a measure of the investors risk aversion multiplied by the variance of the portfolio all right so now we're going to actually use the information that we went through the document to calculate first the returns and then for the individual assets and then we're going to calculate the variance covariance matrix and then the weights of an equally weighted portfolio with the expected return in standard deviation and then the optimal weights for the optimal risky portfolio and then show you the expected return the standard deviation and the Sharpe ratio of that portfolio and then finally we're going to finish up with calculating the optimal amount that a risk averse investor would put into the risky portfolio and the amount that they would put in the risk-free asset to make the complete portfolio so on this page I've given you the prices the scrim from these come from Yahoo Finance for for stocks Bank of America AT&T Westar Energy and Union Pacific to calculate the returns I'm just going to put in here equals and open parentheses the most recent stock price divided by the one before excuse me - the one before divided by the previous month stock price and that gives me two point three eight percent I can pull this across and it's going to complete the calculation for the rest of these and I can pull this down I don't want to go down to the last row because it just gives me an error term I'll show you that so error term we don't want that we're going to take that out so these are the monthly returns for each of these different individual stocks so the average monthly return I can calculate as equals to average I'm going to highlight the returns for Bank of America and then close parentheses so it looks like average highlight all the returns close parentheses again I can drag this across I get little plus sign here go across I'm going to do the same thing for variance here I'm going to put in equals var again I'm going to highlight these returns close parentheses and this gives us the sample variance if we wanted the population variance we would put VAR p for now though we just want to we're just looking at a sample of return so we're just going to use the sample variance the average annual return that is equal to that point 8 multiplied by 12 and the annual variance is equal to our monthly variance again multiplied by 12 I can grab these pull them across so as we see here AT&T has a much higher variance than anyone else and a lower return so not a terribly attractive investment at this point we're going to see though if they can add to the value of the portfolio by combining it with the other three stocks that we have here so I'm going to go up here and it says returns I don't want to type in the returns here I just want to set them equal to the annual return below so Bank of America was nine point nine one AT&T is equal to ten point four for Weststar is equal to fifteen point six seven then Union Pacific is equal to thirteen point nine nine at this point we're going to calculate the variance covariance matrix and it's easier to name our arrays so our arrays of prior returns for each stock so that we can put them in here easier so that's what I'm going to do now where we name our our cells or our arrays is up in this box where it currently says q1 so what I want to do here is I want to highlight all the Bank of America returns and I'm going to put in here BAC that's Bank of America's ticker symbol are 84 returned so now that array whenever I reference BAC ret I'm referencing that whole column the next one I'm going to use AT&T so I highlighted all of the percentages there I put ATT ret the next one for Weststar energy again I'm going to highlight all of their returns and I'll put it in Weststar return ret and finally I'm going to highlight Union Pacific their tip ten percent symbol is unp so I'm going to put in here unp ret that makes life a lot easier the reason why I didn't do that before was that if I had done that and I would have put average BAC ret here and then pulled it across it would have just pulled across it would have referenced the same array and would have just been point eight point eight point eight four for each one of those returns so we didn't want to do that yet so now I'm going to calculate the variance covariance matrix to do this I'm going to put in equals Kovar for covariance and in this case we're actually calculating the variance but just to be consistent we'll use the same command in each one kovar there's going to be BAC return BAC return and to get the annual covariance I'm going to multiply by 12 for the next cell over I'm going to put an equals Kovar BAC return ATT return multiplied by 12 for the next one I'm going to put in equals kovar BAC return Weststar return there we go and multiply that by 12 and finally I'm going to put in Kovar BAC return unp return multiplied by 12 so as you can see I took BAC or Bank of America bank of America Bank of America ATT Bank of America West star Bank of America Union Pacific to get this row the variance covariance matrix is symmetrical meaning that the covariance of Bank of America and AT&T is equal to the covariance of AT&T and Bank of America so I can just set this equal to the one up there set the Westar Energy and Bank of America to the same as Westar Energy in bank of America and then finally Bank of America and Union Pacific and then I just continue to do this for the rest of the variance covariance matrix so here is going to be the covariance the ATT return and the 8 t return multiplied by 12 this is the covariance of AT&T and Weststar energy again multiplied by 12 and this is the covariance of AT&T and Union Pacific and again I'm going to just kind of set these equal for the next row it's going to be the covariance of Westar Energy and Westar Energy multiplied by 12 the covariance of Westar Energy and Union Pacific multiplied by 12 and then finally it's the variance of Union Pacific so we're just going to put in cove our Union Pacific return unp return x 12 okay so now we have our variance covariance matrix and so now we can calculate the expected return and the standard deviation of returns for our first are equally weighted portfolio so our equally weighted portfolio our weights are going to be 0.25 0.25 0.25 0.25 and then I'm going to put in here equals the sum of those and it has to be equal to 1 so our expected return if we go back to the document that we went over earlier our expected return is going to be equal to the weights transposed so we get a row instead of a column multiplied by the expected returns all right so to calculate the expected return then I put in equals and then alt for matrix multiplication and then the word transpose because I want to turn my weights my column of weights here into a row of ways so I want to highlight these weights close parentheses comma highlight the returns close parentheses and here's where I'm going to hold down shift and ctrl so ctrl shift hold those down press Enter and you should get 12.5% the standard deviation is a little more complicated again taking back a look back at this document here I give you the command for it and it's equal square root FML FML because we're making two multiplications the first is the weights multiplied by the variance-covariance matrix the second multiplication is here the product of these two multiplied then by the weights again and then the square root is because we take the square root of that to get the standard deviation so to calculate the standard deviation of the portfolio I start with equals square root mm olt mm olds transpose open parenthesis here's where we put the weights in again because we're taking them from a column to a row close parenthesis then we're going to multiply those transpose weights by our variance covariance matrix close parentheses comma more then we're multiplying the product of these two again by the weights but we don't transpose them this time because we're going for that that one by one matrix that that's singular number at the end close parenthesis 2 close parenthesis then hold down control shift and enter and I get 12.5 2% for the standard deviation so our Sharpe ratio in this case would be equal to the expected return minus the risk-free rate divided by the standard deviation so I'll get 0.75 9 1 5 3 so now what I want to do is see if I can do better than that so I'm going to go down here to the optimal risky portfolio weights I'm going to start with an equally weighted portfolio so 0.25 0.25 0.25 0.25 equals the sum and that always have to has to equal to 1 in our portfolio so I'm going to calculate the expected return to the standard deviation in the same way make sure to use these weights not the ones above because we're going to change these weights in order to get our optimal weights for our portfolio so I have that information just like I did above control shift enter 12.5 it matches so I didn't do anything wrong there equals square root again mmm Alt mmm alt transpose transpose again use these weights not the ones above comma my variance covariance matrix close parentheses comma my weights again close parentheses you'll do again to close parentheses here control shift and enter 12.5 2 that matches so I'm happy then I want to calculate my Sharpe ratio because this is how I'm going to optimize my portfolio I'm going to maximize my Sharpe ratio so this is equal to my expected return - the the risk-free rate divided by the standard deviation is equal to 0.75 9 1 3 or seven five nine one five three just like up here and so now what I'm going to do is use solver so I'm going to press data up here I have solver if it is not installed on your excel here's how to do it go to file go to options go to add-ins so down here it says manage Excel add-ins I want to go to that and then you'll want to select solver add-in if it's not already selected so make sure there's a checkbox there again I'll walk through that I go to file options add-ins Excel add-ins down here at the bottom go and then select solver add-in once you do that if you select data go to solver it should pull this up it should be blank for you I'm going to reset it all to walk you through it so our objective is to maximize the Sharpe ratio so set objective the number for the Sharpe ratio right here to maximize make sure that max by changing what variables so we're looking for the optimal weight so we're going to select our weights and then we have to subject it to the constraint that the sum of these weights are equal to one so add the cell reference this is equal to one where's okay and then the next box down here this is important so make unconstrained variables non-negative our unconstrained variables are going to be our weights so by saying that they have to be non-negative we're saying we can't you know we've already said the sum of these has to be equal to one but now we're saying none of these can be less than zero so make sure that is selected press solve says found a solution or happy we got a solution here's what it looks like so like before I said when we looked at the expected return and the standard deviation that Bank of America wasn't very attractive at least it's not zero here it could be a zero weight it's very low we're saying we only want 3.8 percent of our risky portfolio to be in Bank of America the other ones are pretty evenly spread out west our energy is about half our portfolio based on its historic performance 18t and Union Pacific are twenty nine and nineteen percent each that gives us a much better Sharpe ratio we went from point seven five nine one five three to one point oh one six to eight seven so we improved our our risk return trade-off fairly dramatically by changing the weights in our portfolio finally we're looking at this from the point of view of the risk averse investor so we go down here we know that our proportion in the risky portfolio is equal to the expected return on the portfolio minus the risk-free rate divided by the level of risk aversion a multiplied by the square excuse me the variance of the portfolio so this is going to be equal to the expected return on the portfolio minus the risk-free rate open parenthesis our level of risk aversion multiplied by our standard deviation squared and the up caret here is if you press shift and the number six you get the up caret squaring it I don't have to hold down control shift in this case I just hit that so what this means is that 97 percent of our portfolio would be put in the risky asset or the risky portfolio and then 3 percent would be in our risk-free asset or our t-bill or money market fund if I change these numbers that proportion is going to change so if I go from a risk-free rate of 3 percent to say 6 percent the risk-free asset becomes more attractive we would put 30 percent in the risk-free 70% in our risky portfolio I hope that was helpful please let me know if you have any questions on how to calculate the risky portfolio or the the allocation of funds into the risky portfolio or the risk-free asset for our complete portfolio

Channel: Shane Van Dalsem

Views: 167,301

Rating: 4.9274611 out of 5

Keywords: Portfolio, investments, portfolio optimization, sharpe ratio

Id: CNIVd_b7YJc

Length: 25min 6sec (1506 seconds)

Published: Wed Feb 10 2016