What makes commercial underwriting different
Commercial real estate is underwritten as a business, not as a house. The property’s value is derived from the income it produces, the leases that guarantee that income and the credit of the tenants paying it. Two identical buildings on the same street can be worth 30% apart because one has a ten-year lease to a national tenant and the other has six months left on a local tenant’s term.
That is why this model is built around the rent roll. Everything downstream — the pro forma, the debt sizing, the exit valuation — is derived from the lease schedule you enter.
- NLA (net leasable area) is the rentable square footage, excluding common areas and mechanical space.
- WALT (weighted average lease term) measures rollover risk. Below three years, expect lenders to price accordingly.
- Escalations: fixed annual bumps of 2–3% or CPI-linked. Fixed bumps are far easier to underwrite.
- Recoveries (NNN) shift taxes, insurance and CAM to tenants — this is the difference between gross and net leases.
NOI, cap rate and the value relationship
Net operating income is the engine of every commercial valuation. It is calculated after vacancy, credit loss, operating expenses and capital reserves, and before debt service, capital expenditure, tenant improvements and leasing commissions.
Value is then simply NOI divided by a capitalization rate. This is why a 50 basis point move in exit cap rate changes the sale price by 6–7% — much more than most investors anticipate when they model their returns.
NOI = EGI − Operating expenses − CapEx reserve
Value = NOI ÷ Cap rate
A 50 bps increase in cap rate on a 6.00% → 6.50% deal
reduces value by 7.7% at the same NOI.| Exit cap rate | Exit value | Change in value |
|---|---|---|
| 7.00% | $4,285,714 | −1.5% |
| 7.25% | $4,137,931 | −4.9% |
| 7.50% | $4,000,000 | −8.0% |
| 7.75% | $3,870,968 | −11.0% |
| 8.00% | $3,750,000 | −13.8% |
How lenders actually size a commercial loan
Commercial lenders size loans on coverage and yield, then cap the proceeds with loan-to-value. The binding constraint is whichever produces the smaller loan, and knowing which one binds tells you what to negotiate.
- DSCR constraint: annual debt service cannot exceed NOI ÷ 1.25. A $300,000 NOI supports $240,000 of debt service, which at 6.9% over 30 years is roughly $3.55M of loan.
- Debt yield constraint: NOI ÷ loan amount must exceed 9–10%. A $300,000 NOI at a 9% debt yield caps the loan at $3.33M.
- LTV constraint: 65–70% of appraised value. On a $4.35M value at 65%, the cap is $2.83M — usually the binding constraint for a well-leased asset.
- In a rising-rate environment the DSCR constraint binds first; in a low-rate environment LTV does. The workbook computes all three.
What actually moves a levered IRR
Levered IRR is driven by three things in order of magnitude: the exit cap rate, the NOI growth rate and the cost of debt. Everything else — small differences in vacancy, minor expense variances — moves it by tens of basis points.
This is why stress-testing matters more than precision. Running 25 basis points of cap-rate expansion and 100 basis points of rent-growth reduction tells you more about the deal’s risk than refining the insurance estimate to the dollar.
- Exit cap rate: a 50 bps move swings levered IRR by 200–400 bps on a typical 10-year hold with debt.
- NOI growth: the difference between 2% and 3% annual growth compounds to roughly 10% of NOI over a decade.
- Cost of debt: on 65% leverage, a 100 bps rate increase reduces cash-on-cash by 40–60 bps.
- Hold period: extensions after year 7 usually add less IRR than the additional risk they carry.
The five risks this model exposes
Underwriting is as much about identifying what kills the deal as it is about forecasting returns. These are the five questions a lender or investment committee will ask, and the tab where the answer lives.
- Rollover risk: WALT on the rent roll tab. If more than 30% of NLA expires in any single year, that is a refinancing risk.
- Coverage risk: DSCR on the pro forma tab, tested in the worst year of the hold, not year one.
- Valuation risk: the exit analysis at 25, 50 and 75 bps of cap-rate expansion.
- Capital risk: the CapEx reserve line. Under-reserving guarantees a surprise assessment.
- Execution risk: the difference between in-place rent and market rent on the rent roll. Mark-to-market upside is real, but only if you can actually lease the space.
How to use this tool
- Enter the property and leasing assumptions. Load the NLA, base rent per square foot, other income, operating expenses per square foot, structural vacancy and credit loss. These drive the entire pro forma.
- Input the rent roll. Enter each tenant with square footage, rent, escalation and remaining term. The weighted average rent and WALT calculate automatically — check that WALT is above three years.
- Size the debt and set the exit assumption. Enter LTV, interest rate, amortization and term, then set an exit cap rate at least 25 bps above your going-in cap rate.
- Download and stress-test. Download the workbook and run three scenarios: exit cap +50 bps, rent growth −1%, and a 60-day vacancy on the largest tenant. If the DSCR holds above 1.20x in all three, the deal is financeable through a downturn.
What is inside the download
A rent roll with WALT and weighted-average rent, a 10-year pro forma with escalation and opex growth schedules, and a debt & equity tab with DSCR, debt yield, exit analysis, levered IRR and a simple promote waterfall.
Rent Roll & Escalations— a separate worksheet incre-underwriting-model.xlsx.10-Year Pro Forma— a separate worksheet incre-underwriting-model.xlsx.Debt & Equity— a separate worksheet incre-underwriting-model.xlsx.