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

Key names are given with a starting capital letter, for example:

<F12> refers to the "F" (function) keys at the top of the keypad.

Where 2 or 3 keystrokes are required in sequence, eg:

Typical screen size:

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.

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

2. Move cursor to M1

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

**alpha (A-Z) or label****numerical or numbers**

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.

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

AIM

On completion of this unit the user will be able to:

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

**to determine the present value of progress payments to determine the "expected cost to build " .****to 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".****to 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.

Real estate is unique in that the total implied time period of a cash flow is ALWAYS perpetuity:

present value of the 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

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.

CONSTRUCTING A SIMPLE CASH FLOW

Enter the following:

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>

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.

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

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.

TOTALS:

B15:=sum(B11:B13)

Save as DCF1.WK1

UNIT 3

AIM

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.

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.

Continuing with the DCF.

A3: Discount rate at % per annum:

C3: 8.3

F3: 1 + (C3/100)

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

Where:

n = period in years.

For B16: 1/(1.083°) = 1

NB:

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

Highlight B16

Format C16:C18 to 3 decimal places.

The resultant discount factors are:

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.

B19: =SUM(B18:G18)

Format to 2 decimal places.

Net present value (NPV) = 0.00.

Save file as DCF1.

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

LESSON 4

AIM

**initial yield (IY)****equivalent yield (EQVY)****equated yield (EQTY)**

Retrieve previous DCF; DCF1.WK1. The initial yield return on investment is the most common used in industry. It has 3 important assumptions:

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

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.

**the sale price is known = -2000****the end market value is known = sale price = 2000****the net annual income is known = initial rents = 166**

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?

EQUIVALENT YIELD (EQVY) MODEL

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

RH Click on the Tab at the bottom of Sheet 2. Rename CALCULATIONS.

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

166.00 172.64 179.55 186.73 194.20

Highlight C6:G6 <Edit><Copy>

ENTER MARKET RENTS INTO DCF

A1: EQUIVALENT YIELD MODEL

Highlight C12

Find the IRR by gradually increasing the discount rate until the NPV = 0.

SHEET 1

EQUATED YIELD (EQTY) MODEL

A1: EQUATED YIELD

G13: =G12*100/8.3

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.

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.

PROBLEM 1

Using the cash flow in DCF3 determine the market value of an investment property from the following information:

Equated yield rate analyzed from comparable properties: 12% per annum

PROBLEM 2