Portfolio Optimization Excel Model with Harry Markowitz's Modern Portfolio Theory
Originally published: 29/12/2018 08:55
Last version published: 30/01/2019 14:53
Publication number: ELQ-95014-2
View all versions & Certificate
certified

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

Find the efficient frontier of optimal portfolios, the Capital Allocation Line, tangent portfolio and more

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

This Best Practice includes
1 Excel File

Acquire business license for $5.00

Add to cart

Add to bookmarks

Discuss


4.7 / 5 (14 votes)

please wait...