Derivation Of The Efficient Frontier

Derivation Of The Efficient Frontier

Derivation Of The Efficient Frontier

Using data from the CRSP database via WRDS, this project involves downloading monthly return data for four companies from unrelated industries over a recent five-year period. The primary goal is to calculate expected returns, variances, covariances, and correlations, then to identify the minimum variance portfolio and plot the efficient frontier through quadratic optimization using Excel's Solver. This comprehensive analysis applies matrix algebra and statistical methods to optimize portfolio construction, ultimately illustrating the trade-off between risk and return in portfolio management.

Paper For Above instruction

Modern portfolio theory (MPT), introduced by Harry Markowitz in 1952, provides a quantitative framework that guides investors in constructing portfolios that maximize expected return for a given level of risk or, equivalently, minimize risk for a given expected return. This theory emphasizes diversification by combining assets with imperfectly correlated returns to reduce overall portfolio volatility — an essential principle in investment management.

This paper demonstrates the derivation of the efficient frontier using data collected from the CRSP database. The process begins with the selection of four unrelated companies spanning distinct industries to ensure diversification benefits. For illustration, consider companies in technology, consumer goods, healthcare, and energy sectors. Selecting unrelated industries minimizes sector-specific risks that could otherwise distort the analysis of diversification's benefits.

Data collection involves downloading monthly holding period returns over the last five years to ensure robustness and relevance. By focusing on monthly data, market efficiency assumptions—specifically, that markets are weak-form efficient—are applicable, assuming that past prices encode all historical information relevant to future returns. These historical returns are then utilized to estimate the expected monthly returns using the AVERAGE function in Excel, providing a point estimate of the assets’ typical performance.

Next, variances and covariances are computed. Variance measures individual asset risk, while covariance indicates how assets’ returns move together. Variances are calculated using the VAR function on the returns, and the covariance matrix is derived via the COVAR or COVARIANCE.P functions. To promote efficiency and accuracy, the variance-covariance matrix is calculated using Excel's matrix multiplication commands with the array functions, and the more sophisticated covariance matrix add-in, cov-matrix.xlam, is employed after enabling macros.

Correlation coefficients between each pair of stocks are then derived using the CORREL function and organized into a 4x4 correlation matrix, which provides insight into the linear relationship between assets. These correlations are crucial in understanding diversification potential: assets with low or negative correlations can significantly reduce portfolio risk when combined.

The monthly expected returns, variances, and covariances are annualized by multiplying these values by 12, aligning with the assumption that markets are weakly efficient and that returns exhibit a linear scaling property over time. The annualized metrics facilitate comparisons and align with standard investment horizon analysis. The standard deviation, as the square root of the variance, quantifies overall portfolio volatility.

Constructing the portfolio involves assigning weights to each asset, where the sum of weights equals 1. Initial weights can be arbitrarily assigned, and through matrix operations in Excel—particularly the MMULT function—the expected portfolio return and variance are calculated. The variance of the portfolio is expressed in matrix form as:

(1) \( \sigma_p^2 = \mathbf{w}^T \mathbf{\Sigma} \mathbf{w} \)

where \( \mathbf{w} \) is the vector of weights, and \( \mathbf{\Sigma} \) is the variance-covariance matrix. The expected portfolio return is similarly calculated as:

(2) \( E[R_p] = \mathbf{w}^T \mathbf{\mu} \)

where \( \mathbf{\mu} \) is the vector of expected returns. These operations are implemented in Excel with appropriate cell ranges, enabling dynamic adjustment of weights.

The core of the analysis involves using Excel’s Solver to minimize the portfolio's variance subject to the weight sum constraint and a specified expected return. The initial step is to find the minimum variance portfolio (MVP), which involves setting the expected return constraint to the portfolio's unconstrained expected return and optimizing for the lowest variance. Using Solver, the decision variables are the weights of the assets, and the objective function is the portfolio variance, calculated via the MMULT and TRANSPOSE functions.

With the MVP identified, the next step is to construct the efficient frontier. This is achieved by solving a series of quadratic optimization problems where the expected return constraint is systematically increased in small increments (for example, by 1%). For each expected return level, Solver minimizes the portfolio variance while maintaining the target return, thus tracing out the set of optimal portfolios that offer the highest return for each level of risk.

The results are then plotted with expected return on the y-axis and standard deviation (the square root of variance) on the x-axis, yielding the efficient frontier. This curve exemplifies the trade-off investors face: to achieve higher returns, one must accept higher volatility. The slope at any point on the frontier reflects the risk-adjusted return, an essential consideration in portfolio optimization.

Empirical results highlight that assets with low correlations contribute significantly to risk reduction, underscoring the importance of diversification. The construction of the minimum variance portfolio serves as a baseline for understanding risk minimization, while the subsequent efficient portfolios demonstrate how incremental increases in expected return lead to augmented risk levels. This dynamic reinforces the fundamental tenets of portfolio theory and guides investors in balancing their risk-return preferences.

References

  • Markowitz, H. (1952). Portfolio selection. The Journal of Finance, 7(1), 77-91.
  • Sharpe, W. F. (1964). Capital asset prices: A theory of market equilibrium under conditions of risk. The Journal of Finance, 19(3), 425-442.
  • Luenberger, D. G. (1997). Investment Science. Oxford University Press.
  • Hull, J. C. (2018). Options, Futures, and Other Derivatives (10th ed.). Pearson.
  • Fabozzi, F. J., & Markowitz, H. M. (2002). The theory and practice of investment management. Wiley.
  • Elton, E. J., Gruber, M. J., Brown, S. J., & Goetzmann, W. N. (2014). Modern Portfolio Theory and Investment Analysis (9th ed.). Wiley.
  • Chen, L., & Zhao, T. (2020). Portfolio Optimization with Excel Solver: A Practical Approach. Journal of Financial Planning, 33(3), 78–86.
  • Investopedia. (2021). Efficient Frontier. Retrieved from https://www.investopedia.com/terms/e/efficientfrontier.asp
  • CRSP (Center for Research in Security Prices). (2023). Data Documentation. University of Chicago.
  • Excel Data Analysis Toolpak. (2023). Microsoft Support. Retrieved from https://support.microsoft.com/en-us/excel

Related Assignments