investment analysis on spreadsheet

INVESTMENT ANALYSIS - ON SPREADSHEET

NOTATION USED

<> means you press that key

<> + <> means you press both keys at the same time

<E> means press the enter or return key

NB the following instructions are for Excel. You don't need to use Excel. You can access free spreadsheets in Libre Office and Google Docs. The notation will be different but the methodology is the same.

PART 1

BOOTING IN EXCEL

AIM

Upon completion of this part you will be able to boot in Excel and use basic keystrokes.

NOTATION USED IN THESE LESSONS

< > means perform the keystroke in brackets, for example;
<E> means press the Enter key

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

<NumLock> means press the NumLock key. Make sure that NumLock is OFF
<F1 > <F12> refers to the "F" (function) keys at the top of the keypad. For example,
< F5 > means press the " F5 " key NOT < F > and then < 5 > .

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

<Alt> + <F5 > means both the <Alt> and < F5 > keys are pressed together

DRIVE SELECTION

a: means select drive a.

BOOTING INTO EXCEL

Excel is located in the Windows environment. Therefore, it is necessary to firstly boot in Windows. After entering Windows, the Excel Window is found by entering:

<Window> <Excel> <¿>

The Excel spreadsheet will appear. This is a set of gridlines with cell addresses:

Columns: A H Rows: 1 20

This may vary slightly due to different computer configurations. If your screen does not show at least rows 1-20:

In the View menu make sure there is a check against Formula and Status Bar only.

MOVING THE CURSOR

The cursor can be moved with the arrow keys from cell to cell but it is much easier and quicker to use the mouse.

PART 2

USING EXCEL

AIM

This part will introduce you to the basic elements of Excel.

LOOKING AT THE SPREADSHEET

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

2. Move cursor to H1 and then to I1

3. Depending on your computer’s configuration, at I1 Column A may disappear so that the screen (window) can accommodate Column I. The spreadsheet has scrolled from right to left. At any one time (under normal formatting) there are at least 8 columns and 20 rows on a screen or window.

4. You can move back to A1 at any time using <Ctrl>+<Home>. Move back to A1.

5. Note in Excel that the 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 >:

EXAMPLE: To move cursor to AB1000:

< F5 > type in Reference box: AB1000 <OK>
<Ctrl>+< Home >

ENTERING DATA

There are two types of data:

1. Alpha (A Z) or label or string
2. Numerical or numbers

If the first letter of an input is alpha, Excel will interpret it as a LABEL (in computer jargon this is also known as a "string"). If the first letter is a number, 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 input display window using < F2 > .

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 using 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 other sheets for calculations, notes or data which will not be printed in the final report
windows will be covered later.

6. Save and save often!

7. When using a formula, break it up into small parts that show the answer for each part.
This will enable easier debugging.

PART 3

CONSTRUCTING A SIMPLE CASH FLOW (DCF)

AIM

On completion of this lesson the participant will be able to:

1. Use a number of useful spreadsheet commands
2. Construct a simple cash flow.

CASH FLOWS

The following DCF is the DCF covered in Appraisal Two, pages 54/62 under the heading "Feasibilities and Rates of Return". Always bear in mind the purpose of the DCF, it is only a “means to an end”. There 4 main uses in investment analysis:

To determine the present value of progress payments to determine the "expected cost to build ".
To determine the highest and best use of land that is subject to a number of competing land uses and subsequently, the determination of land value (sometimes called site value). Also known as hypothetical development and a "feasibility study" over development land.
To determine the market value of an investment property (for example, a block of flats) that is subject to a complex rent schedule. This is also known as the reversionary method of investment analysis and is the most popular method for such properties.
To prepare an investment report for an investment property. This can be either an analysis of past performance (for example, for portfolio culling purposes) or an analysis of expected return on investment.

Often the time period is determined by the client but otherwise, it should be as short as possible. A good time period is 2/3 years but 5 years is more common. Never prepare a DCF for 10 or 20 years!

USES OF DCF BY THE REAL ESTATE PROFESSIONAL

Always bear in mind the purpose of the DCF as it is only a means to an end.

There 4 main uses in investment analysis:

1. To determine the present value of progress payments to determine the "expected cost to build of a building.

2. To determine the "highest and best" use of land that is subject to a number of competing land uses and hence land value (sometimes called "site value").
Also known as hypothetical development and a "feasibility study" over development land.

3. To determine the market value of an investment property (eg block of flats) that is subject to a
complex rent schedule.

This is also known as the reversionary method of investment analysis and is the most popular method for such properties.

4. To determine market rental from a complex rent schedule.

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

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 probability any change in a cash flow (eg rent levels) then you must input a level amount.

That is, do not input cash flows that show increases or decreases solely because it will make your cash flow "look good".

If you are determining "market value", it is what the market forecasts that counts, not what you may forecast. Future "guestimates" are too dangerous under current professional liability law.

Real estate is an unique economic factor as the total implied time period of the cash flow is ALWAYS in perpetuity:

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

END MARKET VALUE (EMV)

Because land is held under freehold tenure (or in the case of Canberra, “quasi freehold”), 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. It is immaterial whether or not the investor intends to keep the property for a longer period. Opportunity cost arguments indicate that the assumption of a sale on completion is exactly the same as assuming ownership in perpetuity. The end market value is determined by the initial yield formula:

EMV = EAI * 100/CR

Where:

EMV = end market value
EAI = end net annual income
CR = capitalization rate as a percentage.

Generally, EAI is equal to the last net annual income expected in the cash flow period. The reversionary value or capitalization rate should be analyzed from market transactions for example, sales of CBD office blocks using 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 investment analysis.

MARKET VALUE OF AN INVESTMENT PROPERTY

The above capitalization formula is derived from the following sum of a geometric progression. The sum of single present values for each periodic income and benefit is equal to:

PV.PMT = ( 1 - (1/(1+i)^ⁿ))/ i

Where:

PV.PMT = the present value of 1 per annum

n = the period less than perpetuity

i = interest rate as a decimal

The limiting value is found by increasing n:

As n --------------------------------> perpetuity

1/(1+i)^ⁿmust --------------------> 0

Therefore when n = perpetuity:

PV.PMT = 1/i or EAI/(100/CR) if the interest rate is expressed as a %.

CONSTRUCTING A SIMPLE CASH FLOW

The following part is designed for self instruction. You should proceed at the rate or pace most suitable for you. Enter the following into the relevant cells:

A1: SIMPLE DISCOUNTED CASH FLOW

E10: (space)$'000

E8: YEAR

Align E8 to the right and underline as follows:

Excel <Format><Cells><Alighnment> ¤ Right <OK>

<Format><Cells><Font> Underline ò Single <OK>

B9 to G9: 0,1,2,3,4,5

Highlight B9:G9 by holding the LH button of the mouse down and dragging across the cells. Underline as above

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:

Highlight A11:

Excel Version 3.0: <Format> <Column width><20><OK>

Excel Version 5.0: <Format><Column><Width>

Enter in Column Width box: 20 <OK>

SAVE FILE

Place the formatted disk into a: <File><Save as>

Change drive to a: in Drives box

Enter file name: DCF1.XLS <OK>

.XLS is the extension used for an EXCEL worksheet file. Check that the file has been properly saved by;

<File><Open> Screen shows DCF1.XLS in File name box:

<Cancel>

ENTER CASH FLOW DATA

A12: Rental Income:

A13: End market value:

B 11: 2000

C12: 166

B11 is the only negative amount in the cash flow because it is the only cost (purchase price).

If you make a mistake and wish to clear the cell: <Edit><Clear><All>

COPY COMMAND

To copy the value 166, to cells D12:G12:

Highlight source cell C12

<Edit><Copy> a moving broken line appears on the cell’s borders

Highlight cells D12:G12 by dragging and holding down the LH button of the mouse

<Edit><Paste>

SAVE FILE

Now that you have given the file a name it can be quickly saved using:

<File><Save>

END MARKET VALUE (EMV)

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. This is a basic assumption in the initial yield model. The model assumes that there is no increase in value as the rent remains level 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.

Excel interprets the above as a formula because it starts with "=".

EXAMPLE: Change the last net annual income (G12) to 200. G13 shows an EMV of 2409.64. Change G12 back to 166.

A15: Totals:

SUMMING THE COLUMNS

The automatic summation of each column can be achieved by using the = SUM() and COPY commands:

B15: =SUM(B11:B13)

With experience, this will be quickly be carried out by pointing with the cursor. Copy the special command to cells C15:G15 as:

Highlight B15:

<Edit> <Copy>

Highlight C15..G15 <Edit><Paste>

The Totals row should show: -2000 166 166 166 166 2166

IMPROVING THE LOOK OF THE CASH FLOW

Highlight A11:A15

Excel Version 3.0: <Format><Alignment> ¤ Right <OK>

Excel Version 5.0: <Format><Cells><Alignment> ¤ Right <OK>

Save File.

Congratulations! You have now completed a cash flow.

QUESTIONS – UNDERSTANDING YOUR SUCCESS!

1. The cash flow accounts for ALL expected income and benefits to be derived from this property from 0 to ..............(fill in the missing word).

2. Why is the End Market Value necessary in a cash flow of an investment real estate property?

