Can I Afford This Property?Commercial real estate financing, worked out

Preparing a comparable-rent reconciliation before changing model assumptions

A practical guide for sponsors to build an evidence-based rent reconciliation workpaper that supports underwriting discussion and distinguishes market data from pro forma assumptions.

Source: CodexTopic: Valuation and collateral evidence1411 wordsUpdated 2026-11-19

A practical guide for sponsors to build an evidence-based rent reconciliation workpaper that supports underwriting discussion and distinguishes market data from pro forma assumptions.

When a lender reviews a commercial real estate financing request, the property's income-generating capacity is central to repayment analysis. The OCC Commercial Real Estate Lending handbook states that repayment of loans financing income-producing CRE typically depends on the property's ability to service debt from cash flow, and because collateral value is largely determined by NOI, it is important to analyze and understand income-generating capacity including whether cash flow and NOI projections are reasonable and supported [1]. A comparable-rent reconciliation is the workpaper that shows how the sponsor arrived at the projected rental income in the pro forma, and it is the single most effective document for turning assumptions into evidence.

What a rent reconciliation workpaper contains

A rent reconciliation workpaper is a document that lists every unit or suite in the property and compares its current rent to the market comparable rents the sponsor is using to project future income. The workpaper shows the current rent roll as a snapshot, then adds the trailing twelve months of actual collected income to reveal trends in effective rent, concessions and collection losses. The OCC handbook notes that even though a review of the rent roll might indicate a high rate of occupancy, actual collections should be examined to determine the true economic occupancy and evaluate the competency of property management [1]. The reconciliation workpaper makes this comparison explicit.

Suggested approach: Create a spreadsheet with one row per unit. Columns should include unit identifier, unit type, square footage, current scheduled rent, current collected rent, market comparable rent (from the appraisal or broker opinion), the dollar and percentage difference between current and market, and a column for notes on lease terms, concessions or tenant improvements that explain the difference.

Distinguishing the rent roll from the T-12

A current rent roll is a snapshot of what tenants are obligated to pay this month. A trailing twelve-month operating statement (T-12) is twelve historical months of actual revenue and expenses. The timing difference matters. The rent roll shows what is scheduled; The T-12 reports historical income and expenses on its stated accounting basis. The OCC handbook advises examiners to consider historical, current and projected rental rates, operating expenses, capital expenditures, and vacancy and absorption rates [1]. To compare totals, align the T-12 period with the rent roll date and identify any months where effective rent differed from scheduled rent due to concessions, free rent periods or bad debt.

Suggested approach: On a separate tab of the workpaper, list each month of the T-12, the gross scheduled rent for that month, the actual collected rent, and the difference. Then calculate a trailing twelve-month average effective rent per unit type. Compare this average to the current rent roll and to the market comparable. The gap between scheduled and collected is a data point, not a problem, but it must be explained.

Distinguishing gross rent, collected income, NOI, capex, debt service and equity cash flow

Each of these metrics serves a different purpose in the financing discussion. Gross rent is the total of all lease obligations before any vacancy or collection loss. Collected income is what actually arrived. NOI is collected income minus operating expenses. Capital expenditures are improvements that extend asset life or increase value, not routine repairs. Debt service is the scheduled principal and interest payments. Equity cash flow is NOI minus debt service and capex reserves.

The OCC handbook states that collateral value is largely determined by a property's NOI, so it is important to analyze whether cash flow and NOI projections are reasonable and supported [1]. A reconciliation workpaper should show each of these line items separately so the lender can see how the pro forma NOI was built. Do not equate a cap rate with a debt constant; a cap rate converts NOI to value, while a debt constant converts loan amount to annual debt service. They are different calculations for different purposes.

Suggested approach: Add a summary tab to the workpaper that shows the pro forma in this order: gross potential rent, vacancy and collection loss at the market rate, effective gross income, operating expenses at the market rate, NOI, annual debt service at the requested loan amount and proposed terms, capital expenditure reserve, and projected equity cash flow. Cite the source for each assumption: the current rent roll, the T-12 average, the market comparable report, or the lender's indicative terms.

