The Real Estate Pro Forma Modeling Master Class

Build dynamic real estate pro forma models by linking inputs to formulas. Generate monthly and annual cash flows and investment returns, with time-weighted returns.

master real estate pro forma modeling in Excel for Windows with an interactive, step-by-step approach, using placeholders, foundational finance knowledge, and practice with downloadable sections.

Open a blank Excel workbook, turn off gridlines, set a clean font size, configure partial calculation with iterative options, and save to begin the summary dashboard.

Create and format a property details table in the summary tab, using center across selection, blue for manual inputs, and formulas for average unit size and units per acre.

Create a parking information table to record uncovered spaces, carport spaces, and garage spaces; compute total spaces and total spaces per unit with a dynamic sum formula.

Add acquisition information, including purchase price, closing costs, upfront working capital, and escrow closing date, to prepare for hold period financing and potential operating shortfalls or future construction costs.

Build the acquisition loan information table, tracking loan amount, LTV, going-in DSCR and debt yield, plus interest rate, interest-only period, loan fee, amortization, and term with formulas.

Learn to add a refinance information section to a real estate pro forma, detailing refinance month, loan amount, cap rate, LTV, dscr, debt yield, and interest terms.

Consolidate key deal metrics into a summary details table, including purchase price, going in cap rate, price per unit, and total project cost, plus stabilized yield on cost.

Learn to build and format a renovation details table with total construction costs, per unit costs, exterior and interior renovation budgets, and return on cost.

Build sale details table in the pro forma. Include exit cap rate, cap rate expansion or compression, sale price, sale price per unit, cost of sale, and annual growth rate.

Build and balance sources at close and uses at close by detailing equity, acquisition loan proceeds, closing costs, upfront working capital, loan fees, then post close equity and return metrics.

Build a sale information table in real estate pro forma modeling, including closing costs, hold period, and exit cap rate, then project levered IRR, equity multiple, and cash on cash.

Explore revenue modeling for real estate pro forma, detailing current in-place rents, assumed stabilized rents, rent increase timing, other income projections, vacancy, and market rent growth in the revenue tab.

Create an excel in-place rents pro forma that supports up to 20 unit types, using placeholders, computes dollar per square foot, weighted averages with sum product, and iferror for div-zero.

Build the stabilized rents table from the current in-place rents, calculate per-unit renovation premiums, and apply dynamic formulas that reflect unit count and square footage.

Create an other income per unit per month table for a multifamily property, including late fees, pet fees, and storage income, with placeholder blue inputs and a calculated total.

Build and format a rent increase timing table that captures start and end months, computes the total period in months, and labels months for accurate rent projections.

Learn to build year-by-year economic vacancy, credit loss, concessions, and growth assumptions for multifamily pro forma, including market rent and other income growth across an 11-year cashflow model.

Expand the rent model to monthly projections, calculating in-place and stabilized rents across 132 months, applying market rent growth, and tracking stabilized unit percentages and gross potential rental revenue.

Build month-one in-place and stabilized rent formulas, then apply annual market rent growth with HLOOKUP and compounding to project gross potential rental revenue.

Compute the percentage of stabilized units over time with Excel formulas and rent increase timing to derive monthly gross potential rental revenue.

Model the T12 operating expenses in the real estate pro forma, detailing payroll, admin, marketing, repairs, utilities, taxes, insurance, and management fees, with dollars per unit per year.

Develop hold period operating expenses from the T12 table to drive cash flows using per unit per year calculations, and adjust line items like payroll, administrative costs, marketing, and utilities.

Create a property tax information table with millage rate, fixed charges, and reassessment triggers to influence net operating income and exit value.

Build a ten-year property tax growth rates table in year zero format, applying annual percentage assumptions to assessed value and fixed charge assessments for monthly cash flow projections.

Design a hold-period property tax projection in a spreadsheet, detailing tax rate, growth of assessed value, fixed charge assessments, and total property taxes, with reassessed upon purchase logic.

Build the construction budget tab in the real estate pro forma, detailing exterior and interior capital budgets with line items, totals, and a month-based timeline.

Format currency values, set up per-unit totals, and define month start and end for construction cost projections in the real estate pro forma modeling master class.

Build a construction timeline for cost projections by creating month and year headers, copying revenue data, applying dynamic year calculations with the round up function, and formatting for cash flow.

Build a reusable Excel formula to model exterior construction cash flows by month, using an if and and logic to allocate straight-line costs within the construction window and update subtotals.

Set up a checks column in Excel to validate construction cash flows using an absolute difference under 1 cent, returning OK or error, with conditional formatting to highlight results.

Copy exterior capital data into the interior budget, switch totals to per unit values, and model 24-month interior cash flows for multifamily renovations with monthly start and end dates.

