Real Estate Underwriting Model: How to Build an Excel Spreadsheet
By Maurice L. Naylon IV, CPA
A real estate underwriting model should make a deal easier to question. It should show where each important assumption came from, how the property produces cash, what the financing requires, and which variables control the projected return. If the workbook only produces an attractive internal rate of return, it is not doing enough.
The common problem is not an incorrect formula. It is a workbook that mixes facts, assumptions, and calculations until no one can tell which number changed or why. Historical income may sit beside a broker projection. Debt service may be hard-coded. Renovation spending may appear in the purchase budget but disappear from the cash-flow schedule. The model can still balance while the investment logic is incomplete.
A disciplined underwriting spreadsheet has a visible structure: source data, assumptions, operating forecast, capital, debt, sale, investor cash flow, sensitivities, and checks. Build those pieces in that order. Then make the summary report what the schedules calculate rather than becoming a second model.
Working rule: Every material output should trace to a formula, every formula should trace to a labeled input or schedule, and every important input should trace to a source or stated judgment.
Start With the Decision, Not the Tabs
Before opening Excel, write down the decision the model must support. Are you deciding whether to submit an offer, choosing between two financing structures, testing a renovation program, or presenting an investment to partners? The answer determines the required detail and time periods. If the decision process is not yet defined, start with how to analyze a real estate deal.
A quick acquisition screen used when buying an investment property may use annual periods and a limited set of assumptions. A value-add multifamily deal may require monthly renovation, unit downtime, lease-up, and financing schedules. A development or major repositioning model may need construction draws, interest carry, and staged occupancy. More detail is useful only when it changes a decision or reveals risk.
Decision question | Model response |
What can the property earn? | Historical normalization and operating forecast |
How much capital is required? | Sources and uses plus future capital schedule |
What can the property support? | Debt sizing and coverage calculations |
What is the investment worth? | Direct capitalization and sale or DCF analysis |
What can equity receive? | Levered cash flow and return calculations |
What can go wrong? | Sensitivity tables and coherent downside cases |
Use a Workbook Architecture That Can Be Audited
The exact tab names can vary, but the calculation flow should be obvious. Keep raw or transcribed source data separate from forecast assumptions. Do not bury debt terms inside an operating formula or place sale assumptions inside an investor-return table.
Tab | Purpose | Key control |
Read Me | Scope, version, conventions, and model limitations | Identifies owner and review date |
Sources | Rent roll, trailing statements, leases, taxes, insurance, and market evidence | Records source date and unresolved items |
Assumptions | Purchase, operations, capital, debt, and exit inputs | Contains inputs, not calculated outputs |
Operations | Revenue, vacancy, other income, expenses, and NOI | Reconciles history to forecast |
Capital | Initial and future improvements, timing, contingency, and reserves | Links spending to cash flow |
Debt | Loan sizing, amortization, interest, fees, and payoff | Opening balance plus draws less principal equals ending balance |
Returns | Unlevered and levered cash flows, IRR, equity multiple, and cash yield | Uses one signed cash-flow series |
Sensitivity | Key variable combinations and complete downside cases | Reports the same outputs as the base case |
Summary | Decision, price, financing, returns, risks, and open diligence | Contains no independent assumptions |
Step 1: Build a Source and Assumption Log
List each material input, its value, unit, source, source date, model location, owner, and status. Sources may include the rent roll, general ledger, trailing operating statement, tax bill, insurance quote, service contracts, inspection report, lender term sheet, market study, and investor business plan. Distinguish received evidence from an analyst estimate.
A source log does not make an assumption correct. It makes the assumption reviewable. If property taxes were copied from a broker memorandum, the reviewer should see that immediately. If insurance is a current quote, the model should preserve the quote date and coverage context. If rent growth is judgment, label it as judgment and test it.
Step 2: Separate Inputs, Formulas, and Links
Use one consistent visual convention. Many analysts use blue font for hard-coded inputs, black for formulas, and green for links from another workbook. A restrained cell fill can work as well. Whatever convention you choose, document it and apply it consistently.
Never hard-code a material assumption inside a formula. Instead of multiplying rent by 1.03, point the formula to a labeled 3.00% growth cell. Avoid duplicate inputs. Purchase price, loan rate, exit capitalization rate, and hold period should each have one controlling location unless the model intentionally uses different cases.
Use data validation for bounded or categorical inputs when it reduces accidental entries. For example, a case selector can use Base, Downside, and Upside. A yes-or-no switch can control an interest-only period. Validation is a control, not a substitute for reviewing the assumption.
Step 3: Build Sources and Uses
Sources and uses establishes the investment basis and funding requirement. Uses may include purchase price, closing costs, financing costs, immediate repairs, renovation, escrows, working capital, and initial reserves. Sources may include senior debt, subordinate financing, seller financing, and equity. Total sources must equal total uses.
Illustrative uses | Amount | Illustrative sources | Amount |
Purchase price | $6,000,000 | Senior loan | $3,900,000 |
Closing and financing costs | $120,000 | Investor equity | $2,470,000 |
Immediate capital | $250,000 |
|
|
Total uses | $6,370,000 | Total sources | $6,370,000 |
In this hypothetical example, equity is calculated as the remaining funding requirement. It is not a plug hidden in another schedule. If loan proceeds change, the required equity should change automatically.
Step 4: Build Historical and Forecast Operations
Start the operating schedule with units or rentable area and the economic drivers that create revenue. For multifamily, that may mean unit count, monthly rent, loss-to-lease, vacancy, concessions, bad debt, and other income. For commercial property, lease terms, reimbursement structures, rollover, downtime, tenant improvements, leasing commissions, and renewal assumptions may require tenant-level schedules.
Present historical results beside the forecast when possible. Reconcile the rent roll to the operating statement and cash collections. The real estate accounting guide explains how the underlying records should separate operations, capital, financing, and ownership activity. Normalize expenses line by line. Property taxes, insurance, management, payroll, utilities, repairs, contracts, and administrative costs do not necessarily continue at the seller’s reported level after acquisition.
Calculate net operating income under a clearly stated convention. Keep debt service, depreciation, income taxes, owner distributions, and most capital expenditures outside NOI. If lender underwriting uses a replacement reserve or other adjustment, show the lender convention separately instead of quietly changing the investment NOI.
Step 5: Model Capital and Timing
Capital spending affects cash even when it is outside NOI. Create a schedule with project, cost, start, completion, contingency, funding source, downtime, and expected operating effect. Connect renovation timing to unit availability and rent changes. Do not assume that every dollar of planned renovation is spent on day one or produces higher rent immediately.
Separate initial work funded at closing from future capital funded through operations, reserves, loan draws, or additional equity. The return schedule should capture the actual timing of each cash outflow.
Step 6: Build Debt as a Schedule
Enter loan amount, interest rate, amortization period, payment frequency, interest-only period, term, maturity, fees, reserves, covenants, extension assumptions, and payoff timing. Use a complete amortization schedule. The ending balance should equal the opening balance plus new draws and capitalized interest, less principal payments and payoff.
For the illustrative $3,900,000 loan at 6.25% with 30-year amortization, monthly principal and interest are approximately $24,013 and annual debt service is approximately $288,156. Dividing the illustrative $392,700 Year 1 NOI by annual debt service produces approximately 1.36x DSCR. A lender may use different income, expenses, reserves, stress rates, and sizing constraints.
Step 7: Calculate Sale Proceeds and Returns
Model the sale with the correct forward NOI, exit capitalization rate, selling costs, and debt payoff. If the property sells at the end of Year 5 using Year 6 NOI, label that convention. A one-period mismatch can materially overstate value.
Build unlevered cash flow before financing and levered cash flow after debt activity. Then calculate metrics from the appropriate series. Cash-on-cash return normally compares a period’s distributable cash with invested equity under the stated convention. Equity multiple compares total equity inflows with total equity outflows. IRR incorporates timing, but it can conceal scale and interim liquidity risk. Report several measures rather than letting one output become the investment thesis.
Step 8: Add Checks Before Sensitivities
Model checks should be visible and specific. A green “OK” is useful only when the reader can see what was tested and the tolerance. Checks should identify the failing schedule, not simply announce that something is wrong.
Check | Expected result |
Sources less uses | $0 |
Beginning cash plus inflows less outflows | Ending cash |
Opening debt plus draws less principal and payoff | Ending debt |
Unit or area detail | Property control total |
Historical revenue and expense detail | Reported statement total |
Sale price less costs and loan payoff | Net sale proceeds |
Equity cash-flow series | Matches return calculations |
Base-case selector | Matches displayed assumptions |
Step 9: Build Sensitivities That Answer Decisions
Begin with variables that can change price, liquidity, or return: purchase price, achievable rent, vacancy, expense growth, renovation cost and pace, loan proceeds, interest rate, exit capitalization rate, and sale timing. A two-variable table is useful for seeing how two assumptions interact, such as rent growth and exit cap rate.
Do not stop with independent sensitivities. Build at least one coherent downside case. Slower renovation may produce lower rent growth, more vacancy, additional interest, and a later sale at the same time. A complete scenario tests whether the investment can remain funded and comply with its financing - not merely whether the IRR declines.
Step 10: Design a Decision Ready Summary
The summary should state the property, strategy, proposed price, total equity, financing, base returns, downside results, principal risks, open diligence, and recommendation. It should report from the schedules. If a reviewer can change a return directly on the summary, the model has two sources of truth.
Show the assumptions that control the conclusion. A summary that displays a 17% IRR but omits the rent-growth, renovation, leverage, and exit assumptions is advertising, not analysis. Include enough context for a reviewer to disagree intelligently.
Common Spreadsheet Errors
· Hard-coding debt service, sale value, or growth inside formulas.
· Using the seller’s expenses without explaining normalization.
· Treating renovation spending as a source-and-uses item but omitting its timing from cash flow.
· Applying an exit capitalization rate to the wrong NOI period.
· Mixing annual and monthly rates or cash flows without conversion.
· Calculating IRR from a range that omits later equity contributions.
· Allowing summary cells to become independent assumptions.
· Hiding errors with IFERROR instead of resolving the underlying logic.
· Using one optimistic case without checking liquidity and loan maturity.
· Sending a workbook without source notes, version control, or a written decision.
A Practical Model Review Checklist
· Define the decision, property, business plan, and required period detail.
· Index source documents and identify missing evidence.
· Use one controlling cell for each material assumption.
· Separate source data, inputs, calculations, and outputs.
· Reconcile historical statements, rent or lease data, and collections.
· Build sources and uses so equity is calculated transparently.
· Model operations, capital, debt, sale, and equity cash flow in sequence.
· Label NOI, cash flow, value, and return conventions.
· Add visible balance, roll-forward, and control-total checks.
· Test key variables and at least one coherent downside case.
· Review formulas, units, signs, dates, and cash-flow timing.
· Record the version, preparer, reviewer, open items, and final decision.
When an Independent Model Review Can Help
A focused review can be useful when you have a property, workbook, and specific decision: checking model structure, tracing a return, identifying missing expenses, comparing loan terms, testing a price, or deciding which assumptions require more diligence. Review when to hire a real estate consultant if you are deciding whether a short call or a larger engagement fits the issue. Walutes Capital offers a 30-minute Zoom consultation for $75. We can review documents and models during the call and provide a follow-up email with salient points or models discussed.
If you want an independent review from the combined perspective of a CPA, investor, developer, and asset manager, book a 30-minute real estate consultation. A larger engagement may be appropriate when the workbook must be built or rebuilt, source documents require extensive validation, leases must be abstracted, or the analysis needs continued updates through diligence. The overview of real estate consulting services explains the available deal-analysis, underwriting, and development-advisory context.
For the broader analytical framework, use the real estate underwriting guide. For a full acquisition-model architecture, use the real estate financial-modeling guide. For property-specific operating analysis, learn how to underwrite a multifamily deal step by step. The Investor’s Guide to Real Estate also includes free calculators and financial models. Walutes Capital’s services page describes larger underwriting, development, accounting, and asset-management engagements.
Frequently Asked Questions
What should a real estate underwriting model include?
At minimum, include documented assumptions, sources and uses, property operations, capital spending, debt, sale proceeds, investor cash flow, return measures, sensitivities, model checks, and a decision summary. The required detail depends on the property and business plan.
Should I build a real estate underwriting model monthly or annually?
Use the period that captures material timing. Annual periods may work for a stable property and simple hold. Monthly periods are often more useful for renovations, lease-up, tenant rollover, construction, interest-only periods, draws, or a sale during the year.
What color should inputs be in an underwriting spreadsheet?
The convention matters less than consistency. Many analysts use blue font for inputs, black for formulas, and green for external links. Document the convention and avoid relying on color alone to communicate meaning.
How do I check whether an underwriting model is correct?
Trace key outputs to schedules and inputs, recalculate material formulas independently, verify units and timing, reconcile source documents, and review explicit balance and roll-forward checks. A balanced model can still contain weak assumptions, so test both mechanics and judgment.
What is the difference between a pro forma and an underwriting model?
A pro forma generally presents projected operations or cash flow. An underwriting model usually adds acquisition basis, financing, valuation, investor returns, sensitivities, diligence findings, and decision controls. Usage varies, so describe what the workbook actually includes.
Can Walutes Capital review my underwriting spreadsheet?
Yes. A focused model or document review can occur during a 30-minute consultation. A full model build, extensive document validation, lease abstraction, or continuing analysis requires a separately scoped engagement.
Disclaimer
This article is provided for general educational and informational purposes only. It does not constitute tax, accounting, legal, investment, lending, appraisal, or other professional advice and should not be relied upon as a substitute for advice tailored to your circumstances. Real estate investments involve risk, and financing, tax, legal, valuation, and operating consequences vary based on each investor, property, transaction, lender, jurisdiction, and applicable law. Illustrative calculations and model structures are simplified and do not establish market terms, value, expected returns, or financing eligibility. Before making an investment or implementing a tax, accounting, legal, financing, or valuation strategy, consult qualified professionals who can evaluate your specific situation. A 30-minute consultation does not establish a formal CPA, tax-preparation, legal, investment-advisory, lending, appraisal, or attestation engagement.
Comments