3. Format the numbers in the cash flow using <Format><Numbers> and then highlight [red][# ###] format in the format box. Note how the new format makes negative numbers stand out.

Your cash flow should look like the cash flow below.

4. Change the format for the numbers in the cash flow so that negative numbers are in brackets and red.

TO PRINT CASH FLOW

Highlight A1:H17 <File><Print> ¤ Selection <OK>

Note that Excel enables you to preview the printout with:

<File> <Print Preview>

This is a very useful feature as the Cash Flow can be for example, centered on the page.

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 analyzed in today's dollar.

When previous changes in value are analyzed 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 investment analysis there is really no need to go to monthly periods because of the inherent errors in the cash flow. Some valuers use annuities due as the payment mode for rents when period is quarterly or less.

PART 4

DISCOUNTING THE CASH FLOW

AIM

At the end of this part you 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.

ENTER THE DISCOUNT DATA

A3: Discount rate at % per annum:

A4: As a decimal:

A5: Base:

C3: 8.3

C4: =C3/100 shows 0.083

C5: =1+C4 shows 1.083

A16: Discount factors:

B16: =1/C5^B9 shows 1

NB: "^" = "to the power of"

THE DISCOUNT FACTOR

The discount factor has been calculated using the following PRESENT VALUE OF $1 formula:

PV(1) = 1/(1+i)ⁿ

Where:

PV(1) = present value of 1

i = interest rate as a decimal eg 8.3% = 0.083

n = period in years.

The value in B16 is 1/(1.083⁰) and therefore, equals 1. The discount rate at period 0 always = 1. However, instead of inputting "1" it is good practice to input the formula and if the resultant value is "1", then you know the formula is correct and you can copy it with confidence.

COPY B16 to C16:G16 as shown above

We get #DIV/0! errors. Why? Look at the formula in C16: 1/(D5^C9), D5 is a blank cell! Therefore, there is a division by 0 error. This problem is caused by the COPY command using “relevant” addresses.

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 C5 so that it does not change with the COPY command. This is done by making the reference in the formula to C5, a FIXED address.

FIXED CELL ADDRESSES

B16: <F2> (edit) in the formula box change C5 to $C$5

The insertion of the "$" symbols tells Excel to fix the cell address C5. When used in a COPY command it's value will not change. COPY B16 again.

Format the discount factors to 4 decimal places as follows:

Highlight the range: B16:G16:

Version 3: <Format> <Number> In the Code box, enter 0.0000 <OK>

Version 5: <Format><Cells><Number> In Code box enter 0.0000 <OK>

Any number entered in the range B16:G16 will now automatically change to 4 decimal places. The discount factors are:

1.0000 0.9234 0.8526 0.7873 0.7269 0.6712

SAVE file using <Save as..>

Give the file a new file name: DCF2.XLS.

NET PRESENT VALUE (NPV)

You are now ready to discount the cash flow:

A18: Discounted cash flow:

B18: =B16*B15 shows -2000

COPY to B1 8 to C18:G18.

Note that in this case we require all the addresses to be relative.

A19: Net Present Value (NPV):

B19: =SUM(B18:G18) shows 0.00. It may be in scientific notation ie to the power of 10.

FORMAT:B18:G19 to 2 decimal places

SAVE file.

Congratulations! You have just completed a discounted cash flow.

UNDERSTANDING YOUR SUCCESS!

The Net Present Value (NPV) = 0. This means that if the cash flow is discounted at 8.3% the present value of the incomings (rents + EMV) = the starting market value or the purchase price. The investor can use a cash flow as above 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 simple capitalization formula can be used instead.

NET PRESENT VALUE (NPV)

The NPV is a most important statistic. It is the expected absolute amount that will be received by an investing for the subject cash flow if discounted at the subject rate. In this case where the

NPV = 0 then the return on investment is equal to the discount rate. This will be explained in the next module.

IMPROVING THE APPEARANCE OF THE SPREADSHEET

For spreadsheets and particularly Discounted Cash Flows, it is important that negative numbers be clearly identified. Therefore, instead of using a small “-” in front of the negative number that can be easily lost, it is good practice to place the number within brackets. That is, instead of

-2000, use (2000). You can see that (2000) clearly identifies the number as a negative number.

The spreadsheet can easily be reformatted to negatives in brackets as follows:

Highlight B11:G15

<Format><Cells><Number>

In code box enter: # ###;(# ###) or for negative in red: # ###; (red)(# ###)

This will tell Excel to format all the numbers in the range B11:G15 so that:

There are no decimal places
There is a space separating the “thousand” number
All negative numbers are in brackets.

This format substantial improves the look of the spreadsheet and is more professional looking. It is recommended that “$” not be used as it is unnecessary.

Using the above method reformat B19 to 2 decimal places and negatives in brackets.

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. Return the discount rate to 8.3%. Suppose the seller raises the purchase price (starting value) to 2500 (000). What should the new discount rate be that shows a NPV = 0.

What is this rate called and why?

4. Without considering the discount rate, how can a preliminary perusal of the NPV alone be used to assess the value of the land?

PART 5

RETURNS ON INVESTMENT

AIM

On completion of this part you will be able to determine the following returns on investment for an investment property:

Internal rate of return (IRR)
Initial yield (IY)
Equivalent yield (EQVY)
Equated yield (EQTY)

THE INITIAL YIELD (IY) MODEL

1. Retrieve previous DCF file (DCF2.XLS). With the Starting market value of (2000) discount again at 8.3% pa. Make sure that NPV=0.00.

8.3% pa is the initial yield rate of return on investment.

2. The initial yield rate of 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) rental income.
The initial rent remains level from 1 to perpetuity
The End Market Value (EMV) = the Starting Market Value (SMV). That is, it is assumed there are no capital gains.

3. In DCF2, the initial rent is 166 (000). Under the Initial yield model it is assumed that 166 will continue into perpetuity and that the Starting Market Value (2000) = the End Market Value (166 * 100/8.3 = 2000):

166 000 pa = = = = = = = = = = = ================== = = = = >

O = = = = = = = = ======= = = = = = = = = = = = = = = = = = = = = > perpetuity

(2 000 000) 2 000 000

ANALYZING SALES TO FIND THE INITIAL YIELD RATE OF RETURN

Sales are analyzed to determine the discount rate for use in a DCF investment analysis. This can be equated with the capitalization rate used in the capitalization method. The example in DCF2 can be used to analyze sales to find the market discount rate.

EXAMPLE

The sale price is known = (2000)
The end market value is known = 2000
The net annual income is known = initial rent = 166

DETERMINE THE INTERNAL RATE OF RETURN (IRR)

The internal rate of return (IRR) is that discount rate that results in a NPV=0.

Enter any starting rate in C3 for example, 6% per annum.
This will give a positive NPV (>0)
Therefore, the rate needs to be increased until the NPV = 0.00 that is, 8.3%. You can carry this out using “trial and error”. With practice, the IRR can be quickly found.

INTERNAL RATE OF RETURN (IRR)

The discount rate that determines the NPV that =0 is called the "internal rate of return" (IRR)

Therefore, for the initial yield model, the internal rate of return = 8.3% per annum. All measures of rate of return on investment should be the internal rate of return as it is the most used measure, not only in the real estate industry, but for all investments. That is, its usefulness arises from its universality in investment analysis. After the necessary adjustments to the model, the IRR can be directly compared with other investments such as for example, government bonds.

HOW IS THE INITIAL YIELD MODEL USED?

Industry uses the initial yield as the basic measure of real estate return. That is, an investment property is evaluated in the market place using 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 only 3 5% per annum. This is obviously, much less than the actual expected return on investment. Therefore, there is necessary to improve the model so that it can be used to directly to compare with opportunity cost investments. The initial yield model can be improved to incorporate other returns on investment.

EQUIVALENT YIELD (EQVY) MODEL

The equivalent yield model is a better measure of the expected rate of return of an investment property as it includes all of the expected increases in rents. For example, under the lease agreement rent reviews to market will 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 or starting rent of 166 because of the ratchet clause in the standard lease document. It is necessary to construct a separate cash flow of the expected rent increases. As this will not be part of the final report (printout) but rather, a working cash flow it should be in another window.

WINDOWS IN EXCEL

It was pointed out in the first module that the Excel spreadsheet is very large and is never fully utilized in practice. It is good spreadsheet practice to use windows for all ancillary calculations or where you would use appendices in a investment report. If you use a suitable window with the same address for all your spreadsheets then you know where to look up ancillary data and calculations.

The Excel book can be used to for ancillary (not printed) information. Create a new sheet 2 (if you do not already have one) as follows:

<Insert><Worksheet>

Sheet 2 tag appears at the bottom of the spreadsheets. Enter Sheet 2 by clicking on the Sheet 2 tag. Sheet 2 can be used to determine the market rents as follows:

A1: MARKET RENT

A2: Expected rate of increase (% pa):

E2: 4

A4: YEAR:

C4:G4: 1 to 5

A6: RENT PA:

C6:=SR starting rent in C15 Sheet1 (166)

D6: =C6*(1 +($E$2/100)) shows 172.64

QUESTION: Why is E2 a “fixed” address?

Copy D6 to E6:G6

C6:G6 shows the following range of market rents (rounded to 2 decimal places):

166.00 172.64 179.55 186.73 194.20

Name D6:G6 MR2 to MR5 respectively (market rents for the years 2-5)

Sheet 2 will look like this. As the window is “hidden” and will not be printed out in the investment analysis report, there is no need to improve its appearance with better formatting.

Return to Sheet 1 by clicking on the Sheet 1 tag at the bottom.

ENTERING MARKET RENTS IN SHEET 1

A1: EQUIVALENT YIELD MODEL

E12:=MR3

F12:=E12

G12:=MR5

G13:= B11

At 8.3% the NPV=39.44. Find the IRR by gradually increasing the discount rate until the NPV = 0.

NB: NPV need not exactly = 0.

Sheet 1 will look like this:

Equivalent yield = about 8.795% per annum.

That is, the rate of return measure has increased from 8.3% for the initial yield model to about 8.795% for the equivalent yield model because that model takes into account the expected future rent increases. That is, the inclusion of the expected rent increases accounts for an additional 0.495% per annum return and therefore, is a better measure of the rate of return on investment as it more closely approximates the full or total expected return on investment.

Save as DCF3.XLS

EQUATED YIELD (EQTY) MODEL

The equated yield model goes the next and final step by including in the DCF the expected capital gains (if any). The expected capital gains is a function of the final market rent. Change DCF3 as follows:

A1: EQUATED YIELD MODEL

G13: =G12*100/8.3 shows 2340

Note that the capitalization rate of 8.3% remains the same. This is because it is the Initial yield rate analyzed from comparable sales using the initial yield model. That is, it is independent of the higher discount rate (equated yield).

G13 shows about 2340. That is, the model assumes that the expected capital gains will be the difference between the Starting Market Value and the End Market Value (2340 - 2000). At the equivalent yield discount rate of about 8.795%, the NPV is about 222.85.

Determine the IRR or equated yield, that is, the new discount rate that shows a NPV of about 0. The answer is about 11.48% 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.48% can be compared directly with opportunity cost investments. For example, if NSW Treasury Bonds are paying 8% per annum, depending on the nature of the real estate investment (risk) the investor must decide whether or not the extra margin of 3.48% is sufficient to cover the extra risk of investing in the subject property

Save as DCF4.XLS.

DCF4 should look as follows:

PRESENTATION AND PRINTOUT

The appearance of the printed spreadsheet can be improved by removing the gridlines. This is done as follows:

Use <File><Page Setup..> command:

<Sheet>

Print

x Gridlines remove x

<OK>

Highlight A1:H20

<File><Print> ¤ Selection <OK>

EXERCISES

1. Using the cash flow in DCF4 determine the market value of an investment property using the following information:

Expected increase in market rents: 6% per annum

Equated yield rate analyzed from sales of comparable properties: 12% pa

Save as DCF5

2. Using the cash flow in DCF4 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

Save as DCF6.

3. Using the basic 5 year cash flow in DCF4. How can you incorporate the following features into the DCF?

Rental adjustments to take into account that the annual rent is paid quarterly
Rental adjustments to take into account that the rents are paid “in advance”. That is, as “annuities due”.

PART 6

