Rama Malladi Investments
(Portfolio Statistics, Diversification, and Optimization)
1. Historical stock returns risk
2. Two-stock portfolio
3. Benefit of Diversification
4. Risk and return in a 2-stock portfolio
5. Many-stock portfolio
6. Efficient frontier (portfolio optimization)
Let’s get started
2
1. Two_stock_portfolio.xls
2. Many_stock_portfolio.xls
3. Q2_Q3.xls
• Income – Consumption = Potential Investment.
• Present value of all investments = F(contribution to
investment, investment return, investment risk, inflation).
• In a world without uncertainty, an investor would choose to
own the investment that will provide the highest return.
• In the real world, fraught with uncertainty, investors cannot
possibly know which investment will provide the highest
return, or even if an investment will earn a profit.
• Therefore, we need to understand investment return risk.
Investments
3
Selected Realized Returns, 1926-2010
Two-stock Portfolio Return Risk
5
R1 and R2 are stock returns, are stock standard
deviations, is correlation between stocks, and w1, w2 are
weights of stocks.
21 σ and σ12ρ
1 1 2 2P or tf ol io Re tur n = [ ] [ ] [ ]PE R w E R w E R=+
A c c ounting f or the A c c ounting f or the
r isk of s toc k 1 r isk of s toc k 2 A djustm e nt f or how m uc h the tw o s toc ks m ov e toge the r
2 2 2 2
1 1 2 2 1 2 12 1 2( ) 2
P or t f ol i o R i sk = ( ) ( )
P
PP
Var R w w w w
Stde v R Var R
= + +
=
You invested 70% of your money in a stock portfolio that has an
expected return of 14% and a standard deviation of 18%. You
put the rest of your money in a risky bond portfolio that has an
expected return of 6% and a standard deviation of 12%.
The stock and bond portfolios have a correlation of -0.55.
What is your portfolio's expected return and standard deviation?
Two-stock Portfolio Return Risk
1 1 2 2[ ] [ ] [ ] 0 . 7 1 4 % 0 . 3 6 % 1 1 . 6 %PE R w E R w E R= + = + =
A c c o u n tin g f o r th e A c c o u n tin g f o r th e
r is k o f s to c k 1 r is k o f s to c k 2 A d ju s tm e n t f o r h o w m u c h th e tw o s to c k s m o v e to g e th e r
2 2 2 2
1 1 2 2 1 2 1 2 1 2( ) ( ) ( ) 2 ( , ) ( ) ( )PV a r R w S D R w S D R w w C o r r R R S D R S D R= + +
2 2 2 2( 0 .7 ) ( 0 .1 8 ) ( 0 .3 ) ( 0 .1 2 ) 2 ( 0 .7 ) ( 0 .3 ) ( 0 .1 8 ) ( 0 .1 2 ) ( 0 .5 5 ) 0 .0 1 2 1 8 2 4
( ) 0 .0 1 2 1 8 2 4 1 1 .0 3 %PS td e v R
= + + − =
==
6
Benefit of Diversification
• Combining airline stocks reduces volatility only slightly compared to the
individual stocks
• Combining airline and oil stocks reduces volatility below that of either stock
7
Two-stock Portfolio Return Risk
8
Two_stock_portfolio.xls
• Compute the return risk of this 2-stock portfolio.
In TI BA-II Plus calculator, Exp. Ret. is shown as
and Std. Dev. as
XXS
Two-stock Portfolio Return Risk
9
Two_stock_portfolio.xls
• As the weights of stock A and stock B change, portfolio risk
changes.
Many-stock Portfolio Return Risk
10
• Most individuals own more than two stocks. Regardless of the
number of securities in a portfolio, the expected return is
always a weighted average of the individual expected returns.
• The standard deviation is, however, more complicated.
• Recall that when we were evaluating the standard deviation
of the two- stock portfolio, we had to account for the
covariance between the two stocks.
• Similarly, when we have a three-stock portfolio, we must
account for the covariance between each pair of stocks.
Many-stock Portfolio Return Risk
11
• The return standard deviation of a many-stock portfolio is
thus given by (using the covariance form):
• To compute portfolio risk, we need covariance matrix.
1 1 2 2P or tf ol io Re tur n = [ ] [ ] [ ] ... [ ]P N NE R w E R w E R w E R= + + +2 2 2 2 2 21 1 2 2 1 2 1 , 2 1 3 1 , 3 2 3 2 , 3or tfo l io R is k = ... 2 2 2 ...P N Nw w w w w w w w w = + + + + + + +
,
11
P or t f ol i o R i sk =
NN
P i j i j i j
ij
ww
== =
Covariance matrix (easy way, in-built)
• Excel has a built-in tool to create a variance/covariance
matrix.
• Be sure to calculate the expected returns and standard
deviations because they will be required later.
Many_stock_portfolio.xls
Covariance Matrix
Covariance matrix (using matrix algebra)
• Excel’s covariance tool has three drawbacks:
1. It only creates the lower diagonal;
2. it only calculates population covariances; and
3. it only uses formulas for the variances.
• The last point means that the variance/covariance matrix
won’t update correctly if the returns are changed.
• To fix these problems, we could use matrix algebra.
• Using matrix notation, the sample variance/covariance matrix
(V) is calculated as:
where D is the difference matrix, D’ is the transpose of
the difference matrix, n is number of data points (5 here).
'
1
DDV
n= −
Covariance matrix (using matrix algebra)
14
• D is the difference matrix = , matrix of the returns less the E(R).
• Matrix multiplication is done using the MMULT function: MMULT(ARRAY1, ARRAY2)
• Following the rules of matrix algebra, the number of columns in ARRAY1 must be
equal to the number of rows in ARRAY2.
• To calculate the transpose of the difference matrix, use the TRANSPOSE function:
TRANSPOSE(ARRAY)
• To create the covariance matrix, select I2:L5 and then CTRL+SHIFT+enter:
=MMULT(TRANSPOSE(B2:E6-B7:E7),(B2:E6-B7:E7))/4.
• This will create the output shown as below.
• If you get a #VALUE! error instead, then you need to remember to enter the
formula using Shift+Ctrl+Enter.
XX−
Equal-weighted portfolio return, risk, Sharpe ratio
15
• Let’s begin by assuming that our portfolio will hold all four stocks in equal
proportions (i.e., 25% in each). Set up the data as shown in below.
• To calculate the expected return of the portfolio in B50 enter:
=SUM(TRANSPOSE(B45:B48)*$B$7:$E$7) as an array formula (Ctrl+Shift+Enter).
Note that we need to transpose one of the ranges because their orientation
differs. The expected return of the equally weighted portfolio is 9.77%.
• Portfolio standard deviation in matrix notation: σP = W′ V W
where W is the column vector (range) of weights, V is the variance/covariance matrix, and W' is the
transpose of the weight vector.
• Enter the following in B51 (array form. to see standard deviation of 8.56%)
=SQRT(MMULT(MMULT(TRANSPOSE(B45:B48),$I$2:$L$5),B45:B48))
• Sharpe Ratio = (Portfolio Return – Rf)/Portfolio Std Dev
Efficient Frontier
• By changing stock weights, we can produce infinite portfolios.
• If we were to create a graph showing the risk and return combination for
every possible portfolio, it would be known as the feasible set.
• Portfolio B is on the edge of the feasible set and has the same expected
return as A, but with less risk. So, any investor would prefer B over A.
Similarly, portfolio C has the same amount of risk as A, but it has a much
higher return. So, any investor would prefer C over A.
• Portfolios B and C are on the upper edge of the feasible set, which is
known as the efficient frontier. Portfolios on the efficient frontier will
always be preferred over portfolios that are inside the feasible set.
17
Efficient Frontier
• Unless we know an investor’s utility function, we cannot determine which
portfolio on the efficient frontier would be chosen by that investor.
• Note the portfolio labeled “Minimum Variance Portfolio.” This is the least
risky portfolio that is located on the efficient frontier.
• Finding the portfolios that lie on the efficient frontier is a quadratic
(nonlinear) optimization problem.
• Find the set of portfolios that minimize risk for each feasible expected
return subject to two (sometimes three or more) constraints:
1. The sum of the weights must equal 1
2. Calculated return must equal the specified target return
3. (optional) Each of the weights must be between 0 and 1.
• The third constraint specifies that short sales are not allowed, which is a
common constraint for individual and institutional investors.
Efficient Frontier (Min Variance)
• First, Std Dev for portfolio in cells…
B17: =SQRT(MMULT(MMULT(TRANSPOSE(B11:B14),$I$2:$L$5),B11:B14))
B16: =SUM(TRANSPOSE(B11:B14)*$B$7:$E$7)
B18: =(B16-$F$2)/B17
B19: =B16-0.5*$G$2*B17^2
• Second, use Solver with the following settings
• Click on Solve, you will see the following
Show us something Cool
19
Efficient Frontier (Max Return)
• Goal is to obtain max return possible (UFO’s 11.02%) with minimum risk
• First, copy cells B15:B19 to L15:L19
• Next, use solver as shown below.
• Click on Solve, you will see the following
• Remaining portfolio combinations are automatically filled
• You will now see an efficient frontier
20
Efficient Frontier (In-class assignment)
• Cross-check your results below. Focus on Minimum Variance
and Maximum Return first.
• Details are in textbook pages 439-444.
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%
12.00%
0.00% 5.00% 10.00% 15.00% 20.00%Exp
ec
te
d
Re
turn
Portfolio Standard Deviation
Efficient Frontier
21
Five-stock portfolio return risk (In-class assignment)
Q2_Q3.xls
22
Practical Application (Robo Advisors)
23
Take-home assignment
• Draw the Efficient Frontier for the following four stocks based
on the data given: GM, GE, MSFT, AAPL.
HW_portfolio.xls
24
Repeat take-home assignment for ANY four user
selected stocks. Use annual returns for the last
five years.
“R” and/or
“MATLAB” and/or
“Python” and/or
“SPSS” and/or
“Excel/VBA”.
Extra credit assignment (only first 3 entries considered)
(10 HONOR points + Show me something cool eligible!)