Rack rented freehold valuations by spreadsheet

Citation

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

Rack rented freehold valuations by spreadsheet

Rack rented freehold valuations by spreadsheet

Nick French

Introduction

In the earlier articles in this series, I looked at the use of implicit and explicit valuations to value rack-rented property; property let at Market Rent[1]. Since the publication of those articles, I have received a number of requests to illustrate how this might be calculated on a spreadsheet.

The following is therefore an exposition of one approach to designing a spreadsheet to undertake this valuation. It should be stressed that there are many approaches to designing spreadsheets ranging form simple static models (illustrated here) to more complex flexible models (to be discussed in the next Education Briefing). If you wish to consider other approaches or to develop your own spreadsheets specifically for property applications[2], the following books are recommended.

Books

• •

  Philip Bowcock and Natalie Bayfield (2001) Excel for Surveyors, Estates Gazette Books.

• •

  Philip Bowcock and Natalie Bayfield (2003) Advanced Excel for Surveyors, Estates Gazette Books.

• •

  Stephen Fawcett (2003) Designing Flexible Cash Flows, Estates Gazette Books.

The example

If we refer to the simple example that we used previously, 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 calculation of the annual growth is shown in Figure 1 and the two valuations, implicit and explicit, in Figure 2.

Figure 1

Calculation of annual expected growth

Figure 2

Rack rented valuation

Spreadsheet construction – general

A spreadsheet is called a “workbook”. Each workbook is made up of a number of “worksheets”. These are separate pages that can be accessed by the “tabs” at the bottom of the workbook.

It is important that when you are writing a spreadsheet from scratch that you follow some simple rules.

• •

  Always put all salient information (such as interest rates, no of years, income amounts etc. In fact anything that you might use in a formula) on an input screen on the first worksheet.

• •

  Use as many worksheets as appropriate. Do not do everything on one sheet. Double clicking on the tab and overtyping can name each sheet.

• •

  Use the name function (under insert/name or by double clicking on the cell address on the left corner of the lower top menu) to name each of your inputs.

• •

  Do not ever put numbers in your typed in formulae. Every formula should relate to either a cell address or a cell name.For example, if you are writing the Present Value formula where your input for “i” is 10% and for the number of years “n” is 5 Do not write=1/(1+.10)^5 Write=1/(1+A4)^C7Where cell address A4 contains your input for the interest rate and C7 is the respective number of years. Or it is even better to name the cells. So if you name A4 as “i” and C7 as “n”, you can write the formula as: Write=1/(1+i)^n

Spreadsheet construction – static model

A static model is one where the framework of the spreadsheet model is fixed. For example in this case, the lease length (holding period) will be fixed at 15 years, so that the sale of the property will, for the explicit DCF model, happen at the beginning of year 16. A flexible model, to be discussed in the next Education briefing, would allow the user to change the holding period. Apart from that the static model could be considered to be a misnomer as all the other variables can be changed.

Thus if we look at the inputs required in our example, the spreadsheet model will allow us to change the inputs for the Rent, the ARY, the Equated Yield and the Rent Review Pattern. This is illustrated in Figure 3 that shows the Input/Output page for our example. The Bold Text for the four variables listed above are true inputs, they can be changed at any time and the valuation models on the next worksheets will adjust accordingly (see on).

Figure 3

Spreadsheet input/output page

The inputs

Looking at the first box in Figure 3 (B5 to D11), it can be seen that:

1. 1.

   Rent of £1,000,000 is typed into C7

2. 2.

   The ARY of 8 per cent is typed into C8

3. 3.

   The Equated Yield of 10.75 per cent is typed into C9

4. 4.

   The Rent Review pattern of five years is typed into C10

The lease length of 15 years is noted in the input screen but we will not use this input in the static model in this article; we will add this input to our flexible model in the next Education briefing.

Named inputs

