Creating Optimal Portfolio and The Efficient Frontier

ISSN(E):2522-2260
ISSN(P):2522-2252
Indexing/Abstracting
Teaching Module
Creating Optimal Portfolio and the
Efficient Frontier Using Microsoft
Excel®
Author(s)
Saurav Roychoudhury1
Affiliations
1
Department of Business, Capital University, Columbus, OH,
USA 43209. Email: sroychou@capital.edu
Manuscript Information
Published by Citation in APA Style
Department of Quantitative Methods Roychoudhury, S. (2018). Creating Optimal Portfolio and the
Efficient Frontier Using Microsoft Excel®. Journal of
Quantitative Methods 2(2), 104-136.
University of Management and This manuscript contains references to 10 other manuscripts.
Technology, Lahore, Pakistan
The online version of this manuscript can be found at
https://journals.umt.edu.pk/sbe/jqm/volume2issue2.aspx#
This manuscript has been published under the DOI: https://doi.org/10.29145/2018/jqm/020207

terms of Creative Commons Attribution-
ShareAlike 4.0 International License (CC-BY
SA). JQM under this license lets others
distribute, remix, tweak, and build upon the
work it publishes, even commercially, as long Additional Information
as the authors of the original work are credited
for the original creation and the contributions Subscriptions: editorasst.jqm@umt.edu.pk
are distributed under the same license as
original.
Email Alert: editorasst.jqm@umt.edu.pk
For further information, please visit
http://journals.umt.edu.pk/sbe/jqm/Home.aspx
Creating Optimal Portfolio and the Efficient Frontier | 104
Teaching Module
Creating Optimal Portfolio and the Efficient Frontier

Using Microsoft Excel®
Saurav Roychoudhury1
https://doi.org/10.29145/2018/jqm/020207
Abstract
Portfolio managers and investors strive to achieve the best possible
trade-off between risk and return, and one of the tools they use is
constructing mean-variance efficient portfolios. Finance students
learn about optimal portfolios and efficient frontiers, though it is
difficult to replicate them unless they have access to sophisticated
software. This paper develops a teaching module that uses
Microsoft Excel® to create mean-variance portfolios and traces out
the efficient frontier using real-world data. In the process, the
students learn to determine optimal investment allocations in a
portfolio, select the optimum investment portfolio given investor’s
objectives and preferences and learn about factors that influence
different asset allocations. For multiple assets (N>3), the paper uses
Matrix algebra in Excel®. The paper enables students and investors
to learn how to construct real-world mean-variance efficient
portfolios using Excel®.
Keywords: Optimal Portfolio, Efficient Frontier, Risk, Expected
Return and Risk-free asset.
JEL Classification: G11, A22, A23
1
Department of Business, Capital University, Columbus, OH, USA 43209.
Email: sroychou@capital.edu
Author’s Note: The author gratefully acknowledges an NSF grant # 0618252 on
Development and Dissemination of Computational Science Educational Materials
and Curricula at the Undergraduate Level in the United States. I hereby declare that
this paper is partially extracted from an unpublished teaching module titled “The
Optimal Portfolio and the Efficient Frontier” which I developed as part of this NSF
grant #0618252.
Journal of Quantitative Methods Volume 2(2): 2018

1. Introduction
Finance theory has become increasingly mathematical, but there is a
dearth of computational finance materials at the undergraduate level
which is likely because undergraduate finance and economics tend to
be taught in business schools or under social sciences and more
mathematically oriented courses are in mathematics and natural
science departments. In recent years we have seen an increase in
academic programs that support computational finance but mostly at
the graduate level (Roychoudhury, 2007). One of the first papers
which laid the groundwork for mathematical theorization in finance
was Portfolio Selection by Markowitz (1952). The paper introduced
the Modern Portfolio Theory (MPT) and formulated the concept of
optimal portfolios and the efficient frontier. This paper provides a
teaching tool to create mean-variance portfolios and traces out the
efficient frontier using real-world data and Microsoft Excel®. In the
process, the students learn to determine optimal investment
allocations in a portfolio, select the optimum investment portfolio
given investor’s objectives and preferences and learn about factors
that influence different asset allocations.
The finance industry uses proprietary software that runs into
thousands of dollars in annual license fees that estimate the efficient
frontier and the optimal portfolio. However, Microsoft Excel® has
developed into a powerful tool that can be used to model and do
sophisticated calculations in finance. For example, see Wann (2015),
Boudreaux et al. (2016), and Wann & Lamb (2016). Hess (2005)
finds that "hands-on" use of spreadsheet modeling in class, improves
understanding and retention of the concepts. Incorporating powerful
Excel tools into finance teaching can help students understand the
concepts of finance intuitively, and bridge the gap between financial
theories and real-world applications (Zhang, 2014). Finance faculty at
most leading business schools advocate the use of Excel to prepare
students for the workforce.
Investors and portfolio managers concentrate their efforts on
achieving the best possible trade-off between risk and return. For
portfolios constructed from a fixed set of assets, the risk/return profile
varies with the portfolio composition. Portfolios that maximize the
return, given the risk, or, conversely, minimize the risk for the given
return, are called optimal portfolios. The set of optimal portfolios in

the risk/return plane is called the efficient frontier. In this paper, to