COMPOUND FUNCTIONS

AIM

On completion of this module the participant will be able to set a spreadsheet using 1 23's special functions to determine basic partial interests.

INTRODUCTION

The investment analysis of property often requires the investment analysis of "partial instruments". That is, parties other than the freehold owner have interests in the property. For example:

* Lessee and lessors' interests

* Mortgagee and mortgagors' interests.

Because such interests have a time period less than perpetuity they cannot be valued with the present value of an annuity in perpetuity or the "capitalization method":

MV = NAI * 100/CR

Where:

MV = market value

NAI = net annual income

CR = capitalization rate as a %

The investment analysis of partial interests requires the use of the 4 basic compound formulae. Using computer and financial calculator notation:

* FV = future value

* PV = present value

* FVPMT = future value of an annuity or payment

* PVPMT = present value of an annuity or payment

ALL compound functions can be calculated knowing 3 of the following input s (based on financial calculator notation):

n = number of periods

I = interest rate as a % (i = interest rate as a decimal)

PMT = payment or annuity eg rent

FV = future value

PV = present value

In this module we will construct a useful spreadsheet which can be used to value any compound function.

STAGE 1 SET UP MAIN WINDOW

A1: COMPOUND FUNCTIONS IN VALUATION

A2: (underline)

E4: INPUTS

E5: (underline)

A8: PERIODS (n):

A10: INTEREST RATE PER PERIOD %:

A12: PAYMENTS (pmt):

A14: FUTURE VALUE (FV):

A16: PRESENT VALUE (PV):

E8: 35

E10: 8

E16: 1 0000

Name the following cells:

E8: " n"

E10: "I"

El2 "PMT"

E14: "FV"

E16: "PV"

CALCULATION WINDOW AT AA1

GOTO AA 1

AA1: CALCULATIONS

AA3: base:

AA4: FV:

AA5: PV:

Name AA3 "base". Enter the following formulae:

AB3: =1 +(I/100) (should show 1.08)

AB4: =base^n (should show 14.79)

AB5: =1/AB4 (should show 0.0676)

Check your calculations by noting that AB4 is always > 1 and AB5 is always

< 1. There are 2 types of compound function:

1. Annuities or periodic payments eg rents

2. Lump sums.

Enter the following labels:

AA8: FUTURE VALUE OF PMT:

AA10: PRESENT VALUE OF PMT:

AA12: FUTURE VALUE OF LUMP SUM:

AA14: PRESENT VALUE OF LUMP SUM:

Enter the following formulae:

AE8: =(( +AB4 1)/(l/100))*PMT

AE10: =((1 ab5)/(l/100))*PMT

AE12: =AB4 * PV

AE14: =AB5 * FV

Name the above cells as follows:

AE8: "FVPMT" AE10: "PVPMT" AE12: "FV1" AE14: "PV1"

NB You may have to widen column AE.

Cells AE8, AE10 and AE14 show 0 because PMT and FV are 0 inputs in the main window. AE12 should show 147853.4.

< Home >

USING THE =IF() SPECIAL FUNCTION

How can we get the right answer in the main window that is, how can we eliminate any unwanted calculations? In this example, we only want to know the FV of a PV of 10000. We are not interested in the PMT calculations as the FV is a lump sum calculation only. We can use the =IF() special function to filter out unwanted calculations:

=IF(condition, x, y)

If "condition" is positive (ie not 0) then the spreadsheet will go to cell x. If "condition" is negative (ie 0) then it will go to to cell y. If the subject cell has an entry then it will respond with a positive condition whereas if it is empty or has a 0 value it will respond with a negative condition. This special function is very useful in spreadsheet construction where it is necessary for certain decisions to be made.

A19: ANSWER

A20: underline

A22: =IF(+FVPMT, +AA8," ")

A23: =IF(+PVPMT, +AA10, " ")

A24: =IF( + FV1, + AA12," ")

A25: =IF(+PV1, +AA14," ")

E22: =IF(+FVPMT, +AE8," ")

E23: =IF( + PVPMT, + AE10, " ")

E24: =IF( + FV1, + AE12," ")

E25: =IF(+PV1, +AE14," ")

NB" " means"(space)"

This will result in the correct answer at A24 of "FUTURE VALUE OF LUMP SUM: 147853.4 and the other cells blank.

NB: to obtain the correct answer you must have either a blank or 0 in those inputs which are not required for the correct answer.

ANNUITIES

If you input a PMT of 1000 and 0 for FV and PV you get 2 answers:

FUTURE VALUE OF PMT: 172316.80 PRESENT VALUE OF PMT: 11654.37

This is because there is no =IF() to filter FVPMT and PVPMT. Can you input a =IF() to obtain only one or the other answer?

Save file CF1.

PART 7

LESSEE'S AND LESSOR'S INTERESTS

INTRODUCTION

For most tenanted properties there are 2 interests:

1. Lessee's interest (LEEI)

2. Lessor's interest (LORI)

The valuer can be called upon to value either interest. The sale price of any tenanted property is the lessor's interest.

SETTING UP A LESSEE AND LESSORS' SPREADSHEET

Retrieve last file: CF1

A1: LESSEE AND LESSOR INTERESTS

The easiest answer to the question posed at the end of module 5 to filter out unwanted PMT calculations is to insert the following =IF() decision function:

INSERTING NEW ROWS:

A14: <Edit> <Insert> <Row> ???highlight A14:A16 <1 >

A14: FUTURE VALUE PAYMENTS? (Y/N):

E14: N

GOTO AA1.

Enter the following formulae:

AC8: =IF( + E14 = "Y",(( + ab4 1 )/(l/100) *PMT),0)

AC10: =IF(+E14="N",((1 ab5)/l/100)*PMT),0)

The =IF() functions tell the spreadsheet which calculation to perform depending on the response in E14. For example, at AC8 if E14 has the input of "Y" then it will calculate the FVPMT otherwise it will input 0. You should be able to see by now how decision making functions such as =IF() can "beef" a spreadsheet up and give it "muscle". Test the calculation with the following inputs:

n = 5 I = 8 PMT = 1000 N FV = O PV = O

Will show:

FUTURE VALUE OF PMT: 3993

LESSEE'S I NTEREST ( LEEI)

A lessee has an interest in property if he/she enjoys a "profit rental" that is, an economic interest. If the profit rental is 0 or negative then he/she will not have an interest in the property.

PROFIT RENTAL (PR): Profit rental is the difference between the rent being paid by the lessee and the market rent. Therefore, we need to input market rent to find the PR, if any.

*Create a new row at A14

A13: MARKET RENT:

E13: 1500

Name E13: "MR"

GOTO AA1

AA14: PROFIT RENTAL:

AC14: =MR PMT (should show 500)

Name AC14: "PR"

AA15: PV PR:

AC15:=1F( +AC14,((1 AB5)/(l/100)) *PR,0) (should show 1996)

<HOME>

E28: =IF( +AC15, +AC15,"")

The main sheet will show 2 answers:

PRESENT VALUE PMT: 3993

LESSEE'S INTEREST: 1996

LESSOR'S INTEREST (LORI)

We now have the lessee's interest. The lessor's interest consists of 2 parts:

LORI = PVPMT + RV

Where:

PVPMT = PV of the current rent

RV = reversionary value

We already have the PVPMT = 3993.

REVERSIONARY VALUE (RV)

The RV is the PV of the "unencumbered market value of the property" (UMV). That is the PV of the market rent capitalized into perpetuity due at the end of the lease (5 years).

UMV = MR * 100/RCR

Where:

UMV = unencumbered market value

MR = market rent

RCR = reversionary capitalization rate

Insert 2 new rows at A12.

A12: REVERS CAP RATE %:

E12: 10

Name E12:"RCR"

GOTO AA1

AA21: UNENCUMB MV:

AC2 1: =MR * 1 00/RCR (should show 15000).

However, the investor will not receive that amount until the it falls due at the end of the lease in 5 years time:

AA22: REVERSIONARY VALUE: (may have to widen column AA here)

AC22: =AC21 *AB5

Name AC22: "RV" (should show 10209)

<Home>

A31 :=IF( + E1 5,"LESSEE'S INTEREST:","")

E31: =IF( +AC17, + PVPMT + RV,"") (should show 14201).

Save spreadsheet as LESSI.WK1.

PART 8

PARTIAL INTERESTS I N REAL ESTATE

MORTGAGEE AND MORTGAGOR' INTERESTS

The compound function spreadsheet already constructed can be used to determine mortgagee and mortgagor interests in real estate.

MORTGAGEE'S INTEREST (MORI)

MORI is simply the amount owing on the mortgage at the date of investment analysis. For example, if the subject property is resumed for a public work the compensation payable to the MORI is the amount of monies owing on the mortgage. The amount owing on a mortgage is the present value of all future repayments.

DETERMINING THE REPAYMENTS

Retrieve the previous spreadsheet used for lessee's and lessor’s interests LESSI.WK1.

A1: MORTGAGEE AND MORTGAGOR INTERESTS

Suppose the mortgage terms are: 100 000 borrowed over 15 years @ 9% per annum. There is 12 years to run on the mortgage. That is, this is year 3.

Enter: N = 15 I = 9

A12: AMOUNT BORROWED:

A14: NUMBER OF PERIODS REMAINING:

E12: 100000 (name "P" for principal)

E14:12 (name "PR" for period remaining)

GOTO AA1

AA10: PVPMT FACTOR 15 YEARS:

AE10: =(1 PV)/(I/100) (should show 8.061)

The present value of 1 per period for 15 years @ 9% per annum is 8.061. The loan amount of 100 000 must be a present value figure as the borrower (mortgagor) receives the money today (time period 0).Therefore, the annual repayments for a loan of 8.061 is 1 per annum. Using proportions the annual repayments for a loan of 100 000 must be 100000/8.061.

AA11: MORTGAGE REPAYMENTS:

AE11: =P/+AE10 (should show repayments of 12 405 pa).

Save as MORTG.XLS

Now determine the PV of the outstanding repayments:

AA12: PVPMT FACTOR 12 YEARS:

AE12: =(1 (1/BASE^PR))/(I/100)

Name AE12: "PVPMT,12" (should show 7.1607)

Check by making sure it is less than PVPMT FACTOR 15 YEARS.

<Home>

Erase rows 15 30.

A16: MORTGAGEE'S INTEREST:

E16: =AE11*PVPMT12 (should show 88 835).

A20: MARKET VALUE:

E20: 150000

Name E20: "MV"

Market value can be calculated from the previous spreadsheet LESSI that is, lessor's interest.

A22: MORTGAGOR'S INTEREST:

E22: =E20 E16

PART 9

ANALYZING COMPLEX RENTAL SCHEDULES

