Discounted cash flow (dcf) - introductory course


UNIT 1

AIM

Upon completion of this lesson, the user will be able to use basic keystrokes.


NOTATION USED

< > means perform the keystroke in brackets eg <Enter> = press the Enter key
Key names are given with a starting capital letter, for example:
<F1 >
<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: 1-31
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 SPREADSHEET

1. The cursor starts at cell A1 (Home). It can be moved about the spreadsheet using the mouse cursor.

2. Move cursor to M1
3. 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 left.
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 keyboard.

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 >.


ENTERING DATA
There are two types of data: 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.

RULES FOR GOOD SPREADSHEET CONSTRUCTION 1


1. Allow plenty of space in the spreadsheet. There is no shortage of room!

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 later.

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.


LESSON 2
CONSTRUCTING A SIMPLE CASH FLOW (DCF)

AIM


On completion of this unit the user will be able to:
CONSTRUCTING A SIMPLE CASH FLOW (DCF)

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 possible.

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:


O = = = = = = = = = = = = / = = = = = = = = = = = = = = > perpetuity
present value of the               present value of the
lease period.                          reversionary period.

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


Where:

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 CASH FLOW
D6: (space)$'000

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 mouse.
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.
SAVE FILE <File> <Save> “DCF1”

ENTER CASH FLOW DATA

A12: Rental Income:

A13: End Market Value:

B 11: -2000
C12: 166 D12: 166 E12: 166 F12: 166 G12: 166

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%:


G13: =G12*100/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.


EXAMPLE


Change the last net annual income (G12) to 200. This will show an EMV of 2410.
Change G 12 back to 166.
Underline by copying ALL the cell addresses B9.. G9 to B14. A15:
TOTALS:
The automatic summation of each column is achieved by using the =SUM() function and the COPY command:
B15:=sum(B11:B13)
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:

 



UNIT 3


AIM
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 or less.

Continuing with the DCF.
Load DCF1 onto your screen if not already there.

DISCOUNTING THE CASH FLOW

ENTER THE DISCOUNT DATA

A3: Discount rate at % per annum:

C3: 8.3
E3: base:
F3: 1 + (C3/100)
The base (F3) shows 1.083.

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 power of" The discount factor has been calculated using the following PRESENT VALUE OF $1 formula: PV = 1/((1+i)^n)
Where:
PV = present value of 1
i = interest rate as a decimal
n = period in years.


For B16: 1/(1.083) = 1

NB:
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

The 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 command.

FIXED CELL ADDRESSES

Highlight B16
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 again
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:

B18: =B16*B15


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 Value:
B19: =SUM(B18:G18)

Format to 2 decimal places.

Net present value (NPV) = 0.00.


Save file as DCF1.
The spreadsheet 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 instead.


EXERCISES


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.

LESSON 4


AIM
On completion of this lesson the user will be able to determine the following returns on investment for an investment property: INITIAL YIELD (IY) MODEL 1.

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 perpetuity:

NET ANNUAL INCOME - INITIAL YIELD

166 000 per annum

0-----------------------------------------------------------------------------> perpetuity

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 RETURN (IRR)

To 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?

Industry 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

The 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 window.


SHEETS IN EXCEL

The 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 data:

A1: MARKET RENT
A2: Expected annual increase as % pa:

E2: 4

A4: YEAR:

C4:G4 Enter 1,2,3,4,5

A6: RENT PA:

C6:166 - starting rent
D6: =C6*(1 +($E$2/100)) 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

Highlight C12
<Paste><Special><Numbers> This will place the numeric data from the CALCULATIONS sheet only and not the formula.
G13:-B1 1 At an 8.3% discount rate the NPV=50.32.

Find the IRR by gradually increasing the discount rate until the NPV = 0.
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 on investment.

SAVE AS "DCF2” The resultant spreadsheets should like this: CALCULATIONS SHEET

 

SHEET 1





EQUATED YIELD (EQTY) MODEL

The 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 already loaded:

A1: EQUATED YIELD

G13: =G12*100/8.3
Format to 2 decimal places. 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; 339.72.
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. The resultant spreadsheet should look like this:

 


PROBLEM 1


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


PROBLEM 2


Using the cash flow in DCF3 determine the discount rate analyzed from the following information: Sale price: 1500 Expected annual increase in rents: 5% per annum Starting rent: 155 Rental reviews at years: 2, 4



 See compound functions – spreadsheet
22