Construct the Efficient Frontier

Construction a. Estimation The goal is to obtain the tender ingredients expected returns, standard diversions and correlations. Historical data ar used for this purpose. As a rule of thumb, volt years of daily data atomic number 18 probably right (one year should be the absolute minimum). accompaniment in mind the following 1) make sure to use the ad notwithstandinged close prices to calculate returns (so that you wint have large, spurious negative returns due to dividend payments or splits), and 2) calculate log returns (so that you john pith daily returns to obtain holding period returns, if of all time withdrawed).In Excel, the function for mean and standard deviation argon = average (range) and stdev(range). To calculate the correlation intercellular substance, 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 historic returns ar negative or below the risk-free appreciate. 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 tone 1. Variance/co difference matrix, The expected return and var. for the portfolio are You can think of the version as the freighted average of all the covariances, ? i? j? ij where the burdens 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 commencement exercise 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 b1 0.. 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. Portfolios 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 apparently 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/covariance column, and then a ll 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 portfolios variance, and taking square root of it, we have its standard deviation, in cell b45. The portfolios return in b44 is careful as the weighted average of individual security returns.The slope of the CML is simply the rise (i. e. , portfolios return minus the risk-free rate) over run (i. e. , the portfolios std). Step 3. Obtain minimum variance portfolio minimize STD plain 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 ever-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 portfolios 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 maximise Slope subject to sum of weight = 1. 0 Follow the same logic/procedure as in Step 3, remove 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 maximize cell b46, except that, aside from the weights-summing-to-one constraint, you would add six more constraints a34 gt 0, a 35 gt 0, , a39 gt 0. It turns out that, the weights on Securities 2 and 3 are zero, since they manipulate the most amount of short selling in the unconstrained case (Step 4).However, it is not always genuine 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 in effect(p) 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 flummox some points (50 in my example) within the reasonable range of returns and std.