Identifying the reporting basis and reconciling periods

Financial statements may be prepared on a cash basis or an accrual basis. The workpaper should state which basis the property's statements use and reconcile to the same basis as the pro forma. If the T-12 is on a cash basis and the pro forma assumes accrual, the difference in timing of revenue recognition must be explained. The OCC handbook does not prescribe a reporting basis, but it does require that the analysis be supportable [1].

Suggested approach: At the top of the workpaper, note the reporting basis of the historical statements, the basis of the pro forma, and any adjustments made to align them. If the property uses cash basis, note that rent received in advance or in arrears may cause month-to-month variation that averages out over twelve months.

Using the reconciliation to inform the financing discussion

The workpaper is not a submission to the lender; it is a preparation tool for the sponsor. When the lender asks why the pro forma rent is higher than the current rent roll, the sponsor can point to the reconciliation and show which units are below market, by how much, and what lease events will bring them to market. The OCC handbook advises that each factor should be considered under both normal and stressed conditions [1]. The workpaper supports that discussion by showing the range of outcomes: what happens if all below-market units renew at market, versus what happens if half turn over at a discount.

The reconciliation also supports the appraisal review. The OCC handbook includes a checklist question asking whether the appraisal analyzed and reported data on current revenues and expenses and whether the projected income and expenses are supportable given the market [1]. A sponsor who has prepared a rent reconciliation can answer that question with evidence before the lender asks.

Suggested workpaper structure

Create a three-tab spreadsheet:

Tab 1: Unit-by-unit reconciliation. Columns: unit ID, type, square feet, current scheduled rent, current collected rent, market comparable rent, difference ($), difference (%), lease expiration, notes on concessions or TI.

Tab 2: T-12 to rent roll comparison. Columns: month, gross scheduled rent, collected rent, difference, cumulative average effective rent.

Tab 3: Pro forma summary. Line items: gross potential rent, vacancy/collection loss, effective gross income, operating expenses, NOI, debt service, capex reserve, equity cash flow. Each line item references a source from Tab 1 or Tab 2 or the market report.

FAQs

How do I find market comparable rents if the appraisal is not yet ordered?

Ask a local commercial broker for a rent comparables report, or use your own knowledge of competing properties. For the workpaper, label the source clearly: broker opinion, CoStar data, or your own survey. The workpaper is a preparation tool, not a certified appraisal. You can update it when the appraisal arrives.

What if my current collected rent is higher than the market comparable?

That is a data point worth explaining. It could mean the property is above-market due to location or amenities, or it could mean the market comparables are not truly comparable. The OCC handbook advises that comparables should be truly comparable with respect to property characteristics and location [1]. The workpaper should note the discrepancy and the reason.

Does the reconciliation guarantee the lender will accept my pro forma?

No. The reconciliation is evidence, not a promise. The OCC handbook states that inadequately supported or questionable analysis should be challenged [1]. A well-prepared reconciliation reduces the chance of that challenge, but the lender's underwriting and approval process is independent. Submission of a workpaper is a preliminary request, not an engagement or lender submission.


We prepare the loan request, take it to the lenders suited to the property and the plan, and negotiate the terms through closing [2]. If you have a property and a business plan, send us the property, the business plan and the capital you need, and we will tell you how we would approach it.

Run the numbers on your deal

Try your own NOI, value and lender tests to see which one limits the loan: DSCR, LTV or debt yield.

Open the loan sizing calculator

Talk through your financing

Working on a real property? Send us the deal and a financing advisor will review it and get back to you. It costs nothing to ask and does not commit you to anything.

Contact us

Requests are reviewed by Wraith Real Estate, a commercial real estate debt advisor (not a lender).

Sources

  1. OCC Commercial Real Estate Lending handbook
  2. Wraith commercial real estate debt