Commercial Real Estate & Mortgage Finance
CRE Debt Yield, Loan Constant & Leverage Spread Formula
Underwrite commercial real estate via Debt Yield = NOI / Loan, Monthly-Pay Annual Loan Constant k_debt, DSCR = Debt_Yield / k_debt, and Positive vs Negative Cash-on-Cash Leverage.
Catch Negative Cash-Flow Leverage when Loan Constant exceeds Cap Rate despite a lower interest rate!
Commercial real estate (CMBS and life-company) lenders size loans using three linked ratios: (1) Going-In Cap Rate = NOI / Purchase_Price; (2) Lender Debt Yield = NOI / Loan_Amount (immune to cap-rate compression or artificial 40-year amortization extensions!); and (3) Annual Mortgage Constant k_debt = r_yr / (1 - (1 + r_yr / 12)^(-12 * N_yr)), which sets DSCR = Debt_Yield / k_debt and Levered Cash-on-Cash Return CoC = (NOI - Loan_Amount * k_debt) / (Purchase_Price - Loan_Amount)! Crucially, Positive Cash-Flow Leverage requires Cap_Rate > k_debt — NOT merely Cap_Rate > Interest_Rate_r!
Verify this exact trap on Purchase_Price = 12,500,000 (B2), Annual_NOI = 850,000 (B3 -> Cap Rate = 6.80%), Loan_Amount = 10,000,000 (B4 -> 80% LTV, Equity = 2,500,000), Note_Rate_r = 0.06 (B5 = 6.00% < 6.80% Cap Rate!), and Amort_Years_N = 30 (B6): Debt Yield = 850,000 / 10,000,000 = 0.0850 (8.50%), YET 30-year monthly principal amortization lifts Annual Loan Constant to k_debt = 0.071946 (7.1946% > 6.80% Cap Rate!), giving DSCR = 1.1814 and dragging Equity Cash-on-Cash DOWN to 0.0522 (5.22% < unlevered 6.80% — Negative Cash Leverage!)! Boost NOI to 1,000,000 (8.00% Cap > 7.1946%) to watch Cash-on-Cash jump to +11.22%!
| CRE Underwriting Metric (12.5M Price, 850k NOI, 10M Loan @ 6%/30Y) | Formula | Verified Output |
|---|---|---|
| Lender Debt Yield | NOI / Loan_Amount | 0.0850 (8.50%) |
| Annual Mortgage Constant (k_debt) | r / (1 - (1 + r/12)^(-12*N)) | 0.071946 (7.19% > 6.80% Cap!) |
| Debt Service Coverage Ratio (DSCR) | Debt_Yield / k_debt | 1.1814x |
| Equity Cash-on-Cash Return (CoC) | (NOI - Loan * k_debt) / Equity | 0.0522 (5.22% Negative Leverage!) |
Annual Monthly-Amortizing Mortgage Constant k_debt & DSCR Formula
Validate Price > Loan > 0, NOI > 0, r > 0, and N >= 1.
=LET(p_pr, B2, noi_v, B3, ln_a, B4, r_nt, B5, n_yr, B6, IF(OR(ln_a <= 0, p_pr <= ln_a, noi_v <= 0, r_nt <= 0, n_yr < 1), "Invalid CRE Input", LET(k_db, r_nt / (1 - ((1 + (r_nt / 12))^(-12 * n_yr))), ROUND((noi_v / ln_a) / k_db, 4))))Levered Equity Cash-on-Cash Return Formula
Compare CoC directly against unlevered Cap Rate B3 / B2 to flag negative cash-flow leverage before signing a term sheet.
=LET(p_pr, B2, noi_v, B3, ln_a, B4, r_nt, B5, n_yr, B6, IF(OR(ln_a <= 0, p_pr <= ln_a, noi_v <= 0, r_nt <= 0, n_yr < 1), "Invalid CRE Input", LET(k_db, r_nt / (1 - ((1 + (r_nt / 12))^(-12 * n_yr))), ROUND((noi_v - ln_a * k_db) / (p_pr - ln_a), 4))))Try the example
Use the original values or change them to test your understanding.