create optimal portfolios in Excel and to trace the efficient frontier,
we use a dataset of US stocks. The stocks prices are one of the most
widely available financial data in the United States. There are several
good websites where students can download price information. Some
of them are Yahoo! Finance, MSN Money, NASDAQ.com, and
Bloomberg.com. In this paper, price information from Yahoo!
Finance has been used. For the data and solutions used in this paper,
refer to the Excel file (JQM_EF_Excel.xls) which is available for
download at http://bit.ly/EFrontier. This paper is organized as
follows; the next section is a review of concepts such as expected
return, risk, and diversification. This is followed by the section 3
where we construct the portfolio model for 2 risky assets, 3 risky
assets and an ‘N’ number of risky assets. Section 4 implements the
model in Excel and section 5 concludes.
2. Overview
2.1. Expected Return
Stock price changes or returns are random variables as the future
returns on a stock are uncertain and unpredictable. A stock can have
significant ‘up’ and ‘down’ movement even within a small time-frame
like a single day. Figure 1 shows how the stock price of Microsoft
stock fluctuated in a single year. For finding an estimate of future
returns on assets such as Microsoft stock, we need to estimate the
expected value of the Microsoft stock returns. Ideally, we would try to
come up with an expected value of the stock by associating the
returns with a probability distribution (very much like the coin-toss
example where the probability of getting ‘heads’ and ‘tails’ is 50%
each).
However, in reality, no model of finance is likely to claim that
investors can find great bets “+$1 million with 99% probability” and
“−$100 with 1% probability.” Such an expected return would be way
out of line. In financial markets, no one knows the correct model of
expected stock returns well enough to know if the stock market can
set the price of the Microsoft stock to offer an expected rate of return
on Microsoft of 7% or 12% a year. As it is difficult to estimate true
expected returns historical return averages are used as proxies. In this
module, we employ the most widely used measure: Expected return

on a stock is the mean return of its historical returns. For example, the
expected mean monthly return of Microsoft would be simply
1 N
E (rmsft )   ri,msft
N i 1
where ri ,msft is the historical monthly return for i th month and
N is the number of months over which the returns are averaged.
The expected return of a portfolio,  p , is the weighted
average of the expected returns on the individual assets in the
portfolio, with the weights being the percentage of the total portfolio
invested in each asset.
E (rp )  s A E (rA )  s B E (rB )     sn E(rn )
n
 p   si  i
i 1
where E (rp )   i is the expected return on the individual

stocks, s i is the weight, and there are n stocks in the portfolio.
2.2. Risk
Statistically, risk measures how dispersed are the outcomes from
the center (mean or expected return). Standard deviation is the
most common measure of portfolio risk. The higher the level of
standard deviation, the more variability between the pay-offs or
returns.
Looking back at our example, we can deduce that the
variance can be expressed as
r   
2
 
2
N
for N observations and the standard deviation is expressed
as the square root of the variance
r   
2

N
When we say something like “this investment is risky” we
generally refer to the downside risk only. That is, to an investor,
the relevant risk of investing in a portfolio is the chance that he

would end up with returns which are less than the expected returns.
There is a subtle difference between this and how we measure risk
statistically (by variance or standard deviation). When we measure
risk statistically, we measure the variability or dispersion around
the mean. We put equal weights to the variability of returns both
above and below the mean. Though the portfolio expected return is
simply the weighted average of the expected returns of the
individual assets in the portfolio, the risky-ness (measured by the
standard deviation,  p ) is not the weighted average of the
individual assets’ standard deviations. The portfolio risk is
typically smaller than the average of the standard deviations of the
individual assets.
2.3. Diversification
Diversification is equivalent to not putting all eggs in one basket. It
is akin to not putting all your money in one risky asset but
allocating your money across a number of risky assets.2 A well-
diversified portfolio will significantly lower the downside risk
without lowering your expected return (Roychoudhury, 2007).
Take the coin-toss example. Suppose the return on $10,000 you
have put in a risky stock depends on the flip of a coin. Heads, it
quadruples in value (becomes $40,000); tails, you lose $10,000.
The expected return is very good at 100%3. Unfortunately, the
downside risk is terrible – there is a 50% chance that you would
lose your $10,000.
As a rule, portfolio risk declines as the number of stocks in
the portfolio increases4. Thus, careful diversification can create a
portfolio that is less risky and earns more on average for the same
degree of risk than any single company stock.
2
In this paper we are looking at stocks as the risky asset. In the real world, the
investor will have other choices like investing in bonds, Bank CDs, real estate,
commodities and derivatives.
3
It is 50%  $40,000  50%  $0  $20,000 or 100% return on the initial
$10,000 investment.
4
In practice, investment managers and finance practitioners find that if you hold
anywhere between 25 and 30 stocks you can capture most of the diversification
benefits. Adding assets beyond a certain number (like 30) does not generate
much incremental benefit as far as reducing portfolio risk is concerned.

3. The Model
We know that it is essential to diversify, but it still does not tell
you how much of each security you should purchase. How do you
determine investment allocations in a portfolio? Where is the best
investment portfolio given the investor’s objectives and
preferences? What would influence different allocations? To
answer these questions, we start with a modified version of the
Markowitz’s (1952) portfolio theory model.
We begin with the simplest example – one period, two
assets, and, normally distributed returns. The model assumes that
investors are risk-averse, meaning that if there are two assets that
offer a same expected return, the investor will prefer the less risky
asset. An investor will undertake increased risk only if
compensated by higher expected returns. The model also assumes
that the investor’s risk-reward preference can be explained entirely
by expected return and volatility (measured by standard deviation
of historical returns). A risk-free asset exists (in the form of US
Treasury Bills), and it is possible to borrow and lend money at the
risk-free rate. All stocks are perfectly divisible (e.g., it is possible
to buy 1 / 1000 th of a share) and there are no transaction costs or
taxes.
We use the following notations
ri  the return (sometimes called rate of return) on asset i
n  number of available assets
r f  the return on the risk-free asset
 i  mean of the return on asset i
 i  the standard deviation of the return on asset i
 i2  the variance of the return on asset i
 ij  covariance between the returns on assets i and j
 ij  correlation between the returns on assets i and j
si  the share of asset i in the portfolio
r  rp  the (rate of) return of a portfolio
   p  the mean of the portfolio return

