Tutorial: Constructing Efficient Frontier using Markowitz model

hello everyone this is the tutorial on constructing the portfolios Asian frontier using the workouts model the template for for constructing the efficient frontier is available on website in a tab Markovitz the problem is already outlined and some of the inputs have already been entered and some of the formulas have already been presented in this template as well all you have to do is fill out the green shaded areas for the annualized expected returns and the annualized variance covariance matrix in here the steps to arrive to the efficient frontier are outlined to your right so the first step we have to make sure that the formulas are correct and the expected returns and the variance commute matrix are in annualized forms so you can see that here we'll put our percentage expected returns and here will be our annualized variance covariance matrix our variance terms are simply the weighted sum product of the weights determined for each stock which will be determined by the software times all the covariances of stock one but every other asset if you look at the second term similarly it's the weighted sum product of the weights for each stock by each of its covariance terms with all other assets the variance of the portfolio is simply the sum of the variance terms and the standard deviation of the portfolio is the square root of the variance of the portfolios for the return terms it's a little bit easier it's just the weighted expected return for each of the stock with the return of the portfolio equals to the sum of these return terms essentially the return on the portfolio is the weighted average of its individual returns where the weights are determined for each of the asset the portfolio so our next step is to generate the expected returns and to generate the various securities matrix the data for this example I'm going to use the data that I already have I have five stocks the data runs from 4th of January 2011 and until March 2014 these are the daily return data so first thing I will calculate my average daily returns and to do so I'm going to use the formula average in Excel and select the stocks notice that we don't need to use the market index for the markets optimization model so this is my average daily return I can simply copy the formula reference for all other stocks now the requirement is that we have the annualized returns so my annualized returns simply going to be the daily return times the annual ization factor and I'm going to use 250 as the number of trading days in a particular year on average these are my annual average returns for the five stocks you can see that over the period 2011 to 2014 on average every year each of my 5 stocks have given me quite nice returns the lowest was 14% and the highest was 18.7% note that my data starts from 2011 the reason for that is that I could obviously get a longer time series of my data but I wanted to avoid the years 2007 2008 2009 partially 2010 as the years mark but the global financial crisis and the consequence of that crisis the volatility in the markets was quite high and I do not believe that this excess volatility will be evident in the forthcoming years so this is the reason why I excluded the years from my analysis when constructing the historical averages for the markets these average returns could now be copied into my Markowitz model setup um sorry okay I can actually make them in that percentage form with a few decimal points this is a much better representation of it now we are off to constructing variance covariance matrix and the quickest way to construct the variance covariance matrix is through the use of the data analysis plug-in and there's a option for the covariance clicking okay and for the input range I just input all of my columns with the returns for all of my five stocks that I'm analyzing you could include the labels if you wish but if you do include the labels make sure that you click on the labels in the first row in your data since my selection does not contain the labels I'm going to exclude them for now the output well the output will be just below my average returns that I had beginning so I'm going to output it here click OK this is by variance covariance matrix again this matrix is in its daily form first thing I have to do is to make sure that this data is converted to its annual presentation again I'm using a factor of 250 number of trading days in any gear on average and I'm going to copy it for all of my stocks that I'm considering now you can see that this matrix is not complete as there are some missing elements in the upper right corner so I have to fill out these values and since the variance matrix is symmetric matrix I'm simply going to make sure that I'm copy the correct numbers as I go along okay so the matrix below the selection that I've you see on your screens is the annualized variance covariance matrix I'm going to copy this matrix into my variance covariance template values only you actually don't need to see all the numbers here so we can reduce the amount of zeros that we observe by making it six or four decimals whatever you prefer now we are actually ready to run solver for the first time and get our first point on the efficient frontier I'll let me make it a little bit smaller so I can see the graph perhaps move the graph a little bit up I guess the first point that could be plotted is well sorry let me just go in the solver and explain what is going on so the mark of its model a set up sets the objective and my objective is the cell I 14 which refers to the variance of the portfolio you can as easily set the objective equals to the I 15 which is the standard deviation of the portfolio it wouldn't make any difference the objective is the variance and I would like to minimize the variance by changing the cells b3 f3 if you look at the b3 f3 these are my weights so the software will alternate and attempt to try different numbers in this particular range of cells which are the weights of the assets in the portfolio and come up with the solution we will give you the minimum value in I 14 which is the variance of the portfolio I didn't post some additional constraints my first constraint is I 16 equals to J 16 the reason for that constraint is that the return on the portfolio that turns out to be will have to be equal to the target return that I wanted to be so in order to trace the efficient frontier I will have to set a return to 16 15 14 13 and so forth and get the portfolio for that level of return with the minimum variance so this is why we have that particular constraint so as you can see we'll be changing the target quite often in the range from the minimum return that we have which is 14 point 16 percent to the maximum return on the asset which is eighteen point 77 percent so these are the limits of what we can get in the portfolio constructed without short sales based on the five stocks of this return collector sticks my other constraints are that weights cannot be greater than one and weights cannot be negative so essentially this constraint limits me from short sales and my last constraint is h3 which is the total or the sum of all the ways the assets in the portfolio have to sum up to a hundred percent these are the only constraint that I make so before running solver let me set the target first I'll set the target to the lowest return available among all of my stocks the answer that I would expect solver to give me would be hundred percent in the stock 5 which has 14.6% that will be the lowest point on the efficient frontier so I will go back to solver run this problem now and the spotter says that it found a solution and all constraints and optimality conditions are satisfied I will keep the solution I agree with solver that this is a correct solution press ok and as you can see that the answer that we got is actually what we expected in the beginning all other stocks will be 0 except for the stock 5 which we would invest hundred percent it has expected return of 14 point 16 percent and thus 100% of that stock equals to the 14.6% for the target portfolio that we achieved I've already created a template for you that you can simply copy and paste special into the output and your first point appears on the graph already so I will try the point with the highest return eighteen point seven again the solution that I expect from the solver is to have 100% invested in stock four and zero percent and every other step running solver with exactly the same set of constraints clicking on solve an extra little solution can be established and the solution is as expected 100% invested in stock for I paste special and I got my highest point so this is the point with standard deviation nineteen point seven nine an expected return of eighteen point seven seven where we invest in hundred percent of stock for this is the one where we have hundred percent stock five so what you can do now you have to populate your graph with the intermediate points I guess the next point we can try is seventeen percent because it looks like it's somewhere in the middle between these two points so I'm going to put seventeen percent go into the solver run the solver get the solution copy my solution into sure so we got an intermediate point so now we can find intermediate point between this intermediate point and the lowest one and it looks like it's going to be fifteen and a half percent so I'm going to change my target to 15.5% run the solver again to get the point the optimal or efficient particular case I got the solution copy solution and paste special values only and got another point here we're going to keep repeating this and change the target every time we need a new point for example it looks like there's a lot of empty space here so we would want to have a point somewhere here around 18% so let's see what happens when we set the target for the portfolio equals to the 18% solver found solution we copy the output percentage breakdown and statistics without portfolio in our plotting area and we have achieved that particular point I could try point sixteen point three and it looks like between these two so let's try sixteen point three go to data solver around the solution take the output from this solver run paste it into our spreadsheet and we got another point so all you have to do is you you continue to populate your graph with more points and once you have a sufficient number of points perhaps 15 or 20 points you will be able to perform further analysis

Channel: Vitali Alexeev

Views: 83,588

Rating: 4.9235668 out of 5

Keywords: Investment Analysis, Economics of Equity Markets, Financial Markets, Equity Markets, Portfolio Construction, Portfolio Efficient Frontier, Harry Markowitz (Academic)

Id: JRDWD6DE59k

Length: 15min 48sec (948 seconds)

Published: Sun Mar 01 2015