Designing Flexible Cash Flows

Scott Fawcett

• Publisher: Estates Gazette
• Year: 2013
• ISBN:
  9781135323615
• Edition: 1
• Topic: Law
• Subtopic: Property Law
• Index: Law

Whether calculating a net present value, assessing an internal rate of return, or considering the impact of debt on a transaction when analysing investments in property it is very hard to get away from the need for cashflows. These cashflows range from the very simple to the extremely complex and can take anything from minutes to days to produce. Of course, valuation software exists that will produce these for you - but sometimes there are situations where you can't avoid having to do the job yourself!

Chapter 1

Introduction

How flexible is flexible?

A cashflow is simply a list of numbers showing, in property investment terms, the rental income from an investment together with associated costs and capital expenditure, if required.

At its simplest, and quickest, typing in a series of numbers to show the income from an investment will do the job. However, the whole process becomes very time consuming if there are lots of tenants or properties, or if it is necessary to change your assumptions to see how variations impact the overall investment return.

Consequently, it is important to be able to write cashflows that can react to changing inputs. Flexible cashflows can incorporate any possible variation you can think of, for example variable ERVs (estimated rental values) and void periods, different rental growth rates for each element of the property, options to operate or ignore break clauses and so forth.

It is not hard to imagine that such a degree of flexibility is complex to design and is certainly not for the faint-hearted!

However, not all analyses will require this. It may suffice to simply ignore rental growth and be able to do no more than vary ERVs for a number of different tenants. Writing a cashflow to do this is fairly simple once you have learnt a few basics and will form a good platform to develop upon over time. Essentially, start small and build up.

There are plenty of examples contained within the following, and it is important to work through these and to try out your own variations. Given its widespread use, all of the examples use the Microsoft Excel®¹ spreadsheet and you will find the help files within this program of great assistance. At the end of the main text, there is a brief review of frequently used functions that should also provide a handy reference point.

The object is not to learn these methods by rote – rather to understand how the techniques work and how you can apply them. Every cashflow you write will be different and you will no doubt need to be able to apply these ideas in a whole host of innovative ways in order to solve the problems that will arise.

It is worth mentioning that the techniques described do not pretend to be the ultimate in cashflow design and it is recognised that you may already have your own preferred approach to dealing with some cashflow elements. However, if you do not like, for example, the way that void costs are treated, or growth is applied then perhaps you can combine your own methods with some of the other ideas contained herein so as to improve the whole.

There are always ways to improve, simplify and create more elegant solutions. The challenge is to be able to evolve these yourself.

1  Microsoft® and Excel® are registered trademarks of Microsoft Corporation, but for convenience the text refers to this simply as “Excel”. Screen grabs are reproduced by kind permission of the Microsoft Corporation.

Chapter 2

Tools of the Trade

Essential functions that you need to know

If you are going to program a flexible cashflow, there are some functions that you will have to get to know and love. There are also techniques that you will need to grasp.

We will start with some of the Excel spreadsheet functions that will be of particular use.

The IF statement is your friend

The essence of a spreadsheet is to have the value of individual cells changing depending on the value of other cells. In a simple SUM calculation for example, the output of the formula will vary depending on the values in the SUM range but it will always give the summation of those cells.

The IF statement enables the spreadsheet author to do much more than this by allowing the operative formula (i.e. the formula which gives the cell’s result) within the cell to be changed. So, taking the above example, the SUM formula could become a multiplication calculation.

Simple IF statements

At its simplest, the IF statement need not contain other formulae. The structure of the statement is as follows:

=IF(Logical Test, TRUE, FALSE)

The “Logical Test” is essentially a question and can use equals, greater than, less than, etc. comparators. If the answer to the question is true, then the value generated by the TRUE part of the function is used, if not then the FALSE value is used.

A simple example of how this works is as follows:

=IF(B10=5,“B10 equals 57”,“B10 does not equal 5”)

The results of this are straightforward and, of course, only one number in B10 will generate the TRUE result.

It is worth briefly noting the use of the quotation marks. These are used to make text appear in cells, rather than figures. The above formula could have a numerical output if the following were used:

=IF(B10=5,200,1000)

Let’s try using a simple IF statement.

1. Open a blank spreadsheet and enter the following:

• B4: Input:

• B6: Is input 5?

• C4: 5

• C6: =IF(C4=5,“Yes”,“No”)

2. Cell C6 should give the answer “Yes” while the number 5 is in C4.

3. Save this spreadsheet as we will use it in the next example.

	B	C
2	Simple IF Statement
3
4	Input:	5
5
6	Is input 5?	Yes

4. Changing C4 from “5” will alter C6 to the false result.

IF and formulae

The next stage of using the IF statement is to introduce different formulae into the true and false outputs. Consider the earlier example, where an addition formula becomes a multiplication, this could be expressed as follows:

=IF(A1=“Multiply”, B1*B2*B3,SUM(B1:B3))
IF A1 equals “Multiply” then insert B1*B2*B3 otherwise add together B1, B2 and B3.

Here, the true part of the IF statement contains the multiplication and the false the addition. If A1 is anything other than the word “Multiply” then an addition will be carried out.

Note the quotes around “Multiply” to show a text input, the lack of the need for an equals sign before the SUM formula and the double brackets at the end of the formula – first closing the SUM formula and then the IF statement.

Here is an illustration of this in action.

1. A...