Efficient Frontier Optimal
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
Weights
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"
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
Weights
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"