Mean-Variance Portfolio Optimization with Excel

This Excel spreadsheet implements Markowitz’s mean-variance theory. It optimizes asset allocation by finding the stock distribution that minimizes the standard deviation of the portfolio while maintaining the desired return.  A series of sample stocks are included, but the spreadsheet can be adapted to other stocks selections.  Full instructions are within the spreadsheet, but click on the picture below for an overview.

You may need to enable Excel’s Solver by going to File > Options > Add-Ins > Manage

The genesis of modern portfolio theory was in the 1950s with Harry Markowitz’s pioneering work in mean-variance portfolio optimization.  Before his innovation, finance was far more influenced by heuristics than by mathematical modeling. Mean-variance optimization is now the primary technique employed by hedge funds and pension funds for portfolio diversification.

Mean-Variance optimzation with Excel

Most investors trade risk off against the expected return.  Mean-variance optimization identifies the investment portfolio that minimizes risk (i.e standard deviation) for a given return.  The efficient frontier is the line that forms when the expected returns are plotted against the minimized standard deviation.

Mean-variance portfolio optimization has, however, several limitations.

  • Employing standard deviation (or variance) as a proxy for risk is valid only for normally distributed returns. While this may be true for traditional stocks, bonds, derivatives and hedge funds demonstrate skew and kurtosis (which invalidates the application of Markowitz’s theory).
  • The premise of the theory implies that investors will not alter their asset allocation after it has been optimized
  • Investors or fund managers may not necessarily want to minimize risk (i.e. standard deviation). They may, instead, wantto reduce the correlation of a fund to a benchmark

Markowitz’s approach to portfolio optimization has been modified to account for transaction costs (which can make portfolio changes unprofitable) and to only penalize downside volatility (since most investors do not mind sudden large upswings in volatility).

Download Excel Spreadsheet for Mean-Variance Portfolio Optimization


4 thoughts on “Mean-Variance Portfolio Optimization with Excel

  1. Thanks for the wonderful spreadsheet. I did all the steps in the same way as you did in yours but i am getting Negative Portfolio Weights Do you know why that could be happening?

  2. Guten Tag

    Ich möchte gerne ein efficient frontier zeichnen mit full forward currency hedge (h= 1) und ohne currency hedging aber ich weiss nicht wie ich es zeichnen kann. Kann jemand mir bitte helfen? Vielen Dank

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 12 + 12 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)