3.1. Two Risky Assets

We assume that there are only 2 risky assets, A and B, available for
consideration in an investment portfolio. The portfolio return is given
by
r  rA s A  rB s B (1)
The portfolio shares need to add up to one:
s A  sB  1 (2)
Taking expectations of (1):
Er   s A ErA   s B ErB  (3)
yields the mean portfolio return
   A s A   B sB (4)
Based on equation (4), portfolio variance is
 p2   A2 s A2  2 AB A B s A s B   B2 s B2 (5)
which simplifies to:
 P  s A2  A2  (1  s A ) 2  B2  2 s A (1  s A )  A, B  A  B (6)
Combining equations (4) and (5), and using (2) to eliminate

the portfolio shares, provides the feasible combinations of mean and
standard deviation. The portfolio frontier is a plot of these feasible
combinations of overall portfolio risk and returns.
Combining equations (4), (5), and (2) yields the portfolio
frontier for two risky assets:
p
1
( B   A ) 2

 12 (    B ) 2  2  AB  A B (    A ) (    B )   B2 (    B ) 2 
(7)
The equation represents a hyperbola in mean-standard deviation
space.
3.2. Three Risky Assets

For a portfolio consisting of three risky assets A, B, and C, the return

for the portfolio is simply
  s A  A  s B  B  sC C , and the portfolio risk is given as
 P2  s A2 A2  s B2 B2  sC2  C2  2s A s B Cov rA , rB   2s A sC Cov rA , rC   2s B sC Cov rB , rC 
(9)
Please refer to Appendix A1 for a detailed derivation.
3.3. N Risky Assets

As discussed in the background section, as we keep on adding more
and riskier assets, the portfolio risk is expected to go down, but at the
same time, the math also tends to become messier. We resort to
matrix algebra which can represent a lot of data by sorting them into
groups or cohorts which are called rectangular arrays. A brief
refresher of matrix algebra is provided in the mathematical appendix.
We assume here that investors may invest in a total of n risky
assets and that no risk-free asset exists. Short sales are not restricted.
The portfolio frontier, in this case, was rigorously derived by Merton
(1972)5.
Mathematically the portfolio frontier can be found by
minimizing portfolio variance subject to a given expected return. This
creates an envelope portfolio (Benninga, 2014). The dual of this
decision problem does not provide the same solution: Maximizing the
expected return subject to a given portfolio variance only produces
the upper half of the portfolio frontier. The lower half is dominated as
a higher expected return can be found for any possible variance. The
upper half of the portfolio frontier obtained in this manner is called
the efficient frontier for apparent reasons. The envelope is the set of
all envelope portfolios, and the efficient frontier is the set of all
efficient portfolios (Black, 1972). Empirically, if the assumptions
leading to mean-variance analysis are justified, we expect that no
individual’s complete portfolio lies below the efficient frontier.
Consider the following variable definitions:
5
The model used in this paper is a simpler version to suit the level of advanced
undergraduate students.

 
   ij , represents the n x n variance-covariance matrix of the n
asset returns, where
 is a 1 x n column vector of the expected returns,  i .
s represents a 1 x n column vector of the portfolio shares or weights,
si .
1 represents a 1 x n column vector of 1's
 1 represents the inverse of a matrix 
The portfolio frontier is found by minimizing portfolio variance
subject to a given portfolio mean:
Minimize with respect to s:
1 T
s s (10)
2
Subject to the constraints
T s   p (11)
1T s  1 (12)
Thus the portfolio variance is minimized subject to a given
expected portfolio return  p and given that all portfolio shares add up
to 1.
Using the Lagrangian6 method with multipliers  and  for
constraints (11) and (12), respectively, produces the following first-
order condition:
s T    T  1T  0 (13)
Solving for the portfolio weights gives us:
s T *    T  1   1T  1 (14)
6
For students new to Lagrangian multipliers it might be a good idea to look at an
undergraduate Finance and Economics book like Alpha C. Chiang’s
“Fundamental Methods of Mathematical Economics.” A brief overview on
Lagrangian multipliers is written by Steuard Jensen and is available at
http://www.slimy.com/~steuard/teaching/tutorials/Lagrange.html

As,  is positive definite7 we can conclude that s T * does

minimize the variance and that the solution obtained here for the
portfolio shares is unique. In this partial-equilibrium framework,
nothing guarantees that all portfolio shares are positive or below one.
Post-multiplying equation (13) by s and using constraints (11)
and (12) gives:
 p2    p   (15)
Post-multiplying equation (18) by  : and separately by 1

yields the following two equations:
 p    T  1    1T  1  (16)
1    T  1 1   1T  1 1 (17)
Define:
A   T  1  , B  1T  1 1 , C  1 T  1  , and D  AB  C 2
(18)
Note that A, B, C, and D are scalars that depend only on the
constant parameters of the set of available assets. It is now
straightforward to solve for  and  from equations (16) and (17):
B  C

p
(19)
D
A  C 

p
(20)
D
Plugging (19) and (20) into equation (19) yields an explicit expression
of the portfolio frontier:
 2

B 2
p  2 C p  A
(21)
p
D
The portfolio frontier is a hyperbola in mean-standard
deviation space as in the case of 2 risky assets. The reason is intuitive:
7
For the variance-covariance matrix to have an inverse, it requires that no two
assets are perfect substitutes; so that the matrix is not singular.