As pointed out previously, an ideal application of a DCF is in the analysis of a complex rent schedule. These are the kind commonly found for CBD commercial buildings. Complexity arises through:

1. Different leases having different rent review dates, starting dates and lengths

2. Different calculations for recoveries for example, “increase in statutory outgoings” compared with “all outgoings”.

3. Different bases for the calculation of rent reviews for example, “market rents” compared with “overage” (%) rents based on gross income (usually for the retail component).

4. The use of different incentives that are supposedly, “secret”.

EXAMPLES OF DIFFERENT LEASE TYPES

The following are 3 different leases in a building. The valuer’s job is to determine the Annual Rental Equivalent” (ARE) from a rent schedule made up of the 3 leases:

1. New lease. Anchor retail tenant for a term of 25 years

Rents payable: A starting rent of 200 000 per annum then 2% of gross turnover whichever is the higher.

Recoveries: A pro rata contribution on ALL outgoings starting at 25 000 per annum.

2. New lease. A government department for a term of 15 years

Rents payable: Starting rent of 20 000 pa then to market at 2 yearly review periods.

Recoveries: A pro rata contribution on increases in statutory outgoings starting at 5 000 per annum.

Incentives: Rent free period for the first 2 years.

Free fitout worth 50 000.

3. A private company for a term of 10 years and with 8 years to run.

Rents payable: Starting rent of 12 000 per annum then to market every 3 years.

Recoveries: Pro rata increases in ALL outgoings from base year

Current recoveries are 3 000 pa

Incentives: Rent free period for the first 2 years.

Free fitout worth 150 000.

Determine the annual rental equivalent for the building.

TASK 1: DETERMINE THE L ENGTH OF THE DCF

As the anchor tenant has a 25 year lease and is the majority contributor of rent, the length should be based on that tenant’s period. Therefore, the length is the maximum period for reasonably certain forecasting: 5 years.

TASK 2: INPUT THE EXPECTED CASH FLOW FOR TENANT 1:

The rent will not fall below 200 000 because of the “ratchet” clause. Therefore, we know that it will be at least 200 000 for the 5 year period. Expectations of an increase is a function of:

* FUTURE COMPETITION.

Are there any DAs with local and surrounding councils for competing retail space?

* EXPECTED DEMOGRAPHICS OF THE RETAIL CATCHMENT AREA.

Is the population expanding or decreasing? Is the average household income increasing or decreasing? If the expected demographics are better than today then one can expect an increase in rent in the immediate future.

* EFFECT OF A NEW STORE.

Does the history of this retailer show that market will improve as company goodwill increases through advertising and promotion? Does the retailer have a quality name and image? That is, is it Djs or Myers?

Taking all of the above into account it is expected that rents for tenant 1 will increase by 5%pa for the 5 year period. After 5 years it will be deemed to be constant to perpetuity. Note, all these projections are in REAL terms.

The forecast change for outgoings is a more certain and easier to forecast than for future rents. Using a weighted trend line from BOMA historical costs it is expected that the cost of outgoings will increase at 8% per annum on average.

TASK 3: INPUT THE EXPECTED CASH FLOW FOR TENANT 2

Historical comparable rents on large area lettings are analyzed to obtain an expected annual increase of about 5% per annum.

The recoveries are for increases over the base year only. Therefore, there will be no recoveries paid for the first year, and the maximum amount in the third year. The weighted trend line analyzed from BOMA data shows that the expected increase in statutory outgoings is about 10% per annum.

TASK 4: Input the expected cash flow for Tenant 3

The initial 2 years of the lease are ignored completely as the value of the lease to a purchaser of the building today, is the right to receive all FUTURE income and benefits arising from the current leases. Therefore, the rent free period and cost of fitout is completely ignored. This logic also applies to premiums paid at the beginning of old leases.

The future market rents for this size company is analyzed from a history of comparable rents. This shows an expected trend line of 8% per annum. The expected increase in costs are analyzed from BOMA’s historical data and are expected to be 8% per annum average.

CREATING MAIN WINDOW AT A1

A1: ANNUAL RENTAL EQUVALENT FOR A COMPLEX RENT SCHEDULE

Widen column A to 20 characters

Save as ARECRS1.XLS

A3: Annual discount rate % pa:

A4: As a decimal:

A5:base:

C3: 8

C4:=C3/100 shows 0.08

C5:=1+C4 shows 1.08

E3:PV(1):

F3:=1/C5^5

E4:(space)1-PV(1):

F4:=1-F3

G3:PV.PMT:

H3:=F4/C4

A6:MARKET RENT ESCALATION %:

C6:Tenant 1:

D6:5

D7:8

F6:5

F7:10

H6:8

H7:8

E6:Tenant 2:

G6:Tenant 3:

A7:RECOVERIES ESCALATION %:

C7:Tenant 1:

E7:Tenant 2:

G7:Tenant 3

C9:YEAR

D9:(space)$’000

B10:F10:enter 0 to 4

A11:TENANT 1

A12: Rent:

A13: Recoveries:

A14: Totals:

Align A12:A14 to the right

A15: TENANT 2

A16: Rent:

A17: Recoveries:

A18: Fitout:

A19: Totals:

Align A16:A19 to the right

A20: TENANT 3

A21: Rent:

A22: Recoveries:

A23: Totals:

Align A21:A23 to the right

A24: GRAND TOTALS:

A26:Discount factors:

A28: TENANT 1:

A29: NPV:

A30: ARE:

Align A29:A30 to the right

C28: TENANT 2:

C29: NPV:

C30: ARE

Align C29:C30 to the right

E29:TENANT 3:

E30:NPV:

E31:ARE:

A32: GRAND NPV:

A33: GRAND ARE:

APPLYING NAMES TO RELEVANT CELLS

Highlight D6

Version 5: <Insert><Name><Define> In Names in Workbook box type: T1MRE <OK>

(stands for Tenant 1 Recoveries Escalation)

In the same way name the following cells:

F6:T2MRE (Tenant 2 Market Rent Escalation)

H6:T3MRE

D7:T1RE

F7:T2RE

H7:T3RE

CREATE AA1 WINDOW

GOTO AA1 Scroll to right so that AA1 is in the top lefthand corner

AA1:EXPECTED MARKET RENTS AND RECOVERIES

AA3:TENANT1

AA4:Market rent escalation:

AD4:=T1MRE should show 5

AA5:Recoveries escalation:

AD5:=T1RE should show 8

COPY AA3:AD5 to AA7 and AA11 this is the quick way to set up the data for Tenants 2 and 3.

EDIT the appropriate cells to correspond to the necessary data for Tenants 2 and 3:

Reduce width of columns AB,AC,AD to 6 characters each.

AE2:AJ2: 0..to...5

AE4:200

AE5:25

AE8:20

AE9:5

AE12:12

AE13:3

AF4:=(1+($AD$4/100))^AF2*$AE$4 the brackets are important

AF5:=(1+($AD$5/100))^AF2*$AE$5

AF8:=(1+($AD$8/100))^AF2*$AE$8

AF9:=(1+($AD$9/100))^AF2*$AE$9

AF12:=(1+($AD$12/100))^AF2*$AE$12

AF13:=(1+($AD$13/100))^AF2*$AE$13

COPY AF4 to AG4:AJ4

COPY AF5 to AG5:AJ5

COPY AF8 to AG8:AJ8

COPY AF9 to AG9:AJ9

COPY AF12 to AG12:AJ12

COPY AF13 to AG13:AJ13

NAMING CELLS

Enter the following cell names as follows:

AE3:AJ3: T1MR0....to... T1MR5

AE6:AJ6: T1R0...to.....T1R5

Highlight AE3:AJ4

Version 5:<Insert><Name><Create> Create Names in x Top Row <OK>

Highlight AE5:AJ6

Version 5:<Insert><Name><Create> Create Names in x Bottom Row <OK>

(Delete x for left hand column)

Use the same procedure to name the cells for Tenants 2 and 3.

BACK TO A1 WINDOW

B12:=T1MR0 shows 200

C12:F12:=T1MR1...to...T1MR4

B13:=T1R0 shows 25

C13:F13:=T1R1....to....T1R4

B14:=SUM(B12:B13)

COPY B14 to C14:F14

Carry out the same procedure for Tenants 2 and 3.

Remember to include rent free periods and fitouts if applicable.

Fitouts are entered as a negative number as it is a cost born by the landlord.

B24:=SUM(B14,B19,B23)

COPY B24 to C24:F24

Change B10:F10 from 0..to...4 to 1...to...5. This is because we now assume that the rents and outgoings are paid in arrears.

B26:=1/$C$5^B10 shows 0.9259

COPY B26 to C26:F26

B29:=SUM(B26*B14,C26*C14,D26*D14,E26*E14,F26*F14) shows 991.64

B30:=B29/H3

Repeat the above procedure for Tenants 2 and 3.

B32:=SUM(B26*B24,C26*C24,D26*D24,E26*E24,F26*F24) shows 1065.49

B33:=B32/H3

UNDERSTANDING YOUR SUCCESS !

The results of the Annual Rental Equivalents can be interpreted as follows:

NPVs: The NPVs for each tenant are the present values for the DCF over the 5 year period. Obviously each should be substantial and certainly not a negative value.

The Grand NPV is the present value of the DCF for the whole building over the 5 year period. To obtain the market value of the building it would now be necessary to add the Reversionary Values for each of the leases. This should be done using different Initial yield Capitalization rates for each tenant as each has a different risk.

AREs: The main purpose of the exercise is to determine the Annual Rental Equivalents of the 3 leases and building for analyses and comparison purposes. Therefore, after applying the DCF we now know that the rent being paid by tenant 1 is equivalent to about $248 000 per annum, tenant 2; about 3 400 per annum and tenant 3; about 15 100 per annum.

This clearly shows the impact of the cost of fitout on the ARE and the landlord would require a long term lease as compensation. Such AREs can now be used to compare leases with comparable buildings and tenants, a source of a great deal of conflict during the rent review period. However, a DCF as shown, should be the basis of such analysis as this is the only method that can successfully analyse a complex future cash flow commonly found in rent schedules.

MORE ON PRESENTATION

DCFs by their very nature involve speculation and forecasting. Therefore, the final investment analysis and rental analysis report should be couched in terms appropriate to such forecasts. The better report is one that present 3 scenarios to the client; pessimistic, expected and optimistic. This would provide a better “feel” by showing a possible range of rents.

The DCF can be shown in the final report without the gridlines as follows:

<File><Page Setup..><Sheet> delete x against Gridlines <OK>

PART 10

FINE TUNING

At the end of Part 5 you were asked how can you improve the cash flow to take into account 2 typical features of rental income:

* Rent are paid at intervals less than a year

* Rents are paid in advance (annuities due).

