Portfolio Optimization using Excel Solver

so compute the monthly returns so I'll show you how to do that once you've done this this is the rest is mechanical you drag and drop yeah computing monthly returns is not that difficult average it turns mechanical will do that compute the variance covariance matrix four of these returns now you start here guys this is the key point here this is loop you come back to number four five six in in point seven this is a repetitive process now for the first case you start picking with pick two companies then compete the expected return standard deviation and portfolio beta for the minimum variance portfolio now this part is just part that I showed on the video as well calculating the core portfolio variance in standard division with two stocks three stocks for stocks the same procedure it's just number of stocks increase nothing change now you have to find minimum variance portfolio using solver so this is obviously everyone knows it is everyone aware of that yes I'll do that don't worry I need to just go through this guy because it seems like students aren't using this guide in the right way so then do not assume short sale of the asset is not allowed that implies you have to create a condition in so we're making sure that the there is no negative allocation they could be possible the negative allocation so which I'll show you when I show you it will become clear for now let's assume there is no short sale short sale means you actually don't buy this value actually borrow the stone right then in the portfolio weights you get a negative value that negative value implies a short sale of the stock that's technical term but we'll see and you repeat these steps including more stocks each time three four five up to 30 stocks but you just look goes on and on then this part this one so once you finish with number sorry once you finish calculating the standard deviation and mean of the portfolio with two stocks you add the third stock and repeat part five point five six seven eight there they're out there you calculate the media but do we have to say no you don't write them you put them on a curve to see if the total eristic line what I am saying is I'll show you in a minute I think this is where the confusion comes for you pick the first two you add sequentially the remaining you don't remove anything from the portfolio you keep adding that's fine if you pick the two then picked it in between them anything one by one yeah yeah yes you have to pick random - yeah you bet you first make the random two choice and then one by one at remaining one by one so you have a portfolio of twenty-nine basic 29 portfolios yeah out of 30 stocks you have 29 portfolios yeah because you start with two stocks not one as a portfolio so you have now 29 different standard deviations which come on the y-axis and in the x-axis you have numbers showing the portfolio size and you draw the total risk and see if the total risk really declined exponentially as the theory says so if the test of the theory that you're doing basically portfolio analysis so we will do that in a minute now once you finish that so there are other things to look at but we'll probably come back to this later now and then you is the part a this is the second part you have to create this efficient using two portfolios which I will again provide further videos on this okay this last part is the easiest the difficult part here in the last part is theory part you just need to explain what efficient said whether they fizzle easy you think easy that's good it says writing part takes time than this now I see where the confusion okay thank you alright let's go and do this is what I downloaded previously this is date sorry I'll have to change them to date now not time 8 long date okay short date yeah the usual thing yeah you you download one by one now this is footsie 100 the market return and these are stocks real waterfall market space is filled with energy and so on now we need this footsie 100 is if we are calculating beta if we are picking the beta from available calculations already from you yeah couple you know when you go to Yahoo you can copy the beta from there do you all know that beta is already there yeah some of the may not have then you calculate so that's why you need footsie so I downloaded that as well and these are individual companies now these are prices notice that it's upside down the latest price is at the top and at the bottom is the earliest so we just need to do reversing yeah sorting this which is again usual thing data set it by date and my lease as it is so that's fine yeah it's all recorded yeah next I'll just copy this for convenience you you do it the way it's good for you actually I shouldn't have highlighted all of it one hundred and seven data points isn't it so we will need that later copy this appears to be weekly yeah yeah doesn't matter its weekly or monthly you can you can that you should get you should get the same is up one it's enough as long as you justify frequency yeah the same yeah constant time period for all through this and PETA if you're talking about beta it's usually S&P 500 as a market return everyone use market Elizabeth yeah choose from the same now you can have any any others but then do you have anything to justify that yeah you might want to diversify your portfolio is another thing yeah but but this is it the the question of market return which is the best 10 is it Nasdaq if you choose Nasdaq some NASDAQ stocks and you choose not that some of them yet it's just how do you decide yeah how do you decide which one to use I mean if the one use if it is simply 500 index as a marketing to know do you want use none that has a market return so I mean NASDAQ stocks usually beta of now that stocks will be relative to the Nasdaq movement while the beta for the same pitch looks are relative to the year so these different you get different battles then but I think it should matter for that for someone in practicing this thing but just for consistency just keep it and you have 500 different con companies basically coming right so returns are there now now we will use this returns to estimate the expected return which is usually as we said historical return iya historical return the reason we use the averages is that we don't have expected return anymore yeah because the expected return something comes in the future what we can do is just use the past returns as an expected return so that is average return yeah so I'll just say expected return e-r yeah er whoops it put the trademark thing for some reason so this is the expected return and that is usually the average of all this yeah so average so these are my expected returns so in the future I expect to earn about 0.2% if I invested in you now if I invest in Tootsie and this is also expected returns of past returns are proxies Patino expected return now I need to find excess returns or deviations I should say deviation could be easy because you might confuse it with the excess return like that risk this premium now each of these minus the average and I'll just put the dollar sign in front of the the number sign here so that the row is fixed the rest can vary it would be easier for me now now it's trouble here I don't know up to which point to come so I need to just have some in timeframe here copy tastes right so I still have time to go so these are deviations from the mean return these are deviations from the mean return next the difficult part here is the variance and covariance calculation so this is what we're doing basically for all of them now so we just calculate the deviation so if you remember I'll just draw it to give you intuition so keep an eye on this so it's not a deviation formula with that it's the sum of the deviations right I just calculated I just calculated here this whole bunch is just the thing inside this yeah Xin - me that's what I just done if you got the formula you see xib to 1/8 minus the x-bar fixed it with the dollar sign yeah now I need to square the men that sum them up next yeah it's the variance covariance matrix comes out that goes next so you need to understand the intuition of what you're doing that's that's why I'm writing recording this I mean I could give you all these things and jump in to just mechanics but what's the point better to understand what you're doing so next thing is to calculate the sum of the squared deviations now and I call this X bar sorry X Prime and X X is being the deviations because we have large matrix K is better to use the matrix algebra right mmm you LT means matrix multiplication the formula mm um so I will transpose returns of all deviations um and multiplied by themselves itself basically all this transpose I do the prime here implies if you've done the calculus linear algebra class in the past prime basically says transpose yeah you're multiplying matrix by itself but you need to make sure that there is conformity of the matrix dimensions yeah 3 by 2 2 by 3 so the inner inner dimension should match that's what I am doing here by transposing so if I have if I have 3 by 2 matrix like 3 rows and 2 columns to calculate this match it by itself I have to transpose this matrix so that the outer part will become in the part with inner planet becomes the outer dimension in I because this should be confirmed this would be suitable yeah otherwise 3 by 2 make it fight 3 by 2 matrix multiplication multiplication doesn't work yeah that's that's gonna be a linear algebra class so that's exactly what I did here I am multiplying that matrix by transposing the voltages first so that I get it and also could we get some more things are we - you have you think you are you want to go to the condition time I mainly feel free to eat 40 ml before feedback are you ok I have checked the today's one then though until until 4:00 I was checking but nothing up there yeah feel free to email and see how it goes so this is by the way when it comes to the multiplication guys this thing here has to be done with ctrl alt shift ctrl shift enter I think not sorry ctrl shift on toe I'm just pressing the ctrl shift end but I'm saying out as well so control shift enter because it's array function apparently we need to use the data arrays and matrix formulas it has to be control shift enter not just enter right so what to say combination of combination to complete the computation or something like that yeah so it's there so however what's the result here now this is a huge matrix now it becomes a square matrix I'll have to kind of do the thing what you call this the dimensions now change to the number of companies so we have how many rows here how many columns 1 2 3 4 5 6 7 8 9 10 11 year so I have to create or highlight the area which can combine 11 columns and alone rows it becomes a square matrix because notice what is really that we have sorry when you have a 3 by 2 matrix multiply it by 3 by 2 matrix transpose you end up with 3 by 3 matrices so you have a square matrix that's similar to this one we have 20 matrix because we want to find it ours will be 11 by 11 matrix yeah clip will be taken out of the inner dimension disappeared or communication all the dimensions become the new matrix dimensions so next thing we have to do is now highlight the area that covers 11 columns and 11 rows so this would be 30 to 40 42 that should be 11 okay thank you now once you've done this you click in there in here in here right in here and control shift enter done so we just done the numerator of the formula there next the denominator so that becomes the poufy add the denominator becomes the variance-covariance matrix I'll just type kovar this is what investment bank is to in investment banks when they manage portfolios except that much of it is now done by coding not not in Excel because it's easier you have pre-written code algorithm we just enter the stocks and we spit out all this it's like a black box but if you know the intuition now when you go to work you know that what's doing what this computer doing is just in your mind you understand it yeah you don't treat it as a black box like like some unknown stuff it's not it's very easy right next thing so we've just done this part now all you need to do is divide by n minus 1 divided by n minus 1 then that will be this divided by n minus 1 so n is the number of observations that we have the returns so I will just use count the function that counts the number of observations of the area that I covered so I'll just cover this column I will let it count it Oh and -1 now I'm missing the bracket that's done now that's it now all I need to do is just recreate this book done down so it's 45 55 yeah okay this would be now covariance covariance matrix and as a result what I can do is I'll just copy these guys names because I'm gonna use this it's my it's my kind of label these names are labels for me basically you will see what I mean in a minute control V and write them so I will just do the same thing here paste values transpose control dance so this is covariance matrix all I need is it now because this footsie is not going to be in my portfolio all I need is this area this is the area I will be focusing mostly on this is the variance for variance covariance matrix between the shares this first row and first column should be excluded in my case yeah because I want to show you how to calculate the betas as well that's why I included since you don't have them all Polly will city will be this yellow area only focusing on the shares here only shade is highlighted area will not be included in you again next now we have variance covariance matrix we can now calculate the portfolio return and variance for this 11 sorry 10 stocks here sorry 11th is the food see so I'll start with two stocks on little just to follow the guide remember the card yeah we've done this guy's we are right here compute the average turns done it and compute variance covariance matrix so we are three be at three be done it now point four now pick two companies from the set yeah now I need to pick two companies I will just put pick the two first to your choice no I just highlighted this this will be the first two stocks here so I will type here not too angry name two stocks only two stocks so which ones are they real and Vodafone so copy and if I need this I have the average on this side so let me have a look I could use this no problem all I need is not variance covariance matrix apparently in this case so let me create this now portfolio weights weights so we need to define that portfolio weights and I highlight this to a little copy control next thing I'll just type here some and here I will insert a formula some of whatever appears right under the weights should be appearing right here so I will impose a restriction on this at some point you will see that next since we have all of these why don't we now calculate the standard deviation and the stock solid portfolio return for this company two companies the portfolio right so let's create this expected return of the portfolio at P o it you could put anything else and a deviation and I'm sorry this is variance because we will then get the standard deviation next let's do it here what is the expected return in portfolio guys do you remember the formula for expected return in portfolio analysis hmm expected return is basically okay next blue intuitive way than button that's very time let's do so this time we've just done and created all the matrices now we need to get the expected return of the portfolio which is weight of the first portfolio is to stop times the return of the first stock last weight of the second times in terms that's it we just reduce the matrix for them that's the only difference here we'll use the matrix algebra and for that we will use it will be using matrix multiplication so far we've been dealing with matrix yeah so now this would involve two by one because two stocks yeah two point one matrix times one and if you notice if you notice my average returns are already two pi one sorry they are one by two and the weights here are 1 by 2 yeah the weights that would appear right here will be one column by but two rows yeah the other one is what's that one row two columns yeah so I do not have to transpose what I mean is this if I go back if I go back you see this is this part here this part this part this two is basically this is the return of the first company this is the average return of the second company just highlight that OMA and come back and this should be multiplied by whatever appears right here after the optimization so change I don't actually need it has the yeah so control shift enter again keep it as it is it will be valued as it's a normal thing because we don't know the weights but let's say find in input forty percent in there and sixty percent in here I get my average return yeah if I enter it but what I am here trying to do is I want solver to decide how much weight I should put in Rio and how much weight I should put in water phone yeah for now I will delete them for that reason so this value is not an error it's correct next this one is a bit tricky so here now we need to calculate the variance covariance matrix here we multiply the transpose of the variance covariance by the weights and times it by the variance covariance matrix for the second time so mmm ult mmm evil t now this would be let me have a look transpose of the variance covariance matrix and you have to remember that we only have two stocks in this case guys look at this look at these they're two stocks here that's why we have only got this tiny bit Grillo and waterphone Rio and gotough on here comma not in this one should be right here comma then another matrix multiplication this is now the multiplication of the weights these ones times the variance covariance matrix we'll go back and highlight this again I guess I have all brackets right so - yeah control-shift-n - its value now the standard deviation is the square root of all this yeah they transfer human did these are covariance matrix the last part of your publishing the page because your two stocks is this is just the weight so control-shift-n to come next okay leave the next one there next guys it's the optimization part so because we want to have the highest return for that you know for a given minimum risk you have to use solver to get this minimize so what solver does now is look at the intuition here it's not difficult solar now tries to randomly enter various weights to see what minimizes this but maximizes this oops for some reason my the forma didn't work here so do I have anything wrong here so two here and two by two there so it's 2 by 1 and that is two by two please mistake there that's why he should have should have appeared let me just double-check your eye color so it's C 346 D 347 C and D done there and stocks correctly so what what what's not working here Abby 18 yeah one one quick clarification here guys this should have been this part here the transposed part is the weights do these two weights and this one in fact should be the variance and this would be the weights let's go take a look I swapped orders that's why the matrix didn't confirm the inner products didn't match in in the dimensions didn't match yeah it should work now so what solver does now is it randomly enters the different combination of weights and eventually it chooses the one that gives you the highest return for a given risk so it keeps calculating until it finds it so this could be something like 0.001 and this could be something like 0.9999 yeah and it gives you eventually this return but it basically does this in repeated order so I'm gonna delete this and leave it like this now go to data I don't have the solver now for somebody to disappear yeah I added it actually several times remember the mostly that here is well how to add it but it keeps going away disappearing so I have to go back now again and raise the options with lose it preferences tools tools add-ins yeah that's one it's just the Mac version complicated this is the one okay next now the objective is to minimize this so just click mean by changing by changing these and what I want is a restriction we will add that this number this number that comes right here exactly we can have equal to 1 but small or equal to that's fine it usually goes to the equals anyway so okay now that's done click solve and see if it what what it does it just with brute force adds different numbers into this two stocks and ends up giving you the best value so it's done keeps all the solution ok Wow it did do anything for some reason fun enough let me do this let me extend this and I don't know why it did this where's the yeah there's nothing it's actually producing 0 so let me see my data solver is nothing wrong with the let's change it let's do it equal to 1 and that's why I just put 0 so that means your portfolio is not useful at all it's not efficient but let's see if it works this way yeah it should have worked actually without a problem but here now it looks like portfolio let me do the standard deviation st if you had today sorry its square root isn't it and why did i do why did I do a standard do it because with one number you cannot get the standard deviation with just one period this doesn't vary right so guys so somewhere decided that this combination of weights this combination of investment this combination of investment gives us the minimum variance for a given region now this is one you do the next one by adding to this the third stop the same exercise continue doing this now you have a choice here either you add two stops at a time or one stop it if you want to escape accelerated processing at a later time so then you end up having 15 portfolios not 29 and then throw the standard deviation against against your portfolio size so once you calculate this you create a new worksheet where you tabulate to spot three-spoke sorry two four six thirty stock portfolios against standard deviation of each portfolio so in this case to stop that even as standard deviation of this goes on then create a table of sorry graph where this is your standard deviation and this is your X the number okay and you could see something like can you also upload this excel file yeah yes because ya know I will upload this yeah now that's to stop the third stop is similar what changes is that you just add the at the third stop here and next you just cover this 3 yeah used to be this 2 next you could cover the 3 next you just cover the four of them until you come to the 30 stock this is tends to publicity but that's repetitive nothing difficult is it and well promise you this is my own experience when I learnt it when I looked at the trade is doing this is just they're not doing anything they are not that smart as they promote themselves to be they are doing things like what's in the book all is being done is algorithm they entered the stock returns they get the standard deviation they get and then they realize that this combination will be best so we'll just you know spend I mean put the money in in these proportions that's it that's all that they're doing portfolio managers but there's nothing more than that the only thing is that you have to be strong because you is their case of Marcus declining and increasing and you will be under stress that's why you get paid well probably not for the knowledge but being able to withstand all the pressures from the market movements just sometimes you really get stressful now next thing if those who are calculating betas that you don't have data right for example then you just come to the part where you calculated the returns this is this one here this one here return these are returns now the beta for the first company so just let's create these guys let's create betas and let's bring the bring the they hear these guys all I need is now a statistical input here that's it that's basically slope function slope now I need to choose my why I need to choose my X and all I will do here is the standard simple equation this powerful function one that is just estimates this regression this is what we're looking at the slope gives us this paper which is the this is the stock return this is the market return and this measures the sensitivity of to the market return yeah that's the intuition behind this function so slope so I just need to highlight the Y and remember I have the Y in the first case yeah first row so I'll just highlight that coma and highlight the X you can then compare it what you the one that you copied from the website that's the beta yeah I use the food so did I what did I do now let me go back let me go back and see so be was footsie yes because that's a market in my you're using wise thank you thank you I described one thing and did one thing many thanks sorry guys may the brain malfunction badly that's the better sorry I was surprised to see a very low beta yeah and now guys once you do that all I you should to do is highlight this highlight this effort really for dollar Sonya and just just to disguise so all the betas are there you don't have to come up with anything just first one the rest will be done because you see this is the next talk isn't it yes babe I don't remember ba systems or some other is ba t our boss because I downloaded a long time ago remember this is this is from 2005 so probably and always do one when I did my Master's portfolio beta is now wait these weeks yeah now the portfolio to stock portfolio here to stop portfolio is basically the these betas times the weights so you just it's again Matt matrix multiplication isn't it mmm ult oops my double click is working bad it's so is is basically now we have to have row by column multiplication 2 by 1 1 by 2 sorry stop and I should have it so we will do so this times this and go back control shift enter no so that again it didn't cut home why do I choose the beta twice I did it choose sorry thank you two stocks it's this one isn't it yeah so I have to be very careful now and then come back yeah done it without me trying to do anything so anyway control shift enter that's the guys this is the portfolio beta for to sauce times the weights so it's this one here Beto 420 is it was the first of time to debate what this was the second four times a week so you know don't look I wanted to learn what the series said look I want you to realize what I wanted to just remember this I'm not doing this for you know taking the time I want you to tell me if this thing this is data isn't it this is the systematic risk but this part disappears I want you to see if this is serious working here in the end we have 30 stop paper you need to tell me if as you increase as you increase the stocks beta goes down on that series says it doesn't prove the favor you know for you to understand what you doing oh this is gives that you can think of that way as well now the standard deviation that we have there will tell you where that this function is is correct or the verify that please explode like this exponential declines at lab now the papers tell you as they as described goes to increasing to 30 Stokes will be beta remain the same why can we get you know if I do the three stock paper portfolio data will be what will is a very near one one one one for the same number okay there will be some variation ignore it but I want you to understand this I mean this is the gist of doing finance this is the aim of this module if you look at the aim says to understand more than portfolio theory and applications I mean this this exam is not it's not so see if you can memorize it and come back and say okay I got a but what's the point when you go to you know work somewhere else you need to be confident this whole thing should be valuable to you not just a paper a OBO see it should be something that you enjoy quick marks till your chances of passing the interview first interview but when you do intuitive than reasonable thinking that's what guarantees you a job yeah obviously marks also guarantee but if you understand it you certainly get the high marks any questions on this so all of it is done the last part is creating this efficient frontier which I will leave to the later discussion because you just finish this in the next of course with you in the room present if I just upload the videos I don't know how it goes this file will go yeah yeah you can check the formulas for example I would say if use this as a template yeah use this as a template all you need to do here is drag and then you type is do not need to learn how to drag it because you might mess up if you drag and without understanding so study properly right you get you down within an hour and then all you need to do is just copy and paste copy and paste 30 stocks here it's all dragging away it's the first time well thank you for also mutt ranking is it a dragon is there anything complicated no so I'm gonna save this and upload this I'll actually say I just need to push the button now first thing is I haven't received much in many emails that I expected for her use only a few it's just you need to email me if you don't understand anything rather than going and trying to do it and taking your time you are getting frustrated email me up I'll upload it on blackboard next week we start with a share valuation then bond valuation and then peaches

Channel: LondonPhD

Views: 12,511

Rating: 4.878788 out of 5

Keywords: efficient portfolio, portfolio frontier, efficient frontier, variance-covariance matrix, optimal portfolio, Excel

Id: c2M1AZk5ATg

Length: 51min 5sec (3065 seconds)

Published: Sun Nov 13 2016