Computing Markowitz Efficient Frontiers Using a Spreadsheet Optimizer

Abstract

Markowitz showed that assets can be combined to produce an “efficient” portfolio that will give the highest level of portfolio return for any level of portfolio risk, as measured by variance or standard deviation. These portfolios can then be connected to generate what is termed an “efficient frontier” (EF). Discusses the calculation of the efficient frontier for combinations of assets, again using the spreadsheet optimizer. To illustrate the derivation of the efficient frontier, uses the data from the Investment Property Databank Long Term Index of Investment Returns for the period 1971 to 1993. Many investors might require a certain specific level of holding or a restriction on holdings in at least some of the assets. Such additional constraints may be readily incorporated into the model to generate a constrained EF with upper and/or lower bounds. This can then be compared with the unconstrained EF to see whether the reduction in return is acceptable. To see the effect that these additional constraints may have, adopts a fairly typical pension fund profile, with no more than 20 per cent of the total held in property. Shows that it is now relatively easy to use the optimizer available in at least one spreadsheet (EXCEL) to calculate efficient portfolios for various levels of risk and return, both constrained and unconstrained, so as to be able to generate any number of efficient frontiers.

Citation

Byrne, P. and Lee, S. (1994), "Computing Markowitz Efficient Frontiers Using a Spreadsheet Optimizer", Journal of Property Finance, Vol. 5 No. 1, pp. 58-66. https://doi.org/10.1108/09588689410063210