A simple solution is to use quarterly periods (the most common period used in industry for both rental and outgoings payments) and this would overcome the first feature and make the second feature less important as its effect is mitigated.

However, going to quarterly modes would require a 20 period cash flow for a 5 year period and therefore, “landscape” printing to show such a large cash flow. A more elegant response is to convert the quarterly payments to both annual and annuities due equivalents. This has the advantage of keeping the cash flow simple and easy to read but just as accurate as the long version.

ADJUSTING THE CASH FLOW TO QUARTERLY PAYMENTS

Open file ARECRE1.XLS

GOTO window AA1

AA16: CONVERSION TO QUARTERLY PAYMENTS

AA17: Payment period (1=year 2=half year 4=quarterly):

AG17:4

AA18:Interest rate per annum:

AG18:10

AA19:Interest (decimal) per period:

AG19:=AG18/100/4 shows 0.025

AH17:base:

AI17:=1+AG19 shows 1.025

AH18:FV(1):

AI18:=AI17^AG17 shows 0.90595

AH19:FVPMT:

AI19:=(AI18-1)/AG19 shows 4.1522

Using <F2> (edit) change the following cells formulae as follows:

AE4:=200/AG17

AE5:=25/AG17

AE8:=20/AG17

AE9:5/AG17

AE12:12/AG17

AE13:3/AG17

GOTO Window A1

GRAND NPV has increased to 1129.85

GRAND ARE has increased to 282.98

This shows the difference that quarterly payments make to the value of the cash flow.

Save as ARECRS2.XLS

QUESTION

What difference would monthly payments make to the GRAND NPV and ARE?

The above changes are certainly accurate enough for most investment analysis purposes. However, you will notice that according to this model both the outgoings (recoveries) and rents are paid at the same time (the end of each quarter). However, the main difference between rental returns and other investments is that rents are paid in advance and therefore, provide the property investor with an extra margin compared with otherwise equivalent investments such as shares or bonds.

How can payments in advance be incorporated into the cash flow?

The advantage to the investor is that he/she can invest the income received for that period and therefore, the advantage is interest earned at bank rates for the period of payment.

FINE TUNING

AE4:=200/AG17*AI19*AI17

Add *AI17 to the formulae in cells AE8 and AE12

The new values are:

GRAND NPV:1155.27

GRAND ARE:289.34

You can see that it makes little difference to the values and would be even less for 12 monthly payments. However, it is an important and necessary calculation for half yearly or yearly rental payments.

Save.

USING EXCEL BOOK

You will have noticed that Excel has a BOOK mode. That is, when open the Excel Window there are 6 sheets shown by the tabs at the bottom of the window. The rear 5 sheets can be used to support the first sheet and therefore, as “windows” of supporting data or calculations as windows were used before.

Any cell on page 1 can refer to any cell or range on the rear sheets using cell or range names.

EXAMPLE:

GOTO Sheet 2 by clicking on the tab at the bottom of the spreadsheet.

ON SHEET 2

A1:50

Name A1 TESTCELL as follows

<Insert><Name><Define>

In Names in Workbook box enter: TESTCELL <OK>

GOTO Sheet 1

A37:=TESTCELL shows 50

In this manner any of the 5 rear pages can be used to support the cash flow on Sheet 1.

EXERCISE

Using <Edit><Cut><Paste> transfer all the working data in Window AA1 to Sheet 2.

Save as ARECRS3.XLS

GRAPHING THE CASH FLOW

Valuation reports can be improved by importing graphs into the report. Excel has a very powerful graphing tool called the graph wizard. It is shown as the button that looks like this:

Highlight A12:F14

Click onto the CHART WIZARD

Click on the vacant part of the spreadsheet. Shows ChartWizard window Step 1 of 5.

Check that the range shown is: =$A$12:$F$14 <Next>>

In Window Step 2 of 5 click onto Line chart box <Next>>

In Window Step 3 of 5 click onto format 4 <Next>>

Step 4 of 5 shows the small graph if OK <Next>>

Step 5 of 5 Add a Legend? ¤ YES

Axis Titles

Category [X]: YEAR

Category [Y]:$’000 <Finish>

Chart shown in vacant area of spreadsheet.

Save as ARECRS4.XLS.

To print into a report:

Highlight graph by clicking onto the graph to highlight it.

This is shown by 6 black squares on the boundaries.

<Edit><Copy>

This puts the graph onto the CLIPBOARD to be used elsewhere.

Click onto the top lefthand corner symbol. <Minimize>

Open Word 6 to represent the report into which you intend to insert the graph.

IN WORD

<Exit><Paste Special...>

Highlight Picture <OK> INVESTMENT ANALYSIS - ON SPREADSHEET

NOTATION USED

<> means you press that key

<> + <> means you press both keys at the same time

<¿> means press the enter or return key

NB the following instructions are for Excel. You don't need to use Excel. You can access free spreadsheets in Libre Office and Google Docs. The notation will be different but the methodology is the same.

PART 1

BOOTING IN EXCEL

AIM

Upon completion of this part you will be able to boot in Excel and use basic keystrokes.

NOTATION USED IN THESE LESSONS

< > means perform the keystroke in brackets, for example;
<E> means press the Enter key

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

<NumLock> means press the NumLock key. Make sure that NumLock is OFF
<F1 > <F12> refers to the "F" (function) keys at the top of the keypad. For example,
< F5 > means press the " F5 " key NOT < F > and then < 5 > .

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

<Alt> + <F5 > means both the <Alt> and < F5 > keys are pressed together

DRIVE SELECTION

a: means select drive a.

BOOTING INTO EXCEL

Excel is located in the Windows environment. Therefore, it is necessary to firstly boot in Windows. After entering Windows, the Excel Window is found by entering:

<Window> <Excel> <¿>

The Excel spreadsheet will appear. This is a set of gridlines with cell addresses:

Columns: A H Rows: 1 20

This may vary slightly due to different computer configurations. If your screen does not show at least rows 1-20:

In the View menu make sure there is a check against Formula and Status Bar only.

MOVING THE CURSOR

The cursor can be moved with the arrow keys from cell to cell but it is much easier and quicker to use the mouse.

PART 2

USING EXCEL

AIM

This part will introduce you to the basic elements of Excel.

LOOKING AT THE SPREADSHEET

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

2. Move cursor to H1 and then to I1

3. Depending on your computer’s configuration, at I1 Column A may disappear so that the screen (window) can accommodate Column I. The spreadsheet has scrolled from right to left. At any one time (under normal formatting) there are at least 8 columns and 20 rows on a screen or window.

4. You can move back to A1 at any time using <Ctrl>+<Home>. Move back to A1.

5. Note in Excel that the 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 >:

EXAMPLE: To move cursor to AB1000:

< F5 > type in Reference box: AB1000 <OK>
<Ctrl>+< Home >

ENTERING DATA

There are two types of data:

1. Alpha (A Z) or label or string
2. Numerical or numbers

If the first letter of an input is alpha, Excel will interpret it as a LABEL (in computer jargon this is also known as a "string"). If the first letter is a number, 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 input display window using < F2 > .

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 using 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 other sheets for calculations, notes or data which will not be printed in the final report
windows will be covered later.

6. Save and save often!

7. When using a formula, break it up into small parts that show the answer for each part.
This will enable easier debugging.

PART 3

CONSTRUCTING A SIMPLE CASH FLOW (DCF)

AIM

On completion of this lesson the participant will be able to:

1. Use a number of useful spreadsheet commands
2. Construct a simple cash flow.

CASH FLOWS

The following DCF is the DCF covered in Appraisal Two, pages 54/62 under the heading "Feasibilities and Rates of Return". Always bear in mind the purpose of the DCF, it is only a “means to an end”. There 4 main uses in investment analysis:

To determine the present value of progress payments to determine the "expected cost to build ".
To determine the highest and best use of land that is subject to a number of competing land uses and subsequently, the determination of land value (sometimes called site value). Also known as hypothetical development and a "feasibility study" over development land.
To determine the market value of an investment property (for example, a block of flats) that is subject to a complex rent schedule. This is also known as the reversionary method of investment analysis and is the most popular method for such properties.
To prepare an investment report for an investment property. This can be either an analysis of past performance (for example, for portfolio culling purposes) or an analysis of expected return on investment.

Often the time period is determined by the client but otherwise, it should be as short as possible. A good time period is 2/3 years but 5 years is more common. Never prepare a DCF for 10 or 20 years!

USES OF DCF BY THE REAL ESTATE PROFESSIONAL

Always bear in mind the purpose of the DCF as it is only a means to an end.

There 4 main uses in investment analysis:

1. To determine the present value of progress payments to determine the "expected cost to build of a building.

2. To determine the "highest and best" use of land that is subject to a number of competing land uses and hence land value (sometimes called "site value").
Also known as hypothetical development and a "feasibility study" over development land.

3. To determine the market value of an investment property (eg block of flats) that is subject to a
complex rent schedule.

This is also known as the reversionary method of investment analysis and is the most popular method for such properties.

4. To determine market rental from a complex rent schedule.

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

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 probability any change in a cash flow (eg rent levels) then you must input a level amount.

That is, do not input cash flows that show increases or decreases solely because it will make your cash flow "look good".

If you are determining "market value", it is what the market forecasts that counts, not what you may forecast. Future "guestimates" are too dangerous under current professional liability law.

Real estate is an unique economic factor as the total implied time period of the cash flow is ALWAYS in perpetuity:

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

END MARKET VALUE (EMV)

Because land is held under freehold tenure (or in the case of Canberra, “quasi freehold”), 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. It is immaterial whether or not the investor intends to keep the property for a longer period. Opportunity cost arguments indicate that the assumption of a sale on completion is exactly the same as assuming ownership in perpetuity. The end market value is determined by the initial yield formula:

EMV = EAI * 100/CR

Where:

EMV = end market value
EAI = end net annual income
CR = capitalization rate as a percentage.

Generally, EAI is equal to the last net annual income expected in the cash flow period. The reversionary value or capitalization rate should be analyzed from market transactions for example, sales of CBD office blocks using 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 investment analysis.

MARKET VALUE OF AN INVESTMENT PROPERTY

The above capitalization formula is derived from the following sum of a geometric progression. The sum of single present values for each periodic income and benefit is equal to:

PV.PMT = ( 1 - (1/(1+i)^ⁿ))/ i

Where:

PV.PMT = the present value of 1 per annum

n = the period less than perpetuity

i = interest rate as a decimal

The limiting value is found by increasing n:

As n --------------------------------> perpetuity

1/(1+i)^ⁿmust --------------------> 0

Therefore when n = perpetuity:

PV.PMT = 1/i or EAI/(100/CR) if the interest rate is expressed as a %.

CONSTRUCTING A SIMPLE CASH FLOW

