Construct the Efficient Frontier
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 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 returns 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 standard 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
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 returns 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 standard 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