Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Finance questions

Question

Evaluate 3x - x for x = -2 Answer:

Answered: 1 week ago

Question

What is group replacement? Explain with an example. (2-3 lines)

Answered: 1 week ago