Question

Solve using excel:

A. Suppose you are considering the purchase of an apartment building that has 12 units that can be rented out at $1,050 per month. You have estimated operating expenses and expected vacancy and collection losses for the first year to be $35,700 and $30,240, respectively. You also have estimated that you will be able to generate an additional $3,840 in the first year from garage rentals on the property. If the expected purchase price of the property is $1,100,000 and you are planning on making a 10% down payment, calculate the debt yield ratio.

B. Assume you have taken out a balloon mortgage loan for $2,500,000 to finance the purchase of a commercial property. The loan has a term of 5 years, but amortizes over 25 years. Calculate the balloon payment at maturity (Year 5) if the interest rate on this loan is 4.5%.

Answer 1

a. Debt yield ratio = Net operating income / Debt

= 89100/ 990,000

= 9%

Workings:

Net operating income = Rental income + Garage rentals - Operating expenses - expected vacancy and collection losses

= (12* 1050) + 3840 - 35700 - 30240= 89100

Debt = 1,100,000 *(1-10%) = 990,000

b. Balloon payment = $2,196,447.59

Using financial calculator or excel:

Step1:

NPER	300	[25years*12]
FV	0
PV	2500000
Rate	0.38%	[4.5%/12]
PMT	13895.81	[-pmt(rate, nper, pv,fv)]

Step 2:

NPER	240.00	[ 300- 60]
FV	0
PMT	13895.8
Rate	0.38%	[4.5%/12]
PV	$2,196,447.59	[-pv(rate,nper,pmt,fv,0)