Portfolio Optimization with Three Stocks
Dr. Jang H Cho
You have $130,000. Due to your risk aversion, you decided to allocate $30,000 on risk-free
assets (such as, Treasury bills or Certificates of deposits) and $100,000 on risky assets.
Select three stocks. It is recommended that your three stocks are from different industries to
diversify your portfolio as a top-down portfolio management approach. Although this guide
uses three stocks, you can apply the methodology explained here for many stocks. When you
select the three stocks, think about the style of the stocks, specifically, whether they are
growth stocks, value stocks or blend stocks (aka core stocks). From school library, you can
use Morningstar and search companies to see their style.
For example, as of April 2017, Exxon Mobile is Blend (core) stock. See below the company
profile from Morningstar.
Once you selected your three stocks based on top-down approach with consideration of the
style of the stock and their performance and market price, etc. What is the next then? You
have to decide how much you will allocate for each stock with the total $100,000 on the
entire risk portfolio (the three stocks).
1
These instructions will help you create an efficient portfolio frontier with three stocks using
Excel.
Follow the methodology and report the excel sheet that contains your portfolio optimization.
(No need of Word document.)
Upload your work online on Canvas assignment page.
Your excel sheet should include at least the ones with the thick frame as you can see them in
this guide. For example, see below.
Don’t forget to show the investment opportunity set graph as shown on page 8.
The guide starts from the next page.
2
1.
Collect the price data (“adjusted closing prices” if you use Yahoo.) for the three stocks, (in this case
MSFT, XOM, DIS), where they are Microsoft, Exxon Mobile and Walt Disney, respectively.
We want data that are sufficient in frequency and duration, so monthly data for the last 5 years or 3
years worth of weekly data is recommended. This data can be easily collected from Google or Yahoo
Finance. This example uses monthly data.
2.
Copy the price data into three
columns in Excel and calculate the
return between periods (in this
example, between months) as
follows:
Rt = LN(Pt/Pt-1)
Do this for each successive pair of
returns for each of the three stocks.
3.
Calculate the mean return of each stock using the MEAN function and create three excess returns
columns for each stock by subtracting the mean return from each of the individual period returns.
ERt = Rt - Rmean
3
4.
We are going to now calculate the variance-covariance matrix for these returns using the matrix
operations built into Excel.
Create a 3x3 matrix and label the rows and columns.
Highlight the cells of the table and, with the highlighted
cells, use the following command to matrix-multiply the
excess returns:
=MMULT(TRANSPOSE(I3:K69),I3:K69)/(COUNT(I3:I69)-1)
Then press either Ctrl+Shft+Enter or Cmd+Enter
depending on whether you use Mac or PC. The variance-
covariance matrix should fill in completely.
The entries along the main diagonal of the matrix represent the variance of each stock return, while
the off-diagonal entries are the covariances of the respective corresponding stocks. Use the square
roots of the variances to find the standard deviation of each stock.
For example:
Cov(RXOM, RDIS) = 0.00184
Var(MSFT) or 2MSFT = 0.00388 then MSFT = 0.00388 = 0.06229
4
5.
This part explains only concepts on how to determine the frontier coordinates (or, investment
opportunity set). You can plot the three coordinates of the return versus the risk of each company:
see the three dots below in the figure. Now imagine the frontier created by the combination of
different weights of each asset.
You don’t know how to determine the
frontier sets now. You will learn how
to determine the frontier in the
following parts in this document.
At least, you know there do exist this
frontier by imagination.
Now also imagine two portfolios 1 and 2
on the frontier, which we will combine
later to create the efficient frontier for all
three assets.
There must be the lines (capital allocation
lines: “CAL”) that are tangent to the
portfolios 1 and 2.
It is not difficult to know that there are
respective constants on the y-axis.
To do: have a low constant (like -0.05)
with your choice and a high constant
(like 0.1).
Try to understand the fact that the two
constants determine the portfolio 1 and 2
on which the CALs touch.
If you recall, the Sharpe ratio is the slope of the capital allocation line (CAL). Keeping that in mind,
we can see that, in the first portfolio, the Sharpe ratio is maximized. The Sharpe ratio is minimized
with the Portfolio 2: the slope is maximum negative with the Portfolio 2.
To help us create these portfolios 1 and 2 and the frontier curve, we will use the “Solver” in Excel.
5
6. Use the following diagram as reference for the next steps
Set up the table as shown in N10 – Q16 with the given constants. You have to command
“=sum(P13:P15)” for P16 cell. Similarly, “=sum(Q13:Q15)” for Q16 cell.
Although we will use the Excel Solver to find
the optimal weights on the portfolios, we
need to seed the table with some initial
weights. A reasonable set of initial weights
might be an equal percentage (0.33) in each
asset, although any combination can be used
so long as the sum is equal to 1.
Calculate the mean, variance, standard deviation, covariance, and correlation for the two portfolios.
We will fill in the cells in P19 ~ Q24 as follows. Note that when you execute the commands, click
Ctrl+Shft+Enter (or Ctrl+Enter) on PC and Cmd+Enter on Mac.
Mean: =MMULT(TRANSPOSE(P13:P15),S5:S7) then as above press [Ctrl+Enter]
Variance: =MMULT(MMULT(TRANSPOSE(P13:P15),N5:P7),P13:P15) [Ctrl+Enter]
Standard Deviation: =SQRT(P20)
Covariance: =MMULT(MMULT(TRANSPOSE(P13:P15),N5:P7),Q13:Q15) [Ctrl+Enter]
Correlation coefficient () = (Covariance)/(12): =P22/(P21*Q21)
The Sharpe ratio is calculated as the (portfolio mean – intercept)/(portfolio std dev)
Sharpe Ratio: =(P19-P11)/P21
6
7.
Recall we gave the initial weights of 0.333 in the cells P13 ~ Q15. We now use the Solver to find the
actual optimal portfolio weights.
For the first portfolio, we are looking
to maximize the Sharpe Ratio.
The target cell is the Sharpe ratio,
which the Solver will try to maximize
by changing the values within the
cells that contain the portfolio
weights, subject to the constraint that
the weights add up to 1. Once you set
up the parameters, click “Solve.”
*caution: uncheck the non-negativity
of the solver.
We will repeat the Solver procedure for
the second portfolio, except this time
we are looking to minimize the Sharpe
Ratio.
Note:
Be sure to select the correct sets of cells
to ensure the minimization is correct.
We now end up with two portfolios
whose asset weights have been
optimized. Recall the Portfolio 1 and 2
are those on the frontier curve.
Caution: If the optimized weights for
portfolios 1 and 2 are too big in size,
like -5.2 or 12,890.45, then use different
values for “constants.” The optimized
weights should be less than or at most
around 1 to -1. Also check with the
graph of investment opportunity set as
shown in the next page. If the shape is
not like the shape of “C”, then redo
determining the weights.
7
8.
We can now treat these two portfolios like individual assets and combine them in different weights
as we have been doing in the two-asset model where:
Portfolio mean (µP) = 𝑤1 𝐸[𝑟1 ] + 𝑤2 𝐸[𝑟2 ]
Portfolio variance (P2) = (w1•1)2 + (w2•2)2 + 2•w1•w2• ()•1•2
Portfolio standard deviation (P) = √𝑉𝑎𝑟𝑖𝑎𝑛𝑐𝑒
Create a table calculating the portfolio mean and standard deviations for combinations of the
various weights of each portfolio. Let w1 range from -1 to 2 and let w2 = 1 - w1
For example:
If we use the values above, 𝐸[𝑟1 ] =0.02159, 𝐸[𝑟2 ] =0.00732, 1 = 0.05896, 2 = 0.04364, =
0.62611
Then for w1 = 0.1 and w2 = 0.9,
µP = (0.1)(0.02159) + (0.9)(0.00732) = 0.0087.
P2 = [(0.1)(0.05896)]2 + [(0.9)(0.04364)]2 + 2(0.1)(0.9)(0.62611)(0.05896)(0.04364) = 0.00187
P = 0.00187 = 0.0432.
Repeat the calculations for other weights.
If we plot the monthly mean versus
standard deviation, we end with a curve
that illustrates the frontier for the three
assets. Report this graph as well.
To convert to annualized values, do:
𝜎𝑃 = √12 × (𝑚𝑜𝑛𝑡ℎ𝑙𝑦 𝜎𝑃 )
𝐸[𝑟𝑃 ] = 12 × (𝑚𝑜𝑛𝑡ℎ𝑙𝑦 𝐸[𝑟𝑃 ])
(where, 12 represents the number of
months.)
Example,
0.1497 (14.97%) = √12 × 0.0432
0.105 (10.5%) = 12 × 0.0087
8
9.
We can use the table we just created to the relative weights of XOM, MSFT, and DIS stock that is
required to achieve a portfolio that has a particular rate of return and standard deviation.
Suppose we want to maximize the returns on a portfolio that has a risk of 5% (monthly std. dev.).
According to the table, we can achieve the monthly return of 1.731% from a portfolio that is
comprised of 70% Portfolio 1 and 30% Portfolio 2.
We can calculate the ultimate weights of each asset by multiplying the weight of portfolio by the
weight of the asset within that portfolio.
Weight on XOM = (0.7)(-0.0753) + (0.3)(0.8248) = 0.1947
Weight on MSFT = (0.7)(0.4259) + (0.3)(0.2807) = 0.3824
Weight on DIS = (0.7)(0.6494) + (0.3)(-0.1055) = 0.4229
To replicate the risk and return of this portfolio, we would buy (or short, if negative) the respective
proportion of each asset. So if we had a $100,000 investment fund, we would buy $19,469 (=
$𝟏𝟎𝟎, 𝟎𝟎𝟎 × 𝟎. 𝟏𝟗𝟒𝟕) worth of ExxonMobil, $38,237 worth of Microsoft, and $42,295 worth of
Disney stock.
If we take the weights of each portfolio and
multiply by the weight of each asset in that
portfolio, we can find the weight of each of the
three assets that will generate the respective
mean and standard deviation.
Thus we have created an efficient frontier for
three assets. If we want to create an efficient
frontier for more assets, we would include
more columns of returns, but otherwise the
process is identical.
9
10.
In conclusion,
If you have $100,000, how much do you have to spend on each stock to achieve a certain expected
rate of return (“mean”) and the risk (“std dev”)? The following nicely summarizes the plan.
Notice that the negative values stand for the amount of short sales.
[End of this document]
10
