Modern Portfolio Theory: Developing a Global Minimum Variance Portfolio (GMV) in Excel

This is the first installment in a series of posts dedicated to “Modern Portfolio Theory”.  In this post, I will show you how to build a Global Minimum Variance (GMV) Portfolio in Microsoft Excel.

The GMV Portfolio is the portfolio with the highest return and the least risk.  It takes into account all securities available and uses their returns, variances, and correlations to reduce as much of the non-systematic (firm-specific) risk as possible.

I first learned all about Modern Portfolio Theory during my studies at university.  I found the topic very interesting and practiced making portfolios on my own time.  I would use any asset I could to try and find more and more efficient portfolios.  Modern Portfolio Theory is nice because it simplifies things.  The theory lets you build an efficient portfolio by only using three variables: 1) return, 2) variance, and 3) correlation.  This saves the investor a lot of time when choosing securities to add to his or her portfolio.

But, I do feel these simplifications are the biggest disadvantage of the theory as well.  Returns, variance, and correlation are based on historical returns (although returns used can be expected returns based on CAPM or other models).  The problem here is that we believe that the past will repeat itself.  I guess it can be argued that correlation should not see much change, but it is possible that return and variance will greatly change in the future; new management taking over, changes in legislation, changes in energy costs, political changes, and many other possibilities.  There is also the question of whether variance is truly a good marker for risk.  Variance includes both positive and negative variations from the mean, and in investing, we really are only concerned about negative variations from the mean; we are usually happy with positive variations.  And lastly it does not take into account business analysis.  You may find companies that are highly uncorrelated, but if they’re all bad companies you won’t get great returns.

But the purpose of this post is not to say if Modern Portfolio Theory is good or bad.  The purpose of this post is to show you how to use it.  So, let us begin making our GMV!

I will be using 6 securities from different industries within the Dow Jones Industrial Average: 1) 3M (Conglomerate), 2) Bank of America (Banking), 3) ExxonMobil (Oil & Gas), 4) McDonald’s (Fast Food), 5) Pfizer (Pharmaceuticals), and 6) Wal-Mart (Retail).  I chose these securities randomly and without prior knowledge to their correlations with each other.  You can retrieve the data for each of these companies from Yahoo Finance, the stock symbols are below.  Make sure you select “monthly” for prices and a date rang of June 01, 2002 to June 01, 2012.  This will give us 121 data points and cover 10 years, which should include a full business cycle.  Our returns will be in monthly terms, so our portfolio will be expressed in monthly terms.  When you get your data in Yahoo Finance, scroll down to the bottom of the page and click on “Download to Spreadsheet”.  This will give you a CSV file that can be opened in Excel.

  • 3M – MMM
  • Bank of America – BAC
  • ExxonMobil – XOM
  • McDonald’s – MCD
  • Pfizer – PFE
  • Wal-Mart – WMT

Once you have all the files downloaded, start a new Excel where we will import the historical prices.  You want to use “Adj Close”, which stands for adjusted close.  This is the price the stock closed at the end of the day and is adjusted for stock splits and dividends.  Your Excel file should now look like this:

Our next step is to calculate the monthly returns for each security.  Make 6 new columns for returns and calculate the returns by the following formula:

You should have 120 returns for each security.  I have formatted mine so that they all show 4 decimal places.  Don’t forget that you can copy formulas by clicking on the bottom right corner of the cell with the formula in it and drag to copy that formula to other cells.

Next we need to find the correlations between each security.  To do this we will make a correlation matrix.  The first step is to select all the cells that include the returns, including the titles.  Copy this range and go to an empty space to the right of the data.  Next we will right click and select “Paste Special… ”.  Here you will select both “values and number formats” and “Transpose”, then hit OK.  Your sheet should look like this:

Now we can make the correlation matrix.  Set up the 6×6 matrix with the company titles for both the rows and columns.  Now enter the following formula:

