Formula
Effective gross income (EGI) = (Gross scheduled annual rent + Other income) × (1 − Vacancy and credit loss ÷ 100)
Net operating income (NOI) = EGI − Operating expenses − Replacement reserves
Annual debt service = 12 × Proposed loan × r ÷ (1 − (1 + r)^−N), where r = Interest rate ÷ 12 and N = Amortization (years) × 12
DSCR = NOI ÷ Annual debt service; Maximum loan at target DSCR = present value, at r over N, of a monthly payment of (NOI ÷ Target DSCR) ÷ 12
Expense ratio = (Operating expenses + Replacement reserves) ÷ EGI; the sensitivity rows recompute DSCR with the rate +1 point, vacancy +5 points and expenses ×1.1
Worked examples
Example 1: $328,000 gross income, 7 percent vacancy, $1.9 million loan at 7 percent
Use the defaults: Gross scheduled annual rent $320,000, Other income $8,000, Vacancy 7 percent, Operating expenses $118,000, Replacement reserves $6,000, Proposed loan $1,900,000, Interest rate 7 percent, Amortization 25 years, Target DSCR 1.25. Gross potential income = $320,000 + $8,000 = $328,000. EGI = $328,000 × 0.93 = $305,040. NOI = $305,040 − $118,000 − $6,000 = $181,040. Monthly rate r = 0.07 ÷ 12 = 0.0058333; N = 300. Monthly payment = $1,900,000 × 0.0058333 ÷ (1 − 1.0058333^−300) = $13,428.80; Annual debt service = $161,146. DSCR = $181,040 ÷ $161,146 = 1.12, below the 1.25 target, so the result shows a warning. Maximum loan at 1.25: allowed debt service = $181,040 ÷ 1.25 = $144,832 a year = $12,069.33 a month; the loan that payment supports at 7 percent over 300 months = $1,707,653. Expense ratio = ($118,000 + $6,000) ÷ $305,040 = 41 percent. Sensitivity: at 8 percent the payment rises to about $14,664 and DSCR falls to 1.03; at 12 percent vacancy EGI falls to $288,640, NOI to $164,640 and DSCR to 1.02; with expenses at $129,800 NOI falls to $169,240 and DSCR to 1.05. The lender will offer about $1.7 million, not $1.9 million.
Example 2: a small building where the proposed loan is far too large
Enter Gross scheduled annual rent $150,000, Other income $0, Vacancy 10 percent, Operating expenses $62,000, Replacement reserves $3,000, Proposed loan $900,000, Interest rate 7.5 percent, Amortization 25 years, Target DSCR 1.25. EGI = $150,000 × 0.90 = $135,000. NOI = $135,000 − $62,000 − $3,000 = $70,000. r = 0.075 ÷ 12 = 0.00625; monthly payment on $900,000 = $6,650.90; Annual debt service = $79,811. DSCR = $70,000 ÷ $79,811 = 0.88: the property does not cover the debt at all. Maximum loan at 1.25 = present value of ($70,000 ÷ 1.25) ÷ 12 = $4,666.67 a month over 300 months at 0.625 percent = $631,492. Expense ratio = $65,000 ÷ $135,000 = 48 percent. Sensitivity drops to 0.80, 0.78 and 0.80. The buyer asked for $900,000 against NOI that supports about $630,000. Unless the price falls or the equity rises by roughly $270,000, there is no conventional loan here; the number to lead with in the conversation is the maximum loan, not the DSCR.
Example 3: a larger property on a 30-year amortization with a 1.30 target
Enter Gross scheduled annual rent $600,000, Other income $20,000, Vacancy 5 percent, Operating expenses $230,000, Replacement reserves $12,000, Proposed loan $3,500,000, Interest rate 6.75 percent, Amortization 30 years, Target DSCR 1.30. Gross potential = $620,000; EGI = $620,000 × 0.95 = $589,000; NOI = $589,000 − $230,000 − $12,000 = $347,000. r = 0.0675 ÷ 12 = 0.005625; N = 360; monthly payment = $22,700.90; Annual debt service = $272,411. DSCR = $347,000 ÷ $272,411 = 1.27, just below the 1.30 target. Maximum loan at 1.30 = present value of ($347,000 ÷ 1.30) ÷ 12 = $22,243.59 a month over 360 months = $3,429,487, about $70,000 below the request. Expense ratio = $242,000 ÷ $589,000 = 41 percent. Sensitivity: 1.15 at 7.75 percent, 1.16 at 10 percent vacancy, 1.19 with expenses up ten percent. This is a close call that a lender resolves by trimming the loan to $3.43 million or by accepting 1.27 against a 1.25 policy minimum if the 1.30 was a target rather than a floor.