# Construct the Efficient Frontier

956 Words4 Pages
Instructions for 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
…show more content…
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 maximize cell b46, except that, aside from the weights-summing-to-one constraint, you would add six more constraints: a34 > 0, a35 > 0, …, a39 > 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