skip to Main Content

Build Your Own Mean Variance Optimized Portfolio …and Master CFA L1 Study Session 12 in the Process

Follow these easy steps to construct your own customized portfolio using mean variance optimization.

By the time you have built your portfolio, nailing the CFA Level 1 Portfolio Management (SS12)questions on the exam will be a piece of cake…or almost.

When you work or study in finance, regardless of the field you actually operate in or the nature of your work, you will always be seen as one thing by non-finance people: a stock-picking shaman. As soon as you mention that you are operating in the investment field, even if you are actually in back office or compliance they immediately ask you for stock tips. Since I trade option spreads, it’s a bit difficult to casually explain what I do to non-finance people over tacos without killing the vibe. The conversation usually goes like this:

NON-FINANCE GUY: So Miguel what do you do for a living?

FINANCE GUY: I work in finance.

NFG: Oh really so what’s hot right now?

FG: Huh… what do you mean?

NFG: Common man! Tell me where should I put my money.

FG: …well huh…I dont really make discretionary stock selection as part of my job.

NFG: Thats great… but seriously dude, which stock should I pick?


  • Answer A (honest answer): I have absolutely no idea! It all depends of your risk tolerance, time horizon, and your other specific investment constraints and objectives.

skeptical dog

  • Answer B (honest answer without sounding incompetent): Well actually I sell volatility spreads on major North American indices so I do not implement directional trades. Oh and I also sell some at-the money butterflies when I see IV is too high historically speaking.

[youtube height=”300″ width=”400″][/youtube]


  • Answer C (violates everything I ever learned about sound portfolio management): Well….huh…emerging markets are pretty good…hum..Indonesia is booming so maybe that could be a nice place to invest…
I cant believe I said that...

I cant believe I said that…


The truth is you will be asked for stock tips from friends, family, dates, waiters and everyone you meet. A bum actually tried to pick my brains about hot stocks once. He didn’t seem to find it incongruous to ask for change while request advice on portfolio construction. By the way this is why I sometimes enjoy talking with homeless people, they are always full of surprises…

Well next time someone asks you about stock tips, you can kindly refer them to this mean variance optimization primer. Even someone with (almost) no finance background will be able to construct his own portfolio according to his own specification. You will need to have some familiarity with Excel to fully enjoy this guide. Since I want to keep this post short and sweet, I will not provide the detailed Excel steps for each calculation but you can find countless tutorials online or on Youtube that will show you how to use the functions discussed here.


The concept of MVO is to find the best possible combination of stocks given a stock universe. The ultimate goal is to find the optimal risky portfolio which is the portfolio with the highest excess expected return for each unit of risk. Excess expected return is simply the expected return of the portfolio minus the risk free rate. Risk is measured as the standard deviation of the portfolio returns.

To demonstrate the MVO process, Financial Analyst Warrior will perform the portfolio construction on exchange traded funds (ETFs) which represents a wide array of asset classes. At each step we will provide our own results so you can follow along.

Step 1: Specify the securities or asset classes universe

This is where you select which stocks or asset classes will be included in the analysis. If you are performing a top-down portfolio construction, you will specify which asset classes should be included. If you are building a small capitalization U.S. stocks portfolio, you will select a list of stocks that you are interested in. This list must not be too short since you want to benefit from diversification (elimination of firm-specific risk) but the MVO process will be extremely long and tedious if you do not narrow down your selection. Remember that most diversification effect is accomplished with only 30 stocks (firm-specific risk is almost completely eliminated).

Here are the asset classes selected for our optimized portfolio demonstration:

MVO Figure 1

Step 2: Compute the returns of your selected security

For this step, we will assume you do not have access to expensive finance tools such as a Bloomberg terminal. The easiest free method to get the returns is to download them from Yahoo! Finance .