Make sure you put in the $ signs in the right places.  This will allow you to copy formulas without any problems.  The $ will make either the row or column stay in place as you drag across.  Your matrix should look like the one above, but a quick way to see if you have done it right is to see if the diagonal values are all 1.0000.  They should be 1 because the correlation of anything with itself will be 1.

Next we must make a 6×6 Covariance Matrix.  The matrix will have the same layout as the Correlation matrix, so just copy those titles. We will use Excel’s covariance formula, but we have to make one adjustment.  Excel is set to compute the covariance as a population covariance, but we want a sample covariance, so in front of the formula, we must add (120/119).  The formula for covariance is:

Your matrix should look like the one above.  Now we have all the data we need to make our GMV.

Lastly, we need to compute the average returns and standard deviations of each security.  For average returns use the following formula:

And for standard deviation, use this formula:

Now we need to open a new worksheet within the same workbook.  I named the first tab with all the above calculations “Data” and the new tab will be called “MPT”.  MPT is where we will make the GMV portfolio.

Set up your worksheet like below:

Note: When copying values over, you can paste and then select “values and number format”, this way you won’t get a REF error.

Make sure the Portfolio Weights are 1, 0, 0, 0, 0, and 0 for the horizontal (row 27) numbers.  The Portfolio Weights in column H should reference back to the numbers in row 27. Below is an example:

You also need to add in a SUM for the weights to use in SOLVER later on.  The formula should be:

Now we need to find the variance of the portfolio.  To do this we will make another 6×6 matrix.  The formula for the matrix is as follows:

Again, make sure the $ signs are in the right spots, this will make copying the formula much easier.  Now we can find the portfolios total variance by using the SUM function and summing the 6×6 matrix.  It should look like this:

And the Portfolio’s standard deviation can be found by taking the square root of the variance.  It should look like this:

Now we need to compute the return of the portfolio.  You will take the weight of each security and multiply it by the average return.  You do this for each security and then SUM all the securities together.  Below are examples of the formulas used:

You now have your model finished!  YEAH!  CONGRATS!  Now we can start computing the GMV portfolio.

First I suggest creating a chart like the one below:

Now we need to use the SOLVER function in Excel.  Make the “Portfolio’s Variance of Returns” the active cell and start SOLVER, found under “Data” -> “Solver”.  You may need to add SOLVER to your Excel if you have never done it before.  It’s free; just search on Google how to add SOLVER.  Once the SOLVER window appears, you will need to enter the correct data to get the result we want.  The data should be as follows:

  • Set Target Cell: $H$31
  • Equal to: Min
  • By Changing Cells: $A$27:$F$27 (These are the portfolio weights)
  • Subject to the Constraints:
    • $A$27:$F$27 <= 1
    • $A$27:$F$27 => 0 (This is for no short selling. If you want to short sell, leave this out.)
    • $H$27 = 1 (This makes sure our portfolio weight is always 100%, or not borrowing.)

The widow should look like this:

Next, hit SOLVE.  That’s it!  Now you have the weights of each security to make the GMV portfolio.  Now fill in the table and save your work. Your end result should look like this:

Congratulations!  You just made your first Global Minimum Variance Portfolio!

From our solution, we see that we should have the following weights for each security:

  • 3M: 14-15%
  • Bank of America: 0% (due to the negative average return)
  • ExxonMobil: 28-29%
  • McDonald’s: 43-44%
  • Pfizer: 12-13%
  • Wal-Mart: 39-40%

Now remember, this isn’t the portfolio that will give you the highest return.  This is the portfolio that will give you the highest return with the lowest risk (firm-specific risk).

I suggest you try this with other securities, or even better, try making the file with 10 securities, or maybe all the securities in the Dow Jones Industrial Average… or perhaps the S&P500?  Or try a passive investor solution by using ETFs or INDEX Funds.  Then try your results on a stock simulator and see how you do.

I hope you enjoyed this little tutorial.  I hope you feel like your finance and Excel skills have improved.

Next time we’ll look at making the Efficient Frontier.

Thanks for reading and good luck out there!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: