Question

4) A property is expected to generate $500,000 of net operating income over the next 12 months. Discussion with lenders leads to the conclusion that the minimum acceptable debt coverage ratio will be 1.20 and that loan terms will be 4.5 percent per annum, with 20-year amortization (monthly payments). a. What is the maximum supportable annual debt service? b. What size loan does this imply?

Answer 1

(a): Debt coverage ratio = Net operating income/Principal + interest

First let us compute the principal payments over the next 12 months. This can be computed using the PMT function. 20 years = 20*12 = 240 months and so monthly payments will be: PMT (4.5%/12, 240, 500000) = $3,163.25. This includes both principal as well as interest.

So total debt related payments over next 12 months = 12*$3,163.25 = $37,958.96

Now Debt coverage ratio = Net operating income/Principal + interest

Let the maximum supportable annual debt service be “x”

Thus 1.20 >= 500,000/x

Or x>= 500,000/1.20

Or x >= 416,666.67

(b): Now 416,666.67 is the payment over next 12 months. Thus monthly payment or PMT = 416,666.67/12 = $34,722.22

Thus loan size will be the PV. Using the PV function we get:

PV (4.5%/12, 240, -34722.22)

= $5,488,383.22