Any two points on the frontier can be thought of as mutual funds that
are individual assets. Taking different combinations of these two
assets must trace out a hyperbola based on case with 2 risky assets,
but there is no way that this hyperbola can be different from the n-
asset frontier as it can, at no point, lie to the left of the n-asset frontier
(or the frontier wouldn’t be a true frontier). Thus, once we understand
the 2 risky assets case, we can deduce logically that the n-asset
frontier must be a hyperbola, as well (Roychoudhury, 2007). Based
on the above formula it is now easy to find the minimum variance
portfolio of risky assets. Differentiating equation (21) with respect
to  P and setting it equal to zero yields  P  C / B so that we
obtain  p2  1 / B (after using the definition of D in (22)).
4. Solution Methodology and Implementation

Given a potential set of assets, the efficient frontier can be created by
portfolio optimization. Portfolio optimization involves a mathematical
procedure called quadratic programming in which two objectives are
considered: Maximizing return and minimizing risk. It is called a
Quadratic Programming Problem (QPP) as the objective function
consists of second-degree terms. The two objectives are considered:
(i) Minimize risk given a specific return, or (ii) maximize return for a
given level of risk. The QPP for both objectives is generally subjected
to the following constraints:
a) The weight of funds invested in different assets must add to unity;
b) There is no short sale provision.
The constraints imposed on the problem are neither
exhaustive nor irrevocable. There might be a maximum limit on
which an investor can purchase one stock. Similarly, we can modify
the short sales constraint to allow short selling.
Portfolios on the mean-standard deviation (or variance)
efficient frontier are found by searching for the portfolio with the least
variance given some minimum return. Repeating this procedure for
many return levels generates the efficient frontier.
The QPP can be solved using constrained optimization
techniques involving calculus or by computational algorithms
applicable to non-linear programming problems. Of the two

approaches, the non-linear programming is more versatile as it is

comfortable handling both equality and inequality constraints.
We start with a simple Excel exercise in tracing a portfolio
frontier and then move to the constrained optimization techniques.
Consider two possible investments, say, JP Morgan Chase
(JPM) and Oracle Corporation (ORCL). We have about six years or
seventy-two months’ worth of data8 obtained from Yahoo! Finance.
The returns are arranged in ascending order of dates shown in figure
2.
It is a good idea to start by defining your inputs into named
Arrays. With Arrays that you do not have to select the entire range
every-time, you want to calculate a formula using JPM. For the mean
return, you can write =AVERAGE(JPM), for standard deviation9 we
can write it as =STDEVP(JPM), and for a variance, =VARP(JPM)
and so on.
Our objective is to trace the portfolio frontier in mean-
standard deviation space and identify the efficient frontier and the
minimum variance portfolio. For simplicity, assume that both stocks
can only have positive weights and there is no short-selling.
Step 1: Using AVERAGE(), STDEVP() and VARP(), find the mean,
standard deviations and the variance of JPM and ORCL. Also find the
correlation coefficient and the covariance between the two assets,
JPM and ORCL using the CORREL() and COVAR() functions.
Step 2: Start with any portfolio weights; say your entire money is
invested in ORCL. So, the weight of ORCL is 100%, and JPM is 0%
(remember the weights must add up to 1).
Step 3: Use equations (4) and (5) from the previous section to
compute the portfolio means and portfolio variance. To calculate
portfolio standard deviation, simply take the square root of the
8
There is no single consensus on how far back you should go to estimate the
expected returns and volatility, 5 to 6 years or 60 to 72 months is popular in
empirical finance because that is the time frame used to calculate another
important variable called the “beta” which is measure ‘s’, a measure of a stock's
volatility in relation to the entire market.
9
Note that we use STDVEP instead of STDEV. The excel function STDEVP
refers to the population standard deviation whereas STDEV measures the
sample standard deviation. Similarly we use VARP instead of VAR.

portfolio variance. These portfolio values correspond to the portfolio

weights of 0% in JPM is 100% in ORCL.
Step 4: To trace the portfolio frontier we vary the weights of one of
the assets, say JPM (the weight of ORCL will automatically vary as
their sum has to add up to 1). In our example, we create a column
starting from cell B17 (see the screen-shot below) and put weights of
JPM in increments of 5% (you can vary weights in smaller or larger
increments). Cell C17 corresponds to the portfolio variance formula;
Cell D17 is the square root (=SQRT) of the variance in cell C17. Cell
E17 gives the portfolio mean using the formula (4) as before. We
copy the columns C17:E17 till the row corresponding to JPM’s
weight of 100%. The screen-shot in figure 3 shows the columns and
the formulas used.
Step 5: We now have the data to trace out the frontier. Go to the
Chart Wizard or “Insert-Chart.” Select the XY (scatter) and then the
second option on the right which is “Data points connected by
smoothed lines.” Select columns corresponding to the Portfolio Risk
(standard deviation) and portfolio return as shown in the screen-shot
in figure 3. To get a smooth-looking regular shaped frontier, you may
have to vary the units of the X and Y axis under chart options.
We have now successfully created a real-world portfolio
frontier with two risky assets, JPM and ORCL. From figure 3, we can
see that the efficient frontier is traced by the locus of points from “A”
to “B.” Any portfolio like “C” which lies below the minimum
variance portfolio in the picture (or to the south of the minimum
variance portfolio) is dominated by the minimum variance portfolio
and all portfolios which lie to the northeast of it.
How would you interpret the finding? No risk-averse investor
should buy portfolio in the region below A or anywhere else, except
for points on the efficient frontier traced from A to C. Depending on
the risk-taking ability the individual investor can choose between
portfolio allocation A (more risk-averse investor) to allocation B (less
risk-averse investor). Remember, you can only expect a higher return
on the efficient frontier if you are willing to take more risk.
4.1. Finding the Minimum Variance Portfolio (More Accurately)
To find the exact location of the minimum variance portfolio, we
solve the QPP using constrained optimization techniques (we can

very well do it after the pain we went through deriving all the
formulas). We start with the non-linear programming method using
solver and then move to the calculus approach.
4.1.1. Minimum Variance Portfolio using Solver
Excel solver is a powerful tool for optimization10 and produces
targeted results for your models (the technical jargon is “calibrate
your model”). 11 Microsoft solver can be added by selecting “Tools-
Add-ins” and choosing the “Solver Add-in.”
By using the solver, we can calculate the minimum variance
portfolio. The screen-shot in figure 4 shows the solver dialog box. In
this box, we have asked the solver to minimize the variance in cell
B12, by changing the weight of JPM (cell B17) in the portfolio. In
order to ensure that the weights of JPM and ORCL are positive we
put a non-negative constraint by ensuring the weight of JPM and
ORCL’s weight are greater than equal to zero. The relation formula 1-
B7 in cell C17 corresponding to ORCL’s weight ensures that the sum
of the two weights does not exceed 1.
Clicking on “Solve” in the solver dialog box gives (see
screen-shot below), the minimum variance portfolio with 64.9%
invested in JPM and 35.1% in Microsoft. The minimum Variance
portfolio corresponding to 7.01% risk (standard deviation) and 0.81%
returns matches our result obtained previously in “Tracing a Portfolio
Frontier.”
4.1.2. Minimum Variance Portfolio using Calculus Method
Recall the section where we derived the minimum variance portfolio
for 2 risky assets (all those derivations coming to some use now).
Equation (8) gave us the optimal portfolio weight as
 B2   A, B  A  B  B2  CovrA , rB 
