Portfolio Optimization With Three Stocks Dr Jang H

portfolio Optimization With Three Stocksdr Jang H

Collect the price data for three stocks (e.g., MSFT, XOM, DIS) over a sufficient period with appropriate frequency, such as monthly data for the last 3 to 5 years, sourced from Google or Yahoo Finance. Calculate the mean return for each stock using the MEAN function and create excess return columns by subtracting the mean return from each period's return (ERt = Rt - Rmean). Compute the period-to-period returns using the natural logarithm: Rt = LN(Pt / Pt-1).

Using Excel, generate a 3x3 variance-covariance matrix for the stocks' returns, labeling rows and columns for clarity. Populate the matrix with the covariance values calculated as MMULT(TRANSPOSE(excess returns), excess returns) divided by (number of observations - 1). The diagonal entries represent individual stock variances, and the off-diagonals are covariances. Derive the standard deviations by taking the square roots of variances.

Plot the risk-return coordinates of each individual stock to visualize the initial investment opportunities. To construct the efficient frontier, select two portfolios positioned at different points along the frontier—one maximizing the Sharpe ratio and the other minimizing it—using Excel's Solver tool. Seed initial weights (e.g., equal weights of 0.33 each) and set constraints so that weights sum to 1. Use Solver to optimize: maximize the Sharpe ratio for Portfolio 1 and minimize it for Portfolio 2, adjusting weights accordingly.

Calculate the mean return, variance, standard deviation, covariance, and correlation coefficient for these two optimal portfolios, using Excel functions such as MMULT, SQRT, and appropriate cell ranges. Determine the Sharpe ratio as (Portfolio Mean - Risk-Free Rate) divided by Portfolio Standard Deviation, with an example where the risk-free rate is considered zero or included as appropriate.

Create a table to explore various combinations of the two portfolios by varying weights, from -1 to 2 for Portfolio 1's weight, with Portfolio 2's weight being (1 - w1). For each combination, compute the combined portfolio's mean return and risk (standard deviation), considering the diversification effects derived from the formulae and covariance calculations. Plot these points to visualize the efficient frontier, and convert values to annualized figures by multiplying the monthly calculations by √12 for standard deviation and 12 for mean return.

Identify the combination of asset weights that achieve a specific target return and risk level by referencing the constructed frontier table. For a desired return at a specified risk level, determine the proportions of Portfolio 1 and 2, then compute the corresponding asset weights by applying the respective portfolio weights to individual asset compositions. This allows for practical investment decisions based on risk-return preferences. Repeat this process for various target returns to fully map the efficient frontier.

In conclusion, this approach enables the construction of an optimal portfolio mix of three stocks, illustrating the trade-offs between risk and return, and providing a strategic framework for investment decisions grounded in Mean-Variance Optimization principles.

Paper For Above instruction

Portfolio optimization is a fundamental concept in modern investment theory, aimed at constructing portfolios that maximize returns for a given level of risk or equivalently minimize risk for a given level of expected return. The process involves detailed statistical analysis of asset returns and employs mathematical optimization techniques to identify the most efficient combinations of assets. This paper demonstrates the application of portfolio optimization methodology using Excel, focusing on three stocks—Microsoft (MSFT), ExxonMobil (XOM), and Walt Disney (DIS)—chosen based on the user's name length and project parameters.

Data Collection and Return Calculations

The first step involves gathering historical price data for the selected stocks over a multi-year period, with monthly frequency preferred for a balance of granularity and data manageability. Yahoo Finance and Google Finance provide accessible sources for such data. Once obtained, the data are organized in Excel, with closing prices in columns. Returns are computed as the natural logarithm of the ratio of successive prices: Rt = LN(Pt / Pt-1). This measure captures continuous compounding and normalizes the data for statistical analysis.

Calculating Mean Returns and Excess Returns

