Modern Portfolio Theory: Developing the Efficient Frontier

No, we are not going to go where no man has gone before.  That’s a different frontier.  The frontier I will be showing you today is the Efficient Frontier, an element of Modern Portfolio Theory that shows the most efficient portfolio of securities for each level of risk.

This is an essential tool to gauge if your portfolio is performing well given the amount of risk you are taking.  I will be continuing on from my previous post “Modern Portfolio Theory: Developing a Global Minimum Variance Portfolio”, so check that one out first if you have not done so already.

Okay, so let’s dive in!

So you have the model already built in your Microsoft Excel file from last time.  This model will be used to plot the Efficient Frontier on a graph.  The first step is to make a table to hold the data in.  I suggest making one similar to the table below:

As you can see we already have one data point filled in.  This point is the Global Minimum Variance Portfolio we made last time.  This will be our midpoint.  From here we will choose three values that are less than the GMV (0.005730) and five values greater than the GMV. The “U” is our expected return.  I have chosen to use the following data points:

  • U = 0.0020
  • U = 0.0030
  • U = 0.0040
  • U = GMV
  • U = 0.0080
  • U = 0.0100
  • U = 0.0110
  • U = 0.0120
  • U = 0.0130

After you add these values in, your table should look like this:

Now we need to use SOLVER to get each Portfolio SD at given return.  Open up SOLVER and make the following inputs:

  • Set Target Cell = $H$34 (This is the Portfolio SD)
  • Equal to = Min
  • By Changing Cells = $A$27:$F$27 (These are the weights)
  • Subject to the Constraints:
    • $A$27:$F$27 <= 1
    • $A$27:$F$27 >= 0
    • $H$27 = 1
    • $H$38 = 0.002 (This is the Portfolio Expected Return.  It will change each time we run SOLVER)

The SOLVER should look like this:

Then hit “Solve” and hit “OK” for SOLVER Results.  You should have a SD value of 0.045250 for the return of 0.002.  So now enter this value into the table under Portfolio SD.  Run SOLVER again through each of the U values changing the Constraint $H$38 variable each time (ie: the next one will have $H$38 = 0.003).  When you are done your table should look like this:

As you can see the Portfolio SD has a certain trend.  The GMV is the minimum point; all points above it and below it are higher in value.  And as you deviate from the GMV, the amount of risk goes up.  This is what gives the Efficient Frontier a “Boomerang” shape.

So now that we have are points plotted out, let’s make a chart.

We will be making a “Scatter with Smooth Lines and Markers”.  Make the X-axis the Portfolio SD and make the Y-axis the Portfolio Expected Return.  Your chart should look like this:

I made some formatting changes to mine.  I changed to axis values to be 4 decimal places, rotated the X-axis numbers, and added titles to the axis and the chart.  I also added a second series to show the GMV.  The first series consists of all the data points in the table and the second series only includes the GMV data points.

What we can learn from this chart are the following:

  • The portfolio with the least risk (lowest SD) is the GMV.
  • Any portfolio below the GMV return is considered an inefficient portfolio because there is another portfolio available that has the same risk (SD) with a higher return.
  • For any given risk, we can find a portfolio that will maximize our return.

I hope you enjoyed this tutorial.  Now you have the tools to construct your own Efficient Frontier out of a population of securities.  You can now match your preferred level of risk to your portfolio and maximize your returns.

Next time we will be adding in a risk free asset to get the Capital Allocation Line, or CAL.

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: