Real Estate Portfolio Analysis
Using a Spreadsheet
Optimizer
Peter Byrne and Stephen Lee

Real Estate
Portfolio
Analysis
19

University of Reading, UK
Introduction
The modern spreadsheet is a powerful piece of software. The majority of spreadsheet users will never need most of the sophisticated features to be found in such a package, and many will not even be aware of the inherent functions and tools which exist within the spreadsheet. When spreadsheets are used to do advanced analysis, there is a need to take particular care. This requirement has been identified on several occasions (see for example [1-3 ch. 5]).
In a recent paper, Matysiak[4] suggests, with some justification but a certain lack of detail, the use of software called optimizers for looking at some kinds of real estate investment problems. He specifically considers the risk associated with capital and income cover and, more particularly, the risk-return trade-off in portfolio asset allocation under different scenarios using modern portfolio theory (MPT).
MPT has been advocated as a more rational approach to the construction of real estate portfolios[5]. This is a process which can now be achieved with relative ease using the powerful facilities found in spreadsheets, and does not necessarily require the use of specialist software. This capability is to be found in the use of an add-in tool found in several of the latest generation of spreadsheets, called an optimizer or solver.
Spreadsheet Optimizers
What is an optimizer? In mathematical terms, any method which can be used for finding the optimum value (which may be a maximum or a minimum) of a function or expression f(x1, x2,…xn) of n real variables can be described as an optimizer. If, for example, the expression is concerned with measuring return, then normally we will seek to maximize that value. If the variable being considered is risk, then we will usually wish to find a result which minimizes the risk. Often the values of the variables may be limited or constrained in some way, and this in turn may affect the optimum value that can be achieved for the expression. In some instances we may wish deliberately to restrict the outcome by the way that we define these constraints, and hence observe the consequent change in the output value.
The authors are grateful for the helpful comments of an anonymous referee in preparing this article. Journal of Property Finance,
Vol. 5 No. 4, 1994, pp. 19-31
© MCB University Press, 0958-868X

Journal of
Property
Finance
5,4
20

A spreadsheet optimizer uses sophisticated, but standard, numerical methods to solve equations. These methods are usually iterative, that is they start with a first approximation, or “guess”, and by successive trials home in on the optimal result. There are a number of different methods of optimization available, and the user must be confident in the method employed, because some methods may not yield a solution to particular problems, or there may be multiple solutions in some cases.
Example
For illustrative purposes, Matysiak’s main example is reconsidered here. It has been implemented in this case using Microsoft’s Excel[6] version 5 for Windows spreadsheet, which includes an optimizer called Solver[7]. Similar optimizers are to be found in other spreadsheets; Quattro Pro and Lotus 123 for example, and indeed the results shown below have been confirmed using Quattro Pro.
The example takes the form of a classic asset allocation problem. Here we are seeking to evaluate an investment portfolio which will consist of a mixture of real estate assets only. In essence the problem is to find a mix of these assets which will optimize the risk-return combination with the objective of either maximizing portfolio return or minimizing portfolio risk (see below).
Initially, a scenario type approach is used by Matysiak to assess the relative performance of each asset class under