Rental Property DSCR Underwriting Model - Loan Sizing on LTV, DSCR & Debt Yield (Excel)

Description
THE LOAN AMOUNT IS NOT AN INPUT.
Most rental property calculators ask you to type in the loan amount, then show you the cash flow. A lender does the opposite: they underwrite the income and tell you how much they will lend. This Excel model works the way the lender works, so you find out what a deal actually finances before you write the offer.

HOW THE LOAN IS SIZED
The model computes all three lender caps and lends the LOWEST of the three: the LTV cap (max LTV x the lesser of purchase price and appraised value), the DSCR cap (underwritten NOI / (minimum DSCR x mortgage constant)), and the debt yield cap (underwritten NOI / minimum debt yield).

It then NAMES the binding constraint on the dashboard. You see instantly whether your deal is LTV-bound, DSCR-bound or debt-yield-bound, which tells you what to negotiate: the price, the rate, or the rent roll.

LENDER-NORMALIZED NOI
NOI is rebuilt the way a DSCR lender rebuilds it, not the way an owner wishes it looked: physical vacancy and credit loss deducted, a market management fee charged even if you self-manage, annual replacement reserves per unit, and each operating line inflated separately. Rent is underwritten on the lesser of in-place and market rent (Form 1007 convention), with loss to lease shown in dollars and in percent.

WHAT IS INSIDE - 14 CONNECTED WORKSHEETS
START HERE, DASHBOARD, ASSUMPTIONS (with a Base/Bull/Bear toggle), PROPERTY & UNIT MIX, REVENUE, OPEX, DEBT & DSCR SIZING, P&L, CASH FLOW, BALANCE SHEET (with an automatic balance check), RETURNS, DCF, SENSITIVITY and GLOSSARY. 1,596 live formulas. 0 macros. 0 circular references. Nothing password-protected.

WHAT ELSE IT DOES
STRESS TEST: 7 scenarios, each with a covenant PASS/BREACH flag - two rate shock steps, stressed vacancy, market rent decline, property tax increase, insurance increase, and a combined downside. Every row re-sizes the loan AND shows the DSCR on the loan you already have.
BREAK-EVEN POINTS: the exact vacancy rate, rent decline, reset interest rate and occupancy at which your DSCR covenant breaks, plus your cushion in percentage points.
CASH-OUT REFINANCE: re-run through the same three caps at the refi year, with the binding refi constraint named, old balance paid off, refi costs deducted, down to net tax-free cash.
AFTER-TAX EXIT: sale on your exit cap rate, selling costs, adjusted basis, depreciation recapture taxed at 25%, capital gains tax, mortgage payoff, down to net proceeds. Straight-line 27.5-year residential depreciation runs through the statements, with loss carryforwards.
SENSITIVITY: three formula-driven grids (no data tables, no macros) - Year-1 DSCR vs rate and vacancy, maximum supportable loan vs rate and DSCR floor, and an offer-price grid.

IT DOES NOT SHIP BLANK
A complete worked deal is already in the file: 4 units, $585,000 purchase, 10-year hold. In that demo the model sizes the loan at $426,867 and reports it as DSCR-bound - Year-1 DSCR 1.25x, LTV 73.0%, debt yield 10.2%, equity required $193,169, going-in cap rate 7.47%, Year-1 cash-on-cash 4.5%, levered after-tax IRR 10.2%, equity multiple 1.92x, break-even occupancy 82.1%. Overwrite the blue cells with your deal and everything updates.
SCOPE

1 to 4 unit residential rental: acquisition of a stabilized property, held, optionally refinanced, then sold, from the individual landlord's point of view. The model flags any deal above 4 units as outside residential financing. No ground-up construction, no GP/LP promote waterfall, no commercial multifamily.
Built and tested for Microsoft Excel (Windows and Mac), 2016 and later, including 365. Color code: BLUE = your input, BLACK = formula, GREEN = link to another tab.