sA  
 A2   B2  2  A, B  A  B  A2   B2  2 CovrA , rB 
10
Solver tool uses the Generalized Reduced Gradient (GRG2) nonlinear
optimization code developed by Leon Lasdon, University of Texas at Austin,
and Allan Waren, Cleveland State University.
11
A nice book on using excel tools like Solver and Goal-seek and its application
in Finance is by Simon Benninga, “Principles of Finance with Excel”.

We plug the values of variance and covariances in cell B26 to get the
optimal weight of JPM. Implementing the formula in Excel gives the
same answer as that given by solver (see figure 5).
4.2. Effect of Change in the Correlation Co-efficient in a Portfolio
Stock A and stock B are two risky assets which have the following
characteristics. Stock A has an expected return of 3% but a standard
deviation of 25%, while for stock B, the respective numbers are 2%
and 15%. The correlation coefficient between the two assets is 0.54.
Let us see what happens if the correlation coefficient changes to two
extremes; perfectly negatively correlated and perfectly positively
correlated.
We trace the portfolio frontier using the same methodology as
in “Tracing a Portfolio Frontier” section. We do it for three different
values of the correlation coefficient  AB . For  AB = 0.54;  AB  1
and for  AB =+1 as shown in figure 6.
There is a trick to superimpose all the three graphs in the same
diagram. Select any graph, copy it and paste it over the other graph, it
should clearly superimpose if the size of the graph and the axis are
identical. Do the same for the third graph, and we have a graph very
similar to figure 3 in the Model section. The screen-shot below (figure
6) of our example is shown below. We can observe that the best
diversification can be obtained for  AB  1 .
For a detailed derivation and explanation of the effect of a
change in correlations, refer to Appendix A2.
4.3. Minimum Variance Portfolio for Three Risky Assets
Let us pick a third asset, Haliburton (HAL), which would give us a
three asset portfolio with ORCL and JPM. The primary objective is to
find the minimum variance portfolio and the weights of the three
assets which correspond to the minimum variance portfolio
4.4. Solving the QPP: Minimizing Portfolio Risk Subjected to the
Constraints
 The weight of funds invested in different assets must add to unity;
 There is no short sale provision.

We specify the first constraint by ensuring that the weight of

an asset is one minus the sum of the weights of JPM and HAL. To
incorporate the second constraint, we specify that the weights are
non-negative in the solver dialog box as shown in figure 7. We set the
target cell as B12 which corresponds to the portfolio variance and
allow the solver to change the portfolio weights (Cells B17 and C17)
to solve for the minimum variance portfolio.
The results (see figure 7) are reflected in cells B11 for
expected return on the portfolio, cells B12 and B13 for minimum
variance and the minimum standard deviation. The new portfolio
weights are displayed in cells B17:D17. The minimum variance
portfolio is characterized by an expected return of 0.90% and risk of
6.84% with portfolio weights of 55.8% for JPM, 30.92% for ORCL
and 13.28% for HAL.
4.4.1. Using Matrix Algebra
Formulas become lengthy and complicated as more assets are added
to the portfolio. Excel has functions which allow us to do basic matrix
operations like addition, subtraction, matrix multiplication, inverse,
and transpose. We already started the section by naming the data
inputs into arrays, which is the basic building block of matrix algebra.
We name the following matrices as defined in the model section
under “N Risky Assets.” We can redo the efficient frontier for 3 risky
assets and recreate the whole model using Matrix algebra. The results
are available in figure 8.
4.4.2. 5 Risky Assets
Refer to the file “Portfolio_optimization_Matrix.xls” for complete
solution and details
 is an 1 x 5 column vector of mean returns of 5 stocks. The array is
named “mu.”
s represents a 1 x 5 column vector of the portfolio shares , the array
is named “s.”
 
   ij , represents the 5 x 5 variance-covariance matrix of the n
asset returns, the array is named “Sigma.”
1 represents a 1 x 5 column vector of 1's, named “i.”

 1 represent the inverse of a matrix  , and can be represented by

