Multifamily Pro Forma in Excel: The Complete Underwriting Walkthrough

Multifamily Pro Forma in Excel: The Complete Underwriting Walkthrough

Multifamily is the asset class where most investors learn to underwrite, and Excel is where multifamily gets underwritten. This walkthrough covers the complete workflow for an apartment acquisition — from the rent roll and the trailing twelve months through the NOI build, debt sizing, the 10-year projection, and the return metrics — in the order an institutional analyst would run it.

Two ways to use this guide. If you are building your own apartment building pro forma from a blank workbook, this is the map; the companion tutorial on building a multifamily pro forma from scratch supplies the cell-level mechanics. If you are working inside a multifamily acquisition model — ours or anyone's — this is the checklist of what each stage must accomplish and where deals go wrong.

All figures below are illustrative examples, not market data.

Why Multifamily Underwriting Is Its Own Discipline

A generic income-property model treats revenue as a number. Multifamily underwriting treats revenue as a system — dozens or hundreds of small, similar leases rolling continuously — and that structure changes the analysis in three ways:

Revenue is statistical. With many short leases, vacancy behaves like a smooth percentage rather than a binary event, and rents reprice toward market constantly. The gap between today's rents and market rents is measurable, and closing it is usually the deal.

The seller's numbers are a starting point, not an answer. Every offering memorandum contains a pro forma. Your job is to rebuild it from source documents, because the seller's version was constructed to justify the asking price.

Operations drive value directly. At a given cap rate, every dollar of NOI you add is worth many dollars of value. That arithmetic is why multifamily underwriting spends so much effort on the expense build and the revenue levers — small operating improvements compound into large valuation moves.

Stage 1: Assemble the Source Documents

Underwriting begins with three documents, requested in diligence and trusted in that order:

1. The rent roll — every unit, its type, square footage, current rent, and lease status. This is ground truth for in-place revenue.
2. The trailing twelve months (T-12) — actual monthly income and expenses for the past year. Ground truth for operations.
3. The offering memorandum — read for the story and the unit mix, audited for everything else. Where the OM's pro forma diverges from the T-12, the divergence is the analysis: it tells you exactly which assumptions the price depends on.

If a seller resists producing a current rent roll and a real T-12, that is diligence information too.

Stage 2: Build the Unit Mix and Rent Roll

The rent roll tab is the revenue engine of a multifamily excel model, and it needs two rent columns per unit type, not one:

• In-place rent — what each unit actually collects today, from the rent roll.
• Market rent — what the unit would command re-leased today, from your comp work.

Roll the mix up to two versions of gross potential rent (GPR): in-place GPR and market GPR. The difference is loss to lease — and in a value-add deal, loss to lease is the thesis stated as a number. An illustrative 48-unit property with in-place rents averaging $1,150 against market rents of $1,300 carries a loss to lease of $150 × 48 × 12 = $86,400 per year of theoretically capturable revenue. The pro forma's job is to model how much of that is actually capturable, at what cost, on what timeline.

Structure the tab so the unit mix drives everything: unit type, count, square footage, in-place rent, market rent, with per-square-foot rents computed as a sanity column. Every downstream revenue number should trace back here.

Stage 3: The Economic Vacancy Stack

Beginner models deduct one vacancy percentage. Institutional multifamily models deduct a stack, because the space between GPR and collected revenue has several distinct leaks:

• Physical vacancy — units empty between leases
• Loss to lease — occupied units paying below market (if you built GPR at market rents)
• Concessions — free rent and move-in incentives
• Bad debt / credit loss — billed rent never collected
• Non-revenue units — models, offices, employee units

Each line gets its own assumption, grounded in the T-12's actual history, and the sum is economic vacancy — routinely several points wider than physical vacancy alone. Add other income (fees, utilities reimbursement, laundry, parking, pet rent — multifamily's quiet second revenue stream) and the result is effective gross income (EGI). Underwriting to physical occupancy while the T-12 shows chronic concessions and bad debt is one of the most common ways buyers overpay.

Stage 4: Normalize the T-12 — the Bridge

This stage is what separates underwriting from arithmetic. The T-12 tells you what the property did under the seller's ownership; the pro forma must project what it will do under yours. The connection between them is a line-item bridge — the T-12 to pro forma NOI bridge — and every adjustment on it should be explainable in one sentence:

