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

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

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!)

Jhett Koo

This Best Practice includes
1 Excel File

Acquire business license for $5.00

Add to cart

Add to bookmarks




Discussion feed for Portfolio Optimization Excel Model With Harry Markowitz's Modern Portfolio Theory

The user community and author are here to help. Go ahead!

  • Shan Gao
    Does it consider rebalancing? For example, if I'd like the portfolio to rebalance on annual basis. Would the optimiser take that into account?
    arrow_drop_uparrow_drop_downReply reply
    • Jhett Koo
      Rebalancing needs to be done manually. The model simply finds the optimal target asset allocation using mean-variance analysis.
      arrow_drop_uparrow_drop_downReply reply
    • Richard Laird
      Can I use mutual funds with this?
      arrow_drop_uparrow_drop_downReply reply
      • Jhett Koo
        Yes, you can use any asset price data as inputs.
        arrow_drop_uparrow_drop_downReply reply
      • *del* Ron Strouss
        I wonder if this spreadsheet works with Office 365 ... ???
        arrow_drop_uparrow_drop_downReply reply

      4.6 / 5 (11 votes)

      please wait...