Before proceeding, consider whether Excel is the right tool. Compiling the return data, building the covariance table, and then using solver to manually construct the efficient frontier is time-consuming and cumbersome (especially if you are using it on a portfolio that contains more than a handful of securities). A much better tool is R. Check out the article here for easy-to-follow instructions for building and graphing the efficient frontier. You’ll find it takes a matter of minutes to get up and running with R (it is free) and the code provided allows you to construct optimal portfolios based on mean-variance optimization in a matter of seconds.
In the previous article, we used the monthly return data for the assets in our portfolio to construct a :covariance table. This table enables us to describe the degree to which the prices of the assets move together.
In this article, we’ll construct the efficient frontier using Microsoft Excel. The efficient frontier provides the set of assets that constitute the optimal portfolio.
Building the efficient frontier consists of 4 steps:
- Gathering the average return data for the assets
- Building a weighted covariance table
- Finding the portfolio set with the lowest standard deviation
- Using Excel Solver construct the portfolio with the highest Sharpe ratio
- Graphing the Efficient Frontier
Step 1 – Gathering Average Return Data
We begin with the average return data. We calculated this information in the second article in this series. We’ve summarized the information in the table below.
|Average Monthly Return||Standard Deviation|
One important distinction is that these represent the actual nominal returns based on historical data. It is important to consider that these are risky assets–an investor may loss money. Often, it is useful to compare nominal returns against what an investor could have earned on a risk-free asset. The difference between the nominal return and the risk free return is called the excess return. An alternative method of constructing the efficient frontier uses average monthly excess returns. For our series, we chose to use the nominal rates. (We will adjust for the risk free rate below.)
Notice that we’ve also copied in the standard deviation of the returns for each of the assets. Although we don’t use these data explicitly in constructing the optimal portfolio, we’ll compare the standard deviation of our portfolio to these numbers after we’ve selected our allocation. What you will notice is that the standard deviation of our portfolio is always lower than the standard deviation of any individual asset. This is the essence of the Markowitz Modern Portfolio Theory.
Step 2 – Building a Weighted Covariance Table
In the third article of this series we constructed a covariance table. The covariance table tells us the degree to which the prices of the assets move together. Remember that a covariance can be positive or negative, large or small. Here, we will add a weighted border to the table so that our Excel spreadsheet can automatically calculate the standard deviation of the selected portfolio. An optimal portfolio will have the lowest variance for a given expected return. This is why the Markowitz portfolio theory is often called the mean variance optimization.
We start with the covariance table and add weights along the rows and columns. These represent the weighting of each of the assets in the portfolio. It is important to use formulas so that the weights in the columns are the same as the weights in the row. Refer to the figure below
Notice that the weights add up to 100%. The asset columns are also summed. The spreadsheet is designed to use these sums to calculate the overall standard deviation of the portfolio. For now, set the weight to any arbitrary number, as long as the total weight sums to 100%.
Now that we have gathered the average returns and constructed a weighted covariance table, we are ready to construct portfolio with the lowest standard deviation.
Step 3 – Find the Lowest Standard Deviation
To find the portfolio with the lowest standard deviation we begin by creating a table that summarizes the portfolio’s statistics.
These statistics include: the portfolio average return (calculated as the excess returns over the risk free rate), the standard deviation of the portfolio, and the slope of the capital allocation line (CAL). The slope is often referred to as the Sharpe ratio.
The portfolio average return is calculated using Excel’s sumproduct function. Refer to the graphic below for assistance in setting up this function.
The standard deviation is the square root of the sum of the portfolio’s variance. Refer to the graphic below for assistance calculating the portfolio’s standard deviation.
And the slope (or Sharpe Ratio) is simply the average return divided by the standard deviation.
Use Solver to Minimize Standard Deviation
Now that we have created the portfolio statistics, use Excel’s Solver function to find the lowest possible standard deviation possible with this set of investment alternatives.
Set the target to be equal the Standard Deviation in the portfolio statistics table. Select min as the target solution. Allow Solver to change the weights of the covariance table (use the column since the row of weights is set up with formulas to mirror the column). You’ll notice in the graphic below that constraints includes two types: a sanity check (total weigh must equal 100%) and a positive check. The positive check ensures that the portfolio does not include any short sales. Without this, the portfolio with the lowest standard deviation may include negative weights–meaning that the investor would sell those investments and use the proceeds to purchase the other investments. While this may be fine, we’ve chosen to exclude any investment strategy that includes short sales for the purpose of this paper.
After running solver, we have a new portfolio mix. This mix has the lowest possible standard deviation.
Notice that the standard deviation of this portfolio (2.5%) is lower than the standard deviation of any of the investments. This is key to understanding the Modern Portfolio Theory. Through diversification, we have managed to achieve a portfolio with a lower variance than any of its parts.
Also notice that the portfolio now consists of only two investments: SPY and HYG.
Constructing the Portfolio
Create a simple table, like the one below to record the various portfolio allocations. Use the Paste Special command (CTRL+ALT+V, then select Values) to copy and paste the three portfolio statistics and the allocation into the table.
The next several steps involve an iterative process where we run Solver a number of times–each time increasing the expected return slightly. From here we can graph the Capital Allocation Line.
Create a new cell, called Target Average. Set the target average slightly higher than the average calculated in the previous step (.16%). For the purpose of our set, we set the new target average to .20%.
Notice in the graphic below that we’ve added a new constraint. This constraint allows us to find the portfolio mix that achieves the lowest standard deviation for the given return target.
Notice that the portfolio mix has changed and that the statistics as well as the allocation has been copied down to the portfolio table.
Also notice that the slope has increased from the first mix. The goal in Modern Portfolio Theory is to find the mix with the highest Sharpe Ratio–this mix will provide the highest return to risk.
Continue to run Solver with increasing target averages until the Sharpe ratio is no longer rising with each iteration.
The set with the highest Sharpe Ratio (or slope) is the optimal portfolio. In this set, an investor with these six investment alternatives should consider investing 44% in VWO and 56% in LQD.
Step 5 – Graphing the Efficient Frontier
Now that the portfolio table is complete, we can graph the efficient frontier using Excel. Plot the average return along the Y axis and the standard deviation along the X axis. The optimal portfolio is where the slope of the line is the greatest.
Please feel free to download this example Excel workbook that contains the tables and functions for finding the efficient frontier for the set of investment alternatives contained in this article.
In the next article we will consider how to use this approach when making investment allocation decisions in the real world.