In the UK, valuation methodology has traditionally been classified into five methods:

1. Comparable method.

   Used for most types of property where there is good evidence of previous sales.

2. Investment/income method.

   Used for most commercial (and residential) property that is producing future cash flows through the letting of the property. If the current Estimated Rental Value (ERV) and the passing income are known, as well as the market-determined equivalent yield, then the property value can be determined by means of a simple model.

3. Accounts/profits method.

   Used for trading properties where evidence of rates is slight, such as hotels, restaurants and old-age homes. A three-year average of operating income (derived from the profit and loss or income statement) is capitalised using an appropriate yield. Note that since the variables used are inherent to the property and are not market-derived, therefore unless appropriate adjustments are made, the resulting value will be Value-in-Use or Investment Value, not Market Value.

4. Development/residual method.

   Used for properties ripe for development or redevelopment or for bare land only.

5. Contractor’s/cost method.

   Used for only those properties not bought and sold on the market.

This post concentrates on the second method.

Initial Yield – Gross and Net

A fundamental skill for any property investor is being able to calculate property yields. Yields show the return on investment and there are 2 basic yields “Gross Yields” and “Net Yields”. It is important to understand the difference between them and these yields will let you know if a property that is producing rent is profitable or not. Effectively the cheaper the property and higher the rent you charge, the higher the yield will be.

Gross Yield

Gross yield is calculated by dividing the property’s annual rent by the property’s market value as a percent:

gross yield = annual rent / market value

e.g. The gross yield for a rental of £50,000 for a property with a market value of £1,000,000 is:

50,000 / 1,000,000 = 0.05 or 5%

Net Yield

Net yield is used more frequently than gross yields as it takes in to account operational costs and gives a more reliable view.

Net yield is calculated by taking away the property’s annual operational costs from its annual rent. You then divide this figure by the market value, as below:

net yield = annual rent – annual costs / market value

e.g. If the annual rent for a property is £50,000 and its operating costs per year i.e. insurance, maintenance etc. are £4,000, and if the market value is £1,000,000, then the following expressed as a percentage, would be true.

net yield = 5,000 – 400 = 4,600 / 1,000,000 = 0.046 or 4.6%

Net yield as a percentage = 4.6%

Yields will also differ in different countries. For example management costs can vary as much as from 10% in the UK to over 30% in Europe and beyond.

Reversionary Yield

The open market rent is the best rent that a property can fetch on the open market at that time. In the UK property market rents are generally fixed for 5 years before a review. In between review the market rent (MR) will adjust to changes in supply and demand, and with rising inflation will generally be above the contractual rent. The initial yield shows the relationship between the current rent passing and the current price while the reversionary yield is the MR divided by the current price on a property investment let at a rent below the MR. (equally the net and gross type apply to reversionary yields also).

Equivalent Yield

The equivalent yield is defined as the internal rate of return of the cashflow from the property, assuming a rise to ERV (estimated rental value) at the next review but with no further rental growth. It will always lie between the initial yield and the yield on reversion.

Let look through an excel model to show how this is calculated

Inputs:

Basics: valuation or price paid, costs, periods per annum, in arrears or in advance
Lease Data: current rent passing, reviews every x years, escalating x% per annum, lease start date, lease expiry date, stepped increases.

Key Visual Basic Code:

Public Function YPPERP(ByVal Rate_pa As Double, ByVal Periods_pa As Double, ByVal Arrears As Integer)

Dim i As Double
Dim j As Double
On Error GoTo YPPERP_Err
decOne = CDec(1)
If Rate_pa <= 0 Then
YPPERP = “#NUM!”
Exit Function
End If

If Arrears = 1 Then
‘– in advance
j = CDec((decOne – (decOne + Rate_pa) ^ (-decOne / Periods_pa)) * Periods_pa)
YPPERP = 1 / j
Else
‘– in arrears
i = CDec(((decOne + Rate_pa) ^ (decOne / Periods_pa) – decOne) * Periods_pa)
YPPERP = decOne / i
End If
YPPERP = CDbl(YPPERP)
Exit Function

YPPERP_Err:

YPPERP = FormulaError(“YPPERP”)

End Function

———————————

Public Function YPPERP_DEF(ByVal Rate_pa As Double, ByVal Periods_pa As Double, ByVal Years_deferred As Double, ByVal Arrears As Integer)

Dim k As Double
On Error GoTo YPPERP_DEF_Err
decOne = CDec(1)
If Rate_pa <= 0 Then
YPPERP_DEF = “#NUM!”
Exit Function
End If

k = CDec((decOne / (decOne + Rate_pa) ^ Years_deferred))

YPPERP_DEF = YPPERP(Rate_pa, Periods_pa, Arrears) * k
YPPERP_DEF = CDbl(YPPERP_DEF)
Exit Function

YPPERP_DEF_Err:

YPPERP_DEF = FormulaError(“YPPERP_DEF”)

End Function

Private Function FormulaError(ByVal strFormula As String)

FormulaError = “Error”

End Function

———————————

Public Function NETPRICE(Price As Double, Costs As Double)

On Error GoTo NETPRICE_Err
decOne = CDec(1)

NETPRICE = Price / (decOne + Costs)
NETPRICE = CDbl(NETPRICE)

Exit Function

NETPRICE_Err:

NETPRICE = FormulaError(“NETPRICE”)

