How to calculate the debt service coverage ratio (DSCR) in Excel

Step 1: NOI for each property

One row per property: monthly rent, a vacancy %, monthly operating expenses and the loan amount. NOI is yearly rent after vacancy minus yearly operating expenses, the same NOI the cap rate guide uses: =B2*12*(1-C2)-D2*12.

Operating expenses are the running costs: property tax, insurance, repairs, management and HOA fees. The loan payment is not one of them. It goes on the other side of the ratio.

For Oak St: $2,900 x 12 = $34,800 of rent, 5% vacancy leaves $33,060, less $11,760 of expenses is $21,300 of NOI. The vacancy and expense numbers are your estimates, so keep them in cells you can change.

Step 2: Yearly debt service with PMT

Debt service is every loan payment in the year, principal and interest together. PMT works out the monthly payment from the rate, the number of payments and the loan amount. Put the rate and term in cells (L2 and L3) and lock them with $ so the formula copies down.

PMT returns a negative number because the payment is money going out, so put a minus in front. =-PMT($L$2/12,$L$3*12,E2) is $1,264 a month for Oak St's $200,000 loan at 6.5% over 30 years. Multiply by 12 for the year: $15,170.

Divide the rate by 12 and multiply the years by 12. Using the yearly rate with monthly payments is the most common PMT mistake, and the result is wildly too high.

Step 3: DSCR for every property

Divide NOI by debt service: =F2/G2. Oak St comes out at 1.40, which means NOI covers the payments 1.4 times, with about $6,130 a year left over. Elm Ave is 0.95: its NOI is about $780 a year short of the payments, so the owner tops it up from somewhere else.

Lenders set their own minimum DSCR, and it varies by lender and loan type. Ask yours and put it in L4 (1.25 here is a sample, not a recommendation). =IF(H2>=$L$4,"OK","Below minimum") flags each row: Oak St, Pine Rd and Birch Ct pass; Elm Ave and Cedar Ln (1.20) don't. =COUNTIF(I2:I6,"OK") counts 3.

Keep both sides yearly. Monthly NOI divided by yearly debt service gives 0.12 for Oak St, a number that looks alarming and means nothing.

The largest loan a minimum DSCR allows

Turn the ratio around: the highest yearly payment that still meets the minimum is NOI / minimum DSCR. For Oak St that's $21,300 / 1.25 = $17,040 a year, or $1,420 a month.

PV turns that payment back into a loan amount at your rate and term: =PV($L$2/12,$L$3*12,-F2/$L$4/12). Oak St supports about $224,700, more than the $200,000 it has. Elm Ave supports only $161,400, about $50,600 less than its loan, and Cedar Ln supports $365,000, about $15,000 less.

This is arithmetic on your estimates, not what a lender will offer. Lenders also look at your credit, the property and their own NOI figures, which are often more cautious than yours.

What a higher rate does

The same NOI covers less loan when rates rise. At 7.5% instead of 6.5%, Oak St's yearly payments go from $15,170 to $16,781 and its DSCR drops from 1.40 to 1.27. Change L2 and the whole table updates.

For an interest-only loan, yearly debt service is just loan x rate: $13,000 for Oak St, and a DSCR of 1.64. Make sure the payment in the sheet is the one you actually make. Leaving vacancy out flatters it too: Oak St at 0% vacancy shows 1.52.