Rental Property DSCR Underwriting and Loan Sizing Model – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Sizes the loan the way a DSCR lender does, taking the lowest of the LTV, DSCR and debt yield caps and naming which one is binding.
2. Normalizes NOI with imputed vacancy, a market management fee and per-unit replacement reserves, so the number matches what the lender will actually approve.
3. Carries the deal through a full three-statement build with an automatic balance check, a covenant test every year and an after-tax exit.

REAL ESTATE EXCEL DESCRIPTION

Rental Property DSCR Underwriting and Loan Sizing Model is an Excel template (XLSX) with a supplemental PDF document available for immediate download upon purchase.

Most rental property spreadsheets ask you to type in a loan amount and then show you the resulting cash flow. A lender does the opposite: it underwrites the income first and tells you how much it will lend. This Excel model works the way the lender works, so the loan amount is never an input – it is derived from three caps (LTV, DSCR, debt yield) and the model names which one is binding. That single design choice is what separates an underwriting tool from a wish-list spreadsheet.

THE PROBLEM

Investors and analysts sizing a 1-4 unit rental deal typically build the cash flow first and back into a loan number that "feels right." That inflates the deal: it skips imputed vacancy, a market management fee (even for self-managed properties), and per-unit replacement reserves – all of which a DSCR lender charges against NOI whether or not the number appears on your own spreadsheet. The result is a model that looks fundable and isn't.

WHAT THE MODEL DOES

The workbook computes lender-normalized NOI, sizes the loan as MIN(LTV cap, DSCR cap, debt yield cap), runs exact monthly amortization rolled up to annual periods, tests the DSCR covenant every year, re-sizes a cash-out refinance through the same three caps, and carries the deal through a full three-statement build (P&L, Cash Flow, Balance Sheet) with straight-line 27.5-year residential depreciation and an automatic balance check that must read zero. A Base/Bull/Bear scenario toggle and five stress shocks (rate steps, vacancy, rent decline, tax and insurance increases) run through a dedicated stress panel with a covenant flag on each scenario. Exit is computed after-tax, including 25% depreciation recapture and capital gains tax, down to net proceeds to the owner.

THE 14 WORKSHEETS

START HERE – navigation and quick-start instructions.

Dashboard – loan amount, Year-1 DSCR, LTV, debt yield, cap rate, cash-on-cash, levered IRR, equity multiple, break-even occupancy, NPV, and a model-checks panel.

Assumptions – the only sheet you edit: deal inputs, underwriting basis, lender terms, refinance and exit/tax assumptions, scenario toggle, stress shock inputs.

Property and Unit Mix – up to four unit types, in-place vs. market rent, loss to lease, gross rent multiplier, residential financing eligibility check.

Revenue – rent roll rollup, vacancy and credit loss, other income.

Opex – property tax, insurance, R&M, management fee, reserves, utilities, HOA.

Debt and DSCR Sizing – the three lender caps, the binding constraint, the stress test panel with covenant flags, refinance re-sizing.

P&L – full income statement with depreciation and loss carryforwards.

Cash Flow – operating, financing and investing cash flow, refinance cash-out.

Balance Sheet – full balance sheet with an automatic balance check equal to zero every year.

Returns – cash-on-cash by year, levered IRR after tax, equity multiple.

DCF – unlevered pre-tax property DCF, implied value and cap rate.

Sensitivity – three live formula-driven grids (no data tables, no macros): Year-1 DSCR vs. rate and vacancy, maximum loan vs. rate and DSCR floor, and an offer-price analysis.

Glossary – every term and formula convention defined.

WHO SHOULD USE THIS

Individual rental investors and analysts underwriting a 1-4 unit acquisition (single family, duplex, triplex or fourplex) who want to know the loan a DSCR lender will actually approve before making an offer, not after. It is also useful to brokers and lenders who want to hand a borrower a transparent, lender-style sizing exhibit. Out of scope: ground-up construction, GP/LP promote waterfalls, and commercial multifamily above four units.

WHAT IS INCLUDED IN THE DOWNLOAD

The Excel workbook (.xlsx, no macros, no circular references, no password protection), a written quick-start guide covering every input block and how to read the Dashboard and Sensitivity grids, and a fully built demo case (4 units, 585,000 purchase price, 10-year hold) so every formula is populated and auditable on first open. 1,596 live formulas across the 14 sheets. Compatible with Excel for Windows and Mac; Google Sheets may render XIRR and some charts differently.

Source: Best Practices in Real Estate, Integrated Financial Model Excel: Rental Property DSCR Underwriting and Loan Sizing Model Excel (XLSX) Spreadsheet, FinModelAI