Commercial Property Debt Sizing & DSCR Model: Max Loan Test – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Shows the maximum loan under LVR, DSCR, ICR and debt-yield tests and names the constraint that binds.
2. Tests every covenant every year for ten years and flags breaches, with year-1 headroom and break-even rate.
3. Runs a base and stressed refinance test at loan expiry and reports any equity shortfall before you commit.

REAL ESTATE EXCEL DESCRIPTION

Commercial Property Debt Sizing & DSCR Model: Max Loan Test is an Excel template (XLSX) available for immediate download upon purchase.

The Commercial Property Debt Sizing & DSCR Model answers the question every commercial property borrower and credit analyst has to answer first: how much will a lender actually lend against this building, which covenant limits the loan, and will the deal still refinance when the loan expires? It works the way a lender's credit team works, starting from the rent roll rather than from a single income number.

What it does: you enter up to ten tenancies (area, passing rent, lease expiry date, annual review %, rent-free still to run, outgoings recovery %, market rent and renewal probability) and an outgoings budget split into recoverable and non-recoverable lines. The model builds gross potential rent, probability-weighted downtime and rent-free on each lease expiry, a minimum underwriting vacancy allowance, credit loss, outgoings recoveries and NOI for ten years plus a forward year. It then sizes the loan as the LOWEST of maximum LVR, minimum DSCR, minimum ICR and (optionally) minimum debt yield, and shows which constraint binds.

Main features: a tenant-by-tenant rent roll engine; WALE by income and by area, occupancy and a lease expiry profile; amortising (monthly P&I) or interest-only fixed-rate debt; separate origination sizing hurdles and ongoing covenants; DSCR, ICR, debt yield and LVR for every year with a BREACH flag for each covenant; year-1 headroom, the NOI fall the DSCR covenant can absorb and the break-even interest rate; a refinance test at loan expiry in a base case and a stressed case (rate shock plus cap rate expansion) showing the maximum refinance loan and any shortfall; exit proceeds, cash-on-cash, unlevered IRR and pre-tax equity IRR; and a 7 x 7 sensitivity grid of year-1 DSCR across interest rates and vacancy, colour-coded against the covenant and the sizing hurdle. A dashboard summarises the credit position and ten model checks. 1,571 live formulas, zero errors after a full recalculation, with every headline figure independently re-derived outside Excel.

How to work with it: overwrite the blue input cells on the Tenancy and Assumptions sheets; yellow-filled cells are the main value drivers. Read the binding constraint and headroom on Debt Sizing, the covenant track on Cash Flow, and the refinance and IRR results on Returns. The worked example is a fictional ten-tenancy neighbourhood retail centre bought at a 6.6% initial yield and financed at 57.8% LVR, where the ICR test binds and the stressed refinance still clears.

Why you need it: lenders rarely say which test is limiting the loan, and a deal that sizes today can fail to refinance in five years when rates and cap rates move. This model makes both visible before you sign a term sheet. It is honest about its limits: annual periods, one lease roll per tenancy inside the horizon, expected-value leasing assumptions and pre-tax returns are all disclosed on the cover. It ships fully unlocked, with every formula visible, no macros and no external links.

Currency-agnostic; treat $ as your currency. For planning only, not financial advice. Lenders apply their own credit policies, so confirm terms with your lender.

Source: Best Practices in Real Estate, Integrated Financial Model Excel: Commercial Property Debt Sizing & DSCR Model: Max Loan Test Excel (XLSX) Spreadsheet, g59076599o70