of Business and Economic Statistics, 16, 244–253.Sorenson, H. and D. Alspach (1971), “Recursive Bayesian estimation using Gaussian sums”, Automatica, 7, 465–479.Tanizaki, H. and R. Mariano (1994), “Prediction, ﬁltering and smoothing in non-linear and nonnormal cases using Monte Carlo integration”, Journal of Applied Econometrics, 9, 163–179.
Stochastic Volatility Models
291
Tanizaki, H. and R. Mariano (1998), “Nonlinear and nonnormal state-space modeling with MonteCarlo stochastic simulation”, Journal of Econometrics, 83(1&2), 263–290.Tauchen, G. (1997), “New minimum chi-square methods in empirical ﬁnance”, in Advances inEconomics and Econometrics: Theory and Applications, Seventh World Congress, D. Kreps andK. Wallis (eds), Cambridge University Press, Cambridge.Taylor, S. J. (1986), Modelling Financial Time Series, John Wiley, Chichester.Taylor, S. J. (1994), “Modelling stochastic volatility: a review and comparative study”, Mathematical Finance, 4, 183–204.Tierney, L. (1994), “Markov chains for exploring posterior distributions” (with discussion), Annalsof Statistics, 22, 1701–1786.Wright, J. H. (1999), “A new estimator of the fractionally integrated stochastic volatility model”,Economics Letter, 63, 295–303.Wright, J. H. (2000), “Log-periodogram estimation of long memory volatility dependencies withconditionally heavy tailed returns”, Board of Governors of the Federal Reserve System, International Finance Discussion Papers no. 685, http://www.bog.frb.fed.us.
9
Portfolio Analysis Using ExcelJASON LAWS
ABSTRACTThis chapter analyses the familiar Markovitz model using Excel. This topic is taught onﬁnance degrees and Masters programmes at Universities all over the world increasinglythrough the use of Excel. We take time out to explain how the spreadsheet is set up andhow simple short cuts can make analysis of this type of problem quick and straightforward.In the ﬁrst section of the chapter we use a 2 variable example to show how portfolio riskand return vary with the input weights then he goes onto show how to determine theoptimal weights, in a risk minimisation sense, using both linear expressions and alsousing matrix algebra. In the second part of the chapter we extend the number of assetsto seven and illustrate using matrix algebra within Excel that Markovitz analysis of ann-asset portfolio is as straightforward as the analysis of a two asset portfolio. We takespecial care in showing how the correlation matrix can be generated most efﬁciently andhow within the same framework the optimisation objective can be modiﬁed without fuss.
9.1 INTRODUCTIONThe motivation for this chapter is to show how the Markovitz model of portfolio risk canbe modelled in Excel. The goal of this paper is not to provide an in-depth discussion of thismodel but instead to show how it can be implemented within Excel. Excellent discussionsof this model can be found in a wide range of corporate ﬁnance or investment text books,including Cuthbertson and Nitzsche (2001). However, it is worthwhile to provide a briefoverview of this theory. In essence the underlying rationale is based on the twin facets ofexpected risk and return, with risk being measured by the variance or standard deviationof expected returns. The efﬁcient frontier of the portfolio is given by:1. Those assets which offer a higher return for the same risk, or equivalently2. Those assets which offer a lower risk for the same return.The precise position an investor takes on the efﬁcient frontier depends on his/her utilityfunction based on the two motives of expected return and risk; in other words the utility function is positively sloped in the return/risk domain. Initially I assume that this utilityfunction is very steep (i.e. the individual is highly risk averse) so his/her desired positionis a corner maximum where risk is minimised with respect to just two assets. Later theApplied Quantitative Methods for Trading and Investment. 2003 John Wiley & Sons, Ltd ISBN: 0-470-84885-5
Edited by C.L. Dunis, J. Laws and P. Naı̈m
294
Applied Quantitative Methods for Trading and Investment
analysis is further extended to incorporate a large number of assets and/or objectives otherthan portfolio risk minimisation.
9.2 THE SIMPLE MARKOVITZ MODELIn the simple Markovitz model portfolio returns, rp , in a two-asset model are given by:rp = w1 r1 + w2 r2
(9.1)
and the corresponding variance of portfolio returns σp2 , by:σp2 = w12 σ12 + w22 σ22 + 2w1 w2 σ12
(9.2)
where wi represents the weight allocated to asset i in the portfolio (i = {1, 2}), σi2 represents the variance of the returns on asset i (i = {1, 2}), ri represents the historical returnsof asset i (i = {1, 2}) and σ12 represents the covariance between the returns on assets 1and 2. Using the relationship that the correlation coefﬁcient (ρ12 ) between two assets canbe calculated as:σ12ρ12 =σ1 σ2then equation (9.2) can be rewritten as:σp2 = w12 σ12 + w22 σ22 + 2w1 w2 ρ12 σ1 σ2
(9.3)
This is a much more convenient version of the portfolio variance equation and one thatstudents are often more at ease with as here we use the correlation coefﬁcient whichtakes the range −1 to +1 whereas previously we incorporated the covariance whose sizeis relative to the individual variances.As an illustration of this model we will now apply it using two indices of stock prices,namely, the FT World equity index for the UK (WIUTDK$) and the FT World index forEuropean equities which excludes the UK (WIEXUK$). The frequency of the data forthis example was chosen as weekly and covers the period January 1996 to end June 2002.The raw data and subsequent analysis for this example in this and the next section areincluded in the ﬁle “Laws001.xls”. This ﬁle includes several worksheets, the titles ofwhich are self-explanatory.As highlighted above the Markovitz model is based around asset returns and not “rawprices”. The ﬁrst task therefore is to generate a return series where the weekly return isgiven by:Pt100 loge(9.4)Pt−1where Pt is the current price level, Pt−1 is the price level in the previous period and logeis the natural log transformation. A screen shot of this initial transformation (containedin the worksheet “Returns”) is given in Figure 9.1.
Portfolio Analysis Using Excel
295
Figure 9.1 Worksheet “Returns” in the ﬁle “Laws001.xls”
The Excel instruction necessary to carry out this calculation based on the raw datacontained in the worksheet “Raw Data” is shown in the fourth line of the fourth columnof this screen shot. Note also that throughout this chapter I have rounded the data correctto two decimal places.In order to implement the Markovitz model we must ﬁrst of all ﬁnd the mean andstandard deviation of each of our series. This is easily achieved in Excel using the“AVERAGE” and “STDEV” functions respectively. For completeness we have also calculated the measures of kurtosis and skewness using the “KURT” and “SKEW” functionsrespectively. These measures are shown in the worksheet “Summary Statistics” and arereproduced in Figure 9.2.Here we can see that the distributions of both returns series are “peaked” relative tothe normal distribution.1 The negative skewness also indicates that the distribution hasan asymmetric tail extending more towards negative values. More importantly, in termsof Markovitz analysis, we can see that the European data has a higher average weeklyreturn over the period than the UK data but a higher standard deviation of returns. Thecorrelation between returns is 0.76, which provides opportunities for risk diversiﬁcation.The workbook “Portfolio Risk” implements equation (9.3)2 above using the WIUTDK$and WIEXUK$ data. This is achieved by substituting a range of values for w1 from 0to 1, where 0 implies that 0% of wealth is invested in the WIUTDK$ index (with 100%being invested in the WIEXUK$ index) and 1 implies that 100% of wealth is invested1Since the measure of excess kurtosis (i.e. observed kurtosis minus the normal curve value of 3) is positive.If the kurtosis measure were negative then we would say that the distribution is ﬂat.2In fact it implements the square root of equation (9.3).
296
Applied Quantitative Methods for Trading and Investment
Figure 9.2 Worksheet “Summary Statistics” in the ﬁle “Laws001.xls”
in the WIUTDK$ index (with 0% invested in the WIEXUK$ index). The result of thisanalysis is shown in Figure 9.3.Note in the explanation of the contents of cells C2 and D2 that use has been made ofthe “$” sign. The reason for this is that when we copy the formula in cells C2 and D2down the columns we would like to ﬁx the formula on the cells which contain the returns,standard deviations and correlations in the “Summary Statistics” worksheet,3 varying onlythe portfolio weights.It is evident from these results that as we decrease the proportion of wealth investedin the WIUTDK$ index the portfolio risk declines (as return rises) then begins to rise(as return rises). There is therefore some beneﬁt to be gained from incorporating theWIEXUK$ index into the portfolio.This is best illustrated using an “XY” chart in Excel. To do this highlight cells C2to D12 (inclusive) and then click the “Chart Wizard” button. Select the chart type “XY(Scatter)” and any of the subtypes which include a ﬁtted line (Figure 9.4).4You may add a chart title and titles for each axis if you wish. Finally you end up withthe graph in Figure 9.5, as depicted in the worksheet “Efﬁcient Frontier”. Here we cansee that as we decrease the proportion invested in the WIUTDK$ index the portfolio risk3A short-cut to locking a calculation on a cell address is to use the F4 key. Pressing the F4 key once places a $sign before both the letter and the number. Pressing twice places a $ sign just before the number and pressingthree times places a $ sign before the letter.4It is more than likely (depending upon your set-up) that Excel will have drawn a chart with risk on the verticalaxis and return on the horizontal axis (because our return data was in the ﬁrst column Excel has assumed thisfor the y-axis). It is however standard in this type of analysis to plot the data with returns on the vertical axisand risk on the horizontal axis. To correct this simply select the “Series” tab and change the X and Y valuelocations accordingly.
Portfolio Analysis Using Excel
Figure 9.3
Worksheet “Portfolio Risk” in the ﬁle “Laws001.xls”
Figure 9.4 The Chart Wizard dialogue box
297
298
Applied Quantitative Methods for Trading and Investment
Figure 9.5
Worksheet “Efﬁcient Frontier” in the ﬁle “Laws001.xls”
drops and returns rise (initially). If our objective is to minimise portfolio risk then thereexists some optimal investment in the WIUTDK$ index. It is possible to ﬁnd this optimalinvestment using calculus as follows.We wish to minimise:σp2 = w12 σ12 + w22 σ22 + 2w1 w2 ρ12 σ1 σ2 = w12 σ12 + (1 − w1 )2 σ22 + 2w1 (1 − w1 )ρ12 σ1 σ2= w12 σ12 + σ22 + w12 σ22 − 2w1 σ22 + 2w1 ρ12 σ1 σ2 − 2w12 ρ12 σ1 σ2by varying w1 , noting that w2 = 1 − w1 .This can be achieved by setting the derivative of σp2 (portfolio risk) with respect to w1(the proportion invested in asset 1) equal to zero, i.e.δσp2δw1
= 2w1 σ12 + 2(1 − w1 )(−1)σ22 + 2ρ12 σ1 σ2 − 4w1 ρ12 σ1 σ2 = 0⇒ w1 σ12 + (1 − w1 )(−1)σ22 + ρ12 σ1 σ2 − 2w1 ρ12 σ1 σ2 = 0⇒ w1 (σ12 + σ22 − 2ρ12 σ1 σ2 ) = σ22 − ρ12 σ1 σ2⇒ w1 =
σ22 − ρ12 σ1 σ2σ12 + σ22 − 2ρ12 σ1 σ2
and w2 = 1 − w1
(9.5)
Applying this formula to our dataset we ﬁnd the results of Figure 9.6, as shown in theworksheet “Minimum Variance Using Eqn”. Here we can see that there is some optimalmix of the WIUTDK$ (82%) and the WIEXUK$ (18%) indexes that minimises risk at
Portfolio Analysis Using Excel
299
Figure 9.6 Worksheet “Minimum Variance using eqn” in the ﬁle “Laws001.xls”
2.07% per week with a return of 0.06% per week.5 Note the minimum risk is less thanthat of either of the individual securities.An alternative way to ﬁnd this result which doesn’t use calculus is to use Excel’s in-builtoptimisation tool – “Solver”. To illustrate how this works we have created a duplicatespreadsheet in the worksheet “Min Var Using Solver” and have added an additional lineof calculations to allow us to alter the weights (Figure 9.7).As you can see we have set the initial weights to 0.5 (i.e. 50%) for the WIUTDK$index which automatically implies 50% for the WIEXUK$ index. Any arbitrary values forthe initial weights will be all right. It is important in this respect to remember to set cellB19 equal to 1−A19. Also the values in cells C19 and D19 must be shown according tothe formulae determining them rather than just their actual values. To begin using Solverwe choose “Tools” then “Solver” from the Excel menu. This produces the dialogue boxin Figure 9.8.Here we set the “Target Cell” as D19 as it contains the calculation for the portfoliostandard deviation. We aim to minimise this value (hence we check the radio button“Min”) by changing the weight in the WIUTDK$ index (which is located in cell A19).There is no need to impose the constraint that w1 + w2 = 1 (i.e. A19 + B19 = 1) because,as noted above, w2 is set automatically to equal 1 − w1 . We click the “Solve” button tobegin the optimisation and get the results of Figure 9.9, as shown in the worksheet “MinVar Using Solver”, which as expected is identical to the outcome using the minimumvariance formula. Therefore, again we ﬁnd that there are some beneﬁts, in terms of riskreduction, by changing a portfolio from 100% UK equities to a mix of 82% UK equitiesand 18% European equities.5Note that we can convert these weekly risk and returns to annual risk and returns by multiplying the risk(measured by standard deviation) by the square root of 52 and the returns by 52.
300
Applied Quantitative Methods for Trading and Investment
Figure 9.7
Worksheet “Min Var using solver” in the ﬁle “Laws001.xls” (pre-Solver)
Figure 9.8
The Solver dialogue box
Portfolio Analysis Using Excel
Figure 9.9
301
Worksheet “Min Var using solver” in the ﬁle “Laws001.xls” (post-Solver)
9.3 THE MATRIX APPROACH TO PORTFOLIO RISK6Equation (9.2) can be rewritten using matrix notation as:  σ11 σ12w12σp = w1 w2 ××σ21 σ22w2
(9.6)
Note that if you multiply the matrices out you end up with equation (9.2). The beneﬁt ofusing matrix notation is that we are able to generalise the formula to n-assets as follows:σp2 = w w
(9.7)
where w = (w1 , w2 , . . . , wN ) and  is a covariance matrix with the variance terms onthe diagonal and the covariance terms on the off-diagonal. The returns equation can besummarised by:rp = w µ(9.8)where µ is a vector of assets historical returns.To extend the linear version of the Markovitz equation (9.2) to incorporate more assetsbecomes particularly cumbersome as in addition to adding a term that incorporates thevariance we must also include a term that incorporates the covariance/correlation.76
For this and the following sections, the reader must be acquainted with the rudiments of matrix algebra.For a two-asset portfolio there exists just one unique covariance (σ12 ), for a three-asset portfolio there arethree unique covariances (σ12 , σ13 , σ23 ) and so on. It follows that for an n-asset portfolio there are N (N − 1)/2unique covariances.7
302
Applied Quantitative Methods for Trading and Investment
In Figure 9.10 we have applied the matrix version of the portfolio risk equation when themix of assets is 50:50. You will see that the outcome is the same as we found previously.Just as we have done previously with the linear version of the risk equation we canalso use Solver in this environment to ﬁnd the minimum variance portfolio which we
Figure 9.10
Worksheet “Portfolio Risk using Matrices” in the ﬁle “Laws002.xls” (pre-Solver)
Figure 9.11
Worksheet “Portfolio Risk using Matrices” in the ﬁle “Laws002.xls” (post-Solver)
Portfolio Analysis Using Excel
303
again ﬁnd is an 82:18 split between UK and European equities.8 Note that the screenshots of Figures 9.10 and 9.11 are taken from the same worksheet “Portfolio Risk UsingMatrices”, the only difference is that Figure 9.11 has had Solver applied to ﬁnd theminimum variance portfolio.
9.4 MATRIX ALGEBRA IN EXCEL WHEN THE NUMBEROF ASSETS INCREASESFrom the previous section we can see that modelling portfolio risk within Excel usingmatrices rather than the linear version of the risk equation appears far more convenientand ﬂexible. The ﬁle “Laws002.xls” includes data on equity indices from seven Europeancountries, including the UK and forms the basis of all operations in this section. As before,this ﬁle contains a number of worksheets, the titles of which are self-explanatory. We aregoing to utilise this dataset to show how portfolio analysis for portfolios with more thantwo assets can be modelled within Excel.The dataset comprises weekly European equity price indexes data over the periodJanuary 1996 to end June 2002. The list of data used is:WIFIND$ – FTSE WORLD INDEX – FINLANDWIEIRE$ – FTSE WORLD INDEX – IRELANDWIITAL$ – FTSE WORLD INDEX – ITALYWINETH$ – FTSE WORD INDEX – NETHERLANDSWISPAN$ – FTSE WORLD INDEX – PRICE INDEXWISWIT$ – FTSE WORLD INDEX – SWITZERLANDWIUTDK$ – FTSE WORLD INDEX – UNITED KINGDOMTo avoid issues of exchange rate risk all indices are expressed in US dollar terms. Thetwo worksheets “Raw Data” and “Returns” include the original data and the weekly logreturns respectively.As we did previously with the two-asset portfolio example we again calculate thesummary statistics of the returns datasets. These are then used as the inputs into theportfolio analysis examples which follow. The summary statistics shown in Figure 9.12can be found in the “Summary Statistics” worksheet. Here we can see that there is apositive trade-off between risk and return with the historical returns of Finland (in US8As an aside it should be noted that the minimum variance formula derived above can also be derived usingmatrix notation. Should we do this we ﬁnd that the optimal weight, w∗ , in asset 1 is given by:
w∗ =
 −1 ee  −1 e
where e is a unit vector. An application of this approach to the current dataset can be found in the worksheet“Min Var Using Matrices” where again we ﬁnd the optimal weights are 82% and 18% for WIUTDK$ andWIEXUK$ respectively. Notice here we utilised two new functions, “MINVERSE” and “TRANSPOSE”. Aswith the “MMULT” function, when we use these functions we must ﬁrst of all highlight the cells where theoutput is to be displayed and after completing the necessary ﬁelds in the function wizard we must rememberto press “CTRL + SHIFT + ENTER” rather than simply clicking “OK”.
304
Applied Quantitative Methods for Trading and Investment
Figure 9.12
Worksheet “Summary Statistics” in the ﬁle “Laws002.xls”
dollar terms) being the highest but with these returns also having the highest historicalstandard deviation of returns.9Note that it can be quite cumbersome to compute the covariance matrix when thenumber of assets becomes large as particular care has to be taken when utilising the“COVAR” function to ensure that the correct cells are referred to. When the number ofassets gets larger it is advisable to use the following result to construct the covariancematrix.E Eσij =(9.9)Nwhere E is a vector of excess returns,10 E is its transpose and N is the number of dataobservations.It is an easy task to construct a vector of excess returns. The spreadsheet in Figure 9.13(worksheet “Covariance”) shows how it is done for this dataset.At the bottom of this worksheet is the covariance calculation of Figure 9.14. Notethat the calculated covariances are identical to those found above using the “COVAR”function.119The data in this example has been selected so that it exhibits a positive relationship between risk and return.Quite often data may be selected that does not conform to this expectation as risk and return data is verysensitive to the choice of sample and to the choice of frequency.10Deﬁned as that period’s return minus the mean return.11An alternative method of obtaining the covariance matrix is to use Excel’s “OFFSET” function. This functionallows you to deﬁne a block of cells relative to some initial cell. Its arguments are (initial cells, rows and
Portfolio Analysis Using Excel
305
Figure 9.13 Worksheet “Covariance” in the ﬁle “Laws002.xls” (Excess Return Calculation)
In the worksheet “Portfolio Analysis” we use the matrix approach to portfolio risk:σp2 = w w
(9.10)
to ﬁnd the variance of an equally weighted portfolio.To do this we construct a column vector of weights (see cells B5 to B11 in the “PortfolioAnalysis” worksheet) which represents the w vector above. We then take the transpose ofthis vector12 to obtain the w vector above. Our covariance matrix () is drawn from the“Summary Statistics” worksheet. The matrix algebra involved in calculating the portfoliorisk can be broken down into the following matrix multiplications:(1 × N ) vector × (N × N ) matrix × (N × 1) matrixwhere here N = 7.Taking the last two operations ﬁrst the result we obtain is an (N × 1) matrix. This isthe result found in cells B26 to H26 of the “Portfolio Analysis” worksheet. If we thencolumns) where rows and columns are the row or column shifts from the original cells. In the spreadsheet“Laws002.xls” there are two worksheets that utilise this function. One is an introductory example entitled“Using Offset” whilst the other is an application of this function to the return databank. Once you are familiarwith this function you will ﬁnd that it is the most convenient way to ﬁnd the covariance matrix of largeasset portfolios.12Using the “TRANSPOSE” function within Excel. This is implemented in the same way as all matrix functions.
306
Applied Quantitative Methods for Trading and Investment
Figure 9.14
Worksheet “Covariance” in the ﬁle “Laws002.xls” (Covariance Calculation)
multiply the ﬁrst (1 × N ) vector by the result above (an (N × 1) matrix) we obtain ascalar as a result. That scalar is the portfolio variance and can be found in cell B28(Figure 9.15). Using this dataset we ﬁnd that the risk of an equally weighted portfolio is2.46% per week with a return of 0.12% per week.Fortunately within Excel it is not necessary to break this calculation down into theseindividual parts. Cell 34 includes the formulae necessary to combine the two matrixmultiplications into one cell, whilst cell C37 includes the formulae necessary to eliminateboth the matrix multiplication and also the vector transposition. It is therefore possibleto ﬁnd a portfolio variance in one cell given only a vector of portfolio weights and acovariance matrix. We are now going to utilise this framework and together with Excel’s“Solver” tool ﬁnd the portfolio composition that minimises portfolio variance.Previously when we used “Solver” in a two-asset environment the weight assigned tothe second asset adjusted automatically to changes in the weight of the ﬁrst asset. Withinan N -asset environment this is not possible and instead we have to add a number ofconstraints within the “Solver” dialogue box. The screen shot in Figure 9.16 includes therequired constraints. The signiﬁcance of the constraints are:• $B$5:$B$11<=1 – we cannot invest more than 100% of our wealth in any asset.• $B$5:$B$11>=0 – we cannot be “short” in any asset.• $E$5=1 – we must invest all of our wealth in these assets.Again we are minimising portfolio variance (cell B22) but this time rather than varyingjust one single cell we are varying the contents of the vector of weights (B5:B11). Theresult shown in the worksheet “Min Variance Portfolio” is given in Figure 9.17.
Portfolio Analysis Using Excel
Figure 9.15 Worksheet “Portfolio Analysis” in the ﬁle “Laws002.xls”
Figure 9.16
Worksheet “Min Variance Portfolio” in the ﬁle “Laws002.xls” (pre-Solver)
307
308
Applied Quantitative Methods for Trading and Investment
Figure 9.17
Worksheet “Min Variance Portfolio” in the ﬁle “Laws002.xls” (post-Solver)
This analysis shows that in order to obtain the minimum variance portfolio we shouldassign 65% of our wealth to UK equities, 28% to Swiss equities and 7% to Irish equities.Moreover, based on this dataset we should not invest any wealth in Finnish, Italian, Dutchor Spanish equities.
9.5 ALTERNATIVE OPTIMISATION TARGETSSo far we have concentrated our efforts on ﬁnding the minimum variance portfolio thatﬁts our dataset. However it can easily be shown that a variety of differing objectives canbe modelled within Excel.As an example, consider the scenario above where we found that a portfolio comprisinga 65% investment in UK equities together with a 28% investment in Swiss equities anda 7% investment in Irish equities produced a portfolio with a weekly return of 0.07%and a standard deviation of 1.98% per week. It may be the case that we would like tolimit our investment in UK equities to some predetermined ﬁgure, say 30%. In order toincorporate this into our analysis we simply add this constraint:$B$11=<0.3where $B$11 is the cell that includes the proportion of wealth invested in UK equities.The worksheet “Risk min with 30% max in UK” shows the results of this analysis wherewe ﬁnd the optimal portfolio consists of 17% Irish equities, 3% Italian equities, 10%Dutch equities, 39% Swiss equities and 30% UK equities. The return of this portfolio is0.08% per week with a standard deviation of 2.06% per week.We could also construct the optimisation problem such that we minimise portfolio risksubject to investing at least 5% in each index. This requires us to change the constraint
Portfolio Analysis Using Excel
Figure 9.18 Worksheet “Risk min with 30% max in the UK” in the ﬁle “Laws002.xls”
Figure 9.19 Worksheet “Return Max” in the ﬁle “Laws002.xls” (pre-Solver)
309
310
Applied Quantitative Methods for Trading and Investment
Figure 9.20 Worksheet “Return Max” in the ﬁle “Laws002.xls” (post-Solver)
$B$5:$B$11>=0 to $B$5:$B$11>=0.05, i.e. we must invest at least 5% in each index.The set-up (and results) for such a problem are shown in Figure 9.18.It is also possible to utilise “Solver” to reverse the problem to one of return maximisation subject to a given level of risk tolerance. The worksheet “Return Max” takes thecurrent dataset and attempts to ﬁnd a portfolio that maximises returns subject to a risktolerance level of 2.93%.13 The solver dialogue box is shown completed in Figure 9.19.Here you can see that the focus of attention is now on cell B25 (where the portfolioreturn result is calculated) and that an additional constraint:$B$23<=2.93 – portfolio risk is less than 2.93%has been added. The results are shown in Figure 9.20.Here we can see that a mix of investments of Finnish equities (30%), Irish equities(27%), Italian equities (28%) and Swiss equities (15%) produces a portfolio offering areturn of 0.16% per week with a target maximum standard deviation of 2.93% per week.
9.6 CONCLUSIONAs stated at the outset the aim of this chapter was to illustrate how Markovitz-typeportfolio analysis can be implemented within Excel. This is best achieved by looking atthe problem in its matrix format rather than its linear format and then implementing the13Note that an investment in Irish equities alone produces a risk of 2.93% per week with a return of 0.10%per week.
Portfolio Analysis Using Excel
311
calculations using Excel’s matrix algebra functions. If the problem is set up correctly itis then possible to use Excel’s “Solver” function to optimise the portfolio subject to avariety of constraints and targets.
B