Construct the Efficient Frontier

Construction a. Estimation The goal is to obtain the raw ingredients – expected returns, standard deviations and correlations. Historical data are used for this purpose. As a rule of thumb, five years of daily data are probably right (one year should be the absolute minimum). Keep in mind the following: 1) make sure to use the adjusted close prices to calculate returns (so that you won’t have large, spurious negative returns due to dividend payments or splits), and 2) calculate log returns (so that you can aggregate daily returns to obtain holding period returns, if ever needed).In Excel, the function for mean and standard deviation are â€Å"= average (range)† and â€Å"stdev(range). † To calculate the correlation matrix, use â€Å"correlation† under â€Å"data analysis. † Please note, in practice, the estimates can be adjusted in view of economic outlooks. This is especially so for expected returns. Sometimes, the realized historical return s are negative or below the risk-free rate. They must be adjusted upward – who would ever buy a stock and expect to receive a return less than the risk-free rate (if the beta is not negative)!? II.Efficient frontier construction Step 1. Variance/covariance matrix, The expected return and variance for the portfolio are: You can think of the variance as the â€Å"weighted average† of all the covariances, ? i? j? ij where the weights are xi and xj. Of course, the variance terms are special cases of the covariances when i=j, and ? ij=1. You can calculate the portfolio variance in the spreadsheet in many different ways. The way I do it is to first calculate the variance/covariance matrix, whose entries are ? i? j? ij and ? i2. To this end, we first construct the tandard deviation (std) matrix and the correlation matrix, as shown in the spreadsheet. Then, first multiple the std matrix to the correlation matrix to obtain (multiply the range of b3.. g8 to the range of b10.. g15). Then, multiple matrix to the std matrix again (multiply the range of b17.. g22 to the range of b3.. g8) to obtain the variance/covariance matrix in b24.. g29. Step 2. Portfolio's return, variance, standard deviation and slope To obtain the portfolio variance, we need to further multiply each entry of the variance/covariance matrix by their corresponding weights, xi and xj.Remember, those n portfolio weights are what we are trying to solve for. So we put them in a column (a34.. a39). To facilitate the calculations, I also place the weights at the top of the matrix. The variance/covariance matrix is simply copied from Step 1. Since we will also need the security returns to calculate the portfolio return, they are placed in j33.. j39. Now, we multiply the weights to each column of the variance/covariance matrix using the function â€Å"=sumproduct. † This â€Å"sumproduct† results in each weight in (a34.. 39) being multiplied to each entry in the variance/covarian ce column, and then all summed up. The variance/covariance terms will have only one weight being multiplied to. So we need to multiply this sum by another weight at the top of the matrix (remember: multiplying the sum by something is equivalent to multiplying each individual item by the same thing). Summing all the items in b40.. g40, we obtain the portfolio’s variance, and taking square root of it, we have its standard deviation, in cell b45. The portfolio’s return in b44 is calculated as the weighted average of individual security returns.The slope of the CML is simply the rise (i. e. , portfolio’s return minus the risk-free rate) over run (i. e. , the portfolio’s std). Step 3. Obtain minimum variance portfolio: minimize STD subject to sum of weight = 1. 0 The minimum variance portfolio is the one that has the lowest variance among all possible portfolios. We use the â€Å"Solver† in Excel to find this portfolio. We would like to vary the weights in a34.. a39 so that the variance (or equivalently, std in cell b45) is minimized. In the â€Å"Solver,† enter b45 as the target, and choose â€Å"min. The range for â€Å"Changing cells† should be a34.. a39. The only constraint is: all the weights sum to one, i. e. , set cell b42 equal to 1. 0. Then simply click on â€Å"solve. † The solutions will be in a34.. a39. Of course, the portfolio’s return and std are simultaneously calculated in cells b44 and b45, and the slope linking the portfolio and the T-bill is in cell b46. Step 4. Obtain market portfolio: maximize Slope subject to sum of weight = 1. 0 Follow the same logic/procedure as in Step 3, except that you want to maximize cell b46. Step 5.Obtain market portfolio with no short selling: maximize Slope subject to sum of weights = 1. 0 and all weight being positive This part is just for completeness: to show you how to construct the market portfolio when short selling is prohibited. Here you also ma ximize cell b46, except that, aside from the weights-summing-to-one constraint, you would add six more constraints: a34 ;gt; 0, a35 ;gt; 0, †¦, a39 ;gt; 0. It turns out that, the weights on Securities 2 and 3 are zero, since they command the most amount of short selling in the unconstrained case (Step 4).However, it is not always true that any security that is being shorted in the unconstrained case will have a weight of zero in the constrained case. Security 5 is a case in point. Step 6. Generating efficient frontier Here, everything is already self-explanatory. Essentially, we need to plot the parabola and the CML. To this end, we first get the functions for each, and then use Excel to generate some points (50 in my example) within the reasonable range of returns and std.