The following part is designed for self instruction. You should proceed at the rate or pace most suitable for you. Enter the following into the relevant cells:

A1: SIMPLE DISCOUNTED CASH FLOW

E10: (space)$'000

E8: YEAR

Align E8 to the right and underline as follows:

Excel <Format><Cells><Alighnment> ¤ Right <OK>

<Format><Cells><Font> Underline ò Single <OK>

B9 to G9: 0,1,2,3,4,5

Highlight B9:G9 by holding the LH button of the mouse down and dragging across the cells. Underline as above

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:

Highlight A11:

Excel Version 3.0: <Format> <Column width><20><OK>

Excel Version 5.0: <Format><Column><Width>

Enter in Column Width box: 20 <OK>

SAVE FILE

Place the formatted disk into a: <File><Save as>

Change drive to a: in Drives box

Enter file name: DCF1.XLS <OK>

.XLS is the extension used for an EXCEL worksheet file. Check that the file has been properly saved by;

<File><Open> Screen shows DCF1.XLS in File name box:

<Cancel>

ENTER CASH FLOW DATA

A12: Rental Income:

A13: End market value:

B 11: 2000

C12: 166

B11 is the only negative amount in the cash flow because it is the only cost (purchase price).

If you make a mistake and wish to clear the cell: <Edit><Clear><All>

COPY COMMAND

To copy the value 166, to cells D12:G12:

Highlight source cell C12

<Edit><Copy> a moving broken line appears on the cell’s borders

Highlight cells D12:G12 by dragging and holding down the LH button of the mouse

<Edit><Paste>

SAVE FILE

Now that you have given the file a name it can be quickly saved using:

<File><Save>

END MARKET VALUE (EMV)

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

Excel interprets the above as a formula because it starts with "=".

EXAMPLE: Change the last net annual income (G12) to 200. G13 shows an EMV of 2409.64. Change G12 back to 166.

A15: Totals:

SUMMING THE COLUMNS

The automatic summation of each column can be achieved by using the = SUM() and COPY commands:

B15: =SUM(B11:B13)

With experience, this will be quickly be carried out by pointing with the cursor. Copy the special command to cells C15:G15 as:

Highlight B15:

<Edit> <Copy>

Highlight C15..G15 <Edit><Paste>

The Totals row should show: -2000 166 166 166 166 2166

IMPROVING THE LOOK OF THE CASH FLOW

Highlight A11:A15

Excel Version 3.0: <Format><Alignment> ¤ Right <OK>

Excel Version 5.0: <Format><Cells><Alignment> ¤ Right <OK>

Save File.

Congratulations! You have now completed a cash flow.

QUESTIONS – UNDERSTANDING YOUR SUCCESS!

1. The cash flow accounts for ALL expected income and benefits to be derived from this property from 0 to ..............(fill in the missing word).

2. Why is the End Market Value necessary in a cash flow of an investment real estate property?

3. Format the numbers in the cash flow using <Format><Numbers> and then highlight [red][# ###] format in the format box. Note how the new format makes negative numbers stand out.

Your cash flow should look like the cash flow below.

4. Change the format for the numbers in the cash flow so that negative numbers are in brackets and red.

TO PRINT CASH FLOW

Highlight A1:H17 <File><Print> ¤ Selection <OK>

Note that Excel enables you to preview the printout with:

<File> <Print Preview>

This is a very useful feature as the Cash Flow can be for example, centered on the page.

GOOD DCF CONSTRUCTION 3

When previous changes in value are analyzed in real terms it is most likely that the expected increases will be very small or nil.

PART 4

DISCOUNTING THE CASH FLOW

AIM

At the end of this part you 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.

ENTER THE DISCOUNT DATA

A3: Discount rate at % per annum:

A4: As a decimal:

A5: Base:

C3: 8.3

C4: =C3/100 shows 0.083

C5: =1+C4 shows 1.083

A16: Discount factors:

B16: =1/C5^B9 shows 1

NB: "^" = "to the power of"

THE DISCOUNT FACTOR

The discount factor has been calculated using the following PRESENT VALUE OF $1 formula:

PV(1) = 1/(1+i)ⁿ

Where:

PV(1) = present value of 1

i = interest rate as a decimal eg 8.3% = 0.083

n = period in years.

COPY B16 to C16:G16 as shown above

RELATIVE CELL ADDRESSES

FIXED CELL ADDRESSES

B16: <F2> (edit) in the formula box change C5 to $C$5

The insertion of the "$" symbols tells Excel to fix the cell address C5. When used in a COPY command it's value will not change. COPY B16 again.

Format the discount factors to 4 decimal places as follows:

Highlight the range: B16:G16:

Version 3: <Format> <Number> In the Code box, enter 0.0000 <OK>

Version 5: <Format><Cells><Number> In Code box enter 0.0000 <OK>

Any number entered in the range B16:G16 will now automatically change to 4 decimal places. The discount factors are:

1.0000 0.9234 0.8526 0.7873 0.7269 0.6712

SAVE file using <Save as..>

Give the file a new file name: DCF2.XLS.

NET PRESENT VALUE (NPV)

You are now ready to discount the cash flow:

A18: Discounted cash flow:

B18: =B16*B15 shows -2000

COPY to B1 8 to C18:G18.

Note that in this case we require all the addresses to be relative.

A19: Net Present Value (NPV):

B19: =SUM(B18:G18) shows 0.00. It may be in scientific notation ie to the power of 10.

FORMAT:B18:G19 to 2 decimal places

SAVE file.

Congratulations! You have just completed a discounted cash flow.

UNDERSTANDING YOUR SUCCESS!

NET PRESENT VALUE (NPV)

The NPV is a most important statistic. It is the expected absolute amount that will be received by an investing for the subject cash flow if discounted at the subject rate. In this case where the

NPV = 0 then the return on investment is equal to the discount rate. This will be explained in the next module.

IMPROVING THE APPEARANCE OF THE SPREADSHEET

-2000, use (2000). You can see that (2000) clearly identifies the number as a negative number.

The spreadsheet can easily be reformatted to negatives in brackets as follows:

Highlight B11:G15

<Format><Cells><Number>

In code box enter: # ###;(# ###) or for negative in red: # ###; (red)(# ###)

This will tell Excel to format all the numbers in the range B11:G15 so that:

There are no decimal places
There is a space separating the “thousand” number
All negative numbers are in brackets.

This format substantial improves the look of the spreadsheet and is more professional looking. It is recommended that “$” not be used as it is unnecessary.

Using the above method reformat B19 to 2 decimal places and negatives in brackets.

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. Return the discount rate to 8.3%. Suppose the seller raises the purchase price (starting value) to 2500 (000). What should the new discount rate be that shows a NPV = 0.

What is this rate called and why?

4. Without considering the discount rate, how can a preliminary perusal of the NPV alone be used to assess the value of the land?

PART 5

RETURNS ON INVESTMENT

AIM

On completion of this part you will be able to determine the following returns on investment for an investment property:

Internal rate of return (IRR)
Initial yield (IY)
Equivalent yield (EQVY)
Equated yield (EQTY)

THE INITIAL YIELD (IY) MODEL

1. Retrieve previous DCF file (DCF2.XLS). With the Starting market value of (2000) discount again at 8.3% pa. Make sure that NPV=0.00.

8.3% pa is the initial yield rate of return on investment.

2. The initial yield rate of 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) rental income.
The initial rent remains level from 1 to perpetuity
The End Market Value (EMV) = the Starting Market Value (SMV). That is, it is assumed there are no capital gains.

166 000 pa = = = = = = = = = = = ================== = = = = >

O = = = = = = = = ======= = = = = = = = = = = = = = = = = = = = = > perpetuity

(2 000 000) 2 000 000

ANALYZING SALES TO FIND THE INITIAL YIELD RATE OF RETURN

EXAMPLE

The sale price is known = (2000)
The end market value is known = 2000
The net annual income is known = initial rent = 166

DETERMINE THE INTERNAL RATE OF RETURN (IRR)

The internal rate of return (IRR) is that discount rate that results in a NPV=0.

Enter any starting rate in C3 for example, 6% per annum.
This will give a positive NPV (>0)
Therefore, the rate needs to be increased until the NPV = 0.00 that is, 8.3%. You can carry this out using “trial and error”. With practice, the IRR can be quickly found.

INTERNAL RATE OF RETURN (IRR)

The discount rate that determines the NPV that =0 is called the "internal rate of return" (IRR)

HOW IS THE INITIAL YIELD MODEL USED?

EQUIVALENT YIELD (EQVY) MODEL

WINDOWS IN EXCEL

The Excel book can be used to for ancillary (not printed) information. Create a new sheet 2 (if you do not already have one) as follows:

<Insert><Worksheet>

Sheet 2 tag appears at the bottom of the spreadsheets. Enter Sheet 2 by clicking on the Sheet 2 tag. Sheet 2 can be used to determine the market rents as follows:

A1: MARKET RENT

A2: Expected rate of increase (% pa):

E2: 4

A4: YEAR:

C4:G4: 1 to 5

A6: RENT PA:

C6:=SR starting rent in C15 Sheet1 (166)

D6: =C6*(1 +($E$2/100)) shows 172.64

QUESTION: Why is E2 a “fixed” address?

Copy D6 to E6:G6

C6:G6 shows the following range of market rents (rounded to 2 decimal places):

166.00 172.64 179.55 186.73 194.20

Name D6:G6 MR2 to MR5 respectively (market rents for the years 2-5)

Sheet 2 will look like this. As the window is “hidden” and will not be printed out in the investment analysis report, there is no need to improve its appearance with better formatting.

Return to Sheet 1 by clicking on the Sheet 1 tag at the bottom.

ENTERING MARKET RENTS IN SHEET 1

A1: EQUIVALENT YIELD MODEL

E12:=MR3

F12:=E12

G12:=MR5

G13:= B11

At 8.3% the NPV=39.44. Find the IRR by gradually increasing the discount rate until the NPV = 0.

NB: NPV need not exactly = 0.

Sheet 1 will look like this:

Equivalent yield = about 8.795% per annum.

Save as DCF3.XLS

EQUATED YIELD (EQTY) MODEL

A1: EQUATED YIELD MODEL

G13: =G12*100/8.3 shows 2340

Determine the IRR or equated yield, that is, the new discount rate that shows a NPV of about 0. The answer is about 11.48% per annum.

Save as DCF4.XLS.

DCF4 should look as follows:

PRESENTATION AND PRINTOUT

The appearance of the printed spreadsheet can be improved by removing the gridlines. This is done as follows:

Use <File><Page Setup..> command:

<Sheet>

Print

x Gridlines remove x

<OK>

Highlight A1:H20

<File><Print> ¤ Selection <OK>

