cash flow (dcf) - introductory course
Upon completion of this lesson, the user will be able to use basic
< > means
perform the keystroke in brackets
eg <Enter> =
press the Enter key
Key names are given with a starting capital letter, for example:
<F12> refers to the "F" (function) keys at the top of the keypad.
eg < F5 > means the
press the " F5 " key NOT < F > and then < 5 > .
Where 2 or 3 keystrokes are required in sequence, eg:
<Alt> + <F5
> the <Alt> key is pressed first and at the same time the <
F5 > key is pressed.
Typical screen size:
Columns: A-L Rows:
Screen size will depend on your screen resolution.
MOVING THE CURSOR
The cursor can be moved with the mouse or arrow keys from cell to cell.
LOOKING AT THE
1. The cursor starts at cell A1 (Home). It can be moved about the
spreadsheet using the mouse cursor.
2. Move cursor to M13.
At M1 note that the left hand columns have so that the screen (window)
can accommodate Column M. The spreadsheet has scrolled from right to
At any one time (under typical format) there are 12 columns and 31 rows
on a screen or window. (NB this will depend on the display
configuration for your computer).
4. You can move back to A1 at any time pressing <Home> on the
Move back to A1.
5. The display window is only a small part of the total spreadsheet
which is very large, having 8192 rows and 256 columns. It is not likely
that you will use anywhere near it's full potential.
6. Instead of using the mouse, the cursor can also be moved using the
GOTO key <F5 >.
There are two types
If the first letter
is alpha then Excel will interpret the input as a alpha (in computer
jargon this is also known as a "string"). If the first letter is a
number then Excel will assume that the input is numerical.
If you wish to
withdraw a keystroke before entering use < Esc > . After entering
the input can be edited in the formula window above the spreadsheet.
- alpha (A-Z) or
- numerical or
RULES FOR GOOD SPREADSHEET CONSTRUCTION 1
1. Allow plenty of space in the spreadsheet. There is no shortage of
2. Always "point" cell addresses wherever possible. That is, construct
as much of the spreadsheet as possible with the mouse.
3. Use COPY and MOVE
commands wherever you have to repeat data or a formula.
4. Use range names instead of cell addresses - these will be covered
5. Use "sheets" for calculations, notes or data which will not be
printed in the final report - will be covered later. The sheets are
shown as tabs on the bottom of the display.
6. Save and save often! When a critical point is reached save a
duplicate file with “a” or “2” at the end of the file name.
7. When using formulae, break them up into small parts showing the
answer for each part. This enables easier debugging.
A SIMPLE CASH FLOW (DCF)
On completion of this unit the user will be able to:
CONSTRUCTING A SIMPLE
CASH FLOW (DCF)
- use a number of
useful spreadsheet commands
- construct a
simple cash flow.
Always bear in mind the purpose of the DCF. Remember, it is only a
means to an end. There 4 main uses in valuation are:
Often the fotrecast
period is determined by the client but otherwise should be as short as
- to determine
the present value of progress payments to determine the "expected cost
to build " .
determine the "highest and best" use of land which is subject to a
number of competing land uses and hence land value (sometimes called
"site value") can be found. Also called "hypothetical development" and
a "feasibility study".
determine the market value of an investment property (eg CBD office
tower) which is subject to a complex rent schedule. This is also known
as the "reversionary method" of valuation and is the most popular
method for such properties.
- to prepare
an "investment report" over an investment property. This can be either
an analysis of past performance eg for portfolio culling purposes or an
analysis of expected return on investment.
RULES FOR GOOD DCF CONSTRUCTION 1
1. Keep the cash flow as short as possible
2. If the expected cash flow is nearly "level" then there is no need
for a DCF. A DCF is only used for uneven cash flows.
3. Most cash flows
require prediction of amounts in the future. If you cannot predict with
reasonable accuracy any change in a cash flow (eg rent levels) then you
must input a level amount. That is, do not input cash flows which show
increases or decreases because it will make your cash flow "look good".
You are determining
"market value" so the market forecasts are what that counts. Future
"guestimates" are too dangerous under current liability law.
Real estate is unique in that the total implied time period of a cash
flow is ALWAYS perpetuity:
= = = = = = = = = = = = / = = = = = = = = = = = = = = >
present value of
present value of the
In the DCF, the last cash flow is always the END MARKET VALUE (EMV).
That is, all cash flows end with the expected selling price or expected
market value at the end of the cash flow period. This is determined by
the initial yield formula:
EMV = EAI * 100/CR
EMV = end market value
EAI = end net annual income
CR = capitalisation rate as a percentage.
Generally, the EAI is equal to the last net annual income expected in
the cash flow period. The reversionary value or capitalisation rate
should be analysed from market transactions eg sales of CBD office
blocks using a DCF if the rent schedule is complex.
We will now construct
a simple cash flow according to the assumptions in the "initial yield"
method of valuation.
CONSTRUCTING A SIMPLE CASH FLOW
Enter the following:
A1: SIMPLE DISCOUNTED
A8: YEAR - align to the right and underline
B8 to G8: 0,1,2,3,4,5
USE OF COPY COMMAND
To underline 0 to 5 you need only to copy the underline under year.
1. Highlight A9
2. <Edit> <Copy> using the RH key on the
A11: Starting Market Value:
Note that the label encroaches onto the cash flow period 0. Therefore,
it is necessary to widen column A to accommodate the full label.
TO WIDEN COLUMN
Highlight the A cell at the top of the A column. Move the cursor to the
right until <--> appears. Hold down the LH mouse key and move the
tight boundary of the column until all of A11 is within the column.
CASH FLOW DATA
A12: Rental Income:
A13: End Market Value:
B 11: -2000
Under the initial yield model, the return on investment or
capitalization rate = 166/2000 * 100 = 8.3%. Therefore, the end market
value is the last rent capitalized at 8.3%:
This will show an expected EMV of 2000 which is the same as the
starting value. The assumption in the initial yield method that there
is no increase in value as the initial rent remains throughout the cash
flow. By using the cell address G12 in the formula at G13, if the value
in G12 is changed, G13 will automatically change.
Change the last net annual income (G12) to 200. This will show an EMV
Change G 12 back to 166.
Underline by copying
ALL the cell addresses B9.. G9 to B14.
summation of each column is achieved by using the =SUM() function and
the COPY command:
Highlight B15 and
copy to C15:G15.
You have now
completed the cash flow. The cash flow accounts for ALL expected income
and benefits to be derived from the property from 0 to ........ years.
(fill in the missing number).
Save as DCF1.WK1
The cash flow should
look like this:
At the end of this
lesson the user will be able to discount a cash flow according to
discount theory and determine the net present value (NPV) . Some more
useful spreadsheet commands are covered.
GOOD DCF CONSTRUCTION 3
1. The cash flow is expressed in today's dollars. Therefore, any
expected change in value should be measured in real" terms ie after
inflation. For example, if you are using a trend line based on rental
evidence over the last 5 years to forecast the expected rents in the
cash flow it must be analysed in today's dollar.
When previous changes
in value are analysed in real terms it is most likely that the expected
increases will be very small or nil.
2. During the rent period of an investment building it is unlikely that
the forecasted rents will reduce because of the "ratchet" clause in
standard form lease agreements. However, after the lease falls due
there is no reason why the expected rents cannot fall.
3. For short DCFs such as determining construction costs or a small
feasibility study, a better DCF period unit is the quarter. This is a
most usual unit and in valuation. There is really no need to use
monthly periods because the inherent errors in the cash flow outweigh
any supposed advantage in using the actual rent payment periods. Some valuers use
"annuities due" as the payment mode for rents when period is quarterly
Continuing with the DCF. Load DCF1 onto your
screen if not already there.
THE CASH FLOW
ENTER THE DISCOUNT
A3: Discount rate at % per annum:
F3: 1 + (C3/100)
The base (F3) shows
If not change the number of decimal places by clicking onto the number
format icon to 3 places.
A16: Discount factors:
B16: 1 /(F3^B8) Shows 1
NB: "^" = "to the
The discount factor
has been calculated using the following PRESENT VALUE OF $1 formula:
PV = 1/((1+i)^n)
PV = present value of
i = interest rate as
n = period in years.
For B16: 1/(1.083°) = 1
The discount rate at
period 0 always = 1. However, instead of inputting
"1" it is good
practice to input the formula so that if the result is "1", then you
know the formula is correct and you can copy it with confidence.
COPY B16 to C16:G16
We get ERR...ERR (error) Why? Look at the formula in C16: 1/(G3^C8) G3
is a blank cell!
RELATIVE CELL ADDRESSES
COPY command moved ALL cell addresses in the formula by the number of
columns between our source address B16 and target address C16.
Therefore we have to fix F3 so that it does not change with the COPY
FIXED CELL ADDRESSES
In the formula window
above the spreadsheet change F3 to $F$3.
This tells Excel to
fix cell address F3 when used in a COPY command.
COPY B16 to C16:C18
Format C16:C18 to 3 decimal places.
The resultant discount factors are:
1 .000 0.923 0.853
0.787 0.727 0.6712
Save file as DCF1.
NET PRESENT VALUE (NPV)
A18: Discounted Cash flow:
COPY B18 to to C18:G18.
Note that in this case we require all the addresses to be relative so
we do not fix any of the variables.
FORMAT B18:G18 to 2 decimal places.
A19: Net Present
Format to 2 decimal places.
Net present value (NPV) = 0.00.
Save file as DCF1.
should look like this:
Congratulations! You have just completed a discounted cash flow.
UNDERSTANDING YOUR SUCCESS
The NPV in the above DCF = 0. This means that if the cash flow is
discounted at 8.3% then the present value of the incomings (rents +
EMV) = the starting market value or the purchase price. The above cash
flow can be used instead of the initial yield capitalization method but
as has been pointed out, unless the cash flow is complex, there is no
need to construct a DCF and the capitalization formula can be used
1. Raise the discount rate to
10% per annum. What happens to the NPV? Why?
2. Lower the discount
rate to 7% per annum. What happens to the NPV? Why?
3. Suppose the seller
raises the purchase price (starting value) to 2500 (000). What should
the new discount rate be showing a NPV = 0.
On completion of this
lesson the user will be able to determine the following returns on
investment for an investment property:
INITIAL YIELD (IY)
- initial yield
- equated yield
Retrieve previous DCF; DCF1.WK1. The initial yield return on investment
is the most common used in industry. It has 3 important assumptions:
3. Therefore, the
initial annual amount in the previous cash flow was 166 (000). Under
the initial yield model it is assumed that 166 will continue into
- the expected
net annual income to be derived from the subject property is the
starting (initial) amount.
- the initial
amount will remain level from 0 to perpetuity.
- the end market
value always = the starting market value. That is, it is assumed that
there are no capital gains.
NET ANNUAL INCOME - INITIAL YIELD
166 000 per annum
4. The starting market value and the end market value are the same:
166 * 100/8.3 = 2000
ANALYZING SALES - INITIAL YIELD
Sales are analyzed to determine the discount rate in the DCF. This can
be equated with the capitalization rate. DCF1 can be used to analyze a
sale to find the market discount rate.
INTERNAL RATE OF
- the sale price
is known = -2000
- the end market
value is known = sale price = 2000
- the net annual
income is known = initial rents = 166
find the discount rate enter any starting rate in C3 eg 6% per annum.
This will give a positive NPV>0.
Therefore, this rate
is increased until the NPV = 0.00 ie 8.3%.
The discount rate
which determines a NPV =0 is called the "internal rate of return" (IRR).
Therefore, for our initial yield model the IRR = 8.3% per annum.
All our measures of return will be the internal rate of return as this is
one of the most useful measures in real estate investment.
HOW IS THE INITIAL YIELD MODEL USED?
uses the IY as the basic measure of real estate return. That is, a
property is assessed in the market place by the initial yield
capitalization rate. Because of the inherent assumptions in the model
it is not a good measure of the "actual" return on investment and
cannot be readily compared with opportunity cost investments such as
government bonds. For example, it is common for prestige CBD buildings
in Sydney's financial belt to show an initial yield of 3-5% per annum,
obviously much less than the actual expected return on investment.
EQUIVALENT YIELD (EQVY) MODEL
EQVY is a better measure of the expected rate of return of an
investment property as it includes in the cash flow all expected
increases in rents. For our example, under the lease agreement rent
reviews will only occur in years 3 and 5. That is, the expected new
market rents will only be achieved in years 3 and 5. Rents will not
fall below the "base rent" of 166 because of the "ratchet clause" in
the standard lease document.
It is necessary to construct a separate cash flow which shows the
expected rental increases. As this will not be part of the final report
(printout) but rather, a working cash flow it can be put in another
SHEETS IN EXCEL
Excel spreadsheet is very large and is never fully utilised in
spreadsheet construction. It is good practice to use sheets in the
Excel “book”pages for all ancillary calculations, notes etc. Such as
where you would use appendices in a valuation report.
If you use the same
sheet (eg sheet 1 for calculations) for all your spreadsheets then you
know where to look and find your calculations. For example, you could
use Sheet 2 as the window always used by you for calculations and Sheet
3 for notes etc:
RH Click on the Tab at the bottom of Sheet 2. Rename CALCULATIONS.
Input the following
A1: MARKET RENT
A2: Expected annual increase as % pa:
C4:G4 Enter 1,2,3,4,5
A6: RENT PA:
C6:166 - starting rent
Copy D6 to E6..G6
Format C6..G6 to 2
decimal places. This will show the following range of market rents:
166.00 172.64 179.55 186.73 194.20
Highlight C6:G6 <Edit><Copy>
Go to Sheet 1
ENTER MARKET RENTS INTO DCF
A1: EQUIVALENT YIELD MODEL
This will place the
numeric data from the CALCULATIONS sheet only and not the formula.
At an 8.3% discount
rate the NPV=50.32.
Find the IRR by gradually increasing the discount rate until the NPV =
NB: NPV need not
exactly = O but at 2 decimal places is close enough.
Equivalent yield =
about 8.93% per annum.
That is, the rate of
return measure has increased from 8.3% for the initial yield model to
8.93% for the equivalent yield model
because the equivalent yield model takes into account expected future
rent increases. The inclusion of expected rent increases account for an
additional 0.63% per annum return and is a better measurement of return
spreadsheets should like this:
EQUATED YIELD (EQTY) MODEL
equated yield model
goes the next and final step by inputing into the rate of return model
any expectation of capital gains. The expected capital gains is usually
a function of the final market rent.
Load DCF2 is not
A1: EQUATED YIELD
Format to 2 decimal
G13 shows 2339.72.
That is, the model assumes that the expected capital gains will be the
difference between the starting market value and the end market value;
At the equivalent
yield discount rate of 8.931% NPV = 221.50.
Determine the IRR or "equated yield" by increasing the discount rate
until NPV is almost 0. The answer is about 11.609% per annum. This is
the full return on investment as it takes into account all expected
future income; increases in rent and capital gains. Therefore, 11.609%
can be compared directly with opportunity cost investments.
For example, if semi
government bonds are paying 10% per annum they are probably a better
investment as the margin of 1.609% is not sufficient reward for the
extra risk of investing in real estate.
Save as DCF3.
spreadsheet should look like this:
Using the cash flow in DCF3 determine the market value of an investment
property from the following information:
Expected increase in
market rents: 6% per annum
Equated yield rate analyzed from comparable properties: 12% per annum
Using the cash flow
in DCF3 determine the discount rate analyzed from the following
Sale price: 1500
increase in rents: 5% per annum
Starting rent: 155
Rental reviews at
years: 2, 4
compound functions – spreadsheet