The debt service coverage ratio decides whether a commercial loan gets made and how big it is. A lender takes the property's net operating income, divides it by what the loan will cost to service in a year, and compares the answer to a threshold. Above the threshold, you have a deal. Below it, the loan shrinks until the math works, or it does not happen.
The formula is one line. Getting the inputs right is where the work is, because the operating statement those inputs come from arrives as a PDF, and because a lender will not use your numbers exactly as you typed them. This walks through building DSCR in Excel from a trailing twelve month statement, the adjustments underwriters make before they run it, and the reconciliation that explains the gap between your ratio and theirs.
What is the debt service coverage ratio?
The debt service coverage ratio measures how many times a property's operating income covers its annual loan payments. A DSCR of 1.25 means the property throws off $1.25 of net operating income for every $1.00 of principal and interest due that year. At exactly 1.00 the property breaks even and has no cushion. Below 1.00 it does not earn enough to pay its own debt.
It is the single ratio commercial lenders care most about, because it answers the only question that matters to them: will this building pay the loan back out of its own cash flow, without the borrower having to reach into a pocket.
How do you calculate DSCR?
Divide annual net operating income by annual debt service. Net operating income is effective gross income minus operating expenses, excluding debt service, capital expenditures, and depreciation. Annual debt service is the total of twelve months of principal and interest payments on the loan. Both figures cover the same twelve month period.
As one line: DSCR = Net Operating Income / Annual Debt Service.
Two mistakes account for most wrong answers. The first is putting interest only in the denominator and forgetting principal, which flatters the ratio. Debt service means the full payment. The second is leaving something in NOI that does not belong there, usually a mortgage payment or a roof replacement, both of which live below the NOI line. If you are shaky on that boundary, work through how to calculate NOI in Excel from a T12 first, because a wrong NOI produces a confidently wrong DSCR.
What is a good DSCR for a commercial loan?
Most commercial lenders set a minimum around 1.25x, and many agency multifamily programs sit near 1.25x as well. Anything at or below 1.00x means the property cannot cover its own debt. Between 1.00x and 1.20x you are usually looking at a smaller loan, a longer amortization, or a rate that prices in the risk. Above roughly 1.40x, coverage stops being the constraint and something else, typically loan to value or a debt yield test, starts binding instead.
Thresholds move with asset class and cycle. A stabilized apartment building with long leases gets more rope than a single tenant retail box whose lease expires in three years. Ask the lender for its actual test rather than assuming, because the number in the term sheet is the one that counts.
| DSCR | What it means | Typical lender reaction |
|---|---|---|
| Below 1.00x | Operating income does not cover debt service | Decline, or require a much smaller loan |
| 1.00x to 1.19x | Breakeven to thin cushion | Resize the loan, extend amortization, or price up |
| 1.20x to 1.24x | Just under the common threshold | Case by case, often resized |
| 1.25x to 1.40x | The standard underwriting band | Proceeds sized by coverage |
| Above 1.40x | Comfortable coverage | Loan to value or debt yield usually binds first |
How do you calculate DSCR in Excel?
Build it in three blocks so every input is visible and auditable. Put the operating figures in one block, the loan terms in another, and the ratio in a third. Never bury a hardcoded number inside a formula, because in six weeks you will not remember whether the 1,240,000 in the denominator was principal and interest or interest only.
Start with the loan block. If you know the payment, annual debt service is simply the monthly payment times twelve. If you know the terms instead, let Excel derive it:
| Cell | Label | Formula or value |
|---|---|---|
| B2 | Loan amount | 8,000,000 |
| B3 | Annual interest rate | 6.25% |
| B4 | Amortization, years | 30 |
| B5 | Monthly payment | =PMT(B3/12, B4*12, -B2) |
| B6 | Annual debt service | =B5*12 |
| B8 | Net operating income | 720,000 |
| B9 | DSCR | =B8/B6 |
The minus sign in front of B2 inside PMT is not decoration. Excel treats a loan you receive as a positive cash flow, so without it the function returns a negative payment and your DSCR comes back negative. Format B9 with two decimals and a lowercase x if you want it to read the way a term sheet does.
One more thing worth building in: an interest only row. Many loans have an initial interest only period, and coverage during that window looks much stronger than it will once amortization starts. Calculate both, and underwrite to the amortizing number. A deal that only works during interest only is a deal that stops working on a known date.
Does DSCR include principal and interest?
Yes. Annual debt service is the full annual payment, principal and interest together. Using interest alone produces a materially higher ratio, and it is a common error in borrower models. On a thirty year amortizing loan the principal component is small in year one and grows every year, so an interest only calculation understates the true burden and the gap widens over the hold period.
Lenders also look at a global DSCR on smaller loans, which folds in the borrower's other debt obligations rather than looking at the single property in isolation. If you are financing a property inside a portfolio, ask which test applies before you build the model around the wrong one.
Why does my DSCR not match the lender's?
Because the lender does not use your NOI. Underwriters normalize the operating statement before the ratio is calculated, and the adjustments almost always push NOI down. Four show up on nearly every deal.
They add a market management fee, commonly in the 3% to 5% of effective gross income range, even when the owner self manages and reports no fee at all. They subtract a replacement reserve, often a few hundred dollars per unit per year on multifamily, which never appears on a T12 because it is not a cash expense. They mark vacancy to a market minimum rather than accepting an actual figure of 2% in a market that historically runs at 6%. And they strip out anything nonrecurring, such as a one time legal settlement or an insurance reimbursement that inflated other income last year.
Run their adjustments in a separate column next to yours. The gap between the two NOI figures is usually the entire explanation for a DSCR that came back lower than you modeled, and seeing it line by line is far more useful than arguing about the final ratio. Remember that the lender is running the same statement through its own underwriting review before it quotes anything, so the earlier you can reproduce their view, the fewer surprises land in the term sheet.
How do you get the T12 into Excel in the first place?
The operating statement arrives as a PDF, often as a scan of a printout, and no amount of formula skill helps until the numbers are in cells. Retyping twelve months of line items is where errors enter a model, because a transposed digit in a utilities row looks completely reasonable and quietly moves NOI.
Convert it instead. Upload the statement to the operating statement PDF to Excel converter and every income and expense line lands on its own row with the twelve monthly columns and the annual total preserved. Scanned pages work too, since OCR reads the characters off the image. The same workflow applies to the rent roll, which you need anyway to sanity check gross rental income against in place rent.
Then check one thing before you build a single formula on top of the data. Select an expense column and look at the status bar. If Excel shows a count but no sum, the amounts came across as text and every formula downstream will return zero or ignore rows silently. Data, then Text to Columns, then Finish converts them back to numbers. Our guide to numbers stored as text after a PDF conversion covers the variants, and the broader PDF to Excel for real estate page covers the rest of the deal package.
A short reconciliation checklist before you trust the ratio
- Total the twelve monthly columns and confirm they equal the annual column on the source PDF. If they do not, a row was misread or a subtotal line was captured as data.
- Confirm that debt service, depreciation, amortization, and capital expenditures are not sitting anywhere inside your operating expense block.
- Confirm the numerator and denominator cover the same twelve months. A trailing twelve NOI against a debt service figure from a term sheet dated last quarter is not a ratio, it is a coincidence.
- Recalculate debt service with the amortizing payment, not the interest only payment, unless you are deliberately showing the interest only period separately.
- Run the lender's adjustments in a parallel column and note the resulting DSCR next to yours.
Do that and the ratio you carry into a lender conversation is one you can defend line by line. The formula was never the hard part. The statement was.