Vertex42 The Excel Nexus

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 variance--covariance matrix in C15:E17 is named V. As explained earlier, the portfolio variance is written in matrix form as wTVw and is evaluated with Excel's matrix multiplication 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 3 cell range, H15:J17, is:

=MINVERSE(C15:E17)

Note that for array functions, having selected the cell range for the formulas and entered the appropriate formula, the keystroke combination Ctrl+Shift+Enter must be pressed to complete the entry. (If no array formula brackets {} appear in the cell, then press F2 and try again.)

In order to find two frontier portfolios (labelled g and g+h), 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:

If we define two intermediate column vectors l = V_1e and m = V_1u, shown in the sheet in cells C24:C26 and D24:D26, the matrix multiplication expressions simplify to:

The single cell formula for A in cell G23: =MMULT(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.

A 1 B 1 C 1 D 1 E 1 F |
G |
H |
I |
J |
K | ||||||

2 |
Using Algebra to reproduce Unconstrained Frontier Portfolios | ||||||||||

3 |
1 | ||||||||||

4 |
Asset Data |
Exp Ret |
Std Dev |
Portfolio Weights | |||||||

5 |
TBills |
0.6% |
4.3% |
TBills |
33.3% | ||||||

6 |
Bonds |
2.1% |
10.1% |
Bonds |
33.3% | ||||||

7 |
Shares |
9.0% |
20.8% |
Shares |
33.3% | ||||||

8 | |||||||||||

9 |
Correlation Matrix |
TBills |
Bonds |
Shares |
via fn | ||||||

10 |
TBills |
1.00 |
0.63 |
0.09 |
Exp Ret |
3.90% |
3.90% | ||||

11 |
Bonds |
0.63 |
1.00 |
0.23 |
Variance |
0.0080 | |||||

12 |
Shares |
0.09 |
0.23 |
1.00 |
Std Dev |
8.95% |
8.95% | ||||

13 | |||||||||||

14 |
VCV Matrix |
TBills |
Bonds |
Shares |
VCV inverse | ||||||

15 |
TBills |
0.0018 |
0.0027 |
0.0008 |
901.51 |
-246.92 |
10.80 | ||||

16 |
Bonds |
0.0027 |
0.0102 |
0.0048 |
-246.92 |
171.13 |
-14.52 | ||||

17 |
Shares |
0.0008 |
0.0048 |
0.0433 |
10.80 |
-14.52 |
24.53 | ||||

18 | |||||||||||

19 | |||||||||||

20 |
Finding weights, g and h, to generate points on the frontier | ||||||||||

21 | |||||||||||

22 |
uvec |
l |
m |
g |
h |
g+h | |||||

23 |
A |
3.97 | |||||||||

24 |
1 |
1.20 |
665.40 |
B |
0.20 |
124.0% |
-1851.9% |
-1727.9% | |||

25 |
1 |
0.81 |
-90.30 |
C |
595.91 |
-20.5% |
805.2% |
784.6% | |||

26 |
1 |
1.97 |
20.82 |
D |
104.15 |
-3.5% |
1046.7% |
1043.2% | |||

27 | |||||||||||

28 |
Exp Ret |
0.0% |
100.0% |
100.0% | |||||||

29 |
Std Dev |
4.4% |
237.6% | ||||||||

30 | |||||||||||

31 |
Generatin |
g Frontier Portfolios, using g and h | |||||||||

32 |
1 | ||||||||||

33 |
Target expected return |
7.0% | |||||||||

34 | |||||||||||

35 |
weights |
via fn | |||||||||

36 |
TBills |
-5.6% |
-5.6% |
Exp Ret |
7.0% | ||||||

37 |
Bonds |
35.8% |
35.8% |
Std Dev |
15.7% | ||||||

38 |
Shares |
69.8% |
69.8% |

Figure 6.6 Huang and Litzenberger's direct analytical solution

Figure 6.6 Huang and Litzenberger's direct analytical solution

There are further formulas for the asset weights that represent the two points on the envelope, namely portfolio g (with an expected return of 0%) and portfolio g+h (with an expected return of 100%). These can be expressed as:

Before typing in the formula for g, a (3 x 1) column vector of cells must be selected, because this is an array formula. Similar remarks apply to the entry of the array formula for h. The weights for the two frontier portfolios are shown in cells I24:I26 and K24:K26 of Figure 6.6.

Thus the weights making up vector g (124%, —20.5%, —3.5%) for TBills, Bonds and Stocks respectively give a portfolio on the frontier with expected return 0%. Similarly, the weights making up vector g+h give a second frontier portfolio with expected return 100%. Using the vectors g and h, a linear combination of them in the form g+h*T can be constructed to give the weights for a portfolio on the frontier producing any specified expected return, T. For instance, looking at rows 33 to 38 in Figure 6.6, an expected return of 7% can be achieved with a portfolio containing —5.6% in TBills, 35.8% in Bonds and 69.8% in Stocks (cells D36:D38). That is, the minimum risk portfolio with an expected return of 7% consists of holding a mixture of Bonds and Stocks whilst short selling TBills. This result from HL's approach is identical to the result in Figure 6.5 for Solver optimisation with no constraints on weights.

Other points on the frontier can be evaluated using the array expression g+h*T by supplying different values for the expected return, T. In this way the efficient frontier can be generated. For example, in Excel a Data Table with a range of expected returns from 0% to 10% as inputs and portfolio risk and return as outputs can be used as the basis for an XY (Scatter) chart. In Figure 6.2, the points labelled as the 'Unconstrained Frontier' come from such a Data Table.

As is evident, the spreadsheet manipulations required to evaluate the weights for the two efficient portfolios are complex. This is the ideal situation for a VBA user-defined function to simplify formula entry. In section 6.10, the code to implement Huang and Litzenberger's approach is explained. The function HLPortfolioWeights has four arguments: expret, retvec vcvmat and rf (here = —1). Argument expret is the expected return of the frontier portfolio for which the weights are required, retvec is the vector of expected returns and vcvmat the variance-covariance matrix. The function returns an array of the appropriate portfolio weights. In Figure 6.6, weights from this user-defined function are shown in cells E36:E38 alongside the weights obtained from spreadsheet manipulation.

Was this article helpful?

## Post a comment