How Much Debt Can This Asset Really Support?
A Technical Walkthrough of CRE Loan Sizing Metrics and Excel‑Driven Calculations
This article walks through how lenders actually size a commercial real estate (CRE) loan using three core constraints—Loan‑to‑Value (LTV), Debt Yield, and Debt Service Coverage Ratio (DSCR)—plus the mortgage constant that links payments to loan amount. Using a “CRE Loan Sizing Model” as a case study, it shows step‑by‑step how each constraint produces a different maximum loan, how to identify the binding constraint, and how a borrower might choose a slightly smaller “business‑plan friendly” loan while staying within lender limits. Concepts are illustrated with realistic assumptions (NOI, 30‑year amortization, ~6.5% interest) to mirror a stabilized U.S. CRE deal.
1. What “Loan Sizing” Means in CRE
In CRE, “loan sizing” is the process of translating a property’s income and value into a maximum loan amount that a prudent lender is willing to advance. Lenders usually run three parallel tests:
An LTV test: How large can the loan be as a % of value?
A DSCR test: Can the property’s NOI comfortably cover annual debt service?
A Debt Yield test: How much NOI does the lender get per dollar of loan, independent of rate and amortization?
The lender calculates a maximum loan from each test and then chooses the smallest of the three as the constrained (or underwritten) loan amount.
2. Key Concepts and Formulas
Loan‑to‑Value (LTV)
Definition: LTV is the loan amount divided by the property value; lenders set a maximum LTV (e.g., 60–75% for stabilized U.S. assets depending on asset type and market).
Formula to size a loan:
Max Loan by LTV = Max LTV × Property ValueIn the attached model: Property value is 20,000,000 and max LTV is 60%, so Max Loan by LTV is 12,000,000.
Debt Yield
Definition: Debt Yield = NOI ÷ Loan Amount; lenders impose a minimum debt yield, often 8–12% for stabilized CRE in the U.S. depending on risk.
Formula to size a loan:
Max Loan by Debt Yield = NOI / Min Debt YieldIn the attached model: NOI is 1,250,000 and min Debt Yield is 8.5%, so Max Loan by Debt Yield is about 14,705,882.
Debt Service Coverage Ratio (DSCR)
Definition: DSCR = NOI ÷ Annual Debt Service; lenders usually require DSCR ≥ 1.20–1.35x for stabilized properties in the U.S.
Step 1 – Max Debt Service:
Max Annual Debt Service = NOI / Min DSCRStep 2 – Turn Debt Service into a Loan (using the mortgage constant):
Max Loan by DSCR = Max Annual Debt Service / Mortgage ConstantIn the attached model: Min DSCR is 1.25, so Max Annual Debt Service is 1,000,000 (1,250,000 ÷ 1.25). Using a mortgage constant of 0.075848 (for 6.5% and 30‑year amortization), the Max Loan by DSCR is about 13,184,235.
Mortgage Constant
Definition: The mortgage constant is the annual debt service per dollar of loan; it depends on the interest rate and amortization period.
Conceptual formula:
Mortgage Constant = Annual Debt Service / Loan AmountIn an amortizing CRE loan, the constant can be derived from the standard annuity payment formula using the interest rate and amortization term, then annualized.
In the attached model: At 6.5% interest and 30‑year amortization, the constant is 0.075848—meaning each dollar of loan requires 7.5848 cents of annual debt service.
3. How Lenders Apply Loan Sizing Constraints
In practice, for a stabilized U.S. CRE loan, a lender will:
Start with Property Value and NOI.
In the attached model, Value = 20,000,000 and underwritten NOI = 1,250,000.
Apply the LTV constraint.
With Max LTV = 60%, the LTV test gives a maximum of 12,000,000.
Apply the Debt Yield constraint.
With Min Debt Yield = 8.5%, the Debt Yield test allows up to about 14,705,882.
Apply the DSCR constraint, using the mortgage constant.
Min DSCR = 1.25 gives Max Annual Debt Service = 1,000,000.
Using a constant of 0.075848, the DSCR test allows about 13,184,235 of loan.
Take the minimum of the three loan amounts.
Min (12.0m, 14.7m, 13.2m) = 12.0m.
Therefore, the constrained loan amount is 12,000,000, and the binding constraint is LTV (because LTV “runs out” first).
This “lesser of” approach is standard in U.S. CRE lending: even if DSCR and Debt Yield look comfortable, the lender will not exceed its LTV policy limit.
4. Case Study: Walking Through The Loan Sizing Model
The attached model lays this out in a clean sequence that mirrors real‑world underwriting for a stabilized CRE asset at a U.S. lender.
4.1 Input Assumptions
Key assumptions in the “Assumptions” block are:
Underwritten NOI: 1,250,000
Property Value: 20,000,000
Max LTV: 60%
Min Debt Yield: 8.5%
Min DSCR: 1.25x
Interest Rate: 6.5%
Amortization Period: 30 years
Loan Term: 10 years
These assumptions would be quite plausible for a stabilized, income‑producing asset in a major U.S. market in a mid‑cycle interest rate environment.
4.2 Step‑by‑Step Constraint Calculations
Max Loan by LTV
Inputs: Property Value 20,000,000; Max LTV 60%.
Computation: 20,000,000 × 0.60 = 12,000,000.
Max Loan by Debt Yield
Inputs: NOI 1,250,000; Min Debt Yield 0.085.
Computation: 1,250,000 ÷ 0.085 ≈ 14,705,882.35.
Max Loan by DSCR
Step 1 – Max Debt Service:
Inputs: NOI 1,250,000; Min DSCR 1.25.
Computation: 1,250,000 ÷ 1.25 = 1,000,000 (Max Annual Debt Service).
Step 2 – Mortgage Constant:
Inputs: Interest Rate 6.5%; Amortization 30 years.
The model computes the constant as about 0.075848, the annual payment per dollar of loan for these terms.
Step 3 – Max Loan by DSCR:
Computation: 1,000,000 ÷ 0.075848 ≈ 13,184,235.
Constrained Loan Amount and Binding Constraint
The model then takes the minimum of the three:
LTV: 12,000,000
Debt Yield: 14,705,882
DSCR: 13,184,235
Constrained Loan Amount (min of three) = 12,000,000; Binding Constraint = LTV.
4.3 Custom / Selected Loan Amount
The model includes a “Loan Selection” section with a toggle to use a custom loan amount, which is set to 11,500,000 in the file. This mimics a borrower or credit committee choosing to borrow slightly less than the maximum allowed by the bank’s constraints, often to:
Improve DSCR and Debt Yield.
Reduce refinance risk at maturity.
Preserve flexibility in a volatile interest‑rate environment typical of U.S. CRE cycles.
The Selected Loan Amount in the model is 11,500,000, lower than the 12,000,000 maximum LTV‑constrained amount.
4.4 Resulting Metrics at the Selected Loan
Once the selected loan is set at 11,500,000, the model recomputes all key credit metrics:
LTV:
LTV=11,500,000/20,000,000=57.5%
This is comfortably below the 60% cap.Debt Yield:
Debt Yield=1,250,000/11,500,000≈10.87%
This exceeds the minimum 8.5%, giving the lender a healthy income cushion relative to loan size.Monthly Payment and Annual Debt Service:
Monthly payment: about 72,688.
Annual debt service: about 872,254 (72,688 × 12).
These are based on the same 6.5% interest and 30‑year amortization.DSCR:
DSCR=1,250,000/872,254≈1.43×
This is above the 1.25x minimum, which aligns with common U.S. lender requirements for stabilized assets.Equity Required:
Equity = Value − Loan = 20,000,000 − 11,500,000 = 8,500,000Cash‑on‑Cash (NOI – DS) / Equity:
1,250,000−872,254/8,500,000≈4.44%
This shows the levered equity yield in year 1 under the underwritten NOI.The “Error Checks” block then confirms that LTV, Debt Yield, and DSCR are all within lender thresholds and flags “ALL OK” for the overall status, matching how a credit memo template might show covenant compliance in a U.S. bank or life‑co underwriting model.
5. How This Mirrors Real U.S. CRE Loan Underwriting
The modeling approach used is highly consistent with CRE underwriting practice across U.S. banks, life companies, and debt funds for stabilized assets: they normalize NOI, then size the loan separately by LTV, DSCR, and Debt Yield, pick the most conservative amount, and finally consider any business‑driven decision to borrow less than the maximum. Mortgage constants are standard tools in these models to translate between loan amount and annual debt service for a given rate and amortization schedule.
Comments