Construct the Efficient Frontier

Construction a. mind The goal is to concord the raw ingredients expected returns, standard deviations and correlation coefficients. Historical data argon used for this purpose. As a rule of thumb, five long time of daily data argon probably right (one year should be the absolute minimum). Keep in mind the following 1) make real to use the adjusted close prices to calculate returns (so that you wont strike large, spurious negative returns due to dividend payments or splits), and 2) calculate log returns (so that you basis aggregate daily returns to obtain holding period returns, if ever needed).In Excel, the authority 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 quite a little be adjusted in view of economic outlooks. This is especi all in ally so for expected returns. Sometimes, the realized historical returns are negative or benea th the risk-free rate. They must be adjusted upward who would ever profane a stock and expect to receive a return little than the risk-free rate (if the beta is not negative)? II.Efficient frontier construction feel 1. Variance/covariance matrix, The expected return and variance for the portfolio are You can mean of the variance as the cloged average of all the covariances, ? i? j? ij where the freights are xi and xj. Of course, the variance equipment casualty are special cuticles of the covariances when i=j, and ? ij=1. You can calculate the portfolio variance in the spreadsheet in many varied 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. thusly, first multiple the std matrix to the correlation matrix to obtain ( cypher the range of b3.. g8 to the range of b10.. g15). Then, mult iple 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. measuring stick 2. Portfolios return, variance, standard deviation and slope To obtain the portfolio variance, we need to further multiply distributively(prenominal) entry of the variance/covariance matrix by their corresponding loads, xi and xj.Remember, those n portfolio weights are what we are trying to solve for. So we entrust them in a column (a34.. a39). To facilitate the calculations, I also dwelling the weights at the top of the matrix. The variance/covariance matrix is simply copied from bill 1. Since we testament 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 become =sumproduct. This sumproduct results in each weight in (a34.. 39) universe reckon to each entry in the variance/co variance column, and then all summed up. The variance/covariance terms will have hardly 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 virtuallything 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 cellphone b45. The portfolios return in b44 is calculated as the dull 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 present 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 care to vary the weights in a34.. a39 so that the va riance (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 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 maximize shift subject to sum of weight = 1. 0 Follow the same logic/ functioning as in Step 3, except that you want to maximize cell b46. Step 5.Obtain market portfolio with no short merchandising 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 chalk up 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 patch 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.