EXERCISES

1. Using the cash flow in DCF4 determine the market value of an investment property using the following information:

Expected increase in market rents: 6% per annum

Equated yield rate analyzed from sales of comparable properties: 12% pa

Save as DCF5

2. Using the cash flow in DCF4 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

Save as DCF6.

3. Using the basic 5 year cash flow in DCF4. How can you incorporate the following features into the DCF?

Rental adjustments to take into account that the annual rent is paid quarterly
Rental adjustments to take into account that the rents are paid “in advance”. That is, as “annuities due”.

PART 6

COMPOUND FUNCTIONS

AIM

On completion of this module the participant will be able to set a spreadsheet using 1 23's special functions to determine basic partial interests.

INTRODUCTION

The investment analysis of property often requires the investment analysis of "partial instruments". That is, parties other than the freehold owner have interests in the property. For example:

* Lessee and lessors' interests

* Mortgagee and mortgagors' interests.

Because such interests have a time period less than perpetuity they cannot be valued with the present value of an annuity in perpetuity or the "capitalization method":

MV = NAI * 100/CR

Where:

MV = market value

NAI = net annual income

CR = capitalization rate as a %

The investment analysis of partial interests requires the use of the 4 basic compound formulae. Using computer and financial calculator notation:

* FV = future value

* PV = present value

* FVPMT = future value of an annuity or payment

* PVPMT = present value of an annuity or payment

ALL compound functions can be calculated knowing 3 of the following input s (based on financial calculator notation):

n = number of periods

I = interest rate as a % (i = interest rate as a decimal)

PMT = payment or annuity eg rent

FV = future value

PV = present value

In this module we will construct a useful spreadsheet which can be used to value any compound function.

STAGE 1 SET UP MAIN WINDOW

A1: COMPOUND FUNCTIONS IN VALUATION

A2: (underline)

E4: INPUTS

E5: (underline)

A8: PERIODS (n):

A10: INTEREST RATE PER PERIOD %:

A12: PAYMENTS (pmt):

A14: FUTURE VALUE (FV):

A16: PRESENT VALUE (PV):

E8: 35

E10: 8

E16: 1 0000

Name the following cells:

E8: " n"

E10: "I"

El2 "PMT"

E14: "FV"

E16: "PV"

CALCULATION WINDOW AT AA1

GOTO AA 1

AA1: CALCULATIONS

AA3: base:

AA4: FV:

AA5: PV:

Name AA3 "base". Enter the following formulae:

AB3: =1 +(I/100) (should show 1.08)

AB4: =base^n (should show 14.79)

AB5: =1/AB4 (should show 0.0676)

Check your calculations by noting that AB4 is always > 1 and AB5 is always

< 1. There are 2 types of compound function:

1. Annuities or periodic payments eg rents

2. Lump sums.

Enter the following labels:

AA8: FUTURE VALUE OF PMT:

AA10: PRESENT VALUE OF PMT:

AA12: FUTURE VALUE OF LUMP SUM:

AA14: PRESENT VALUE OF LUMP SUM:

Enter the following formulae:

AE8: =(( +AB4 1)/(l/100))*PMT

AE10: =((1 ab5)/(l/100))*PMT

AE12: =AB4 * PV

AE14: =AB5 * FV

Name the above cells as follows:

AE8: "FVPMT" AE10: "PVPMT" AE12: "FV1" AE14: "PV1"

NB You may have to widen column AE.

Cells AE8, AE10 and AE14 show 0 because PMT and FV are 0 inputs in the main window. AE12 should show 147853.4.

< Home >

USING THE =IF() SPECIAL FUNCTION

=IF(condition, x, y)

A19: ANSWER

A20: underline

A22: =IF(+FVPMT, +AA8," ")

A23: =IF(+PVPMT, +AA10, " ")

A24: =IF( + FV1, + AA12," ")

A25: =IF(+PV1, +AA14," ")

E22: =IF(+FVPMT, +AE8," ")

E23: =IF( + PVPMT, + AE10, " ")

E24: =IF( + FV1, + AE12," ")

E25: =IF(+PV1, +AE14," ")

NB" " means"(space)"

This will result in the correct answer at A24 of "FUTURE VALUE OF LUMP SUM: 147853.4 and the other cells blank.

NB: to obtain the correct answer you must have either a blank or 0 in those inputs which are not required for the correct answer.

ANNUITIES

If you input a PMT of 1000 and 0 for FV and PV you get 2 answers:

FUTURE VALUE OF PMT: 172316.80 PRESENT VALUE OF PMT: 11654.37

This is because there is no =IF() to filter FVPMT and PVPMT. Can you input a =IF() to obtain only one or the other answer?

Save file CF1.

PART 7

LESSEE'S AND LESSOR'S INTERESTS

INTRODUCTION

For most tenanted properties there are 2 interests:

1. Lessee's interest (LEEI)

2. Lessor's interest (LORI)

The valuer can be called upon to value either interest. The sale price of any tenanted property is the lessor's interest.

SETTING UP A LESSEE AND LESSORS' SPREADSHEET

Retrieve last file: CF1

A1: LESSEE AND LESSOR INTERESTS

The easiest answer to the question posed at the end of module 5 to filter out unwanted PMT calculations is to insert the following =IF() decision function:

INSERTING NEW ROWS:

A14: <Edit> <Insert> <Row> ???highlight A14:A16 <1 >

A14: FUTURE VALUE PAYMENTS? (Y/N):

E14: N

GOTO AA1.

Enter the following formulae:

AC8: =IF( + E14 = "Y",(( + ab4 1 )/(l/100) *PMT),0)

AC10: =IF(+E14="N",((1 ab5)/l/100)*PMT),0)

n = 5 I = 8 PMT = 1000 N FV = O PV = O

Will show:

FUTURE VALUE OF PMT: 3993

LESSEE'S I NTEREST ( LEEI)

A lessee has an interest in property if he/she enjoys a "profit rental" that is, an economic interest. If the profit rental is 0 or negative then he/she will not have an interest in the property.

PROFIT RENTAL (PR): Profit rental is the difference between the rent being paid by the lessee and the market rent. Therefore, we need to input market rent to find the PR, if any.

*Create a new row at A14

A13: MARKET RENT:

E13: 1500

Name E13: "MR"

GOTO AA1

AA14: PROFIT RENTAL:

AC14: =MR PMT (should show 500)

Name AC14: "PR"

AA15: PV PR:

AC15:=1F( +AC14,((1 AB5)/(l/100)) *PR,0) (should show 1996)

<HOME>

E28: =IF( +AC15, +AC15,"")

The main sheet will show 2 answers:

PRESENT VALUE PMT: 3993

LESSEE'S INTEREST: 1996

LESSOR'S INTEREST (LORI)

We now have the lessee's interest. The lessor's interest consists of 2 parts:

LORI = PVPMT + RV

Where:

PVPMT = PV of the current rent

RV = reversionary value

We already have the PVPMT = 3993.

REVERSIONARY VALUE (RV)

The RV is the PV of the "unencumbered market value of the property" (UMV). That is the PV of the market rent capitalized into perpetuity due at the end of the lease (5 years).

UMV = MR * 100/RCR

Where:

UMV = unencumbered market value

MR = market rent

RCR = reversionary capitalization rate

Insert 2 new rows at A12.

A12: REVERS CAP RATE %:

E12: 10

Name E12:"RCR"

GOTO AA1

AA21: UNENCUMB MV:

AC2 1: =MR * 1 00/RCR (should show 15000).

However, the investor will not receive that amount until the it falls due at the end of the lease in 5 years time:

AA22: REVERSIONARY VALUE: (may have to widen column AA here)

AC22: =AC21 *AB5

Name AC22: "RV" (should show 10209)

<Home>

A31 :=IF( + E1 5,"LESSEE'S INTEREST:","")

E31: =IF( +AC17, + PVPMT + RV,"") (should show 14201).

Save spreadsheet as LESSI.WK1.

PART 8

PARTIAL INTERESTS I N REAL ESTATE

MORTGAGEE AND MORTGAGOR' INTERESTS

The compound function spreadsheet already constructed can be used to determine mortgagee and mortgagor interests in real estate.

MORTGAGEE'S INTEREST (MORI)

DETERMINING THE REPAYMENTS

Retrieve the previous spreadsheet used for lessee's and lessor’s interests LESSI.WK1.

A1: MORTGAGEE AND MORTGAGOR INTERESTS

Suppose the mortgage terms are: 100 000 borrowed over 15 years @ 9% per annum. There is 12 years to run on the mortgage. That is, this is year 3.

Enter: N = 15 I = 9

A12: AMOUNT BORROWED:

A14: NUMBER OF PERIODS REMAINING:

E12: 100000 (name "P" for principal)

E14:12 (name "PR" for period remaining)

GOTO AA1

AA10: PVPMT FACTOR 15 YEARS:

AE10: =(1 PV)/(I/100) (should show 8.061)

AA11: MORTGAGE REPAYMENTS:

AE11: =P/+AE10 (should show repayments of 12 405 pa).

Save as MORTG.XLS

Now determine the PV of the outstanding repayments:

AA12: PVPMT FACTOR 12 YEARS:

AE12: =(1 (1/BASE^PR))/(I/100)

Name AE12: "PVPMT,12" (should show 7.1607)

Check by making sure it is less than PVPMT FACTOR 15 YEARS.

<Home>

Erase rows 15 30.

A16: MORTGAGEE'S INTEREST:

E16: =AE11*PVPMT12 (should show 88 835).

A20: MARKET VALUE:

E20: 150000

Name E20: "MV"

Market value can be calculated from the previous spreadsheet LESSI that is, lessor's interest.

A22: MORTGAGOR'S INTEREST:

E22: =E20 E16

PART 9

ANALYZING COMPLEX RENTAL SCHEDULES

As pointed out previously, an ideal application of a DCF is in the analysis of a complex rent schedule. These are the kind commonly found for CBD commercial buildings. Complexity arises through:

1. Different leases having different rent review dates, starting dates and lengths

2. Different calculations for recoveries for example, “increase in statutory outgoings” compared with “all outgoings”.

3. Different bases for the calculation of rent reviews for example, “market rents” compared with “overage” (%) rents based on gross income (usually for the retail component).

4. The use of different incentives that are supposedly, “secret”.

EXAMPLES OF DIFFERENT LEASE TYPES

The following are 3 different leases in a building. The valuer’s job is to determine the Annual Rental Equivalent” (ARE) from a rent schedule made up of the 3 leases:

1. New lease. Anchor retail tenant for a term of 25 years

Rents payable: A starting rent of 200 000 per annum then 2% of gross turnover whichever is the higher.

Recoveries: A pro rata contribution on ALL outgoings starting at 25 000 per annum.

