Creating efficient frontiers using excel.
Suppose we have 3 risky assets whose net return has the mean vector and variancecovariance matrix given below:
Asset Mean VarianceCovariance Matrix
1 2 3 0.06 0.12 0.03 1 0.3 0.3 0.3 1 0.3 0.3 0.3 1
Ones Mean Portfolio Return
1 1 1 0.176666122
Portfolio Portfolio Portfolio Variance STD Constraint
2.42961 1.558721 1
0.079372 1.603166 -0.68254
To model the portfolio choice problem, I begin by highlighting the mean vector and giving it a name. To do this, left-click on cell c9 and drag down until cell c11 and then release. Then go to the name-box, which is the white box in the upper right just above the "A" column. Click in the name-box, hit backspace, and then type a name for cells c9 - c11. Then hit return. I used the name "mu" for the vector of mean returns as illustrated below:
Then, I follow a similar approach with the variance-covariance matrix by clicking on cell F9 and then dragging across and down to cell H11. After the variance covariance matrix is highlighted, I go to the name box and give the variance covariance matrix the name "vcov" (Note: I don’t use quotes in the names). The efficient frontier consists of portfolios that only invest in the risky assets. Therefore, I introduce a vector that represents the portfolio weights in each asset. For now, I will
assign the weights arbitrarily. Below, I will use excel to choose the weights optimally. For now, I have placed the weights in cells J9 through J11 and given them the name weights. Also, for convenience, I have created a column of ones and given it the name ones. To illustrate why the names are convenient, note that for given portfolio weights, the mean return on the risky asset portfolio is equal to the transpose of the weights vector multiplied by the mean vector. Using excel's matrix formula's, the transpose of the weights vector is given by "transpose(weights)", and to multiply "transpose(weights)" by the mean vector "mu" simply requires using the excel function mmult, which stands for matrix multiplication. The resulting mean return for the portfolio is given by mmult(transpose(weights),ones). In this expression, excel multiplies the transpose of weights by the vector of ones, producing the mean return on the portfolio. To program the mean return and store it in a cell, one uses the excel format for matrix formulas. For example, to store the mean return for the given weight vector in cell N9, click on cell N9, and then type "= mmult(transpose(weights),mu)" and then hit CTRL SHIFT ENTER. The quantity in the cell will be equal to the mean return and will change when the weights change or when the elements of the mean return vector change. To program the variance of the portfolio return, I use the fact that the variance of the portfolio return is the transpose of the weights vector multiplied by variance covariance matrix multiplied by the weights vector. To program this, first I multiply the transpose of of weights vector times the variance-covariance matrix. This produces the expression mmult(transpose(weights),vcov). Then, I have to multiply this expression by weights. Therefore, the final answer for portfolio variance is mmult(mmult(transpose(weights),vcov),weights). I have typed this expression into cell O9 using the same approach that I used for typing the mean return. Clicking on the cell will highlight the formula at the top of the excel spreadsheet. By changing the weights or changing the elements of the variance covariance matrix, the portfolio variance will change. The standard deviation of the portfolio return is just the square-root of the variance, and is given in cell P9. To compute portfolio std, I named portfolio variance portvar. The formula for portfolio std (standard deviation) is just portvar^.5. It is entered in cell P9 with an equal sign before the formula. Then you hit return to enter the formula.
We are almost ready to start to use excel to compute the...
Please join StudyMode to read the full document