** PLEASE SHOW HOW TO COMPLETE IN EXCEL. I have included the correct answers as I keep being given the wrong answers :(

You are an employee of University Consultants, Limited, and have been given the following assignment. You are to present an investment analysis of a small retail income-producing property for sale to a potential investor. The asking price for the property is $1,250,000; rents are estimated at $160,000 during the first year and are expected to grow at 2.5 percent per year thereafter. Vacancies and collection losses are expected to be 10 percent of rents. Operating expenses will be 35 percent of effective gross income. A fully amortizing 70 percent loan can be obtained at 8 percent interest for 30 years (total annual payments will be monthly payments 12). The property is expected to appreciate in value at 3 percent per year and is expected to be owned for five years and then sold.

Required:

a. What is the first-year debt coverage ratio?

b. What is the terminal capitalization rate?

c. What is the investors expected before-tax internal rate of return on equity invested (BTIRR)?

d. What is the NPV using a 14 percent discount rate?

e. What is the profitability index using a 14 percent discount rate?

Explanation

ASSUMPTIONS:

Asking Price	$ 1,250,000
Rent year 1	$ 160,000
Growth-Rent	2.50%
Vacancy & Collection Loss	10.00%	of rents
Expenses	35.00%	of EGI
Loan-to-Value	70.00%
Loan Interest	8.00%
Loan term	30	years
Appreciation rate	3.00%
Holding Period	5	years
Selling costs	0.00%	of sale price
Equity discount rate	14.00%
Reinvestment rate	6.00%

Equity	375,000
Loan	875,000
Annual Loan Payment	77,045
Mortgage Balance	831,861	year 5

Year	1	2	3	4	5	6
PGI	160,000	164,000	168,100	172,303	176,610	181,025
Vacancy & Collection Loss	16,000	16,400	16,810	17,230	17,661	18,103
EGI	144,000	147,600	151,290	155,072	158,949	162,923
Expenses	50,400	51,660	52,952	54,275	55,632	57,023
NOI	93,600	95,940	98,339	100,797	103,317	105,900
Debt Service	77,045	77,045	77,045	77,045	77,045	77,045
BTCF	16,555	18,895	21,293	23,752	26,272	28,855

Cash flow from sale in year 5
Sales Price	1,449,093
Sales costs	0
Mortgage Balance	831,861
Before-tax cash flow	617,231

a.

FIRST YEAR DEBT COVERAGE RATIO (DCR)

Year	1	2	3	4	5
Debt-Coverage Ratio	1.21	1.25	1.28	1.31	1.34

b.

TERMINAL CAPITALIZATION RATE

NOI Year 6	105,900
Resale Price	1,449,093
Terminal Capitalization Rate	7.31%

c.

BTIRR on Equity

Year	0	1	2	3	4	5
BTCF	(375,000)	16,555	18,895	21,293	23,752	643,503
BTIRR on Equity	15.08%

d.

NET PRESENT VALUE

NPV - Equity 16,711 @ 14.00%

This means that the investor can invest $16,711 more in the property and still earn a 14% IRR.

e.

PROFITABILITY INDEX

Present Value BTCF	391,711
Initial Equity Investment	375,000
Profitability Index:	1.04	@ 14.00%

This means that the investment is profitable in the sense that the investor could invest about 4% more in the property and still earn a 14% IRR.