2. New lease. A government department for a term of 15 years

Rents payable: Starting rent of 20 000 pa then to market at 2 yearly review periods.

Recoveries: A pro rata contribution on increases in statutory outgoings starting at 5 000 per annum.

Incentives: Rent free period for the first 2 years.

Free fitout worth 50 000.

3. A private company for a term of 10 years and with 8 years to run.

Rents payable: Starting rent of 12 000 per annum then to market every 3 years.

Recoveries: Pro rata increases in ALL outgoings from base year

Current recoveries are 3 000 pa

Incentives: Rent free period for the first 2 years.

Free fitout worth 150 000.

Determine the annual rental equivalent for the building.

TASK 1: DETERMINE THE L ENGTH OF THE DCF

TASK 2: INPUT THE EXPECTED CASH FLOW FOR TENANT 1:

The rent will not fall below 200 000 because of the “ratchet” clause. Therefore, we know that it will be at least 200 000 for the 5 year period. Expectations of an increase is a function of:

* FUTURE COMPETITION.

Are there any DAs with local and surrounding councils for competing retail space?

* EXPECTED DEMOGRAPHICS OF THE RETAIL CATCHMENT AREA.

* EFFECT OF A NEW STORE.

TASK 3: INPUT THE EXPECTED CASH FLOW FOR TENANT 2

Historical comparable rents on large area lettings are analyzed to obtain an expected annual increase of about 5% per annum.

TASK 4: Input the expected cash flow for Tenant 3

CREATING MAIN WINDOW AT A1

A1: ANNUAL RENTAL EQUVALENT FOR A COMPLEX RENT SCHEDULE

Widen column A to 20 characters

Save as ARECRS1.XLS

A3: Annual discount rate % pa:

A4: As a decimal:

A5:base:

C3: 8

C4:=C3/100 shows 0.08

C5:=1+C4 shows 1.08

E3:PV(1):

F3:=1/C5^5

E4:(space)1-PV(1):

F4:=1-F3

G3:PV.PMT:

H3:=F4/C4

A6:MARKET RENT ESCALATION %:

C6:Tenant 1:

D6:5

D7:8

F6:5

F7:10

H6:8

H7:8

E6:Tenant 2:

G6:Tenant 3:

A7:RECOVERIES ESCALATION %:

C7:Tenant 1:

E7:Tenant 2:

G7:Tenant 3

C9:YEAR

D9:(space)$’000

B10:F10:enter 0 to 4

A11:TENANT 1

A12: Rent:

A13: Recoveries:

A14: Totals:

Align A12:A14 to the right

A15: TENANT 2

A16: Rent:

A17: Recoveries:

A18: Fitout:

A19: Totals:

Align A16:A19 to the right

A20: TENANT 3

A21: Rent:

A22: Recoveries:

A23: Totals:

Align A21:A23 to the right

A24: GRAND TOTALS:

A26:Discount factors:

A28: TENANT 1:

A29: NPV:

A30: ARE:

Align A29:A30 to the right

C28: TENANT 2:

C29: NPV:

C30: ARE

Align C29:C30 to the right

E29:TENANT 3:

E30:NPV:

E31:ARE:

A32: GRAND NPV:

A33: GRAND ARE:

APPLYING NAMES TO RELEVANT CELLS

Highlight D6

Version 5: <Insert><Name><Define> In Names in Workbook box type: T1MRE <OK>

(stands for Tenant 1 Recoveries Escalation)

In the same way name the following cells:

F6:T2MRE (Tenant 2 Market Rent Escalation)

H6:T3MRE

D7:T1RE

F7:T2RE

H7:T3RE

CREATE AA1 WINDOW

GOTO AA1 Scroll to right so that AA1 is in the top lefthand corner

AA1:EXPECTED MARKET RENTS AND RECOVERIES

AA3:TENANT1

AA4:Market rent escalation:

AD4:=T1MRE should show 5

AA5:Recoveries escalation:

AD5:=T1RE should show 8

COPY AA3:AD5 to AA7 and AA11 this is the quick way to set up the data for Tenants 2 and 3.

EDIT the appropriate cells to correspond to the necessary data for Tenants 2 and 3:

Reduce width of columns AB,AC,AD to 6 characters each.

AE2:AJ2: 0..to...5

AE4:200

AE5:25

AE8:20

AE9:5

AE12:12

AE13:3

AF4:=(1+($AD$4/100))^AF2*$AE$4 the brackets are important

AF5:=(1+($AD$5/100))^AF2*$AE$5

AF8:=(1+($AD$8/100))^AF2*$AE$8

AF9:=(1+($AD$9/100))^AF2*$AE$9

AF12:=(1+($AD$12/100))^AF2*$AE$12

AF13:=(1+($AD$13/100))^AF2*$AE$13

COPY AF4 to AG4:AJ4

COPY AF5 to AG5:AJ5

COPY AF8 to AG8:AJ8

COPY AF9 to AG9:AJ9

COPY AF12 to AG12:AJ12

COPY AF13 to AG13:AJ13

NAMING CELLS

Enter the following cell names as follows:

AE3:AJ3: T1MR0....to... T1MR5

AE6:AJ6: T1R0...to.....T1R5

Highlight AE3:AJ4

Version 5:<Insert><Name><Create> Create Names in x Top Row <OK>

Highlight AE5:AJ6

Version 5:<Insert><Name><Create> Create Names in x Bottom Row <OK>

(Delete x for left hand column)

Use the same procedure to name the cells for Tenants 2 and 3.

BACK TO A1 WINDOW

B12:=T1MR0 shows 200

C12:F12:=T1MR1...to...T1MR4

B13:=T1R0 shows 25

C13:F13:=T1R1....to....T1R4

B14:=SUM(B12:B13)

COPY B14 to C14:F14

Carry out the same procedure for Tenants 2 and 3.

Remember to include rent free periods and fitouts if applicable.

Fitouts are entered as a negative number as it is a cost born by the landlord.

B24:=SUM(B14,B19,B23)

COPY B24 to C24:F24

Change B10:F10 from 0..to...4 to 1...to...5. This is because we now assume that the rents and outgoings are paid in arrears.

B26:=1/$C$5^B10 shows 0.9259

COPY B26 to C26:F26

B29:=SUM(B26*B14,C26*C14,D26*D14,E26*E14,F26*F14) shows 991.64

B30:=B29/H3

Repeat the above procedure for Tenants 2 and 3.

B32:=SUM(B26*B24,C26*C24,D26*D24,E26*E24,F26*F24) shows 1065.49

B33:=B32/H3

UNDERSTANDING YOUR SUCCESS !

The results of the Annual Rental Equivalents can be interpreted as follows:

NPVs: The NPVs for each tenant are the present values for the DCF over the 5 year period. Obviously each should be substantial and certainly not a negative value.

MORE ON PRESENTATION

The DCF can be shown in the final report without the gridlines as follows:

<File><Page Setup..><Sheet> delete x against Gridlines <OK>

PART 10

FINE TUNING

At the end of Part 5 you were asked how can you improve the cash flow to take into account 2 typical features of rental income:

* Rent are paid at intervals less than a year

* Rents are paid in advance (annuities due).

ADJUSTING THE CASH FLOW TO QUARTERLY PAYMENTS

Open file ARECRE1.XLS

GOTO window AA1

AA16: CONVERSION TO QUARTERLY PAYMENTS

AA17: Payment period (1=year 2=half year 4=quarterly):

AG17:4

AA18:Interest rate per annum:

AG18:10

AA19:Interest (decimal) per period:

AG19:=AG18/100/4 shows 0.025

AH17:base:

AI17:=1+AG19 shows 1.025

AH18:FV(1):

AI18:=AI17^AG17 shows 0.90595

AH19:FVPMT:

AI19:=(AI18-1)/AG19 shows 4.1522

Using <F2> (edit) change the following cells formulae as follows:

AE4:=200/AG17

AE5:=25/AG17

AE8:=20/AG17

AE9:5/AG17

AE12:12/AG17

AE13:3/AG17

GOTO Window A1

GRAND NPV has increased to 1129.85

GRAND ARE has increased to 282.98

This shows the difference that quarterly payments make to the value of the cash flow.

Save as ARECRS2.XLS

QUESTION

What difference would monthly payments make to the GRAND NPV and ARE?

How can payments in advance be incorporated into the cash flow?

The advantage to the investor is that he/she can invest the income received for that period and therefore, the advantage is interest earned at bank rates for the period of payment.

FINE TUNING

AE4:=200/AG17*AI19*AI17

Add *AI17 to the formulae in cells AE8 and AE12

The new values are:

GRAND NPV:1155.27

GRAND ARE:289.34

Save.

USING EXCEL BOOK

Any cell on page 1 can refer to any cell or range on the rear sheets using cell or range names.

EXAMPLE:

GOTO Sheet 2 by clicking on the tab at the bottom of the spreadsheet.

ON SHEET 2

A1:50

Name A1 TESTCELL as follows

<Insert><Name><Define>

In Names in Workbook box enter: TESTCELL <OK>

GOTO Sheet 1

A37:=TESTCELL shows 50

In this manner any of the 5 rear pages can be used to support the cash flow on Sheet 1.

EXERCISE

Using <Edit><Cut><Paste> transfer all the working data in Window AA1 to Sheet 2.

Save as ARECRS3.XLS

GRAPHING THE CASH FLOW

Valuation reports can be improved by importing graphs into the report. Excel has a very powerful graphing tool called the graph wizard. It is shown as the button that looks like this:

Highlight A12:F14

Click onto the CHART WIZARD

Click on the vacant part of the spreadsheet. Shows ChartWizard window Step 1 of 5.

Check that the range shown is: =$A$12:$F$14 <Next>>

In Window Step 2 of 5 click onto Line chart box <Next>>

In Window Step 3 of 5 click onto format 4 <Next>>

Step 4 of 5 shows the small graph if OK <Next>>

Step 5 of 5 Add a Legend? ¤ YES

Axis Titles

Category [X]: YEAR

Category [Y]:$’000 <Finish>

Chart shown in vacant area of spreadsheet.

Save as ARECRS4.XLS.

To print into a report:

Highlight graph by clicking onto the graph to highlight it.

This is shown by 6 black squares on the boundaries.

<Edit><Copy>

This puts the graph onto the CLIPBOARD to be used elsewhere.

Click onto the top lefthand corner symbol. <Minimize>

Open Word 6 to represent the report into which you intend to insert the graph.

IN WORD

<Exit><Paste Special...>

Highlight Picture <OK>

Shows the chart as below. This can now be edited with the normal DOC commands and Highlighting the graph <Format><Picture>

For example:

Shows the chart as below. This can now be edited with the normal DOC commands and Highlighting the graph <Format><Picture>

For example: