Recently, I used Modern Portfolio Theory (MPT) framework to create an optimal portfolio. Before I present the results, I will present a brief overview of the steps involved (more details here). Note all of this steps can be performed using Excel.

**Step One:** Data Collection

Get historical data for all the selected equities. Determine the average weekly (or daily) returns and corresponding standard deviation in weekly returns. Find the correlation between selected assets.

** Step Two:** Create Markowitz Efficient Frontier

The portfolio standard deviation is provided by the follow equation:

* σP = sqrt (∑square(wi). square(σi2) + ∑∑ wi .wj.Covij*

Construct six different portfolios with given target returns (0.001, 0.002, etc.) and use the “solver” in excel to find weights such that the standard deviation for the portfolio (expressed above) is minimized. Then, plot these six portfolios with return on y-axis and risk or std. dev on x-axis. The resulting envelope curve is called the “** Markowitz Efficient Frontier**“. All the portfolios on this frontier are efficient in the sense that any portfolio beneath this line will not provide a better risk-return alternative (either the portfolio will have lower return for given risk or higher risk for given return).

**Step Three:** Create Market Portfolio

Market Portfolio is defined as the portfolio with risky assets that provides highest expected return over risk-free rate **per unit of risk** for any available portfolio with risky assets. Or, a portfolio with the maximum sharpe ratio.

**Step Four:** Create Capital Market Line

According to Capital Market Theory, investors who allocate their capital between a riskless security and the risky Portfolio (M) can expect a return equal to the risk-free rate plus compensation for the number of risk units they accept. In other words,

E(RP) = RFR + σP [(E(RM) – RFR)/ σM]

And rearranging the equation above,

σP = (E(RP) – RFR )/[(E(RM) – RFR)/ σM]

For the six portfolios that we plotted on Markowitz efficient frontier, we need to find corresponding portfolio risk given the target expected return for the portfolio from the equation above. And then, we plot the <risk, return> tuples that generate a upward sloping line called “*The Capital Market Line*”

**Step Five:** The Optimal Portfolio

Finally, we are ready for our Optimal portfolio. Optimal Portfolio is represented by point of tangency between the Capital Market Line and the Markowitz efficient frontier

In my scenario, the equities that I selected: AAPL, GOOG, ASIA, NVR and S&P 500 (index fund). The figure above describes graphically the process outlined. And the table below lists the optimal portfolio with equities, their weights and annualized returns.

Bottom line: using MPT, I was able to construct a portfolio with 325% annualized return with 79% volatility risk. That does sound outrageous. Well, the data set includes the returns from the “Great Panic” of 2008. And as you can see from the portfolio weightings, w5 is -302%. That means, shorting S&P 500 massively and compensating by going long on heavily on AAPL and GOOG to an extent.