• Real estate taxes reset. In many jurisdictions, assessed value follows the sale. Underwrite taxes on your purchase price and local rules, not the seller's legacy assessment. This is frequently the largest single adjustment in the bridge, and omitting it flatters NOI badly.
• Insurance re-quotes. Your premium, not the seller's.
• Management at market. Model a market-rate management fee even if you self-manage; lenders will underwrite one regardless, and your time is not free.
• Remove owner artifacts. One-time repairs, personal expenses routed through the property, below-market payroll from an owner-operator doing the work personally.
• Add what is missing. Chronically deferred maintenance shows up as suspiciously low R&M in the T-12 — normalize it up.

The output is two NOI figures the model should display side by side: in-place NOI (the property as it operates today, honestly stated) and stabilized pro forma NOI (the property after your business plan). The going-in cap rate prices the first; your returns depend on reaching the second. A model that shows only one number is hiding the distance between them.

Stage 5: The Expense Build and Its Benchmarks

Enter operating expenses line by line — taxes, insurance, repairs and maintenance, turnover, utilities, payroll, management, administrative, marketing — and check every line two ways:

• Per unit per year. The native benchmark unit of multifamily. Every category has a plausible per-unit range for a given vintage and market, and entries far outside it are either errors or stories that need telling.
• As a percentage of EGI. The aggregate expense ratio is the whole-building sanity check; a pro forma whose ratio is dramatically leaner than the T-12's needs a specific, funded explanation for every point of improvement.

Resist the single most tempting shortcut in multifamily underwriting: assuming expense efficiencies because "the seller was mismanaging." Sometimes true — but every claimed saving should map to a concrete mechanism, or it is just NOI inflation with better manners.

Stage 6: The Value-Add Module

If the plan includes renovations, the pro forma needs a capital module that connects three quantities honestly:

1. The renovation budget — per-unit cost × units renovated, phased over a realistic timeline (you cannot renovate 48 units in a quarter while keeping the property occupied).
2. The rent premium — the market-supported uplift a renovated unit commands, evidenced by comps of renovated product, not wishful spreads.
3. The capture assumption — how much of the theoretical premium you actually achieve, at what lease-turnover pace, with what vacancy drag during construction.

The module should phase renovated-unit income in as units turn, not flip the whole property to stabilized rents in month one. The distance between "premium assumed instantly" and "premium captured on turnover over 24 months" is often the distance between the deal working and not. For the full treatment — budget, premiums, and stabilization math on a worked repositioning — see the value-add multifamily model case study.

Stage 7: Size the Debt

Multifamily loan sizing runs against two constraints simultaneously, and the model should compute both:

• Loan-to-value (LTV): a ceiling as a percentage of price or appraised value.
• Debt service coverage ratio (DSCR): the requirement that underwritten NOI cover annual debt service by a stated multiple.

Proceeds are the lesser of the two. Which constraint binds is itself diagnostic: LTV-constrained deals are pricing-limited; DSCR-constrained deals are income-limited, and in higher-rate environments coverage frequently becomes the binding test. The model should also carry the amortization schedule through the hold — you need annual debt service for the cash flows, DSCR by year for covenant awareness, and the payoff balance at exit. To see the coverage math in isolation before wiring it into a full model, run your numbers through the DSCR calculator walkthrough.

One multifamily-specific wrinkle worth modeling: value-add deals often use bridge financing with an interest-only period, refinancing into permanent debt at stabilization. If that is the plan, model it as the plan — an IO period, a refinance event at the stabilized value, and new permanent debt — rather than pretending one loan spans the hold.

Stage 8: Project the 10-Year Cash Flow

With the NOI engine and debt in place, extend the model across ten annual columns. Multifamily-specific discipline points:

• Grow rents and expenses at independent rates. Margins that silently expand for a decade are a modeling artifact, not a business plan.
• Phase the business plan explicitly. Years one and two of a value-add deal look nothing like stabilized years — renovation spend, elevated vacancy, ramping premiums. Model the ramp.
• Hold economic vacancy honest post-stabilization. Stabilized does not mean perfect.

The final year carries the reversion: forward NOI capitalized at an exit cap rate assumed conservatively relative to your going-in cap, less selling costs and the loan payoff. A multifamily pro forma whose return depends on exit-cap compression is making a market call, not underwriting a property — the model should make that visible, not bury it.

Stage 9: Read the Returns

From the equity cash flows, compute the full suite and read them together:

• Levered and unlevered IRR — the spread shows what the financing contributes and risks.
• Equity multiple — magnitude alongside IRR's speed.
• Cash-on-cash by year — the income story, which in value-add deals is typically thin early and is supposed to be; the model's job is to show the ramp honestly.
• Stabilized yield on cost versus market cap rate — the development-style check on value-add deals: total cost (price plus renovation) against stabilized NOI, compared to where stabilized assets trade. The spread is your margin for being wrong.

If the deal is syndicated, one more layer sits below the deal-level returns: the GP/LP split. A complete multifamily acquisition model carries the equity waterfall — preferred return, return of capital, promote — so the sponsor and investor returns are computed in the same file as the deal, from the same cash flows.

Stage 10: Stress It

Finish with the sensitivity grid: a 5×5 matrix of levered IRR across entry cap × exit cap, or rent growth × exit cap. Then read it the way a committee would — not "what is the number," but "how fast does the number degrade, and where does it break." For value-add specifically, add one more stress that grids often miss: what happens if the rent premium captures at a fraction of pro forma, or a year late. If the deal only clears your hurdle when every lever lands perfectly, the pro forma has delivered its most valuable output: a no.

Laying It Out in Excel

The workflow above implies a tab architecture, and following it keeps the model auditable: an assumptions tab holding every input in one place; the rent roll; operating expenses; the T-12 bridge; debt sizing; the 10-year cash flow; returns; the waterfall if syndicated; the sensitivity grid; and a one-page investment summary for lenders and LPs. Inputs visually distinct from formulas, no hardcoded values inside calculations, and every tab feeding forward in the order underwriting actually runs. When a partner questions a number, you should be able to trace it from the summary back to a source-document input in under a minute — that traceability is the entire point of the structure.

The Multifamily-Specific Mistakes

Beyond the universal pro forma errors, apartment underwriting has its own recurring failures:

1. Underwriting the OM's pro forma instead of rebuilding from the T-12.
2. Ignoring the tax reset on sale — often the largest bridge item.
3. Physical vacancy instead of economic vacancy — concessions and bad debt are real income losses.
4. Instant rent premiums — renovation income captured on day one instead of phased on turnover.
5. Expense "efficiencies" with no mechanism — savings claimed because the buyer is smarter than the seller.
6. Comping unrenovated market rents against renovated product — or vice versa.
7. One loan across a business plan that requires two — modeling permanent debt on a deal that needs bridge-to-perm.

Frequently Asked Questions

What is the difference between a multifamily pro forma and an apartment pro forma template? Nothing substantive — the terms are used interchangeably. What matters is whether the file implements the workflow above: unit-mix rent roll with in-place versus market rents, an economic vacancy stack, a T-12 bridge, dual-constraint debt sizing, and a 10-year DCF with sensitivity.

How many years should a multifamily pro forma project? Ten, by institutional convention — with a projection of year eleven NOI to price the reversion correctly. Value-add plans that stabilize in two to three years still get modeled across the full hold.

What occupancy should I underwrite? Whatever the T-12 and submarket evidence support — as economic occupancy, net of concessions and bad debt, not just physical occupancy. The honest answer comes from the property's history, not from a rule of thumb.

Do I need a separate model for value-add versus stabilized deals? No — a properly built acquisition model handles both, because a stabilized deal is simply a value-add model with the renovation module set to zero. What you cannot do is the reverse: force a value-add plan through a model with no capital-phasing logic.

Can I underwrite multifamily without Excel? Excel remains the professional default because counterparties — lenders, LPs, brokers — expect to receive, audit, and interact with the workbook. Whatever tool you analyze in, the deliverable that circulates is almost always a spreadsheet.

Run the Whole Workflow in One File

Every stage in this walkthrough maps one-to-one onto The Multifamily Sheet: the unit-by-unit rent roll with in-place versus market rents, the economic vacancy build to EGI, the T-12-to-pro-forma NOI bridge, line-item expenses with per-unit and %-of-EGI checks, debt sized to the lesser of LTV and DSCR with a full amortization schedule, the 10-year cash flow with reversion, levered and unlevered returns with a GP/LP waterfall, and the entry-cap × exit-cap sensitivity matrix — fully unlocked, formula-transparent, with a documented methodology PDF and a lender-ready one-page investment summary.

For heavier diligence — deeper T-12 normalization and unit-level analysis on larger or messier deals — the Multifamily Underwriting Suite extends the same conventions, and the full catalog is in the store.

This article is for educational purposes only and does not constitute investment, legal, or tax advice. All deal figures are illustrative examples, not market data. Consult qualified professionals before making investment decisions.