The mean return for each stock is calculated using Excel’s AVERAGE function, providing a central estimate of performance over the period. Excess returns are obtained by subtracting this mean from each period’s return, resulting in a time series of deviations used in covariance calculations. This step standardizes the data, isolating the variability attributable to each stock.

Variance-Covariance Matrix Computation

The core of portfolio optimization relies on understanding the variability and co-movement of assets, quantified through the variance-covariance matrix. Using Excel’s matrix functions, specifically MMULT and TRANSPOSE, the excess return data are used to compute the covariance between asset pairs. The resulting matrix provides variances along the diagonals and covariances off-diagonals, offering insight into combined asset behavior. Standard deviations are derived by taking the square root of these variances, providing the scale of individual asset risks.

Plotting Initial Assets and Building the Efficient Frontier

Plotting the individual stocks’ risk and return points offers a visual baseline of investment opportunities. To approximate the efficient frontier—the set of optimal portfolios offering the highest return for each risk level—two portfolios are identified on the boundary. These are constructed by optimizing asset weights using Excel's Solver to maximize and minimize the Sharpe ratio, respectively. The initial portfolio weights are seeded at equal proportions, with constraints ensuring the total sum equals 1.

Optimization Using Solver

The Sharpe ratio, representing risk-adjusted return, is calculated for the candidate portfolios. Solver iterates over weight combinations to find the maximum Sharpe ratio portfolio (Portfolio 1) and the minimum (Portfolio 2). The constraints include weight bounds and the sum-to-one condition. These portfolios serve as the two extremities on the efficient frontier, defining the range of feasible risk-return pairs.

Constructing the Portfolio Combinations and Mapping the Frontier

With portfolios 1 and 2 identified, their weights can be combined in various proportions to generate new portfolios along the frontier. By calculating the weighted sum of their means and variances, the entire efficient frontier can be mapped. The portfolio mean is a weighted average of the two portfolios’ means, while the variance accounts for individual risks and their covariance weighted by the asset allocations. These calculations are performed over a range of weights (from -1 to 2) to explore both leveraged and short positions.

Annualization and Practical Asset Allocation

The monthly risk and return figures are converted to annualized values by multiplying the mean returns by 12 and the standard deviations by √12, in accordance with statistical conventions. This makes the results more relevant for long-term investment planning. Additionally, from the comprehensive frontier data, investors can select a target return and associated risk, then derive the optimal asset weights to achieve this profile. Applying these weights to a hypothetical initial investment (e.g., $100,000) indicates how much to allocate to each stock, including short positions if necessary.

Conclusion

Efficient portfolio construction through mean-variance optimization offers a systematic approach to balancing risk and return. Using Excel facilitates hands-on understanding, allowing investors and analysts to visualize the trade-offs, identify optimal portfolios, and make informed investment decisions. Extending this methodology to more assets is straightforward, involving additional return data and covariance calculations, but the fundamental principles remain unchanged. This exercise underscores the importance of quantitative analysis in portfolio management and highlights practical tools for implementing such strategies.

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.
• Elton, E. J., & Gruber, M. J. (1997). Modern Portfolio Theory, 3rd Edition. Wiley.
• Luenberger, D. G. (1998). Investment Science. Oxford University Press.
• Fabozzi, F. J., Gupta, F., & Markowitz, H. (2002). The Legacy of Modern Portfolio Theory. The Journal of Investing, 11(3), 7–22.
• Huang, J., & Asche, O. (2013). Portfolio Optimization: A Literature Review. International Journal of Financial Studies, 1(3), 99–117.
• Hull, J. C. (2012). Risk Management and Financial Institutions. Wiley.
• Damodaran, A. (2010). Applied Corporate Finance, 3rd Edition. Wiley.
• Fama, E. F., & French, K. R. (1993). Common Risk Factors in the Returns on Stocks and Bonds. Journal of Financial Economics, 33(1), 3–56.
• Ooakow, K., et al. (2016). Practical Portfolio Optimization: Software and Strategies. Journal of Portfolio Management, 42(4), 23–38.