Excel’s matrix inverse function “MINVERSE(Sigma),” where
“Sigma” is the named variance-covariance matrix.
For matrix multiplication we use “MMULT(),” and for
transposing matrices, we use the “TRANSPOSE()” function. For
details on matrix functions in Excel refer to Excel’s Help menu.
When you enter a matrix algebra formula in Excel, remember to press
CTRL + SHIFT+ ENTER together to execute the formula. Simply
pressing ENTER would give an error.
Rewriting equations (10), (11) and (12) we have the
corresponding equations (with *) in Excel notation the QPP is given
as:
Minimize with respect to s:
1 T
s  s =MMULT(TRANSPOSE(s),MMULT(Sigma,s)) (10*)
2
Subject to the constraints
 T s   p {=MMULT(TRANSPOSE(mu),s)} (11*)
1T s  1 {=MMULT(TRANSPOSE(I), s)} (12*)

We set the target cell in the Solver dialog box equal to
equation (10*) and specify the constraints as in equations (11*) and
(12*) in the “Subject to Constraints” box. Select the range of the
portfolio weights of the 5 risky assets (F4:F8) as the cells that would
be changed by Solver to reach the optimization solution.
The solver solution is shown in figure 9. The optimal portfolio
risk (as measured by standard deviation) is 4.79%, and the
corresponding portfolio expected monthly return is 0.6%. The
weights in the optimal portfolio are also shown in cells F4 to F8.
4.4.3. Using Calculus Method
Refer to the case of “N Risky Assets” in the model section. We start
by re-writing the formulas defined in the model section under “N
Risky Assets” in Excel form. We continue with the same worksheet
in “Portfolio_optimization_Matrix.xls.”
A   T  1 