End Function

———————————

Public Function TEY_NEY(Rate_pa As Double, Periods_pa As Double, toggle As Integer)

On Error GoTo TEY_NEY_Err
decOne = CDec(1)

If Not IsNumeric(Rate_pa) Or Rate_pa <= 0 Or _
Not IsNumeric(Periods_pa) Or Periods_pa <= 0 Or Periods_pa >= 367 Then
TEY_NEY = “#NUM!”
Exit Function
End If

If toggle = 0 Then
TEY_NEY = Periods_pa * (decOne – (decOne / (decOne + Rate_pa)) ^ (decOne / Periods_pa))
Else
TEY_NEY = (decOne / (decOne – Rate_pa / Periods_pa) ^ Periods_pa – decOne)
End If
TEY_NEY = CDbl(TEY_NEY)
Exit Function

TEY_NEY_Err:

TEY_NEY = FormulaError(“TEY_NEY”)

End Function

———————————

Public Function EFFECT_NOM(Rate_pa As Double, Periods_pa As Double, toggle As Integer)
On Error GoTo EFFECT_NOM_Err
decOne = CDec(1)

If Rate_pa <= 0 Or Periods_pa <= 0 Then
EFFECT_NOM = “#NUM!”
Exit Function
End If

If toggle = 0 Then
EFFECT_NOM = ((Rate_pa + decOne) ^ (decOne / Periods_pa) – decOne) * Periods_pa
Else
EFFECT_NOM = ((decOne + (Rate_pa / Periods_pa)) ^ Periods_pa) – decOne
End If
EFFECT_NOM = CDbl(EFFECT_NOM)
Exit Function

EFFECT_NOM_Err:

EFFECT_NOM = FormulaError(“EFFECT_NOM”)

End Function

Years Purchase of a Perpetuity

In property valuations the assumption is that the net rental income being valued, or each tranche of net rental income being valued, is constant and the rent is receivable annually in arrears. The passing rent and the estimate of the rental value are taken as at the valuation date. If there is a rent review or reversion (lease expiry) it is, in traditional valuations, today’s estimate of the rental value that is used to determine the rent that will become receivable following the rent review or a new letting.

As valuations are therefore dealing with fixed or constant income streams, it is possible to arrive at a formula that is a multiplier, which when applied to the rent produces the capital value of that rental stream. This is the role of the years multiplier.

The years purchase multiplier is thus the sum of the present value for each of the years for which the rent is assumed to be receivable. Thus, for example, if the years purchase (YP) valuation yield was say 10% and the valuer assumed that the rent in effect was receivable in perpetuity, then the YP multiplier equals the sum of all the present values of each of the years to perpetuity. Mathematically the present value figures into perpetuity are a geometric series that can be summed and simplified to produce the years purchase in perpetuity formula.

Example 1:

Assume a rent of £100,000 per annum payable annually in arrears, the market yield being 8%. The capital value is:

Rent received: £100,000
YP perpetuity @ 8%: 12.5
Capital Value: £1,250,000

The figures of years’ purchase may be calculated direct from the formula for payments received quarterly in arrears

and below for payments received quarterly in advance

In each case the result is the year’s purchase which the purchaser could afford to pay, and still receive an effective return of 8% as in the example.

Example 2:

Freehold warehouse premises are let at £50,000 per annum on a lease which expires in 6 years time. The current net rental value is £150,000 per annum. The appropriate market yield is 10%. What is the equivalent yield?

Term rent: 50,000
YP 6 years @ 8%: 4.623 = 231,150

(in this case the formula for YP changes slightly to YP = (1-(1+i)^-n) / i – where n = number years in this case 6 and i = the market yield)

Reversion to: 150,000
YP perpetuity def. 6 years @ 10%: 5.645 = 846,710

Capital Value: £1,077,860

A first approximation of the equivalent yield may be made by multiplying the difference between the yields by the fraction resulting from dividing the value of the term by the combined capital value and subtracting the result from the higher yield, giving a value in this case of 9.79%. Where greater accuracy is required it is convenient to use a discounted cash flow model which will enable any remainder to be allocated or with an Excel model.

True and Nominal Yields

Basically, if you just take an annual rent and divide it by the value, that is a Nominal Yield. Why Nominal? It’s because you haven’t made any specific allowance for the payment timing (you are implicitly assuming quarterly in advance). Most yields we do in our head are therefore Nominal Yields (They are actually analogous to a Simple (as opposed to an Annual Effective) interest rate).

If, on the other hand, your yield is like an Annual Effective Rate, perhaps it is the IRR of a bond, then you can never just use that to divide into a rent to get a value, because AER’s assume payments annually in arrear whereas rents are paid quarterly.

The Excel Model

The model below uses the simple VB code (remember to enable macros when you open it) listed above and can be developed further for more sophisticated modeling for stepped rent for instance (also known as the layer method – which I will follow up this post with). For the most part I have taken a lot of VB and moved it to XLL addins to get better performance from templates that I designs for my company, Red Chilli Structured Finance – www.redchilli.com, but I hope this helps explain some of the basics and is a good starter for someone looking to create equivalent yield models.

Equivalent Yield Excel VB Template

Resources

• Financial Modeling Using Excel and VBA
• Building Financial Models with Microsoft Excel: A Guide for Business Professionals
• The Property Developer’s Handbook