We have then “named” all the four variables above by using the excel name function (under insert/name or by double clicking on the cell address on the left corner of the lower top menu) so that we can refer to the names in any formulae rather than the cell address.

1. 1.

   C7 has been named “Rent”;

2. 2.

   C8 has been named “k”;

3. 3.

   C9 has been named “e”;

4. 4.

   C10 has been named “rr”;

You can see that the title of each variable has included the name of the cell as a reminder to the user. Thus cells A7 to A10 have been titled as follows.

1. 1.

   Market rent (rent).

2. 2.

   All risk yield (k).

3. 3.

   Equated yield (e).

4. 4.

   Rent review (rr).

Note that you must name the cell where the number has been inputted not the title. If you make a mistake and name a cell incorrectly, you cannot use that name again in this workbook. So, the incorrect entry must be deleted before you call the correct cell by that name. To do this, go to the standard toolbar menu and click “insert”, then click “name” in the submenu and “define” in the next menu. This then lists all the named cells. Highlight the name that has been entered incorrectly and click the delete button followed by OK. You can then go and name the correct cell with the name that has just been deleted.

Calculated inputs

The four variables listed and named above will be the only variables that the user of the spreadsheet will need to change. However, there are other “inputs” that are needed to undertake the explicit DCF model; namely the annual growth. These are referred to as “calculated inputs” as they flow directly from the four true input variables. Thus you can see that the Calculated Inputs in cells F5 to G8 in Figure 3 undertake the calculation that is shown in Figure 1.

Calculated growth

Looking at the second box in Figure 3 (F5 to G8), it can be seen that the calculated growth formula has been broken down into two component parts and the final answer (g).

The growth formula is:

but as we know the inputs for “k” and “e”, this can be rewritten as:

SF is the annual sinking fund (ASF) for the rent review period (in this case, five years) at “e”. The formula for this is:

This is the first component part of the overall formula and is calculated separately in cell G6 where the formula above is rewritten as an excel formula as:

Cell G6 has then been named as “ASF” and this transferred to Cell G7 where General formula B above is rewritten as an excel formula as follows to calculate d the percentage growth over the rent review period (in this case five years):

Cell G7 has then been named as “P” and this transferred to Cell G8 where the following excel formula is used to calculate the annual growth “g”.

Cell G8 has then been named “g” and this can now be used in the explicit valuation worksheet (see on).

We now have all the inputs (actual and calculated) that we need to undertake the implicit and explicit valuations. However before we move on to creating the valuation models it is useful, for ease of reference, to repeat the information in Figure 3 but to show the formula in each of the cells. This is shown in Figure 4.

Figure 4

Spreadsheet input/output page (showing formulae)

Implicit (traditional) method

As discussed earlier, it is good practice in spreadsheet construction to separate the “Inputs” from the calculations. Thus a second worksheet is used for the traditional valuation and the tab of this worksheet is renamed ‘Traditional method’.

This is shown in Figure 5 where the calculation is very straightforward. The rent figure is brought from the input page by typing =rent in cell D6. Similarly, the YP perp figure in D7 is referenced to the input screen as =k. The valuation is therefore D6*D7 in cell H7. The only slightly complicated entry is in cell B7 where it appears as YP perp @ 8.00%, yet the cell only contains =k. This is because I have used the special format feature called “custom”. To use this, click Format, Cells, Custom and type “YP perp @” .00%” in the type box. This will then display the ARY with the words YP perp @ before the figure. Thus when the input ARY is changed, the valuation description on this worksheet is changed accordingly. The formulae on this worksheet are shown in Figure 6.

Figure 5

Spreadsheet traditional method

Figure 6

Spreadsheet traditional method (showing formulae)

Explicit (DCF) method

As with the implicit valuation, we start a new worksheet for the DCF model. Thus a third worksheet is used for the DCF valuation and the tab of this worksheet is renamed ‘DCF Method’.

