Excel model guide
Updated October 28, 2026
How to structure a multifamily underwriting model in Excel
The best underwriting model is not the one with the most tabs. It is the one your team can audit, update, compare with source documents, and use consistently across decisions.
Direct answer
Separate source facts, assumptions, calculations, and outputs. Use one definition for every KPI, expose the operating and capital bridges, preserve timing, and include checks that fail loudly when the model stops reconciling.
The process
A repeatable way to do the work
- 01
Define the input contract
List required source facts and assumptions with units, dates, signs, provenance, and a clear hierarchy when documents conflict.
- 02
Build operating schedules
Project units, occupancy, rent, other income, expenses, capex, and NOI through explicit schedules rather than opaque hardcodes.
- 03
Add debt and waterfalls
Model financing and ownership economics with dated cash flows and definitions that match the legal terms.
- 04
Design decision outputs
Summarize basis, NOI, returns, debt metrics, sensitivities, and sources in a compact review surface.
- 05
Install audit checks
Test sources and uses, unit totals, cash flow roll-forwards, debt balances, formula consistency, and error cells.
A copy-ready Excel layout for the first screen
Create a worksheet named Screen and put the labels below in column A and their inputs or formulas in column B. Keep the source, effective date, and adjustment explanation in columns C through E beside each input. This is a starter calculation layout. The full acquisition model still needs dated operating, capital, financing, and sale schedules.
Enter annual dollar amounts rather than mixing monthly rents with annual expenses. Enter vacancy, concessions, credit loss, and operating expenses as positive deductions. If the rental income input already includes those losses, do not deduct them a second time. Leave unavailable inputs blank until supported. An observed zero should be entered as the number zero.
- Format B16 as a percentage, B17 as a multiple, and dollar values as currency.
- A blank result means the stated prerequisite is missing or the denominator is not positive. It does not mean the deal passed.
- Use the same NOI period and definition for the cap rate and DSCR. Label reported history separately from recast or forecast operations.
- These formulas do not calculate IRR, equity multiple, a renovation plan, a waterfall, or the proceeds available at sale.
| Cell | Label | Input or Excel formula |
|---|---|---|
| B5 | Purchase price ($) | Enter asking or negotiated price; name the basis in column C |
| B6 | Rentable unit count | Enter the reconciled unit count |
| B7 | Annual scheduled rental revenue ($) | Enter before vacancy, concessions, and credit loss |
| B8 | Annual rental revenue losses ($) | Enter vacancy, concessions, and credit loss, once |
| B9 | Annual other recurring income ($) | Enter a supported value, including zero when verified |
| B10 | Annual operating expenses ($) | Exclude debt service, depreciation, and capital spending |
| B11 | Annual debt service ($) | Enter principal and interest for the stated loan period |
| B14 | Effective gross income ($) | =IF(COUNT(B7:B9)=3,B7-B8+B9,"") |
| B15 | Net operating income ($) | =IF(AND(ISNUMBER(B14),ISNUMBER(B10)),B14-B10,"") |
| B16 | Going-in cap rate | =IF(AND(ISNUMBER(B15),ISNUMBER(B5),B5>0),B15/B5,"") |
| B17 | DSCR | =IF(AND(ISNUMBER(B15),ISNUMBER(B11),B11>0),B15/B11,"") |
| B18 | Price per unit ($) | =IF(AND(ISNUMBER(B5),ISNUMBER(B6),B5>0,B6>0),B5/B6,"") |
Start with model architecture
A maintainable workbook typically separates setup and source inputs from operating schedules, capital, debt, returns, and outputs. The names are less important than the boundaries. When a fact from the rent roll is mixed into the same cells as a growth assumption and a return formula, review becomes guesswork.
Use a consistent visual language for sourced inputs, underwriting assumptions, formulas, and links—but do not rely on color alone. Each material input should also carry a label, unit, period, and source or rationale.
| Model layer | Purpose | Minimum control |
|---|---|---|
| Sources / inputs | Preserve facts and assumptions | Source, date, unit, sign |
| Operations | Build revenue, expense, and NOI | Unit and annual totals tie |
| Capital / debt | Time uses and financing cash flows | Sources equal uses; debt rolls |
| Returns / outputs | Support the investment decision | Metrics tie to cash flow |
Model the business plan at the level decisions are made
If renovation pace drives value, show units entering renovation, downtime, cost, and premium by period. If lease rollover drives value, model expiration and renewal cohorts. If expense efficiency drives value, show the specific accounts and timing. A single stabilized growth rate may be fast, but it hides the mechanism the team must execute.
Balance granularity with usability. Unit-level detail is useful for near-term leasing; long-dated forecasts can often roll to unit types or annual assumptions. The model should become simpler as uncertainty grows, not manufacture false precision.
- Make year one sensitive to actual lease and closing dates.
- Keep acquisition, operating, capital, and disposition cash flows distinct.
- State whether metrics are levered or unlevered and before or after fees.
- Centralize scenario controls so base, downside, and upside do not drift apart.
Checks belong in the decision surface
A hidden audit tab that nobody reviews is weak protection. Surface failed checks near the relevant output and stop downstream conclusions from appearing final when a critical reconciliation is broken. Distinguish hard errors from warnings: a sources-and-uses imbalance should block review, while a missing market-rent source may remain visible as an unresolved assumption.
Version the key assumptions and output summary when the deal advances. This makes it possible to explain why returns changed between initial screen, LOI, diligence, and final IC instead of comparing two opaque workbook files.
- Clear B9: effective gross income and NOI should become blank until other income is verified. Entering a supported zero should restore the calculation.
- Increase operating expenses by $10,000: NOI should fall by exactly $10,000, with debt service unchanged.
- Set debt service to zero: DSCR should remain blank because the coverage ratio is not applicable to a debt-free case.
- Change purchase price while holding operations constant: cap rate and price per unit should change; NOI should not.
- Review all expense additions for overlapping scope before relying on the resulting NOI.
FAQ
Frequently asked questions
What tabs should a multifamily underwriting model include?
A typical model includes setup or inputs, rent roll or unit mix, operating projection, renovation or capex, debt, cash flow and returns, sensitivities, and a summary. The boundaries and auditability matter more than exact tab names.
Should I use a downloaded Excel template for underwriting?
A template can accelerate setup, but it should be adapted to your firm's definitions, approval process, and source requirements. Verify formulas, signs, timing, debt mechanics, and return conventions before relying on it.
How do you audit a real estate underwriting model?
Trace material outputs back through formulas to sourced inputs, test reconciliation checks, review formula consistency, inspect timing and sign conventions, and reproduce core metrics independently.
Continue the workflow
Populate your existing Excel underwriting model
See how Rook maps source documents into the workbook your firm already uses.
Read guideMultifamily underwriting checklist
A stage-gated diligence list for screening and full underwriting.
Read guideCommercial real estate IC memo template
Write a decision document that ties every recommendation back to evidence.
Read guide