71
Portfolio Optimisation
109
for a specified level of risk. Although these appear to be different problems for Solver,
the optimum weights correspond to portfolios lying on the efficient frontier as in the
minimisation formulation.
6.4 GENERATING THE EFFICIENT FRONTIER (HUANG
AND LITZENBERGER’S APPROACH)
If there are no constraints on individual asset weights, the efficient frontier can also be
produced elegantly from algebra. Although some of the more advanced textbooks (such
as Elton and Gruber, 1995) demonstrate this through the iterative solution of a set of
simultaneous equations, there is a better approach. Huang and Litzenberger (denoted HL)
have described how to find two points on the frontier, and then to generate the whole
of the frontier from these points (by applying a result due to Black). This section builds
on their algebraic approach, the calculation sequence being explained using matrices, to
generalise the approach to portfolios with many (i.e. more than three) assets. The context
of the following description is spreadsheet implementation with Excel’s array functions.
This section is of a somewhat more advanced level and may be deferred on first reading
until the remainder of the chapter has been understood.
Sheet EF1HL shown in Figure 6.6 includes named ranges to improve the clarity of
the formulas in the cells. The vector of expected returns (C5:C7) is named e, the vector
of weights (I5:I7) is named w and the unit vector in A24:A26 is named u. The vari-
ance–covariance matrix in C15:E17 is named V. As explained earlier, the portfolio
variance is written in matrix form as w
T
Vw and is evaluated with Excel’s matrix multi-
plication functions in cell I11.
The HL method for finding efficient portfolios requires the inverse of the
variance–covariance matrix, which is written as V−
1
.Excel’s MINVERSE function for
matrix inversion does the actual calculation. The array formula to enter into the 3 by
3cell range, H15:J17, is:
DMINVERSE(C15:E17)
Note that for array functions, having selected the cell range for the formulas and entered
the appropriate formula, the keystroke combination CtrlCShiftCEnter must be pressed to
complete the entry. (If no array formula brackets fg appear in the cell, then press F2 and
try again.)
In order to find two frontier portfolios (labelled g and gCh), Huang and Litzenberger
start by calculating four scalar quantities (A, B, C and D). The first three, A, B and C,
are products of vectors and matrices and the fourth, D, depends on the previous three:
AD u
T
V−
1
e B D e
T
V−
1
e C D u
T
V−
1
u D D BC A
2
If we define two intermediate column vectors l D V−
1
e and m D V−
1
u, shown in the
sheet in cells C24:C26 and D24:D26, the matrix multiplication expressions simplify to:
AD u
T
l B D e
T
l C D u
T
m
The single cell formula for A in cell G23: DMMULT(TRANSPOSE(u), l) involves the
MMULT array function, so it must be entered as an array formula (similarly the formulas
for B and C). Since the calculations for A, B, C and D all result in scalars, there is no
need to ‘select’ an area greater than a single cell for any of them.
