Real Estate Investment Analysis

DCF assignment 2

Rent Rolls Analysis and Potential Gross Income Forecast

A class-A office building known as the Boca Lake is currently available for sale. You have been hired by a client to perform an investment analysis using the DCF approach. As the first step, you need to reasonably accurately forecast the rental revenue potential of the building in the planned 10-year holding period. The seller has provided to you all the lease documents and, after examining all leases, you summarize the information in the following table known as the "Rent Rolls". This information is vital to estimate the near-term rental revenue of the building, and provide a base for forecast beyond the lease expiration. Your objective for this step is to develop a forecast of the Potential Gross Income of this building for the next 10 years. To do that, assume tenants will pay their leasing rate (which escalates at their individual rate) until the year their lease expires.After that, assume the suite will command the market rent as forecasted below.

Current Rent Rolls

Suite #

Sq. Feet

Rent - Current

Escalation

Years to Expiration

1

5,450

$14.10

4.0%

4

2

3,436

15.00

3.0%

3

3

2,695

15.98

3.0%

4

4

2,185

16.00

3.0%

4

5

1,925

15.00

3.0%

2

6

12,759

15.00

3.0%

3

7

10,036

15.00

0.0%

3

8

6,197

vacant

0.0%

0

9

2,964

16.00

0.0%

3

10

3,253

vacant

0.0%

0

In addition, you have obtained a market rent forecast for comparable building in the Boca area from a reputable source.

Year

1

2

3

4

5

6

7

8

9

10

11

$/SF

16.00

17.00

18.00

18.50

19.00

19.00

20.00

22.00

24.00

22.00

24.00

To continue your forecast, you also make the following assumptions:

1. Other income is 1% of Potential Gross Income for all years.

2.Current year operating expense:

Operating Expenses:

Current year

Expected growth

Management fee (% of EGI)

5.00%

NA

Property insurance

42,000

2.50%

Property tax

66,000

2%

Maintenance

57,000

3.00%

Utilities

46,000

3.00%

Workers compensations

105,000

3.00%

Security service

24,000

3.00%

3.Purchase price:

Land: 1,200,000

Building:3,800,000

Total:5,000,000

4.Financing:

Prevailing interest rate on this type of loan is 6.75% for 25 years (monthly payment). The lender requires minimum debt coverage ratio of 1.2 based on first year NOI, that is Max annual debt service = NOI₁ /1.2. Compute the max loan amount based on max annual debt service and round down to the nearest $100,000. Assume this is the amount of borrowing.

1. Other information:

Income tax rate:40.0%

Capital gain tax rate:15.0%

Depr recap tax rate:25.0%

Depreciable life: 39 years

Going-out cap rate:10.0%

Selling expense:11.0%

Required rate of return:12.0%

Requirements:

1.Build a complete DCF model for 11 years of holding period.

2.Produce an after-tax cash flow summary.

3.Compute NPV and IRR for the ATCF.

4.Conduct sensitivity analysis of IRR on these variables: Income tax rate, going-out cap rate, and Required rate of return.

As a general rule for a DCF model, all inputs should go into the assumption section, and the working section should contain only formula.

******I have all the answers. What i am looking for is how to do #4. The answer is $3,700,000 for the loan amount but i do not know how they got that. NOI for the first year is $408,103. Im stuckkkkk*******