Philip Bowcock joins the discussion on the calculation of equated yields, following Wu Shou Zhi’s recent article.
The data provided by Wu Shou Zhi is limited to two examples, and if this is the only information available then the solution he arrives at is unquestionable. However, his method uses rather lengthy formulae, and their interpretation is made more difficult by the obvious necessity of using a small font to accommodate them within the printed page.
The problem which he is solving is basically the solution of two simultaneous equations. Even those members of the profession who have not studied mathematics since their O-Level days will probably remember that a single equation with one unknown can be solved (sometimes with some complications such as several solutions); where there are two unknowns, two independent equations will be required, and so on. Some equations cannot be solved directly, but only by an iterative (or “trial and error”) approach.
In the problem discussed by Mr Wu we have two simultaneous equations, which also cannot be solved directly and which therefore require an iterative method. Hence the reason why his eventual solution is found from the valuation tables.
There is, however, a simple computer-based method of solving the problem using one of the standard spreadsheets. In the method described below a Macintosh computer running Microsoft Excel (v.5.0) was used, but the PC version is almost identical, and no doubt other modem spreadsheets are capable of doing the same calculation.
In this model the cells are set up as follows:
BI the initial guess of the equated yield (12%)
B2 the difference between the growth rates (ie subtract one from the other). The object is to find the equated yield at which the implied growth rate from the two investments is the same, when this difference will be zero.
The entries in each cell are as follows, and can be checked by calling the Tools: Options menu and clicking the Formula box. Care must be taken to enter the lengthy formulae in row 5 exactly as shown.
Unclicking the Formula box should show the following:
One other entry must be altered – under the Tools: Options menu, Calculation Maximum Change should be changed to.0000001 for greater precision, otherwise the iterative process may terminate too soon.
Having entered all the data from the two examples being analysed, we now use the “Goal Seek” function to find an equated yield which will give the same implied growth rate for both investments. If they are otherwise directly comparable, the solution is the equated yield. The Goal Seek function is found in the Tools menu and requires three entries, as follows:
Set cell B2……………………………………..To value 0……………………………………..By changing cell B1
Click “OK” and the screen will show a rapid series of calculations and will then appear as:
The equated yield found is 13.30% and the corresponding implied growth rate for both investments is now 8.10%, which is the solution sought, and is identical to Mr Wu’s example (save for slight approximation in his, owing to linear interpolation from the valuation tables).
The valuation of the freehold property would proceed as indicated by Mr Wu.
The problem of the reversionary interest may be dealt with in the same way, though setting up the spreadsheet is somewhat more complicated. The formulae in the spreadsheet cells are set up in the standard valuation format and the initial guess is entered in cell C12. Cell D12 is the difference between the calculated growth rates and the solution will be found when this becomes zero, as before.
In Formula View cells will appear as shown:
Unclicking Formula View will show:
The analysis is completed by using Goal Seek with the following entries:
Set cell D1………………………………… To value 0………………………………… By changing cell C12 and the solution will be found in cell C12 as shown in the next column, confirming Mr Wu’s solution, subject again to slight error resulting from his linear interpolation.
Unfortunately this is not the end of the story. In the first place, as Mr Wu mentions, the question of sensitivity arises. Data from transactions are seldom as precise as one would wish, and deals can be subject to many different negotiating whims of the parties concerned, which may make little difference to the transaction but can have a profound effect on the solution of the equations above.
By way of example, if the capitalisation rate of the second fully let freehold is changed from 5.6% to 5.5% the implied growth rate becomes 10.45% and the equated yield 15.46%. Similarly, if the sale price of the second reversionary interest is changed from £315,000 to £320,000, the equated yield is calculated at 7.06% – less than half the previous result!
Another complication is that the formulae as set out above take no account of the normal practice of rental payment quarterly in advance, nor of the actual date of the next receipt after the purchase. Both these may have a substantial effect on the results and, while modifications can be incorporated into the calculations, it increases their complexity.
Finally, given that there is variability in all deals done, reliance on only two pieces of data is, to say the least, inadvisable. Most valuers dealing with investment valuations will have many more transactions and each pair of transactions calculated as above will give a different answer. Three observations have three possible pairs, five will have 10 possible pairs, and so on. The problem obviously becomes much more complicated, as we are now looking for a “best fit” for all transactions analysed rather than a precise solution to two cases. Methods of finding this are, however, well beyond the scope of this column.
Philip Bowcock BSc ARICS IRRV is a lecturer in valuation at the Department of Land Management, University of Reading.
