WJM7 EXCEL SPREAD CALCULATORS

 DSCR: Debt Service Coverage Ratio (1.25 most common)

 NOI divided by TAP (DS) = DSCR

Lenders typically require minimum debt service coverage requirements (ability to pay) as part of their loan underwriting guidelines. The typical minimum ratios for income property are 1.10: to 1.25:1%. This is not complicated as it first appears.

For instance, a 1.00:1% debt service coverage simply means that the property has one dollar of net operating income for every dollar of debt payment.

A 1.10:1% ratio means that the borrower has $1.10 of net operating income for very dollar of debt payment. A 1.15:% ratio means the borrower has $1.15 of net operating income for every dollar of debt payment and so on.

The lower the DSCR, the higher the risk. This sounds contrary to reason using the same words in residential lending, but the math is different.

GPI:  Gross Potential Income

          All Positve Income x’s 12

EGI: Effective Gross Income

          GPI – 5% = EGI

NOI: Net Operating Income

          EGI – operating expenses – management fees (5% of EGI) – replacement reserves (per unit, sq ft or pad)

OER: Operating Expense Ratio

17 apartments (200 an apartment)

7 of them are 2 BR 2 BA that rent for $985 Mo (7 x’s 985 = 6895)

8 of them are 1 BR 1 BA that rent for $710 Mo (8 x’s 710 = 5680)

2 of them are 3 BR 2 BA that rent for $1300 Mo (2 x’s 1300 = 2600)

Vending machines on site that generate $58 Mo/cash (58.00)

Laundry facilities (6 washers, 6 dryers) on site that generate $325 per Mo/cash (325)

Expenses:

Taxes: $2900

Electricity: $1152

Trash Removal: $1320

Grounds keeping: $1770

Maintenance: $1440

Insurance: $5172

Mortgage: $97,500

Office Manager: Free rent in a 1 BR, 1 BA apartment

Advertising: $930

Phone: $456

Office Supplies: $1893

Car Lease for office manager: $4800

New Mortgage – 25-year term, 6.875 start rate, 5-year call, $1.3M Loan Amount

------------------------------------------------------------------------------------------------------------------------------

Positve Income:

$6895.00 (apartments)

$5680.00 (apartments)

$2600.00 (apartments)

$325.00 (washer/dryer)

$58.00 (Vending Machines)

=_______________________

$15,558.00 x’s 12

GPI - $186,696.00

$186,696.00 – 5% ($9,334.48)

EGI - $177,361.00

Operating Expenses:

$2900.00 (taxes)

$1152.00 (electricity)

$1320.00 (trash removal)

$1770.00 (grounds keeping)

$1440.00 (maintenance)

$5172.0   (insurance)

$456.00   (phone)

$1893.00 (office supplies)

=________________

$16,103.00 (total operating expenses)

________________

 $8868.08 (management fees/5% of EGI)

__________________

$3400.00 (replacement reserves/17 apartments x’s 200)

__________________

$177,361.00(EGI) minus $16,103.00 minus $8868.08 minus $3400.00

___________________________________________________

NOI - $148,990.00

___________________________________________________

Qualifier calculator:

http://www.calculated.com/

Hit shift, X which will clear the calculator.

Input $1,300,000 and press loan amount.

Input 6.875% and press int

Input 25 and press term

Then press pmt:  =  $9,084.73/month P&I

Multiply by 12  = TAP/DS  =  $109,016.72.

For DSCR coverage of 1.25%, the net operating income would need to be $136,270.91 (1.25 X $109,016.72)

_______________________________________________

$148,990.00 (NOI) divided by $109,016.72=

DSCR = 1.36