Rack rented freehold valuations by spreadsheet - introducing flexibility

Citation

French, N. (2007), "Rack rented freehold valuations by spreadsheet - introducing flexibility", Journal of Property Investment & Finance, Vol. 25 No. 4. https://doi.org/10.1108/jpif.2007.11225dab.001

Rack rented freehold valuations by spreadsheet - introducing flexibility

Rack rented freehold valuations by spreadsheet – introducing flexibility

Introduction

In the previous article in this series, I looked at the construction of a static valuation spreadsheet model to value a rack-rented property by an implicit and explicit method. The following is an expansion of this approach to allow the lease length to vary along with the other variables discussed in the previous article. It should be stressed that the approach adopted in this article to create flexibility over time is only one approach. Spreadsheets have a wealth of functions that can be adapted to allow for flexibility. These are discussed at length in Stephen Fawcett’s (2003) book Designing Flexible Cash Flows published in the UK by Estates Gazette Books. In this article, I shall be concentrating on the logic function of excel called = IF or the IF function.

The example

Using the same example as the previous article, we valued a rack rented office building let at a market rent of £1m. The All Risk Yield (Initial Yield) used in the implicit method was 8 per cent and the corresponding Target Rate (Equated Yield) was 10.75 per cent. This represented a required annual growth expectation of 3.2 per cent. The static calculation of this valuation is shown in Figure 1 using both an implicit and explicit model.

Figure 1 Rack rented valuation

The spreadsheet version of the same revealing the cell entries discussed in the previous article are shown in Figure 2 (Inputs/Outputs), Figure 3 (Implicit Model) and Figure 4 (Explicit Model).

Figure 2 Spreadsheet input/output page (showing formulae)

Figure 3 Spreadsheet traditional method (showing formulae)

Figure 4 Spreadsheet DCF method (showing formulae)

As can be seen, this is a relatively flexible model already as the four main variables (Rent, All risk yield, Rent review and Target rate) can all be changed in the input screen and the valuation will change accordingly. But it does not allow the user to look at different holding periods or lease length.

In the flexible model we will adapt the explicit DCF approach to allow for the lease length to be anything between five years and 50 years. The latter number maybe excessive for any reasonable valuation approach but it is useful to use a long lease length to illustrate the flexibility of using = IF statements.

Using=IF statements (The IF worksheet function)

The “=IF” function works on the principal of: “Ask a question”, “if true put “x”, If false put “y”

The format is=IF(question, x, y)

Within the two values, x or y, the user can include either a formula or, by encasing the statement in double inverted commas (speech marks), words.

Thus the=IF function checks a condition that must be either true or false. If the condition is true, the function returns one value; if the condition is false, the function returns another value. In the excel help, it refers to the logic statement as follows.

Simple example

It is worth looking at a very simple example of an = IF statement. Thus if we go to a blank worksheet, and type in an arbitrary number between 0 and 40 (in Cell C2 in Figure 5). We then type in an = IF statement below that to do one of two things. This is illustrated in Figure 5.

Figure 5 Simple = IF statement

IF the number in C2 is greater than 25, I want the answer to appear in that cell (B4) to be (say) twice the original input. However, IF it is 25 or less, I want the words “Too Low” to appear. Thus I am able to illustrate the two outcomes of an=IF statement. It will either calculate the formula or it will insert words. To do this the = IF statement is written as = IF(C2 > 25, 2*C2,”Too Low”). The first part within the bracket, C2 > 25 is the logical test, we then insert a comma and write what we want to appear in that cell if the logical statement is true (value if true), then another comma and write what we want to appear in that cell if the logical statement is false (value if false). In this example, the “value if true” is a simple formula. The answer should be 2 times the original input. Conversely, the “value if false” is then the words “Too Low” will appear. To make words appear, the = IF statement must put the required words in double inverted commas. A useful variation on this function is to exclude any words from within the double inverted commas (e.g. “”) and the cell in question will display nothing. It will appear blank.

The years columns

As with the static model, the trick for flexibility is to use the year columns (B6-C11) as an “anchor” for the calculation. In other words, if we can get the years to rise and fall in keeping with the lease length, then we can get all the entries on the respective rows to coincide accordingly.

To start this adaptation of the existing spreadsheet, we need to name the cell containing the lease length in the Input/Output screen (Figure 2, C11). We name this cell ll. To name a cell, we double click on the cell address on the left corner of the lower top menu and overwrite the cell reference to ll.

The other thing to remember is that flexible spreadsheets are built up “step by step”. That is, we get on section to work correctly then move onto the next section.

The first years column – Column B

In this case, if we will look at column B, in Figure 4, the initial year in each rent review chunk (at the moment five years) will be 1 or 6 or 11 etc. as the years progress. We leave Year 1 (Cell B8) as the number 1 as that will always apply. Thus we change the cell address below. Remember that the = IF statement makes an addition to what is already there. So the = C8+1 in Cell B9 should remain. What we want to do is to add a logic statement. This is illustrated in Figure 6.

Figure 6 Spreadsheet DCF method (Years columns 1 – as hidden formulae)

The simple logic that we want to use is that once the final year has been reached, then we do not want anything to appear in the cells below. Thus if the lease length, in the inputs, is 15 years, then the final number we want to appear in the final row in column B will be 16 (ie. Lease length +1) so it will read in columns B and C “16 to perp”. If the lease length was 25, we want the worksheet to adjust accordingly and insert the correct numbers in column B through to 26, so it will read “26 to perp” and then nothing in the rows below. To do this we simply tell the worksheet to leave the cells blank when the diagonally contiguous cell in column C reaches the lease length. In this case, if (in Figure 6) Cell C10=15 , then B11 should show 16 but once it is higher then it should stop and show blanks. Remember in an = IF statement a blank is a set of double inverted commas (“”).

