How do you find and calculate the problems in Microsoft Excel?

You are considering the purchase of an apartment complex. The following assumptions are

made:

The purchase price is $1,000,000.

Potential gross income (PGI) for the first year of operations is projected to be $171,000.

PGI is expected to increase at 4 percent per year.

No vacancies are expected.

Operating expenses are estimated at 35 percent of effective gross income. Ignore capital expenditures.

The market value of the investment is expected to increase 4 percent per year.

Selling expenses will be 4 percent.

The holding period is 4 years.

The appropriate unlevered rate of return to discount projected NOIs and the projected NSP is 12 percent.

The required levered rate of return is 14 percent.

70 percent of the acquisition price can be borrowed with a 30-year, monthly payment mortgage.

The annual interest rate on the mortgage will be 8.0 percent.

Financing costs will equal 2 percent of the loan amount.

There are no prepayment penalties.

f. Calculate the loan balance at the end of years 1, 2, 3, and 4.

Mort Bal (1)= $694,152 Mort Bal (2)= $687,820

Mort Bal (3)= $680,961

Mort Bal (4)= $673,533

g. Calculate the amount of principal reduction achieved during each of the four years.

Prin Reduction(1)= $5,848 Prin Reduction(2)= $6,333 Prin Reduction(3)= $6,859 Prin Reduction(4)= $7,428

h. Calculate the total interest paid during each of the four years.

Interest Paid(1)= $55,789 Interest Paid(2)= $55,303 Interest Paid(3)= $54,778

Interest Paid(4)= $54,208

i. Calculate the levered required initial equity investment.

Levered Equity Investment= $314,000

j. Calculate the before-tax cash flow (BTCF) for each of the four years.

BTCF(1)= $49,514 BTCF(2)= $53,960 BTCF(3)= $58,584 BTCF(4)= $63,392

k. Calculate the before-tax equity reversion (BTER) from the sale of the property.

BTER= $449,531

l. Calculate the levered net present value of this investment. Should you purchase? Why?

NPV= $114,187.51 Yes, purchase as NPV is positive

m. Calculate the levered internal rate of return of this investment (assuming no debt and no taxes). Should you purchase? Why?

Levered IRR= 25.03%

Yes, purchase as IRR exceed 14%

n. Calculate, for the first year of operations, the: (1) overall (cap) rate of return, (2) equity dividend rate, (3) gross income multiplier, (4) debt coverage ratio.

Cap Rate= 11.12%

Equity Dividend Rate= 15.77%

GIM= 5.8480

DCR= 1.8033