Build the contingency as a buffer over exterior and interior capital costs, set a 10% rate, and calculate monthly contingencies, per unit figure, and start/end months using min and max.

Build the total capital budget by summing exterior capital budget, interior capital budget, and contingency. Then finalize construction budget tab and move to the financing tab for debt-related cash flows.

Build a debt model by creating a financing sheet to track acquisition and refinancing cash flows, including loan payments, principal and interest, and payoff timing.

Model starting balance and monthly payments for acquisition financing and refinancing, detailing the total loan payment, principal and interest portions, loan payoff, and ending balance across months.

Build a month-by-month loan payoff model in real estate pro forma, using conditional payoff logic, ending balances, and principal-plus-interest components to track payoff at the correct term.

Develop and validate financing checks in a real estate pro forma, using max and sum functions to verify starting loan balance, payments, and payoff with an if test.

Build out refinancing calculations in the pro forma, preserving formulas, and harden the model with a fresh refinancing table, starting balances, and conditional logic for hold, sale, and refinance month.

Compute total loan payment by analysis month and whether in an interest-only or refinanced period, using nested ifs and PMT in Excel.

Learn to model refinance scenarios by building principal and interest calculations, handling refinance months with zero interest, and projecting loan payoff and monthly cash flows across hold periods.

Build monthly and annual cash flow projections for revenue, operating expenses, debt service, and financing metrics in a real estate pro forma model, including acquisition timing.

Format and build the real estate pro forma in Excel, detailing gross potential rental revenue, physical vacancy, credit loss, concessions, and other loss to derive net rental income.

Learn to model other income per unit per month and apply annual growth to generate dynamic month-by-month revenue, culminating in accurate effective gross revenue (EGR) for a pro forma.

Demonstrate operating expense modeling in a real estate pro forma by building dynamic monthly projections, including capital expense reserves and linked expenses with growth from month two.

Model property management fees as a percentage of effective gross revenue in monthly cash flows, and calculate potential reassessed property taxes upon sale using hold period and sale price.

Learn how to model insurance costs and capital expense reserves in a real estate pro forma, applying annual growth rates, annual resets, and month-by-month cash flow calculations.

Learn to model capital expenses in a real estate pro forma by linking construction costs, calculating unlevered working capital and cash flow before debt service.

Model debt service by totaling interest and principal payments, apply levered working capital, and calculate cash flow after debt service within the real estate pro forma.

Learn to build acquisition and sale information in a real estate pro forma, modeling purchase price, closing costs, upfront working capital, sale proceeds, costs of sale, and unlevered cash flow.

Model loan information in a real estate pro forma, including upfront loan proceeds and fees, monthly loan payoffs, and the total levered cash flow, with refinance timing and hold-period logic.

Explore how to model working capital balances and distributions within real estate pro forma analyses, enabling accurate cash flow projections and capital allocation decisions.

Learn to finalize the monthly cashflow tab in real estate pro forma modeling using SUMIF to sum cash flows up to the projected hold period sale month.

Duplicate the monthly cash flow tab to build the annual cash flow tab, adjust year setup, and apply a single dynamic sumifs formula across all line items.

Check working capital ending balance formulas, build annual cash flow from monthly data, and finalize the pro forma with checks and a downloadable model.

Finalize the real estate pro forma by validating expenses, calculating net rentable sf and going-in cap rate, and evaluating acquisition loan metrics using net operating income, dscr, and debt yield.

finalize the refinance information table using sumifs to compute the forward 12 months net operating income, project value with cap rate and loan-to-value, and derive DSCR and debt yield.

Compute total project costs from purchase price, closing costs, construction costs, and loan fees, then determine stabilized yield on cost from net operating income projections; examine renovation budgets and contingency.

Compute exit cap rate dynamics from K18, estimate cap rate expansion over the seven-year hold, and derive sale price, cost of sale, and value CAGR with Excel's rate function.

Finalize sources and uses and compute post-close additional equity with sumif and absolute value. Compute levered IRR with XIRR and levered equity multiple and levered average cash on cash return.

Learn how to compute levered cash-on-cash return across the hold period by modeling annual cash flow, debt service, and equity contributions in Excel.

Create a checks tab to verify monthly cash flows equal annual cash flows, ensure equity aligns with negative cash flows, and confirm construction funding and loan balances are accounted.

Master the real estate pro forma checks by validating equity, negative cash flows in the monthly cash flow tab, construction budgets, loan payoffs, and working capital distributions.

Learn to use the pro forma by adjusting blue inputs, see real-time changes to IRR, levered IRR, and cash-on-cash, and explore effects of purchase price, loan terms, and hold period.

Improve your proforma modeling through practice, building muscle memory for diverse scenarios. Customize templates, embrace iterative models, and practice to master real estate proforma across deals.