Portfolio Optimization Excel Model with Harry Markowitz's Modern Portfolio Theory

Description
Modern Portfolio Theory (MPT) is a mathematical framework for assembling a portfolio of assets such that the expected return is maximized for a given level of risk. Harry Markowitz introduced MPT in
1952, for which he was later awarded a Nobel Prize in Economics. There are many limitations to MPT but it remains popular to this day for a reason - its underlying principles on diversification and correlation and their role in risk management are timeless.

This spreadsheet uses MPT to find the theoretically optimal portfolio of 10 assets. It accepts 10 years of monthly adjusted closing prices as input to calculate average return, volatility and covariance. From there, the minimum variance portfolio and efficient frontier of risky portfolios is found. After entering the risk-free rate and target rate of return, the tangent portfolio, Capital Allocation Line (CAL), and optimal portfolio on the CAL are determined alongside their corresponding asset class weightings. This spreadsheet can also be used to test custom inputs for expected return, volatility and correlation for each asset.

Finally, this spreadsheet handles the scenario where zero short-selling or leverage is permitted, though this requires the use of Excel Solver to obtain a numerical solution. To get the most out of this spreadsheet, I'd recommend understanding the math behind how MPT works or at the very least, the limitations of MPT.

(And please don't naively use the model outputs directly for financial decision-making!)

Enjoy!
Jhett Koo