Debt Service Coverage Ratio Calculator in Excel: Formula to Model
The DSCR formula is a single division — NOI over annual debt service — and Excel will compute it in one cell. What Excel will not do is stop you from dividing the wrong NOI by the wrong debt service, which is how most homemade coverage calculators end up a tenth of a turn away from the number the lender computes. That tenth is the difference between a deal that finances and a term-sheet surprise.
This is the Excel build guide: the cell-level construction of a DSCR calculator that matches lender conventions — the underwritten-NOI adjustments, the PMT mechanics, the IO toggle, the by-year coverage row, and the back-solve — plus the errors that produce confidently wrong ratios. If you want the concept-level treatment of what coverage means and why it binds, that is the DSCR calculator guide and the deeper DSCR explained primer; this article assumes the concepts and builds the machine. All figures are illustrative examples.
The Layout: Three Blocks
Structure the sheet as three visually distinct blocks:
- Inputs: the property's income and expense figures, the loan terms (amount, rate, amortization years, IO months), and the lender-convention toggles described below.
- The underwritten NOI build: your pro forma NOI, then the lender adjustments applied as their own labeled rows.
- The outputs: annual debt service, DSCR, and the back-solved maximum loan.
The block that beginners skip is the second one — and it is the block that makes the calculator agree with the lender.
Block 2: Underwritten NOI, Not Your NOI
Lenders do not test coverage on the borrower's pro forma NOI; they test it on their underwritten NOI, and the standard adjustments are buildable as rows:
Pro forma NOI =EGI − OperatingExpenses
− Vacancy floor adjustment =MAX(0, MinVacancy%×GPR − ActualVacancy$)
− Management floor adjustment =MAX(0, MinMgmt%×EGI − ActualMgmt$)
− Replacement reserves =ReservePerUnit × Units
= Underwritten NOI
The pattern in each row is the same MAX(0, floor − actual) construction: the lender substitutes its minimum where yours is lower and leaves yours alone where it is already conservative. The specific floors — a minimum vacancy percentage even on a full building, a minimum management fee even if self-managed, a per-unit reserve deduction even though reserves sit below NOI in your pro forma — vary by lender and program, which is why they are input cells, set from the term sheet, not constants. Build the block once and every quote you receive becomes three cell edits.
Display both NOIs. The gap between your number and the underwritten one is itself information: it is the haircut the lender applies to your optimism, and it flows dollar-for-dollar into the maximum proceeds.
The Debt Service Cell: PMT, Done Precisely
Annual debt service on an amortizing loan is Excel's PMT function — with three conventions that generate most errors:
=−PMT(Rate/12, AmortYears*12, LoanAmount) * 12
- Divide the rate by 12 and multiply the years by 12. PMT works in periods; a monthly-payment loan means monthly periods. Feeding annual arguments (
PMT(Rate, Years, Loan)) produces a different — and wrong — annual payment, because payment frequency changes the amortization math. - Mind the sign. PMT returns a negative (a payment); negate it, or your DSCR goes negative and the error propagates politely.
- Amortization years, not loan term. A loan with a 10-year term on a 30-year amortization schedule pays the 30-year payment; the term belongs elsewhere in the model (the balloon), not in PMT.
The IO toggle belongs beside it, because which payment the lender tests is a term, not a guess:
=IF(TestIO, LoanAmount*Rate, −PMT(Rate/12, AmortYears*12, LoanAmount)*12)
Interest-only service is lower, so an IO-tested DSCR is higher on the same loan — some programs size on the IO payment during the IO period, others size on the amortizing payment regardless. Ask; then set the toggle to match the answer rather than the flattering option.
And keep the periods consistent. DSCR is annual NOI over annual debt service. Monthly NOI over annual service (or the reverse) produces ratios off by a factor of twelve — an error too large to miss — but the subtler version, seasonalized monthly NOI over one month's payment, produces plausible-looking ratios that swing with the calendar. Annualize both terms, always.
The Outputs
The ratio itself:
=UnderwrittenNOI / AnnualDebtService
formatted to two decimals (1.27x, not 1.3x — lenders live in the second decimal), with a conditional-format flag against the lender-minimum input cell so a failing ratio announces itself.
The back-solve — the maximum loan the income supports at the lender's minimum — is the same algebra run backward, and it is buildable without iteration:
MaxDebtService = UnderwrittenNOI / MinDSCR
MaxLoan = −PV(Rate/12, AmortYears*12, MaxDebtService/12)
PV is PMT's inverse: given the payment the income can support, it returns the loan that payment amortizes. (The IO version is simpler still: MaxLoan = MaxDebtService / Rate.) The derivation and the mortgage-constant intuition behind it are in the max loan from DSCR explainer; in the workbook it is two cells, and they are the two cells that let you size your own proceeds before any lender does.
The By-Year Row: Coverage Through the Hold
A point-in-time DSCR answers the origination question; a projection answers the covenant question. In any multi-year model, add a coverage row under the cash flow:
DSCR_year = NOI_year / AnnualDebtService_year
— where debt service varies by year if the loan has an IO period rolling to amortization, or a refinance event. Conditional-format the row against the covenant minimum. The output worth watching is the shape: a value-add deal legitimately starts thin and builds; a stabilized deal drifting toward the covenant as expenses outgrow rents is the model warning you years in advance, in a row that costs one formula.
Two Companion Cells Worth Adding
While the blocks are open, two adjacent cells earn their keep:
Debt yield. =UnderwrittenNOI / LoanAmount — coverage's rate-proof sibling. Because it ignores rate and amortization entirely, it cannot be flattered by an IO period or a long schedule, which is exactly why some lenders run it as a second sizing test. One formula, and your calculator now reports the constraint pair many term sheets actually apply.
The balloon. If the loan's term is shorter than its amortization, add =−FV(Rate/12, TermYears*12, −PMT_monthly, LoanAmount) — the balance due at maturity. Coverage tells you the loan services; the balloon cell tells you what must refinance, and when, which is the risk the DSCR alone never mentions.
Wiring It Into a Full Model
A standalone calculator answers today's question; the same blocks embedded in a projection answer the hold's. The integration disciplines: give the loan terms one home — named input cells the calculator, the amortization schedule, and the cash flow all reference, so a rate change propagates everywhere or nowhere; let the by-year DSCR row read the projection's NOI rather than a copy of it; and keep the underwritten-NOI adjustment block separate from the pro forma expense build, so the model always knows the difference between what you believe and what the lender will credit. Duplicate loan terms across tabs are how a model quotes two different debt services in one file — the class of error no tie-out forgives.
The Error Checklist
The recurring defects in homemade DSCR spreadsheets, each traceable to a block above:
- Pro forma NOI in the ratio — no underwritten-NOI block, so the calculator flatters every deal by exactly the lender's haircuts.
- Annual arguments in PMT — the payment-frequency error, worth real basis points of phantom coverage.
- The wrong payment tested — IO where the lender tests amortizing, or vice versa.
- Mixed periods — monthly income against annual service, or the seasonal variant.
- Reserves counted twice or not at all — deducted in both the expense build and the adjustment block, or in neither.
- Cash flow in the numerator — debt service inside the number that measures the ability to pay debt service.
- A hardcoded minimum — 1.25 typed into a formula instead of referenced from an input cell, so the next lender's 1.20 quote requires archaeology.
Frequently Asked Questions
What is the DSCR formula in Excel?
=NOI/AnnualDebtService, with debt service built as =−PMT(rate/12, amort_years*12, loan)*12 for an amortizing loan. The construction work is upstream — the underwritten-NOI adjustments — not in the division.
How do I calculate the maximum loan from a DSCR in Excel?
=−PV(rate/12, amort_years*12, (NOI/MinDSCR)/12) — PV run on the maximum supportable monthly payment. For interest-only sizing, =(NOI/MinDSCR)/rate.
Should I use NOI or cash flow in the DSCR? Underwritten NOI — income before debt service, adjusted per the lender's conventions. Cash flow after debt service in the numerator is circular and always wrong.
Why does my Excel DSCR differ from the lender's? Almost always the second block: their vacancy floor, management floor, and reserve deduction against your pro forma NOI — or a payment-convention mismatch (IO versus amortizing, monthly versus annual PMT arguments). Rebuild their adjustments as inputs and the numbers reconcile.
What DSCR do lenders require? Minimums commonly quoted around 1.20x–1.25x for stabilized commercial and multifamily property, varying by asset, market, and program — which is precisely why the minimum is an input cell in this build, set from each term sheet rather than remembered.
The Build, Assembled and Extended
Everything in this guide — the underwritten-NOI adjustment block, the PMT/IO machinery, the two-cell back-solve, and the by-year coverage row — ships assembled in the DSCR & Debt Underwriting Calculator, extended with the pieces a single article cannot carry: the full LTV/DSCR/debt-yield triple-constraint sizing with the binding constraint flagged, a complete amortization schedule, and rate stress built in — fully unlocked, formula-transparent, versioned, with a documented methodology PDF. The full model catalog is in the store.
This article is for educational purposes only and does not constitute investment, legal, or tax advice. All figures are illustrative examples; lender conventions vary and term sheets govern. Consult qualified professionals before making investment decisions.
Get the next breakdown in your inbox
New CRE modeling walkthroughs 3× per week. No spam, unsubscribe anytime.
