In this module, I'm going to show you how to construct portfolios in Excel, how do you compute random portfolios, their returns, their volatilities and to plot the efficient frontier corresponding to the data in one of the worksheets. And what are we going to do is set up the optimization problem that we talked about in the theoretical modules and show you how you can compute the efficient frontiers, and the efficient portfolios on these frontiers using a solver optimization program. In this particular spreadsheet, I'm going to have eight different asset classes: US Bonds, International Bonds, US Large Cap Growth, US Large Cap Value, US Small Cap Growth, Small Cap Value, International Developed Equities and International Emerging Equities. And the mean returns for these different assets are 3.5, 1.75, -6.39 and so on. And these are all in percentages. This matrix down here is the variance-covariance matrix. So the variance of US Bond with itself is 0.001. So this quantity is the variance. The volatility of the bonds is defined as the square root of this quantity and the variance times 100. So it's 3.17%. The covariance between US Bonds and International Bond is 0.0013. The variance of International Bonds is 0.0073. Written in percentages, the volatility is 8.53%. So all I've done here is I've taken the diagonal quantity, took the square root of it and multiplied by 100. I'm going to use this spreadsheet to randomly generate portfolios and then try to find out what the returns and variances or returns and volatilities of that portfolios are going to be. And as we walk through this mean variance optimization module, I'm also going to tell you how to compute the efficient frontier, how do you compute efficient frontier when there is a risk free rate and so on. So here's just placeholders for the portfolios x1 through x8. And each of these portfolios' values are currently being generated from a random distribution. So this is randomly generated. And since x1 through x8 is a portfolio, all I've done is I've taken x8 and written that to be one minus the sum of x1 through x7. That just guarantees to me that this entire thing sums to one, and this quantity here is just a double check. It's summing all the components from x1 through x8, and it's indeed equal to one for the random value that has been generated here. What is the rate of return on this portfolio? It's simply the sum product of all the components here multiplied by the mean return over there. Mean returns are in percentages. The portfolios here are just in straight numbers, and therefore, the number that you end up getting here is actually rate of return in percentages. What about volatility? Volatility of the portfolio is simply 100 times the square root of the variance of the portfolio. How do you compute the variance of the portfolio? You have to take this. It's going to be Sigma_IJ, which is the covariance of asset I with asset J, times x_I times x_J, summed over IJ going from 1 through 8. Take the square root of that and multiply by 100. Now, a mathematically concise way of doing this, and we'll see this later on in the modules, is to take this vector X, take its transpose, multiply it to the covariance matrix and then left multiply with the same quantity. So if you look at this, what I'm doing is I'm taking this vector, I'm taking its transpose and multiplying it to the matrix which is the variance-covariance matrix. If I do that multiplication, I get a vector which is a column vector. I multiply it again by row vector. I get a number, which is the variance of the portfolio. I take its square root and multiply it by 100. And I end up getting the volatility. So that's how the volatility numbers are computed. So what I want to show you over here, all I'm doing in this particular case is randomly generating portfolios. So if I do have nine, a new portfolio comes up there. It has an associated return value, associated volatility value. Again, here's another sample, volatility return. I'm going to do it one more to hope to get a positive return note. There. We get a positive return of 13.67%, but the volatility associated with that is 41.23%. So these volatility numbers and the random returns was randomly generated by creating a portfolio, looking at what the random return is, looking at what the volatility is, and all I did was put them in increasing order and plotted these red dots here. Now, what I want to show you is how I computed this efficient frontier. The frontier which tells me what is the maximum possible return for a given value of volatility. In the module on mean variance analysis, we said that the frontier can be computed in three different ways: either by maximizing the return for a given value of volatility or risk, or minimizing volatility for a given value of return, or maximizing the risk adjusted return. In this particular case, our volatility numbers are given. And for each value of volatility that are randomly generated, I want to compute the maximum possible return. And I'm going to show you now in Excel how to compute that using solver. So we're going to go back to the data sheet. Here's the data sheet. And remember, I showed you that this cell here B22, this particular cell actually has the value of the mean return for a particular portfolio, randomly generated portfolios. Here is the volatility for the given portfolio. And what I want to do is make sure that this volatility number is less than sum budget that I'm going to specify in this orange cell. And I also want to make sure that the quantities that I choose for x1 through x8 is actually a portfolio, which means that if I sum them up, which is what is the numbers in this cell, it must equal one. So orange cells are data that I'm going to provide. This one here simply says that it's a portfolio. This 85.94 is a risk budget that has been given a particular random risk budget. So let's clear this for the moment, and let's try to compute what is going to happen. So I'm just going to clear the contents. Don't worry about this value here because it's zeros, it doesn't like it. And I'm going to show you how to set up an optimization problem to solve it. So here's solver. I'm going to set an objective. I want to maximize the cell B22, which is the net return in percentage. I want to maximize that quantity by changing the portfolio values. These are numbers B20 through I20, x1 through x8. And I want a constraint which says that the volatility number that I'm computing in cell B24 must be less than equal to the budget that I'm going to specify in cell D24, which is what this constraint down here is. The next constraint, J20 equal to L20, simply says that J20, which is the sum must equal one, which is the number that I'm going to specify over there. This is not the constraints. The values involved here are nonlinear. Remember, the volatility is a square divided by square root, and so I'm going to choose an optimization algorithm which is GRG nonlinear, solve and wait for the answer. So it's cranking numbers. It's going up. You say okay. You ended up getting that the maximum possible return that you could get for a risk budget of 85.94 is 58.25. And in order to receive this value, 58.25, this is the fraction that you need to invest, 5.02. You are to take a leverage of five times up there. Short International Bonds, shorts US Large Cap Value, short US Small Cap Value and so on for this particular set of data. So like I said, I don't want you to think that these are representative numbers. These are just some numbers taken from a paper. And if you look at this particular data set, the optimum return for that particular risk level, let me go back, 85.94. The randomly generated bond was -8.73. The best possible return is 58.25, which is exactly what we just computed. So next thing I want to show is that in many cases, you might want to just have long positions, not short positions and go to the solver. And here I'm going to just click 'make unconstrained variables non-negative.' For those of you who are watching this, I want you to pause this for a moment and think to yourself whether the number that I'm going to get, the maximum return that I'm going to get at this point is going to be larger than 58.25 or smaller. And the way I want you to think about is that the constraints, I'll put more constraints and what should that do to the maximum value. So let's solve it and see what you end up getting. So the maximum possible return that you can get if you decide to give yourself a budget of 85.94, and you say that the only thing that you can do is go long on all the assets, is you're going to go to the US Bonds and put all your money there. You're not allowed to short so you cannot increase the investment in US bonds, and you just take the return of that which is 3.15% and take the volatility which is 3.17%. So it's within the budget, but you were not able to use all the budget. So you can only use the budget if you're allowed to have short positions. Okay.
