A rent roll is a dated list of who occupies a building, what they pay, how much space they occupy, and when their key dates hit. A lease is the binding contract that tells you what cash the landlord can bill, what costs the tenant must reimburse, and what happens when either side defaults. Model the rent roll without the lease, and you will misstate NOI, mis-size debt, and misunderstand what breaks first in a downturn.
Rent rolls and leases are the underwriting substrate for stabilized and transitional commercial real estate. The rent roll is a point-in-time schedule of occupied and available space, tenant economics, and critical dates. Leases are the controlling legal instruments that define cash flow, cost responsibilities, remedies, and transfer rights.
Treat the rent roll as “revenue by suite” and you may get a clean spreadsheet and a messy outcome. The lease decides whether “rent” is actually collectable, whether reimbursements stick, and whether an anchor tenant can pull a string that cuts everyone else’s rent. If you want close certainty and covenant headroom, you build a model that respects those facts.
Why rent roll modeling is the fastest way to find hidden NOI risk
Rent roll modeling turns legal language into cash flow you can test. The payoff is practical: you can size debt more safely, forecast rollover exposure, and see which clauses create sudden drops in net operating income (NOI). That is why strong underwriting treats the rent roll as a starting point and the leases as the source of truth.
What you are modeling, and what you are not
A rent roll model translates contractual lease economics into periodic cash receipts and property-level cash expenses, then into asset-level debt service and equity cash flow. It is not an accounting ledger. Instead, it is a projection tool that should reconcile to trailing twelve-month (TTM) property financials in the base period, then roll forward with explicit assumptions for leasing, downtime, reimbursements, and credit loss.
Rent rolls come in a few flavors that matter in underwriting. An in-place rent roll is the current tenant list as of a date. A pro forma rent roll assumes future leasing, rent steps, market resets, and often capital work. A lender rent roll highlights rollover, occupancy, tenant concentration, and the specific terms that drive loan risk.
Some boundary conditions keep you honest. First, the lease governs; when the rent roll conflicts with the lease, model to the lease and log the discrepancy. Next, decide cash versus accrual early; most underwriting runs on cash NOI, while leases contain accrual concepts like free rent and straight-line treatment. Recoveries are not “other income” by default; they are contractual reimbursements with caps, exclusions, and collection risk. Options are not extensions until exercised; treat renewals and expansions as probabilities, not as rent you can count on.
Incentives explain why data is rarely pristine. Sellers like effective rent because it supports a higher value. Lenders prefer contractual rent and enforceability because those support a safer loan. Property managers track billings and collections, which can diverge from what the lease allows because of disputes, informal accommodations, and side letters. Your model needs to reconcile those worlds or at least show the gap.
A useful non-boilerplate angle: “cash cliff” mapping
A simple way to add decision value is to map cash cliffs, which are months where multiple lease features stack up and liquidity tightens. For example, a large lease expiration plus annual CAM reconciliation plus a debt service step can create a short-term squeeze even when the annual NOI looks fine. If you add a one-page cash cliff calendar to your model outputs, investment committees tend to focus faster on what matters: timing risk, not just totals.
Lease structures that change your model mechanics
Every rent roll line implies a lease type. The lease type determines what belongs in revenue, what belongs in expenses, and what belongs in a reserve.
Gross, modified gross, and net leases
Full-service gross leases push operating expenses to the landlord and often use above-base-year reimbursements to share inflation. Modified gross leases are similar but define pass-throughs more precisely; the base year, caps, and expense definitions do the heavy lifting. Net leases shift taxes, insurance, and maintenance to the tenant, but “NNN” still contains exclusions, management fee treatment, and timing quirks that leave residual exposure.
The modeling implication is straightforward. For gross leases, expenses sit in the property P&L and rent is closer to NOI. For net leases, reimbursements are cash inflows that offset expenses, but caps, non-recoverables, and mismatched timing create leakage. That leakage hits NOI, and that hits DSCR.
Retail, industrial, multifamily, and ground leases
Retail adds percentage rent and breakpoints. Percentage rent depends on tenant sales and the lease’s audit rights. If you do not have sales history and the ability to enforce reporting, treat percentage rent as a scenario line item, not as a smooth growth rate that props up value.
Industrial often looks like clean NNN paper until you read the carve-outs. Roof, structure, parking, and paving can sit with the landlord. Expense stops and caps on controllable CAM can quietly transfer inflation risk back to you. Model those terms explicitly; “100% reimbursement” is a phrase, not a fact.
Multifamily is different because leases are short and operational. The rent roll behaves more like a unit roster with market rent, actual rent, concessions, and delinquency. Model vacancy and credit loss at the portfolio level, but keep renewal and trade-out assumptions tied to unit type. Otherwise you lose the timing and the pricing power that actually drive results.
Ground leases deserve extra respect. They can prime mortgage rights, impose lender consent requirements, and shape reversion value. Model ground rent steps and include lender recognition agreement terms in downside cases. If the ground lease constrains remedies, the loan is not as senior as it looks.
Minimum data you need and how to validate it
A usable rent roll must be traceable back to executed documents and collections. In practice, you build a rent roll control file and log every assumption, source, and exception. That discipline saves time later when the investment committee asks why year-one cash moved by $400,000 after “one small amendment.”
At a minimum, capture: tenant legal name and guarantor; premises ID and rentable area standard; lease commencement, rent commencement, expiration, and termination rights; base rent schedule with steps; free rent and abatements; TI reimbursements; security deposit or letter of credit and expiry; operating expense structure and recoverable categories; caps, base year/stop, and gross-up language; renewal/expansion/contraction rights and kick-outs; assignment/subletting terms and recapture rights; default provisions, cure periods, and late fees.
Validation catches most underwriting errors quickly. Tie to executed leases and amendments; abstract from the documents, not from a PDF rent roll. Match billings and collections by comparing contractual rent to tenant ledgers for the last three to six months; that reveals disputes and informal deals that affect near-term DSCR. Confirm area and suite mapping; mismeasured area breaks reimbursements and percentage rent. Reconcile to TTM financials; base rent plus recoveries plus other income should roughly match TTM revenue after you normalize timing and one-offs. Confirm estoppels and SNDAs; when they are missing, enforceability risk rises and lenders price that with structure or proceeds.
If you receive lease abstracts, treat them as non-authoritative until you sample them against source documents. Abstracts often lag amendments and side letters, and those are the documents that tend to move cash.
Modeling architecture: from rent roll to cash flow
A lease model should be modular and testable. The common failure is a single sheet full of hard-coded dates and overwritten cells, which works until diligence changes one rent commencement date and nothing ties out.
A clean build uses: an assumptions module (inflation, market rent growth, downtime, leasing costs, bad debt, renewal probabilities, expense growth); a lease schedule engine (monthly or quarterly base rent, reimbursements, concessions, expirations); occupancy and downtime logic; an expense and recovery model with category mapping and reconciliations; a debt and capital stack module (draws for value-add, amortization, reserves, covenants); and outputs (NOI, unlevered and levered cash flows, DSCR, LTV, debt yield, tenant concentration, rollover).
Time step is a practical choice. Monthly is preferable for transitional assets, heavy free rent, percentage rent, or construction and lease-up because timing drives liquidity. Quarterly can work for stabilized office and industrial if you still handle intra-quarter events like commencements and free rent with a clear convention. The point is to avoid phantom cash that looks fine on paper and fails a covenant test in reality.
Base rent should come from the lease schedule, not from a blanket growth rate applied to current rent. Many leases have step-ups, CPI-linked adjustments with caps and floors, or FMV resets. Fixed steps belong as explicit dates and amounts. CPI clauses require a CPI path plus the clause mechanics; when you do not have a specified index assumption, use a conservative proxy and run sensitivity because caps and floors matter. FMV resets require a market rent assumption and a renewal probability; do not treat them as guaranteed uplift when the tenant has termination rights.
Free rent is timing. If you size debt off cash NOI, free rent reduces cash receipts in those months, full stop. You can track straight-line metrics for reporting, but do not let accrual optics drive a cash covenant.
Concessions, such as moving allowances, early occupancy, and abatements tied to landlord work, often live in side letters. Treat side letters as controlling and track clawbacks and expiration dates. They tend to show up when the deal is under stress, which is exactly when you want your model to be right.
TI and leasing commissions are capital outflows tied to leasing events. They are not operating expenses, but they drain equity cash and drive reserve requirements. Model TI dollars per square foot, LC as a percent of rent or a per-foot metric, and the payment timing (execution, substantial completion, commencement). Lenders frequently underwrite TI/LC as leasing reserves under cash management. Show reserve draws and remaining availability, because reserve exhaustion is a real refinancing and covenant risk.
Expense recoveries: where most models lose the plot
Expense recoveries are often modeled as a flat percentage of total expenses. That is rarely correct. Recoveries depend on lease language, expense category mapping, caps, gross-ups, base years, and occupancy.
Start with recoverability mapping. Build an expense taxonomy aligned to the general ledger and map each category to recoverability under each lease. Non-recoverables often include capital items (except allowed amortized capital), leasing and marketing, debt service, depreciation, income taxes, and certain legal costs. When you misclassify a category as recoverable, you are overstating NOI and understating landlord risk.
Base year and gross-up mechanics matter most when occupancy is below normal. Many office leases allow gross-up of variable expenses to a stated occupancy for base year comparisons. If you skip gross-up, you understate reimbursements and you may misjudge how cash improves as the building leases up, timing that affects debt sizing and sponsor liquidity.
Caps require a split between controllable and uncontrollable costs. A cap usually applies to controllable CAM, not to taxes or insurance. If you model expense growth as one blended rate, you cannot apply caps correctly, and the error compounds.
Recoveries also have reconciliation timing. Many landlords bill estimates monthly and reconcile annually with a lag. Model estimated billings and an annual true-up with collection loss. In stress, tenants often prioritize base rent over CAM true-ups, so apply separate collection assumptions to rent and reimbursements in downside cases.
Rollover, renewal probability, and downtime drive value-add outcomes
Rollover is not a date on a schedule. It is a change in the cash flow regime that brings downtime, capital spend, and a new rent level.
Model renewal as a branch. If the tenant renews, apply renewal rent terms and renewal TI/LC. If the tenant leaves, apply downtime, then new-lease assumptions: market rent, free rent, TI/LC, and rent commencement timing. If a loan requires deterministic projections, use probability-weighted cash flows. For investment committee decisions, a scenario tree often communicates risk better than a single blended line because it shows path dependency.
Tenant concentration deserves its own downside case. If the largest tenant leaves, the asset’s cash flow and valuation can change fast. At single-asset scale, you do not diversify that risk away.
Collections: connect contractual rent to real cash
Many models assume 100% collection on contractual rent, net of vacancy. That assumption fails quietly, then all at once. Disputes, offsets, and timing gaps show up first in near-term DSCR and cash sweeps.
Model collection loss explicitly. Apply a base bad debt factor to billed amounts, then increase it for weaker credits and known disputes. Use separate loss factors for base rent, reimbursements, and percentage rent. If arrears are material, model a collections curve for receivables instead of assuming an immediate catch-up. That timing can decide whether the loan trips a covenant.
Cash management helps but does not change tenant behavior. A lockbox and springing cash dominion reduce commingling risk and improve lender control, but they do not create rent. Reflect covenant triggers that shift control to the lender, because those triggers affect sponsor liquidity and the practical ability to fund TI.
Documents that actually move cash (and enforceability)
Read documents for clauses that change cash flow or enforceability. Lease agreements and amendments drive rent schedules and recoveries. Side letters often carry concessions, termination rights, and non-public arrangements that change NOI and risk. Estoppels confirm key terms and disclose offsets and defaults. SNDAs protect lenders against lease termination after foreclosure. Guaranties provide credit support with limits and burn-offs that matter in stress. Subleases and consents change rent flow and can introduce landlord recapture rights. Retail REAs and CCRs impose operating covenants and restrictions that can limit re-leasing.
Execution order matters. Amendments and side letters often close alongside a sale or refinance. A model built off early diligence can be stale by signing, which is why version control and an exception log matter.
Practical kill tests and decision-useful outputs
A few fast tests save you from wasting time. If you cannot obtain executed leases and amendments for top tenants, assume adverse selection and price accordingly or stop. If “other income” is material and not contract-backed, treat it as non-recurring until proven. If recoveries drive a large share of revenue but the owner cannot provide reconciliation history and lease support, assume leakage. If large rollover hits within the hold and there is no credible leasing plan, the base case is a story. If retail rent depends on co-tenancy and the anchor is weak, the rent roll is not stable. If effective rents look strong while contractual cash rent is depressed by concessions, debt sizing may be tighter than the headline suggests.
- Top-tenant proof: Require executed leases and amendments for the largest tenants before you trust year-one NOI.
- Recovery support: Ask for historical CAM reconciliation packages when reimbursements are a big revenue line.
- Rollover realism: Tie downtime, TI/LC, and free rent to expirations instead of smoothing them across years.
- Output discipline: Show DSCR and covenant headroom under base and downside cases, not just unlevered IRR.
A decision-useful output is not a stack of tabs. It is clarity on cash durability and rollover path dependency. The lease engine should produce: contractual base rent by period with a bridge to collected cash; rollover by year with concentration; market versus in-place rent and the embedded mark-to-market; recoveries with leakage by category and lease type; leasing costs and capex tied to lease events; DSCR, debt yield, and covenant headroom under base and downside cases. When one amendment changes commencement or adds free rent, outputs should update without manual patchwork.
Close the model like you would close a file that might end up in a dispute. Archive the index, versions, Q&A, users, and full audit logs. Hash the final package so you can prove what was used. Set retention that matches policy and deal timelines. Require vendor deletion with a destruction certificate when retention ends. And remember that legal holds override deletion, even when everyone prefers a clean desk.
Key Takeaway
Rent roll modeling works when you treat leases as the controlling documents, translate terms into cash timing, and validate the result against collections and TTM financials. If you can explain where NOI is contract-backed, where it is assumption-backed, and when cash cliffs occur, you are underwriting the property instead of just formatting the spreadsheet.