When you go on Historical Prices, you can download, daily, weekly, monthly or yearly price information for the stock, index or ETF. There are no universal answers with regards to the price frequency to use or how far you should go back in time. I personally like to use monthly price data but the frequency chosen (daily vs. weekly vs. monthly) is less important than the start date you select. Some people argue that you should select data as far back as possible to have a more representative sample. Others think older data is less relevant since market conditions have evolved over time. Yet others prefer selecting the time frame based on the expected investment horizon. For instance, if you plan to build a portfolio to buy a house in five years, you should use five years of price data when computing returns.

Once you have downloaded the prices, compute the holding period return for each time unit. We thus found the daily returns for our 10 asset classes:


Note that the dividends are already included in the adjusted price on Yahoo! Finance so you do not need to include it again.


Once you have all the returns, you can find the expected return by calculating the average return [=AVERAGE]

Step 3: Compute variance and covariance for all pairs

Since the return of a portfolio is the weighted average return of its component stocks, it is relatively easy to compute. However, the risk of a portfolio is less straightforward since you have to take into account the stocks interactions. More specifically, the covariance needs to be computed for all pairs of stocks.

The variance is the volatility (or the average difference from the mean) of an individual stock. Covariance measures both the volatility of the two stocks and how they interact together. This is why the covariance of a pair of securities is measured as the product of each stock’s standard deviation and of their correlation coefficient.


The correlation coefficient is a number between -1 and 1 and measures if two stocks move in the same direction or opposite direction.

In step 3, you will have to compute the covariance of all pairs of stocks in your universe. If you have 5 stocks, you will have 52=25 pairs. Financial Analyst Warrior selected 10 securities so we have 102=100 covariance pairs. To be exact, 10 of those pairs will be a security with itself so it is actually the variance.

You will therefore have to build a variance/covariance matrix such as this:

Covariance Matrix

This matrix can be done in a single step by using an Excel plug-in called “Data Analysis” and selecting covariance. If you don’t have this tool under Data in Excel (2010 version), you can install it by going in File–>Options–>Add-Ins–> and selecting Data Analysis Toolpak (also install Solver Add-in since we will use it later). Once you have installed it and are on the covariance function, you will then simply select the cells where the returns of all your securities are listed and Excel builds the table for you. It actually builds a half table and you will have to fill up the rest but it is simple since the covariance of Gold vs. Russell 2000 is the same as the covariance of Russell 2000 vs. Gold.

Although it is not necessary for the construction of your portfolio, you can also use the Data analysis Excel tool to build a correlation table of your stocks. This allows you to see if you will really benefit from diversification. The lower the correlation coefficient between stocks in your portfolio, the more diversification is possible.

For our portfolio, the correlation matrix looks like this:


This table allows us to see which pairs are strongly correlated (in red) and which have a low correlation coefficient (green). It is interesting to notice that gold is not correlated with anything while the four equity asset classes (S&P 500, Russell 2000, EAFE and Emerging Markets) are strongly correlated.

Step 4: Compute portfolio risk and return

The risk and return of the portfolio will (obviously) depend on the weights of each security in your portfolio. The optimization process will specify the weights but you have to setup your spreadsheet so that you can calculate the standard deviation and expected return given the weights.

The results spreadsheet could look like that:

MVO results

The blue weight cells are the ones that the optimization will play with to find your portfolio. The expected return for each security is the one computed in step 2. The portfolio metrics on the right are all formulas.

a)      Portfolio Variance: This is the most complex calculation of the whole MVO process. There are two ways to perform it: the “manual” way or the “automatic” way. For this MVO, I performed the automatic way by using the MMULT and TRANSPOSE function on Excel but by using the longer “manual” way you will gain a deeper understanding of the concept of portfolio variance.

i.      First, you create another matrix similar to the covariance matrix but without any values in the cells.

ii.      Each cell will be the intersection of two stocks stock A with weight of WA and stock B with weight of WB. The value in each cell will be: WAWBCov(A,B)

iii.      Fill up the table with the above calculation and the portfolio variance will be the sum of ALL the values in the table.

b)      Portfolio Standard Deviation: Squared root of portfolio variance.

