Calculating true equivalent yield on a spreadsheet need not involve purchasing and having to master expensive software. By John Rich
The Investment Property Forum initiative on the recognition of quarterly in-advance income has now been running for three months. A supporting programme of Continuing Professional Development to raise the issue is under way.
Feedback from these initiatives indicates that not everyone has the means or mathematical knowledge to calculate a true equivalent yield (TEY), nor can they necessarily justify the expense of purchasing software which can.
Use a spreadsheet
Given that most surveyors’ offices use Microsoft Excel, there are a couple of ways a spreadsheet can be set up to calculate a TEY on a simple reversionary freehold property. All that is required is a certain minimum level of basic Excel skills, such as being able to identify cell addresses, format cells and use the menus.
Definition
The TEY is that single, quarterly in-advance, all-risks yield which, when applied to both the contracted passing rent and the estimated reversionary rent, produces the same valuation result as either the given sale price or a valuation arrived at using a combination of yields.
It also happens to be the same as the internal rate of return for the cash flow.
The IRR approach
This is a useful discipline to start with, since it sets out very explicitly the cash flow and then uses a standard Excel function to calculate the TEY without using any formulae which might be regarded as specialist to the valuation of property.
One simple aid to understanding what follows is to realise that, in theory, if not in practice, the purchaser does not actually end up out of pocket to the full extent of the purchase price plus costs of £286,722.
Setting aside the possible early payment of a deposit, completion is assumed to be 24 June 1999, which is conveniently a quarter day. The next quarters rent of £5,500 is therefore due, £275,000 is paid to the vendor, and £11,722 paid as fees and stamp duty. The net outlay to the purchaser that day is therefore only £281,222.
Of course, completions other than on quarter days are subject to an apportionment of rent, but that is an undue complication to this example, as is late rent payment.
It is now possible to set up a simple cash flow as shown on the spreadsheet Fig 1.
In order to get the spreadsheet onto one page, rows 13 to 400 have been hidden (using the hide command). They merely contain the respective quarter day dates in column A and the reversionary rental value of £6,250 per quarter in column B. The spreadsheet is run over 400 quarters in order to approximate to a perpetuity.
Using formulae to abbreviate
The same result as above may be achieved using slightly more complex formula to abbreviate the lengthy cash flow and then a different facility of Excel, Solver, to find the unknown interest rate that fits the formulae to give the required result.
Spreadsheet 2 uses the same property as the example and calculates its value in two ways – by “Basic and Increment” and “Term and Reversion”.
A TEY of 8.5% is guessed for each calculation and is found to give too high a value, but it does at least prove that both methods give the same result!
The cells shaded in yellow actually contain formulae and the values shown are calculated by these.
Spreadsheet 3 shows the formulae, and it is these that should be typed into the shaded cells when trying to replicate theset-up.
Spreadsheet 3 is a copy of Spreadsheet 2 made using the Tools/Options facility of Excel to show the formulae put into the yellow shaded cells.
These must be entered precisely as shown, but it is fair to say that, if an error is made, Excel may even come up with a warning and suggested correction.
In particular, it is important to note that a lot of cell addresses are used in the formulae, such as C4.
It is vital that the reference is made to the correct cell containing the source data, in this case 8.5%.
If the top two title rows were omitted, 8.5% would then appear in C2, which would then be the proper cell address in the formulae instead of C4.
Once a spreadsheet such as the above has been set up, it is vital to test it against known data so that the user is completely confident that it is accurate and calculating as expected.
Next comes the bit that Excel makes so easy. Still using Spreadsheet 2, select Tool/Solver (not illustrated).
The contents of 3 cells in Solver Parameters should be noted. Set Target Cell is the address of the cell containing the value net of costs or purchase price, F9.
It can either be typed in or, if the mouse is left clicked on the red arrow at theright-hand end of the box, a reduced Solver Parameters box appears.
If the cell F9 in now clicked with the mouse, it will appear in that box. To return to the main Solver Parameters screen left, click at the right-hand end of the box on the red arrow.
Repeat the procedure in the box headedBy Changing Cells, but this time select C4 ie. the TEY you are seeking. Next, move up to the line showing Equal To and click in the right hand of the three circular buttons. A black blob appears to indicate that you have selected “value of”.
Finally, fill in the purchase price of £275,000 and then click the mouse on the solve button. A gong should sound to tell you a solution has been found, which will hopefully be 9.07%, the same as that arrived at using the IRR method.
One of the great advantages of Excel is that it makes abstract formulae actually possible to solve without vast mathematical knowledge or a pile of tables.
Providing a disciplined approach is adopted of placing brackets round each part of an equation, with the innermost brackets around those parts which have to be worked out first, then with just a basic knowledge of algebra it is not as daunting as it looks. Furthermore Excel will try to put you right if an error is made.
It is useful to note that “to the power of” is designated by ^ (above the 6 on the keyboard) and a “root of” is the inverse of “to the power of”, ie 1/the power of. Hence, the fourth root is the same as ^.25.
It is hoped that this will give a simple and useful introduction to using Excel to work out yields and to undertake simple valuations. If the techniques are mastered, then it is not a great step to move onto the setting-up of similar spreadsheets to deal with more complicated properties and leaseholds.
John Rich MA FRICS is a consultant in private practice
|
Find the TEY for this |
|
|
Lease |
15 years from 25.12.1995 |
|
Rent (a) |
£22,000 pa payable quarterly in advance |
|
Rent reviews |
Every five years |
|
Estimated rental value (b) |
£25,000 pa |
|
Sale price |
£275,000 |
|
Sale/valuation date |
24 June 1999 |
|
Purchaser’s costs |
4.26% – £11,722 including VAT on fees |
|
Gross purchase cost (c) |
£286,722 |
|
Present yield (a)/(c) |
7.6729% (annually in arrears) |
|
Reversionary yield (b)/(c) |
8.719% (annually in arrears) |
|
What is the TEY? |
|
|
Calculating Tey with and without formulae |
|
Note 1: A final value equating to the purchase price is taken as a sale 100 years hence to equate to perpetuity. Note 2: A formula calculates the internal rate of return over 400 periods. The length of a period is immaterial, for we know that four periods equate to one year, so this is a quarterly IRR. Cell B402 is formatted as a percentage to four decimal places. Note 3: The formula has to be typed exactly as shown – ie =IRR(B2:B401,2%). This all means the following = Excel is to carry out a mathematical function; IRR The function is to calculate an internal rate of return; (B2:B401, The calculation is to cover all the cells in the column B between rows 2 and 401; 2%) This is a guess at where the answer may lie, to speed the process of trial and error Note 4: The quarterly IRR is turned into an annual one using a formula. The cell B406 is formated to two decimal places. Note 5: The formula has to be typed exactly as shown, ie =(1+B402)^4-1 This all means the following = Excel is to carry out a mathematical function; (1+B402) The function is to add 1 to the contents of cell B402 (ie the quarterly interest rate); ^4 The above sum is raised to the power of 4; -1 1 is deducted to give the quarterly in-advance annual rate |