Portfolio Optimization in Excel.mp4

hello there so I want to show you how you can actually calculate portfolio expected return and portfolio standard deviation and then I want to demonstrate to you how you can actually use the solver function in Excel to either maximize your return in your portfolio given a set level of standard deviation that you're comfortable with or how you can minimize the standard deviation if you have a specific level of expected returns that you're trying to achieve then you can also maximize the Sharpe ratio which would be the ratio of expected return to standard deviation but the first thing that I need to show you is how you can actually use matrix algebra in Excel in order to calculate portfolio expected return and portfolio standard deviation now just as a brief reminder let me take you back to what you saw on the powerpoints earlier and that would be this right here the portfolio expected return is going to be w transpose mu where w is your vector your K by 1 vector of the weights in your portfolio and mu is the K by 1 vector of the expected returns to each of the assets and so if we want to get expected return we need a weights vector we need an expected return of a vector and we need to take W transpose mu so let's come over here and do that what I've done is I've went and collected some data on a handful of assets just to try to demonstrate this to you by no means is this intended to be comprehensive but I think it will be informative and interesting so what I've done is I've grabbed eight different assets this sp500 exchange-traded fund the spider fund MD Y which is the SP mid-cap exchange-traded fund SL Y which is the S&P small cap exchange-traded fund so you've got large-cap mid-cap small cap equities US equities so I wouldn't also got the ephah minus the u.s. so that's going to be Europe Far East and Australia so that's basically you know developed countries - America and then I also want gotten emerging markets ETF so these are basically all equity exchange-traded funds covering large-cap us mid-cap us small-cap us developed countries excluding the US and then emerging markets countries this is a US Treasurys etf so that's US government bonds this is a corporate government bonds u.s. corporate bonds and then this is a gold ETF so really the only thing that I've left out would be maybe US municipal bonds and then also global sovereign debt and global corporate debt but again this isn't meant to be comprehensive just want to demonstrate how you can do this but it's it's fairly broad ranging in terms of asset classes that we have represented so what I did is I went got the last sixty months worth of data I calculated the average monthly return these different asset classes and you can see that's the average monthly return to each of the asset classes no surprise I'm doing this in January of 2012 Gold has been on a tear over the last five years and then sp500 has really struggled and so of most of the global equity indices to be honest with you Treasuries have done well corporate bonds have done okay ever since the recovery coming on the heels of the financial collapse I've also calculated the variance covariance matrix which I'll show you at some other point in time how to do that but just take it as a given that this is the variance covariance matrix so along the diagonal I have the variances of these assets and then on the off diagonals I have the covariances so what we have is we have this is our mu vector and this is our Sigma matrix so only other thing that we need is our weight vector and if we have the weight vector then we can dive right into and we can calculate the portfolio expected return and standard deviation keep in mind that what we're doing assumes that the returns that we've observed over the past five years are indicative of the returns we expect to earn over the next five years so just a big assumption going into it now that we've said that let's do it let's go ahead and calculate our expected return to our portfolio I'm going to start off with a portfolio that's just equally weighted so I've got one eighth of my portfolio in each of these assets this is going to be a check figure for me just to make sure that I've done my weights correctly so the weights have to add up to a hundred percent right okay so what we want to do right here to get portfolio expected return is we want to just do w transpose mu so here's our W and we're going to transpose it I'm going to multiply it by mu over here and that should give us our expected return so M mult matrix multiplication we're going to transpose the W vector we're going to multiply it against mu always always always when you're doing matrix multiplication in Excel you hold down control shift and enter and it spits out the result now to get our portfolio standard deviation what we need to do is we need to take W transpose multiplied it by Sigma and then multiply that by W so that's coming from our PowerPoint slide as well you can see over on our PowerPoint slide that our portfolio standard deviation is w transpose Sigma W and we need to take the square root of that so we'll do that over here all right here we go we need to take the square root of matrix multiply and what are we going to multiply well first off it's going to be the transpose of that times Sigma okay so now think of that think of that matrix multiply as its own vector and we need to multiply that by W again so I'm going to come back inside here and we're going to do M mult and so we're going to take remember this right here on the on the interior that represents W transpose W that needs to be matrix multiplied against W naught transpose this time close up all the parentheses I should say and I think I did one too many hold down ctrl shift enter and it should spit out the portfolio standard deviation and you can see we get a portfolio standard deviation of four point four six two percent then we can calculate a Sharpe ratio portfolio expected return over standard deviation technically speaking we should subtract out the risk-free rate in the numerator but it's not a big deal we'll just call this the Sharpe ratio even though we haven't taken into consideration the risk-free rate so there's our Sharpe ratio not really all that impressive but now what we can start to do is we can start to manipulate things a little bit and the first thing that I want to do is I want to say okay if I look over here I see that the smallest standard deviation so the single asset in our portfolio with the smallest standard deviation is this corporate bond etf it's got a monthly standard deviation of two point eight five eight percent so what I'm interested to know is could we construct a portfolio that has a standard deviation equal to or less than that but that gets us an average return higher than the monthly average return of 0.539 percent so in other words what we're going to say is could we hit this minimum standard deviation but somehow get a higher expected return than what this asset by itself is doing in order to do that first off I'm just going to actually slide those weights over to begin with don't worry those will change I'm going to slide this formula over as well as just to check figure for us it's also going to be used as a constraint and then these formulas can almost be slid over but I need to lock in some things specifically I need to lock in the C column which is my MU column remember you still got a hold down control shift when you hit enter now I can copy and paste that over and then over in this formula which is the standard deviation I need to lock in my Sigma matrix and my Sigma matrix is this N through you control shift enter I can copy and paste that over a copy and paste that over okay so now what we're going to do is we're going to go into the solver function and again what I want to accomplish is I want to try to max out my return but keep the standard deviation at or below this number which happens to be the lowest standard deviation of all the assets in our portfolio so I go to the solver function and the target cell and for those of you they didn't see the way that you get to the solver function is you go up to the data the data tab at the top and you click on solver the target cell what we want to do is we want to max out our expected return so we click on the expected return and we tell it to max it out by changing all of the weights but we have to give it some constraints the first constraint is that the sum of all of the weights has to equal 1 or 100% so when we add up all the weights in our portfolio we have to have a hundred percent the next constraint is our portfolio standard deviation has to be less than or equal to this two point eight five eight which again is the lowest standard deviation of any of the single individual assets in our portfolio so we click on that and then I'm going to hit the solver button and we'll probably get a an interesting solution to this and then we'll probably come back in and add another constraint okay so let's take a look at what we get here for the answer and you can see here are the weights that Excel has spit out for us you can see they add up to a hundred percent so we have a hundred percent of our portfolio invested and it gives us an expected return of one point three one to seven percent and it gives us a standard deviation of two point eight five eight percent so this is really quite extraordinary this is the power of portfolios and diversification what do I mean by that well over here you know we look at all these standard deviations and clearly one asset have much lower standard deviation than any of the others and that was this corporate bond etf however it's expected return really wasn't that impressed I mean wasn't bad but it wasn't through the roof so by putting together a portfolio we're able to come up with same standard deviation as that asset so you know we've achieved this really low level of risk and volatility but look at the expected return that we're now achieving just by diversifying our portfolio with these optimal weights but what I wanted so that's powerful and impressive and useful and look you know here's the Sharpe ratios to all of the individual assets over here look at the Sharpe ratio that we're able to achieve by employing these optimal weights so man we blow all of these Sharpe ratios out of the water there's no single asset in our portfolio that can come close to competing with the Sharpe ratio that we generate over here notice however we are short selling in this portfolio specifically we are short selling this Aoife exchange-traded fund remember that's the Europe Far East and Australia exchange-traded fund so it's basically developed countries - the US and what the optimal weights are telling us is we should basically short sell 100% of our portfolio in that in in that particular exchange-traded fund so in other words these ideal weights optimal weights are saying whatever the value of your portfolio is go short sell exactly that amount and then you're doubling down and everything else is essentially what it's telling you well you might be able to do that it's possible but there are also a lot of situations where you won't be able to short sell or you might not be able to short sell that magnitude so another thing you can do is you can go back into the solver and you can say well let me just add a constraint and the next constraint is I'm going to say let's make it so that all these weights actually have to be greater than or equal to zero so in that case we're not going to allow any short-selling so now same constraints except we've added the constraint of no short selling you saw that you're gonna get a much different solution here okay so all right now look at the solution he's very very different it's telling you to put 0% in quite a few of these assets and it's telling you to put the lion's share of your money into this corporate bond portfolio which makes perfect sense because we're trying to get this low standard deviation it's telling you to put another fair chunk into the gold and then into the Treasuries with just a little bit into the small cap ETF with US stocks if you do that you still hit this standard deviation of two point eight five eight percent but now your expected return has gone down Pittard pretty considerably now this is still demonstrating the power of portfolios because this corporate bond ETF is only getting 0.539 percent return on average per month however the portfolio is getting point eight four nine five percent which is quite a significant jump and you can see in the Sharpe ratio we're certainly beating the corporate bond ETF and we're also beating gold which happens to have the highest Sharpe ratio of any of the assets in our portfolio so this still demonstrating the power of portfolio optimization and diversification we're still getting a much higher Sharpe ratio than we could with any of the individual assets but certainly if you can't shorts both the expected return and the Sharpe ratio is taking a hit okay next thing I want to accomplish maybe instead of wanting to minimize the variance in our portfolio what we're wanting to do try to get this high return well you're going to struggle to get anything higher than one point four six two percent but what we can do is we can see alright if we want to hit one point four six two percent if you tried to do it without a portfolio if you try to do it without diversification then you would just invest in gold and on average you get this one point four six two percent per month but you'd be bearing standard deviation or a risk of six percent basically per month so we're interested to know could we get that kind of expected return but somehow lower our risk so I'm just going to copy and paste this over just to get us started just like I did last time now I'm going to go back into the solver I'm going to reset everything so now our target cell is actually going to be our standard deviation we don't want to maximize it we want to minimize it by changing again we're going to change the weights and let's add in our constraints so the first constraint is some of the portfolio weights has to equal one our next constraint is going to be that now we want our expected return that number to be equal to or greater than this number right here which is the average return per month that Gold's been achieving I'm also going to put a short-selling constraint on it well you know what let's do it without the short-selling constraint at first and see what happens so go ahead and click solve see what kind of output we get and you can see that once again I mean who there is the power of diversification and portfolio optimization we're still hitting this one point four six two percent average monthly return but look how much we've lowered the standard deviation we've almost cut it in half if you were going to try to do this just with gold you're bearing a six percent standard deviation per month that's the risk through the portfolio down to 3% risk gives you a massive Sharpe ratio but we kind of get a similar result to what we got earlier which was it's telling you got a short sell evil like a madman and again you might be able to do that you might not be able to do it you might not want to do it you know mutual funds and certain other investment pools can't short sell and so it's useful to know that something that you can go in and you can establish one more constraint so let's add the no short selling constraints so we're going to say that all the weights have to be greater than or equal to zero throw that in there solve it and see what we get now and we get a solution that frankly is not all that interesting you get a solution that says all right invest all your money in gold except put just a little bit in this TLT and that keeps you at the one point for 62% return but it slightly reduces your standard deviation so you can see that if you put in the no short-selling constraint it definitely changes the outcome that you get all right let's do one more thing let's just maximize the Sharpe ratio let's just say what could we do what could what could we do in terms of portfolio optimization to just get the highest Sharpe ratio possible so we're going to go back into the solver I'm going to reset everything click OK and now what I'm trying to do is I'm trying to maximize that Sharpe ratio I'm just going to max it out and we're going to do it by changing all of the portfolio weights here and the only constraint I'm going to put in is they got to add up to 100 so all the portfolio weights have to equal 1 click OK I'm going to solve it we might get a crazy solution that's ok if we do let's just see what happens ok so we actually get something relatively similar to what we got back in this column when we didn't put the short-selling constraint in there and you can see that the outcome we get is simply you want to load up on on the corporate bond etf you want to load up on gold you want to load up on the emerging markets and the Treasuries load up on the SP large-cap and mid-cap and you just want to short I like crazy the ephah let's go back in and if you do that you get a Sharpe ratio of 0.46 which is almost twice as high as anything you can get with an individual asset let's go in add the short-selling constraint see what happens so I'm going to highlight all of our portfolio weights and we're going to say that they have to be greater than or equal to zero click OK and we're going to solve it and see what we get and looks like if you want to max this out without short-selling that what it tells you to do is basically put 35% in gold 38% in the corporate bond ETF and another 24 25 percent in US Treasuries and a little bit into u.s. small cap stocks if you do that you have average return of 0.9 to 72 and a standard deviation of 3.0 eight six and a Sharpe ratio of 0.3 so I just want to reiterate and reinforce af-- ocation diversification is going to allow you to achieve a much higher Sharpe ratio then you could get by using any of the individual assets by themselves you can get there one of two ways mostly the way that you get there is by men are minimizing the standard deviation of your portfolio given a certain level of expected returns you can see we're getting 0.9 to 72 percent here on average per month over here that's probably the closest so you can see we're actually getting a higher average return than that asset with a lower risk and so there it is portfolio optimization optimization in Excel

Channel: Colby Wright

Views: 306,811

Rating: 4.9186354 out of 5

Keywords: Portfolio, optimization, matrix, algebra, in, Excel, intermediate, finance

Id: FZyAXP4syD8

Length: 19min 22sec (1162 seconds)

Published: Wed Jan 11 2012