c)      Portfolio Expected Return: This is simply the weight of each component times its expected return as calculated in step 2.

Portfolio expected return


d)      Risk Free Rate: T-Bill rate. Since we are working with monthly data we took a monthly rate.

e)      Sharpe Ratio: This is a very important measure since it gives you the return in excess of the risk free rate per unit of standard deviation:

Sharpe ratio

Step 5: Perform optimization

Once the spreadsheet is properly setup with the formulas from step 4, the actual optimization is a piece of cake. To perform it, you will need another plug-in called solver on Excel. If you have a Mac, you may not be able to find this plug-in so I suggest you perform your MVO on a PC.

To start the optimization, go in Data –>Solver.

Solver parameters


a)      Specify constraints.

This is where you will place constraints given your specific need. Here are two potential constraints you may want to use:

  • Sum of the weights =100%. This is a mandatory constraint.
  • No short selling: If you do not want negative weights for your securities, check the “Make Unconstrained Variables Non-Negative” box.
  • Specific weight range: Say we wanted to have at least 20% of the portfolio in investment grade bonds I would add a constraint that makes the investment grade bond weight cell >= 20%. You can therefore specify ranges for each of the security.

b)      Choose what to optimize. Three choices here:

i.      Minimize portfolio risk, regardless of return: Set the “Set Objective” as the variance cell and select “Min”. Excel will solve for the Minimum Variance Portfolio. This is the less risky combination of stocks that can possibly be achieved

ii.      Minimize portfolio risk while achieving a return of x%: You can specify a specific expected return as a constraint and the solver will make sure it is attained while minimizing risk. The resulting portfolio will plot on the Minimum Variance Frontier. To plot the full Minimum Variance Frontier, find the minimum standard deviation for various expected returns and graph then. The result for our portfolio looks like this:

Minimum variance frontier

iii.      Maximize reward to risk (Maximize Sharpe ratio): This is the point where the excess return [ E(r ) – RF] per unit of standard deviation is maximized. To find it, simply ask the solver to maximize the value in the cell where you put the Sharpe ratio formula. This point will be the optimal risky portfolio. The Capital Allocation Line (CAL) is the one that goes from the risk free rate on the y-axis to the optimal risky portfolio.


Efficient frontier


Once you have the weights for your Optimal Risky Portfolio you can reduce the risk as much as you want by mixing that portfolio with the T-Bills. You will then slide along the CAL in accordance with your degree of risk aversion.


The following table shows the results of the MVO performed on our list of ETFs with and without short selling:

MVO final

Although MVO is interesting as an experiment, it can yield a surprising output. For instance, it suggested I keep half of my dough in investment grade bonds and split the rest of the cheddah in emerging markets and gold. Nothing in U.S. and other developed market equities…This is why you should probably not solely rely on your MVO analysis to build your retirement fund. At the very least, you could use your MVO results to back up your claims at the next cocktail party when someone asks you “what’s hot” right now…

CFA® Level 1 Starter Kit

We also invite you to check out our latest CFA® Level 1 Starter Kit.

We created this CFA® Level 1 Starter Kit to help you better prepare for the upcoming CFA® exam.

CFA Level 1 - Starter Kit

We want to help you maximize your study efficiency and avoid the most common mistakes candidates make on the CFA ® Level 1 exam.

CFA ®Level 1 Starter Kit includes:

  • Top 100 Pitfalls (mistakes) to avoid on the CFA ® Level 1 exam (+50 pages PDF eBook)
  • Exam Strategies (9 pages PDF article)
  • Concentration Tips (9 page PDF article)
  • 3 study schedules : 16 weeks, 14 weeks and 10 weeks study plans (customizable in Excel)

By using the resources found in this Study Kit, you will be able to better focus on your studies knowing that you will have enough time to cover everything before the exam. You will also the learn the most frequent errors CFA® Level 1 Candidates make and how to avoid them on the exam day. Learn more.


Back To Top