Key Takeaways
- A standardized spreadsheet with four input sections (property, purchase, income, expense) ensures consistent evaluation.
- Color-code inputs (blue) vs. calculations (black) to prevent user errors.
- Output metrics should include cash flow, cash-on-cash return, cap rate, DSCR, and total return.
- A traffic-light deal score enables rapid go/no-go assessment against your investment criteria.
A standardized deal analysis spreadsheet ensures consistent evaluation across all potential acquisitions. This lesson walks through building a practical residential deal analyzer.
Input Section Design
Your deal analysis spreadsheet needs four input sections. Property Details: address, type, year built, square footage, bedrooms, bathrooms, lot size. Purchase Terms: purchase price, closing costs (estimate 2-3% of price), renovation budget, financing terms (down payment percentage, interest rate, loan term). Income Assumptions: monthly rent, other income (laundry, parking, storage), vacancy rate, annual rent growth. Expense Assumptions: property taxes, insurance, maintenance (10% of gross rent), management (10% of gross rent), capital reserves (5% of gross rent), HOA/utilities if applicable.
Every input should have a clear default value based on market norms but be easily adjustable for specific properties. Color-code input cells (blue) versus calculated cells (black) so the user knows what to change and what to leave alone.
Output Metrics and Decision Support
The output section should calculate and display: Monthly Cash Flow (after all expenses and debt service), Annual Cash-on-Cash Return (annual cash flow / total cash invested), Cap Rate (NOI / purchase price), DSCR (NOI / annual debt service), Total Return (cash flow + principal paydown + estimated appreciation + tax benefits), and 5-Year Projection showing how these metrics evolve over time.
Include a "Deal Score" summary that compares each output metric to your minimum thresholds. Green indicates the metric exceeds your minimum, yellow indicates it meets the minimum, and red indicates it falls below. This traffic-light display enables rapid assessment of whether a deal meets your criteria without scrolling through detailed calculations.
Key Takeaways
- ✓A standardized spreadsheet with four input sections (property, purchase, income, expense) ensures consistent evaluation.
- ✓Color-code inputs (blue) vs. calculations (black) to prevent user errors.
- ✓Output metrics should include cash flow, cash-on-cash return, cap rate, DSCR, and total return.
- ✓A traffic-light deal score enables rapid go/no-go assessment against your investment criteria.
Sources
Common Mistakes to Avoid
Using someone else's spreadsheet without understanding the formulas and assumptions.
Consequence: Blindly trusting calculations that may have errors or assumptions inappropriate for your market (e.g., 3% vacancy in a 7% vacancy market).
Correction: Build your own spreadsheet or thoroughly review and customize any template. Understand every formula and adjust default assumptions to match your specific market.
Analyzing deals without including all key expense items.
Consequence: Showing positive cash flow on paper by omitting management fees, capital reserves, or vacancy — then experiencing negative cash flow in reality.
Correction: Include property taxes, insurance, maintenance (10%), management (10%), vacancy (5-8%), and capital reserves (5%) in every analysis. No exceptions, even if self-managing.
Test Your Knowledge
1.What are the four input sections of a deal analysis spreadsheet?
2.What does a traffic-light "Deal Score" display help investors do?
3.What metrics should the output section calculate?