Question

An investor is considering combining the following four stocks into a portfolio:

			r
	E(r)	s	DIS	GIS	TGT	DUK
DIS	11%	21%	1	0.23	0.18	0.33
GIS	8%	17%		1	0.17	0.25
TGT	7%	15%			1	0.14
DUK	12%	18%				1

Find the composition of the most efficient portfolio with expected return of 10%.

Note: This is a slight departure from what we have done in this Unit. In some sense, this is a simpler approach. Just do the calculations as you would for nding optimal portfolio without a risk-free rate. Now, use the solver to nd a portfolio that meets the investor's specications.

Answer 1

First let's just build a matrix

Covariance has been obtained simply by multiplying the corresponding correlation with standard deviations of both the stocks involved.

Now Expected Return of portfolio is simply sum of weight x return

so use the function sumproduct

now for the tough part portfolio variance, we use an array function using MMult

as

So let's break down function used

Transpose B3:B6, as the weights are vertical and we want them to be horizontal,

MMULT(I3:L6,B3:B6)

I3:L6 are the variance and covariance, and B3:B6 are weights,

multiplying both of them gives, half of what we want, and then multiplying them again with weight gives the full RHS of the variance equation

Now, to use the solver, set, Variance to the Min

by changing variable cells, B3:B6 (weights)

Constraints, sum of all weights is 1

and Expected Return is 10%

It would look like this

And after solving you will get these weights,

DIS 0.1869

GIS 0.1631

TGT 0.2321

DUK 0.4179

Good luck