as{=MMULT(MMULT(TRANSPOSE(mu),MINVERSE(Sigma)),m
u)
B  1T  1 1
as {=MMULT(MMULT(TRANSPOSE(I),MINVERSE(Sigma)),i)}
12
C  1 T  1 
as {=MMULT(MMULT(TRANSPOSE(I),MINVERSE(Sigma)),mu,
and, D  AB  C 2
as =A*B-(C.^2)
For the return on the minimum risk portfolio we
take  P  C / B as =C./B in Excel notation, and for minimum
standard deviation, we take the square-root of
 p2  1 / B in Excel notation as =SQRT(1/B). Figure 10
shows the solution using the calculus method, and it matches the
solution by the solver method.
For the equation of the portfolio frontier in the mean-standard
deviation space, we take the square-root of equation (21)
 p2 
B 2
p  2 C p  A
(21)
D
As, =(((B*(E39)^2-(2*C.*(E39))+A)/D)^(1/2)) in Excel notation,
where the cell E39 corresponds to a value of the portfolio expected
return.
We can trace the portfolio frontier by selecting different
values of expected return and calculating the corresponding
standard deviation using the above formula. Note: In solving using
the calculus method we do not consider the assumption anymore
that there are no short sale constraints. Figure 10 also shows the
graph of the frontier. Refer to the worksheet
“Portfolio_optimization_Matrix.xls” for more description on how
to select different values portfolio expected returns.
The line tracing the points from the minimum variance
portfolio A to B is the efficient frontier. A risk-averse investor will
never hold a portfolio which is to the southeast of point A. Any point
to the southeast of A such as C would have a corresponding point like
12
Excel does not allow using “C” to name arrays. We use “C.” in place of “C”.

D which has a higher expected return than C with the same portfolio
risk. Clearly, point D will dominate point C. The optimal portfolio is
the portfolio allocation (or points on the efficient frontier)
corresponding to the investor’s risk preference. More risk-averse
investors would choose points on the efficient frontier that is closer to
point A. Similarly, an investor who is less risk-averse or can handle
more risk would choose a portfolio closer to point B.
5. Conclusion
Creating optimal portfolios and tracing the efficient frontier is a skill
required for a student in finance. Microsoft Excel® has developed into
a powerful tool that can be used to model and do sophisticated
calculations in finance. This paper is a teaching module that uses
Microsoft Excel® to create mean-variance portfolios and traces out the
efficient frontier using real-world data. This paper could be used as an
integrated part of a computational finance course or a stand-alone
component within the typical investments or security analysis and
portfolio management course in finance. If the paper suggestions are
followed, the students will be able to build a real-world investment
portfolio of risky assets using modern portfolio theory techniques. The
students should also be equipped to make modifications to the portfolio
if some real-world variable changes or if some assumptions of the
model are relaxed. The investors could also use this modeling
framework to come up with optimal portfolios from a fixed set of risky
assets.
References
Benninga, S. (2014). Financial Modeling(4th Ed.). Cambridge, MA:
The MIT Press.
Black , F. (1972). Capital market equilibrium with restricted
borrowing. Journal of Business, 45(3), 444-455.
Boudreaux, D., Das, P. & Rao S. (2016). Bootstrap simulation with
spreadsheet application. Journal of Economics and Finance
Education, 15(1), 1-8.
Hess, K. (2005). Spreadsheet-Based Modelling for Teaching Finance
and Accounting Courses Retrieved from
http://ssrn.com/abstract=378680.http://dx.doi.org/10.2139/ssr
n.378680

Markowitz, H., (1952). Portfolio selection. Journal of Finance, 7(1),

77–91. https://doi.org/10.1111/j.1540-6261.1952.tb01525.x.
Merton, R. C. (1972). An analytical derivation of the efficient portfolio
frontier. Journal of Financial and Quantitative Analysis, 7(4),
1851-1872. https://doi.org/10.2307/2329621.
Roychoudhury, S. (2007). The Optimal Portfolio and the Efficient
Frontier, National Science Foundation Working paper
#DUE 0618252.
Wann, C. R., & Lamb N. H. (2016). Bond duration: Constructivist
learning using Excel. Journal of Economics and Finance
Education, 15(1), 30-42.
Wann, C. (2015). Black-Scholes option pricing: Implementing a hands-
on assignment using Excel. Journal of Economics and Finance
Education, 14(1), 22-30.
Zhang, C. (2014). Incorporating powerful excel tools into finance
teaching, Journal of Financial Education, 40(3/4), 87-113.

FIGURES
Figure 1: Microsoft Stock Price Movement from Jan 1, 2017, to

Dec 20, 2017.

Figure 2: Time-series Returns of 2 Risky Assets, JP Morgan

Chase (JPM) and Oracle (ORCL). Data is on monthly returns
from 2001-2007. The data is available from the “Master Data”
tab in JQM_EF_Excel.xls and available for download at
http://bit.ly/EFrontier.

Figure 3: Solving and Graphing for the Efficient Frontier for 2

Risky Assets, JP Morgan Chase (JPM) and Oracle (ORCL). Data
is on monthly returns from 2001-2007. The solution is available
from the “Tracing Portfolio Frontier” tab in JQM_EF_Excel.xls
and available for download at http://bit.ly/EFrontier

Figure 4: Using Solver tool in Microsoft Excel® to Find the

Minimum Variance Portfolio for 2 Risky Assets, JP Morgan
Chase (JPM) and Oracle (ORCL). Data is on monthly returns
from 2001-2007. The solution is available from the “2 Assets -
Solver” tab in JQM_EF_Excel.xls and available for download at
http://bit.ly/EFrontier

Figure 5: Using the Calculus Method to Find the Minimum

Variance Portfolio for 2 Risky Assets, JP Morgan Chase (JPM)
and Oracle (ORCL). The figure also shows the solver solution
from Figure 5. Data is on monthly returns from 2001-2007. The
solution is available from the “2 Assets - Solver” tab in
JQM_EF_Excel.xls and available for download at

Figure 6: Effect of Correlation Co-efficient on Portfolio Frontier

for 2 Risky Assets. The solution is available from the
“CORRELATIONS” tab in JQM_EF_Excel.xls and available for
download at http://bit.ly/EFrontier

Figure 7: Minimum Variance Portfolio for 3 Risky Assets using

Solver. The 3 risky assets are JP Morgan Chase (JPM) , Oracle
(ORCL) and Haliburton (HAL). Data is on monthly returns
from 2001-2007. The solution is available from the “Minimum
Variance – 3 assets” tab in JQM_EF_Excel.xls and available for
download at http://bit.ly/EFrontier

Figure 8: Minimum Variance Portfolio for 3 Risky Assets using

Matrix Algebra and Solver. The 3 risky assets are JP Morgan
Chase (JPM), Oracle (ORCL) and Haliburton (HAL). Data is on
monthly returns from 2001-2007. The solution is available from
the “Matrix Algebra – 3 assets” tab in JQM_EF_Excel.xls and
available for download at http://bit.ly/EFrontier
Figure 9: Minimum Variance portfolio for 5 risky assets using

Matrix Algebra and Solver. Data is on monthly returns from
2001-2007 in JQM_EF_Excel.xls and available for download at

Efficient Frontier of 5 risky Assets
1.20%
B
1.00%
Expected Return (monthly)
0.80%
0.60% A
0.40%
0.20%
0.00%
4.70% 4.80% 4.90% 5.00% 5.10% 5.20%
Standard Deviation (Risk)
Figure 10: Efficient Frontier for 5 risky Assets using the Calculus
Method. Data is on monthly returns from 2001-2007 in
JQM_EF_Excel.xls and is available for download at

APPENDIX A
A1. Derivation of portfolio Expected return and variance for a
portfolio consisting of three risky assets A, B and C
The return for the portfolio is simply
r  s A rA  s B rB  sC rC
Taking expectations on both sides of the above equation we get
Er   s A ErA   s B ErB   sC ErC 
This can be re-written as
  s A  A  s B  B  sC  C
The notations have their usual significance.
The variance of the portfolio can be written as,
Var (r )  Var (s A rA  s B rB  sC rC )
Let X = s ArA  sB rB ; then we can re-write the above equation as13
Var (r ) 
Var ( X , sC rC ) = Var ( X )  2 Cov ( X , sC rC )  Var (sC rC ) (A1.1)
Take the first term on the left-hand side, and we get
Var ( X )  Var (s A rA  s B rB )  Var (s A rA )  2 Cov (s A rA , s B rB )  Var (s B rB )
= s A2 Var (rA )  2s A s B Cov (rA , rB )  s B2 Var (rB ) (A1.2)
Now, take the second term on the right-hand side of (A1.1)

2 Cov ( X , sC rC )  2 Cov (s A rA  s B rB , sC rC )
Using the property of covariance14, we can expand the right-hand
side of the above expression as
 2s A sC Cov (rA , rC )  2s B sC Cov (rB , rC ) (A1.3)
similarly we get the third term of (A1.1) as
13
For any X and Y, Var ( X  Y )  Var ( X )  2 Cov( X , Y )  Var (Y )
14
Cov(aX 1  bX 2 , Y )  a Cov( X 1 , Y )  b Cov( X 2 , Y )

Var (sC rC )  sC2 Var (rC ) (A1.4)
Let  A2  Var (rA ) ,  B2  Var (rB ) and  C2  Var (rC ) .
Substituting the values of (A1.2), (A1.3) and (A1.4) in (A1.1) we

have the portfolio variance for three risky assets
 P2  s A2 A2  s B2 B2  sC2  C2  2s A s B Cov rA , rB   2s A sC Cov rA , rC   2s B sC Cov rB , rC 
A2. Effect of the Correlation Coefficient on the shape of the

portfolio frontier
The shape of the portfolio frontier for two risky assets, A and B,
depends on the degree of correlation between the two assets. The
correlation between the risky assets is a crucial aspect of any portfolio
decision, so to get an idea of the general shapes of the portfolio
frontier that are possible, we explicitly consider three extreme
assumptions about the correlation between the returns of assets A and
B. The corresponding figure for reference is A2.1 at the end of
appendix A2. (For a more detailed description see Roychoudhury,
2007 and Benninga, 2014)
 1 (i)
Here , the assets are perfectly correlated. Equation (5) now simplifies
to:
 p2  s A2 A2  s B2 B2  2s A s B A B
 p  s A A  s B B (A2.1)
Figure A2.1 depicts that the portfolio frontier becomes a
straight line sloping up from the point where s A 0 to the point
where s A  1 . The dotted lines indicate the opportunities again when
short sales are permitted. When the returns on the risky assets are
perfectly correlated, no diversification benefits occur and combining
the assets will just lead to a linear combination between the extreme
positions of putting the whole portfolio in either of the assets.
  1 (ii)
The assets are perfectly negatively correlated. Equation (5)

becomes:
 p2  s A2 A2  s B2 B2  2s A s B A B

 p  s A A  s B B (A2.2)
Note that, strictly speaking, the absolute value should also be

taken in equation (A2.1) if short sales are allowed. Diversification
benefits are maximal due to the negative correlation between the asset
returns.
 0 (iii)
The assets are uncorrelated. One may think that this implies
that diversification is not possible; in fact, the benefits of
diversification are quite clear in this case. It is one of the basic
insights necessary to understand portfolio choice. Even though the
middle term in equation (5) drops out due to this assumption, the
analysis, in this case, is substantially more complex than in the
previous two cases. Equation (5) becomes:
p  s 
2
A
2
A  s B2  B2  (A2.3)
Consider the mean/standard deviation tradeoff in this case

derived from equations (4) and (A2.3). The slope of the frontier can
be written as:
d d / ds A  A  B
  (A2.4)
d p d / ds A ( s A A  s B B2 ) /  p
2
If we assume that:  A   B and  A   B , the sign of the

slope in the above equation depends on the denominator. It is easy to
see in figure A2.1 that at some point the slope is vertical. The
portfolio that produces this point is called the minimum variance
portfolio. Further, at the fully undiversified point where s1 = 0, the
slope must be negative. Thus, starting from this undiversified point;
more diversification is beneficial for every investor with mean-
variance preferences: Expected return rises while standard deviation
falls.
It is clear that the portfolio frontier in case (iii) lies between
the frontiers of cases (i) and (ii). It can be shown that this is true for
the general case as well. For general correlation between assets 1 and

2, it is also true that the portfolio frontier has the same hyperbolic
shape as in case (iii).
Figure A2.1: Portfolio Frontiers for 2 Risky Assets, A and B. The

shape of the portfolio frontier depends on the value of the
correlation coefficient between the two assets, A and B.

Creating Optimal Portfolio and The Efficient Frontier

Uploaded by

Copyright:

Available Formats

Creating Optimal Portfolio and The Efficient Frontier

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Creating Optimal Portfolio and The Efficient Frontier

Uploaded by

Copyright:

Available Formats

ISSN(E):2522-2260

This manuscript has been published under the DOI: https://doi.org/10.29145/2018/jqm/020207

Creating Optimal Portfolio and the Efficient Frontier

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

the risk/return plane is called the efficient frontier. In this paper, to

Journal of Quantitative Methods Volume 2(2): 2018

where E (rp )   i is the expected return on the individual

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

3.1. Two Risky Assets

Combining equations (4) and (5), and using (2) to eliminate

Journal of Quantitative Methods Volume 2(2): 2018

For a portfolio consisting of three risky assets A, B, and C, the return

3.3. N Risky Assets

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

As,  is positive definite7 we can conclude that s T * does

Post-multiplying equation (18) by  : and separately by 1

Journal of Quantitative Methods Volume 2(2): 2018

4. Solution Methodology and Implementation

Journal of Quantitative Methods Volume 2(2): 2018

approaches, the non-linear programming is more versatile as it is

Journal of Quantitative Methods Volume 2(2): 2018

portfolio variance. These portfolio values correspond to the portfolio

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

We specify the first constraint by ensuring that the weight of

Journal of Quantitative Methods Volume 2(2): 2018

 1 represent the inverse of a matrix  , and can be represented by

1T s  1 {=MMULT(TRANSPOSE(I), s)} (12*)

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

Journal of Quantitative Methods Volume 2(2): 2018

Markowitz, H., (1952). Portfolio selection. Journal of Finance, 7(1),

Journal of Quantitative Methods Volume 2(2): 2018

Figure 1: Microsoft Stock Price Movement from Jan 1, 2017, to

Journal of Quantitative Methods Volume 2(2): 2018

Figure 2: Time-series Returns of 2 Risky Assets, JP Morgan

Journal of Quantitative Methods Volume 2(2): 2018

Figure 3: Solving and Graphing for the Efficient Frontier for 2

Journal of Quantitative Methods Volume 2(2): 2018

Figure 4: Using Solver tool in Microsoft Excel® to Find the

Journal of Quantitative Methods Volume 2(2): 2018

Figure 5: Using the Calculus Method to Find the Minimum

Journal of Quantitative Methods Volume 2(2): 2018

Figure 6: Effect of Correlation Co-efficient on Portfolio Frontier

Journal of Quantitative Methods Volume 2(2): 2018

Figure 7: Minimum Variance Portfolio for 3 Risky Assets using

Journal of Quantitative Methods Volume 2(2): 2018

Figure 8: Minimum Variance Portfolio for 3 Risky Assets using

Figure 9: Minimum Variance portfolio for 5 risky assets using

Journal of Quantitative Methods Volume 2(2): 2018

Efficient Frontier of 5 risky Assets

Journal of Quantitative Methods Volume 2(2): 2018

Now, take the second term on the right-hand side of (A1.1)

similarly we get the third term of (A1.1) as