Essays /

123 Ewrf Essay

Essay preview

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 different possible, but uncertain, economic states of nature. This is of course ideally suited to spreadsheet analysis, allowing as it does considerable scope for the “what-if ” type of investigation. Table I presents the basic data for the example. In order to use an optimizing tool to examine such a problem, all the elements need to be properly defined before the optimizer is used. Indeed an initial solution is required to act as a starting-point for the optimizer to work from. To do this, the problem is defined in terms of a special case, the naïve portfolio. In the naïve portfolio the proportion, or weight, assigned to each asset in the portfolio is equal and since there are three possible assets the weight of each is 0.33. The...

Read more

Keywords

-3 -31 -32 -5 -6 -66 -75 -868 -91 0 0.00 0.20 0.30 0.33 0.333 0.33333333 0.50 0.65 0.93 0958 1 1.00 1.44 1.66 1.67 1.83 10 100 100.00 11 11.00 12 12.62 123 13.23 14.00 15.20 15.99 16.00 17.7 17.70 18.00 182 19 1952 1984 1987 1988 1991 1992 1993 1994 1σr 2 2.00 2.40 2.83 20 200 21 22 23 23.45 24 25 25.00 26 26.41 27 28 29 3 3.36 3/4 30 30.66 30.77 31 31.67 33.33 341 4 433 5 5.14 5.76 58 6 6.80 68 68.33 69.23 69.34 7 76.55 77 8 8.00 8.04 8.76 9 access accompani achiev act actual ad add add-in adding-in addit adjust advanc advoc affect agre alloc allow also alter altern although alway among analysi analyt anonym anoth apart appear appli appreci approach approxim arithmet arriv articl assess asset assign assist associ austin author avail awar b.d back basic becom benchmark bevan bewar beyond bind black blindfold bound box bring button byrn calcul call cannot capabl capit care case cell cent certain ch chang check class classic clear cleveland click cliff close closer code coeffici combin command comment compar comparison complet complex comput concept concern conclus confid confirm consequ consid consider consist constrain constraint construct contain content context continu copyright correct correl could cours cov covari cover critic crucial data dbase decim decreas default defin delet deliber depart deriv describ detail develop deviat dialog differ difficult direct discuss display distinguish dixon document done e earlier eas easi easili econom effect effici either element emerg employ end englewood ensur equal equat essenc estat estim evalu even evid ewrf examin exampl excel exist expect express f facil far featur figur final financ find first fn follow form formul formula found fraction frontier frontlin fulli function g g.a garbag generat gigo give given go grate great greater grg2 growth guess guid h h.m hargitay help henc highest highlight hold home howev i2 ideal ident identifi ii iii ij illustr implement import in-garbag inc inclin includ incom incorrect increas inde industri infinit inher initi input insid instanc instead introduct invest investig investor invok involv iter iv j journal justif kind known l lack land larger lasdon last latest launch lee length less level levi limit line linear logic london look lotus m macgregor made main maintain major make manag mani manual markowitz mathemat matrix matysiak matysiaksolv maxim maximum may mcb mean measur menu method methodolog microcomput microsoft minim minimum mix mixtur model modern mpt multi multi-asset multipl must n nanthakumaran natur naïv necessarili need negat nevada never new next nj non non-linear none nonlinear normal note notic number numer o.a object observ obtain occas occur offer offic often one optim optimum option order outcom output p p.j packag pair paper paramet part particular per perform permit perspect peter piec pj place point port portfolio portion posit possibl power pp practic prenticehal prepar present press previous principl pro probabl problem proceed process produc program proper properti proport proverbi provid purpos put quadrat qualiti quattro quit r r1 r2 rang ration read real recent recess reconsid refer refere reflect relat relev remain replic requir research restrict result retail return return-risk reveal ri risk risk-return risk/return rj rj1 rj2 robinson role root rport s.e saga sarnat scenario scenario-assist scope sd second section sector see seek seen select sens sensibl set set-up sever show shown similar simplifi sinc size small societi softwar solut solv solvabl solver sophist sought sourc special specialist specif specifi spon spreadsheet squar standard start starting-point state statist steadi step step-by-step stephen still streamlin strong structur stumbl subject substanti success suggest suit sum summar summari support surpris surveyor system t.j tabl take taken tangent target technic tedious tell tendenc term test texa theme theori therefor thing third three thus time tool total trade trade-off tri trial true turn tutori two type uk unaffect unalt uncertain unconstrain understand understood unfortun univers unless us use user usual v valu valuabl valuat vari variabl varianc varieti verifi versa version vi vice vii viii villag vol w waren way weight what-if wi wi2σ window wish within without work would x x1 x2 xn yield zero ρ σ σr