48
Portfolio Optimisation
119
It is now a relatively small step from Problem Three to the development of the capital
asset pricing model in the next chapter–the main hurdle is to in some way aggregate the
beliefs of the individual investors and so produce a theory of asset pricing.
6.10 USER-DEFINED FUNCTIONS IN Module1
The Module1 sheet contains the code for the user-defined functions applied in workbook
Equity1. They provide an insight into the handling of arrays within user-defined func-
tions–array inputs, array functions, calculations with both scalars and arrays and finally
returning array outputs. For example, the code for the PortfolioReturn function is:
Function PortfolioReturn(retvec, wtsvec)
’returns the portfolio return
If Application.Count(retvec) = Application.Count(wtsvec) Then
If retvec.Columns.Count <> wtsvec.Columns.Count Then
wtsvec = Application.Transpose(wtsvec)
End If
PortfolioReturn = Application.SumProduct(retvec, wtsvec)
Else
PortfolioReturn = -1
End If
End Function
The above code illustrates how to check the dimensions of arrays involved in calculations.
The SumProduct function returns a scalar but requires that the two arrays share the same
dimensions. This is achieved by checking that the arrays, retvec and wtsvec, have the same
number of elements and then transposing the wtsvec array if the number of columns in
each array differs. If the arrays have different numbers of elements the function returns
the error value of 1. The need for checking dimensions also arises from allowing the
user-defined function to accept input arrays as either column or row vectors.
The HLPortfolioWeights function (whose code is available in Module1) is notable for
the care needed when handling array functions. One important aspect is being aware of the
dimensions of the arrays within the function. In VBA one-dimensional arrays are stored
as row vectors and, in a similar way, one-dimensional arrays returned from user-defined
functions to the spreadsheet will also be row vectors. We will follow this convention by
ensuring that our user-defined functions return VBA single-dimension arrays into Excel
in the form of row vectors. However our preference within user-defined functions is to
adopt the alternative convention that vectors should be column vectors. For example,
the variables uvec and retvec are transposed if necessary to ensure that they are column
vectors.
Intermediate variables resulting from array functions also need to be treated with care:
Dim l As Variant, m As Variant
l= Application.MMult(vinvmat, retvec)
m= Application.MMult(vinvmat, uvec)
Since the variables l and m are themselves arrays derived from an array function, they
both need to be declared as variants but, as they will be used as inputs to array functions,
without a specific dimension. Within the MMult calculation, vinvmat is an [n, n] matrix
and retvec is an [n,1] vector. Thus the resulting array will be an [n, 1] (column) vector.
The next minor hurdle to overcome concerns the calculation of the four scalars a, b, c
and d. The scalar d is calculated from the other three scalars, each of which is the result
70
120
Advanced Modelling in Finance
of an array function. For instance, a is calculated from the unit vector array uvec and the
array 1:
aD Application.Sum(Application.MMult(Application.Transpose(uvec), 1))
The MMult calculation multiplies together a [1, n] (row) vector with an [n, 1] (column)
vector and thus returns a [1, 1] array (a scalar). Within Excel this doesn’t create any
difficulties, but in VBA we need to use the Sum function to convert the array result into
ascalar.
The final consideration is how to handle mixed-mode calculations involving scalars
with individual elements of arrays. In Excel this is handled without concern, as shown in
cells I24:I26 for the g vector. However, in VBA this is best done within a loop:
Dimwtsvec() As Variant
n= Application.Count(retvec)
ReDim wtsvec(n)
For i = 1 To n
gi = b
Ł
m(i, 1) - a
Ł
l(i, 1)
hi = c
Ł
l(i, 1) - a
Ł
m(i, 1)
wtsvec(i) = (gi + hi
Ł
expret) / d
Next i
Since wtsvec is calculated element-by-element we can declare it as a Variant(), but it
needs to be subsequently dimensioned using the ReDim statement. Within the loop we
can multiply scalars times the individual elements of the array.
6.11 FUNCTIONS FOR THE THREE GENERIC PORTFOLIO
PROBLEMS IN Module1
The Prob1OptimalRiskyWeight function, with four inputs, is a straightforward conversion
of the formula outlined in section 6.7 into VBA code. The required inputs are the return
and risk of the risky asset (r1 and sig1), the risk-free rate (rf) and the risk aversion
coefficient (rraval):
Function Prob1OptimalRiskyWeight(r1, rf, sig1, rraval)
’returns risky optimal weight whencombinedwith risk-free asset
Prob1OptimalRiskyWeight = (r1 - rf) /(rraval
Ł
sig1O2)
EndFunction
The following function called Prob2OptimalRiskyWeight, which can be used for Problems
Two and Three, has seven inputs in all: r1, r2, sig1 and sig2 are the returns and standard
deviations for the two risky assets; rraval is the investor’s risk aversion coefficient, and
the risk-free rate rf . The risk-free rate is set equal to zero for Problem Two:
Function Prob2OptimalRiskyWeight1(r1, r2, rf, sig1, sig2,corr12,rraval)
’returns optimal weight for risky asset1 when combined with risky asset2
’for case with no risk-free asset, enter value of rf <= 0
Dim cov12, var1, var2, minvarw, w,xr1, xr2
cov12= corr12
Ł
sig1
Ł
sig2
var1 = sig1O2
var2 = sig2O2
’first look at case with no risk-free asset
If rf <= 0 Then
minvarw = (var2 - cov12) / (var1 + var2 - 2
∗
cov12)
w= minvarw + (r1- r2) / (rraval
∗
(var1 + var2 - 2
∗
cov12))
88
Portfolio Optimisation
121
’then look at case with risk-free asset
Else
xr1 = r1 - rf
xr2 = r2 - rf
w= xr1
Ł
var2 - xr2
Ł
cov12
w= w / (xr1
Ł
var2+ xr2
Ł
var1 - (xr1 + xr2)
Ł
cov12)
End If
Prob2OptimalRiskyWeight1 = w
End Function
The code for the optimal weight is written in terms of the covariance (denoted cov12).
The variable minvarw is the minimum variance weight w
1
mv
and variable w is the value
w
1
opt
which produces the maximum utility portfolio.
For Problem Three, the function uses the functions for solving Problem One and Two.
As its name suggests, the Prob3OptimalWeightsVec function returns the three optimal
weights in an array. The complete code is shown below followed by an explanation of
some steps:
Function Prob3OptimalWeightsVec(r1, r2, rf, sig1,sig2, corr12, rraval)
’returns optimal weights for risk-free asset and 2 risky assets
’uses Prob2OptimalRiskyWeight fn
’uses Prob1OptimalRiskyWeight fn
Dim w0, w1, w2, rr, sigr
w1 = Prob2OptimalRiskyWeight1(r1, r2, rf, sig1, sig2, corr12,rraval)
w2 = 1 - w1
rr = w1
Ł
r1 + w2
Ł
r2
sigr = Sqr((w1
Ł
sig1)O2 + (w2
Ł
sig2)O2+ 2
Ł
w1
Ł
w2
Ł
corr12
Ł
sig1
Ł
sig2)
w0= 1- Prob1OptimalRiskyWeight(rr, rf, sigr, rraval)
w1= (1 - w0)
Ł
w1
w2= (1 - w0)
Ł
w2
Prob3OptimalWeightsVec = Array(w0,w1,w2)
End Function
Ignoring the risk-free asset, the problem reduces to Problem Two and the split between
assets1 and 2 is calculated:
w1 = Prob2OptimalRiskyWeight1(r1, r2, rf,sig1, sig2, corr12, rraval)
w2 = 1 - w1
From these weights the return and risk of the risky portfolio can be calculated:
rr = w1
Ł
r1 + w2
Ł
r2
sigr = Sqr((w1Ł sig1)O2 + (w2Ł sig2)O2 + 2Ł w1Ł w2Ł corr12Ł sig1Ł sig2)
These quantities are inputs in determining the weight for the risk-free asset (essentially a
Type One problem). Hence the use of the Prob1OptimalRiskyWeight function.
6.12 MACROS IN ModuleM
The macros to generate the efficient frontier using Solver are contained in this module
sheet. Solver is an add-in to Excel and, unless it appears on the Tools menu, needs to
be installed. Additionally, to use the Solver functions in VBA, the module sheet must
include a reference to the SOLVER.xla add-in file:
51
122
Advanced Modelling in Finance
SubEffFrontier1()
SolverReset
Call SolverAdd(Range(“portret1”), 2, Range(“target1”))
Call SolverOk(Range(“portsd1”), 2,0, Range(“change1”))
Call SolverSolve(True)
SolverFinish
EndSub
The EffFrontier1 macro contains a single application of Solver. The SolverAdd function
adds the single constraint needed (where the value 2 represents the equality constraint)
then the SolverOk function sets up the problem (here the value 2 ensures minimisation).
The SolverSolve function solves the problem (the True parameter hides the results screen)
then the SolverFinish function retains the final solution values in the spreadsheet.
Strictly speaking, the Call word is not strictly necessary, but we prefer to include it
when a subroutine requiring parameters is used in the code. The parameters must then be
in brackets when using this syntax.
The EffFrontier2 macro is rather more complicated in that it generates the complete
efficient frontier using repeated applications of the Solver function within a loop. The
number of repeats is predetermined. The most important lesson is to minimise the code
contained in the loop. The Solver problem is set up outside the loop, with the Solver-
Change function changing the right-hand side of the constraint (the target expected return)
inside the loop. Within the loop, the results of each Solver iteration are copied to a range
in the spreadsheet using the PasteSpecial command. The code for the loop is as follows:
Do While iter <= niter
Call SolverSolve(True)
SolverFinish
Range(“portwts2”).Copy
Range(“effwts2”).Offset(iter, 0).PasteSpecial Paste:=xlValues
Range(“priter2”) = Range(“priter2”).Value + pradd
’amend portret constraint in Solver
Call SolverChange(Range(“portret2”), 2, Range(“priter2”))
iter = iter + 1
Loop
The initial part of the macro uses Solver twice, first to calculate the minimum portfolio
return achievable with the given constraints (with the value of 2 in the SolverOk function)
and then to calculate the maximum return (with the value of 1 in the SolverOk function).
The range between these two returns is divided into the chosen number of target returns
and the frontier portfolio weights determined and stored for each return.
SolverReset
’first calculate portfolio min returngiven constraints
Call SolverAdd(Range(“portwts2”), 3, Range(“portmin2”))
Call SolverAdd(Range(“portwts2”), 1, Range(“portmax2”))
Call SolverOk(Range(“portret2”), 2, 0, Range(“change2”))
Call SolverSolve(True)
SolverFinish
prmin = Range(“portret2”).Value
’then calculateportfolio max return given constraints
Call SolverOk(Range(“portret2”), 1, 0, Range(“change2”))
Call SolverSolve(True)
SolverFinish
26
Portfolio Optimisation
123
SUMMARY
The theory of portfoliooptimisation developedby Markowitz underpins the whole Equities
part of the book. We show how the basic formulas for portfolio mean and variance
with two assets can be easily extended, using the array functions in Excel, to cover
many assets. The array functions allowing matrix multiplication and inversion allow us to
implement Huang and Litzenberger’s analytic solution to generate the efficient frontier.
This is illustrated in spreadsheet form as well as within user-defined functions.
Although the theory is important, its practical implementation requires numerical
methods, such as Solver. We demonstrate that the solution obtained by Solver agrees
with the HL analytic solution for the unconstrained case and show how to use Solver
when there are constraints on the holdings of individual assets. Again, we illustrate the
use of Solver both in spreadsheet form and using macros (for repeated applications).
In the next chapter, we cover the next crucial development in finance theory, the capital
asset pricing model and the role of beta. We go on to use the lognormal distribution of
equity returns in forecasting horizon wealth and Value-at-Risk for individual shares and
portfolios.
REFERENCES
Bodie, Z., A. Kane and A. J. Marcus, 1996, Investments, 3rd edition, Richard D. Irwin, Englewood Cliffs, NJ.
Elton, E. J. and M. J. Gruber, 1995, Modern Portfolio Theory and Investment Analysis, John Wiley & Sons,
Chichester.
Eppen, G. D.,F. J. Gould,C. P. Schmidt, J. H. Moore and L. R. Weatherford, 1998, IntroductoryManagement
Science, Decision Modelling with Spreadsheets, 5th edition, Prentice Hall, New Jersey.
Huang, C. and R. Litzenberger, 1988, Foundations for Financial Economics, North Holland, New York.
Taggart, R. A., 1996, Quantitative Analysis for Investment Management, Prentice Hall, New Jersey.
53
7
Asset Pricing
We move from examining individual investors (a micro view) to the markets for assets
and looking at the behaviour of all investors (a macro view). The crucial difference is
the move from making statements about individuals to the drawing of conclusions about
the behaviour of investors as a whole. It is this different focus that allows us to make
statements about the pricing of financial assets. The capital asset pricing model (CAPM)
was developed in the sixties by finance academics. It developed out of the mean–variance
portfolio analysis described in the previous chapter. An important conclusion was that
market prices reflect only part of a portfolio’s risk. The part that is priced, the market-
related risk, is measured by beta. The background to the CAPM is extensively discussed
in Bodie et al. (1996), Chapter 8.
This chapter opens with the single-index model and illustrates the calculation of associ-
ated risk measures, in particular the estimation of betas and variance–covariance matrices
from the returns of individual assets. The central numerical technique is regression, which
is applied to estimate the beta of an asset, i.e. the return on the asset relative to the return
on the market. The betas for a set of assets are usefulin their own right to describe the rela-
tive responsiveness of assets to the market. However, they also facilitate the calculation
of covariances via the single-index model. The EQUITY2.xls workbook contains imple-
mentations of the various estimation procedures and a range of user-defined functions
have been developed to reduce the computational burden of the procedures.
In the previous chapter, we developed the mean–variance model of Markowitz assuming
autility function to describe the preferences of individual investors. In that analysis, the
distribution of asset returns was ignored. We can, as an alternative, generate exactly the
same theoretical results from the mean–variance model by assuming that asset returns
are lognormally distributed whilst ignoring the preferences of individuals.
If log returns can be assumed to be normally distributed, we can derive analytic answers
for both Value-at-Risk and the forecasting of horizon wealth. In this chapter, we include
examples of both these techniques. In a sense, we reap the benefit of Markowitz’s insight
that return and risk can be associated with the mean and variance of the normaldistribution
and thus can use familiar results from the field of statistics. Also, since it is vital to
manoeuvre easily between normal and lognormal moments of asset returns, we give
practical illustrations to reinforce the theory linking the two distributions.
7.1 THE SINGLE-INDEX MODEL
The single-index model provides a useful way of building our understanding of return
and risk en route to asset pricing models. The model posits a linear relationship between
returns on share i and returns on an index, I say. Using R
i
and R
I
to denote returns in
excess of the risk-free rate for the share i and the index I respectively, the equation is
written:
R
i
D˛
i
Cˇ
i
R
I
Ce
i
217
126
Advanced Modelling in Finance
where ˛
i
and ˇ
i
are parameters in the model, such that the return on the share is made
up of a systematic part ˛
i
Cˇ
i
R
I
and a residual part e
i
.The systematic part consists of
˛
i
,the share’s return if the excess return on index I is zero and ˇ
i
R
I
,the return related to
movement in index I. So ˇ
i
is the share’s responsiveness to movements in the index. The
residual return, e
i
,is not related to the index and is specific to share i. It is assumed to be
similar to a random error term, with zero expectation, i.e. Ee
i
D 0 so that the expected
excess return on share i is given by:
ER
i
D ˛
i
Cˇ
i
ER
I
If the index is a stock market index (such as the FTSE100 in the UK or the S&P500
in the US), then the systematic component of return is ‘market-related’ and the residual
component ‘firm-specific’. The beta parameter is the share’s sensitivity to the market.
Using excess returns for an individual share and for an index (say with returns for 60
months) we can estimate the parameters ˛
i
and ˇ
i
using regression. This technique also
provides an estimate of e
i
2
,the variance of the residuals.
The single-index model can also be used to decompose risk (as measured by variance,
in line with the previous chapter). The variance of returns on share i can be split into two
separate components, the first measuring systematic risk and the second measuring risk
that is specific to the individual share:
VarR
i
D
i
2
Dˇ
i
2
I
2
Ce
i
2
This result greatly simplifies the calculation burden of obtaining variance–covariance
matrices, as we shall see in section 7.4.
7.2 ESTIMATING BETA COEFFICIENTS
To estimate betas, it is best to work with log returns (in fact log of excess returns). The
sheet Beta illustrates how the beta coefficient for a share is estimated by regression using
monthly returns on ShareA and Index (columns B and C in Figure 7.1). Here, the index
is the FTSE100 and there are 60 monthly returns for both ShareA and Index. The beta
(strictly speaking, the ‘unadjusted’ beta) is simply the slope estimate from the regression
of share excess returns on index excess returns.
2
34
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
A
B
C
D
E
F
G
H
I
J
Estimating Beta Coefficients using Ln Xs Returns
Mth
ShareA
Index
Fitted Residuals
Excel regression functions
1
-0.1326
-0.0534
-0.0817
-0.0509
INTERCEPT
-0.0013
2
0.0671
0.0868
0.1295
-0.0624
SLOPE
1.5065
3
0.0939
0.0182
0.0261
0.0678
RSQ
0.4755
4
-0.1339
-0.0800
-0.1218
-0.0121
STEYX
0.0595
5
-0.0951
-0.0666
-0.1016
0.0065
6
-0.0381
-0.0455
-0.0698
0.0317
Alpha
-0.0013
7
0.0882
0.0925
0.1381
-0.0499
Alpha (SE)
0.0078
8
0.1140
0.0393
0.0579
0.0561
9
0.0694
0.0422
0.0623
0.0071
Beta
1.5065
10
0.0527
0.0362
0.0533
-0.0006
Beta (SE)
0.2077
11
-0.1298
-0.0006
-0.0022
-0.1276
12
0.0395
0.0223
0.0323
0.0072
Sp Risk from STEYX
13
-0.0358
0.0071
0.0094
-0.0452
Monthly
5.95%
14
-0.0179
-0.0150
-0.0239
0.0060
Annualised
20.60%
15
0.1251
0.0092
0.0126
0.1125
via fn
20.60%
Figure 7.1 Regression of returns on ShareA on market index to estimate beta
Documents you may be interested
Documents you may be interested