As with the traditional valuation worksheet, the trick is to ensure that all inputs are taken from the input screen and that no numbers are retyped into the actual calculation; everything is by reference to inputs and formulae.

The years columns

The other trick is to use the year columns (B6-C11) as an “anchor” for the calculation. This is particularly important when we develop the flexible model in the next Education Briefing. By “anchoring”, I am referring to using inputs to display the years and not typing numbers (apart from year 1). Thus cell B8 has 1 typed into it but C8 is =rr (so the number 5 appears in our example, but if the rent review input on the input worksheet is changed to 3, then the number 3 would appear in our DCF calculation in this cell). We then maintain this flexibility so that in cell B9, where we want the year to read as 6, we do npt type in 6 but instead refer to the cell above plus rr; so B9 is=B8+rr. This logic is then repeated in C9 which is =CB+rr. The two cells B9 and C9 can now be copied down and the years will expand in five year “chunks” for as long as we wish. However in this case we only want B9 copied down two rows and C9 one row, as we are selling the asset in year 16 and cell C11 has “perp” typed into it. This is all illustrated in Figure 7.

Figure 7

Spreadsheet DCF method

The rent column

Moving to the Rent column, cell D8 is =rent and the cell below is =rent*(1+g)^C8, this allows the rent to grow at the implied growth rate of 3.2 per cent per annum where D8 is 5. We can then copy that cell down and because we have used the relative reference of D8, when it is copied to the cell below, it will become =rent*(1+g)^C9 where C9 is 10 years and again to the next row, it will become =rent*(1+g)^C10 where C10 is 15 years.

The YP column

The title of this column uses the custom format function discussed in note of the implicit valuation above. In this case, the custom notation is “YP @” .00% so that when you type =e into this cell it reads “YP @ 10.75%”.

The formula in the first three cells in the column (E8, E9 and E10) is the YP formula by reference to the input screen and reads =(1−(1+e)^−rr)/e, the final cell in this column needs to be the YP perp formula at the ARY and reads =1/k.

The PV column

The title of this column also uses the custom format function. In this case, the custom notation is “PV @” .00% so that when you type=e into this cell it reads “PV @ 10.75%”.

There is no PV requirement in the first row of the DCF worksheet as the YP formula will have already discounted the five rent payments of £1,000,000 back to today. Thus we type 1 into cell F8. The formula for the PV for the next five year “chunk” is then linked to the year’s column and the formula in cell F9 is =(1+e)^−C8. By using C8 in the formula, this is a relative cell and address and when we copy that cell down it will become =(1+e)^−C9 where C9 is 10 years and then again to the next row, it will become=(1+e)^−C10 where C10 is 15 years.

The PV £ column and capital value

The final two columns are extremely straight forward. In the PV £ column, the three numbers in the same row (Rent, YP and PV) are multiplied across the page. This formula in the first row is =D8*E8*F8, which adjust relatively as it is copied down the table. The final column shows the Capital Value and this is simply the summation of the PV£ column by reference to the cell addresses: =sum(G8:G11)

All the formulae are shown in Figure 8.

Figure 8

Spreadsheet DCF method (showing formulae)

Outputs

The spreadsheet calculations, implicit and explicit, are now complete and all that is needed is for the two answers to be copied back to the Input/Output page into the box named Outputs (B13 to D17 in Figures 3 and 4). This allows the user to see the impact of any change in any of the inputs on the same worksheet. To do this, simply move the cursor to the input/output worksheet and press =, then move your cursor to the worksheet where you have the answer that you wish to copy, highlight that cell address and press enter. The answer will now be repeated on the output screen.

Conclusion

This briefing has shown you how to create a very simple spreadsheet model for valuing a rack rented property. In the next briefing, we will develop this spreadsheet to include the ability to change the holding period along with the four variables addressed in this article.

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

Market Rent (Rack Rent) – the best rent readily achievable in the market today.

If you need to develop more generic spreadsheet skills, there are a host of Excel/Lotus texts available from any good book store.