Thus the first = IF statement, which we type into Cell B9, should read:

This means if the number in Cell C8 is less than or equal to 15 (and it will be as it is 5), then put in the number in Cell C8 plus 1, which equals 6. This is what we want to happen. We can then copy this down (for as far as you wish) and, because we have used relative cell addresses, it will put 11 in the cell below and 16 in the cell below that. However, when it is copied to the next cell below the false value clicks in as the word “perp” in C11 is read as being a word and thus the logical test is obviously false and the double inverted commas is displayed so the cell address, B12, appears blank. The formulae for this is shown in Figure 6, the resulting numbers in Figure 7.

Figure 7 Spreadsheet DCF method (Years columns 1 – numbers as displayed)

Note, that in the figures, I have only copied the formula down to B15 to make the display in the article manageable. In the actual spreadsheet, you can copy the formula down as far as you wish.

The second years column – Column C

Thus the first column (Column B) is now flexible, if you go to the inputs and change the lease length to 25, the column will display the relevant numbers (1, 6, 11, 16, 21, 26) in cells B8 to BB13 respectively, but stop and show blanks in the cells below. But we have not yet adapted Column C to correspond, so the numbers appearing there stay the same. This needs to be corrected. This is a two-stage process. First, we introduce an = IF statement to make the years rise and fall in tandem with Column B. Once this is completed we change the = IF statement to ensure that the word “perp” appears in the last row.

The first stage of this is a very simple logic using the fact that Column B is already working correctly. We know that we want a blank to appear in the row in Column C if there is a corresponding blank in Column B. This is easy to achieve.

Thus the first = IF statement in Column C which we type into Cell C8, should read:

This means if the cell to the side (B8) is blank, then C8 should also be blank. If it is not blank, then the cell should display the number plus the rent review number. This is what we want to happen. We can then copy this down (for as far as you wish) and, because we have used relative cell addresses, it will put 10 in the cell below and 15 in the cell below that until it reaches (at lease length 15) 20 in Cell 11.

The problem is that we do not want 20 to appear in that cell (Cell11), we want the word “perp” to appear. To achieve this we use a new function called “nested = IF function”. As the name suggest, this allow the user to put more than one logical test into one statement. Up to seven IF functions can be nested as “value if true” and “value if false” arguments to construct more elaborate tests. This is sometime called the imbedded IF statement.

In this case, we want the existing = IF to continue but with the added test that if the number above is the lease length, then it should put in the word “perp”

Thus the first = IF statement in Column C which we type into Cell C8, is edited to read:

The second IF statement is simply inserted in to the original “value if false” statement. Note that the second IF statement is not preceded by an equals sign ( = ).

This then produces the desired result. See Figures 8 and 9.

Figure 8 Spreadsheet DCF method (Years columns 2 – as hidden formulae)

Figure 9 Spreadsheet DCF method (Years columns 2 – numbers as displayed)

The rent column – Column D

We have nearly finished as the other columns (with one exception, see the YP column below) as easily modified to be flexible. We simply preface all the existing formulae with the same amendment as originally used in Column C. That is we know that we want a blank to appear in the row in Column D if there is a corresponding blank in Column B.

Thus the first = IF statement in Column D which we type into Cell D9, should read:

We can then copy this down and, because we have used relative cell addresses, it will put the relevant increased rent in the cell below and below and below etc.

We leave the formula = rent in the first row D8.

The YP column – Column E

The YP column is slightly different to Column D above. The same preface of = IF(B8=““,”“, is inserted in front of the existing formula in Cell E8 so it reads:

But when this is copied down, it works correctly in all the rows (showing the YP multiplier for each chunk or showing a blank if beyond the lease length) except in the row where “perp” appears. Remember in this row we wanted it to read 1/k and not = (1−(1 + e) ^{^}− rr) / e. Thus we have to nest another IF statement into the existing function.

This is again, relatively simple as we know that we only want = 1/k to appear in the same row as when column C displays “perp”. Thus we can use an = IF statement that says that if the relative cell in Column C=∼“perp”, then put in 1/k, otherwise carry on with the previous = IF logical test. This reads as:

Now when this I copied down, it displays the YP for the rent review in all the chunks of time prior to the lease end but it displays the YP perp in the final row and nothing (blanks) below. This is shown in Figure 10 which should be linked to Columns B and C in Figure 8.

Figure 10 Flexible (Years columns 3 to 7 – as hidden formulae)

The PV column – Column F

The amendment to the PV column is the same as Column D. The same preface of = IF(B9 = “”,””, is inserted in front of the existing formula in Cell F9 so it reads:

We leave the number 1 in the first row F8.

The PV £ column – Column G

The amendment to the PV £ column is the same as Column D. The same preface of = IF(B8 = “”,””, is inserted in front of the existing formula in Cell G8 so it reads:

The capital value column – Column H

There is no=IF statement in the final column, the only amendment that is required is to extend the summation block to cover all the cells in the PV column where there is a formula. In my example, (for illustration) I have only copied the formula down to row 13 because of space restriction in printing out the table, but if you wanted your spreadsheet to allow for a lease length of 50 years, then copy all the formulae in each column down to row 18 and make sure that the summation formula is = SUM(G8:G18)

Conclusion

This briefing has shown you how to create a flexible spreadsheet model for valuing a rack rented property. It should be stressed that the logical tests that I have chosen to use are only examples. Everyone thinks differently and thus different people will use different logic to achieve the same ends. There is no right approach; develop your own style and your own logical tests that work for you.

A copy of the Excel spreadsheet used in this article is available by e-mailing nick.french@brookes.ac.uk

Nick French