Key Takeaways
- Build reusable spreadsheet templates for income analysis, comp grids, and sensitivity tables.
- Color-code inputs (blue) vs. formulas (black) for clarity.
- A 100 basis point cap rate change can shift value by 12-15%.
- Two-variable sensitivity tables reveal the full range of possible outcomes.
Spreadsheet models transform valuation from a one-time calculation into a dynamic tool for scenario analysis. This lesson covers three essential templates—an income approach model, a comp adjustment grid, and a sensitivity analysis matrix—that every investor should build and refine.
Income Approach Spreadsheet Model
The income model starts with unit-level rent data and builds up to a property-level NOI. Row 1: Unit schedule listing each unit by type, square footage, current rent, and market rent. Row 2: Gross Potential Rent (sum of all units at market rent × 12). Row 3: Other Income (laundry, parking, pet fees, late fees). Row 4: Potential Gross Income (PGI). Row 5: Vacancy and Collection Loss (typically 5-10% of PGI). Row 6: Effective Gross Income (EGI). Rows 7-15: Operating expenses itemized (taxes, insurance, utilities, management, maintenance, reserves, landscaping, pest control, legal/accounting). Row 16: Total Operating Expenses. Row 17: Net Operating Income (NOI). Row 18: Capitalization Rate (input cell). Row 19: Indicated Value = NOI ÷ Cap Rate. Build this once with formulas, then reuse for every property by changing inputs.
Comp Adjustment Grid Template
The comp adjustment grid is a standardized format for documenting and applying adjustments to comparable sales. Columns represent the subject and each comparable property (typically 3-5 comps). Rows represent adjustment categories in the standard sequence: property rights conveyed, financing terms, conditions of sale, market conditions (time), location, physical characteristics (size, condition, age, features), and net/gross adjustment totals. Each cell contains the dollar or percentage adjustment applied to the comp to make it equivalent to the subject. The adjusted sale price of each comp provides a range within which the subject's value falls.
| Adjustment Category | Comp 1 ($380K) | Comp 2 ($355K) | Comp 3 ($365K) |
|---|---|---|---|
| Property Rights | $0 | $0 | $0 |
| Financing | $0 | $0 | -$5,000 |
| Conditions of Sale | $0 | $0 | $0 |
| Time (6 mo @ 3%/yr) | +$5,700 | +$2,663 | $0 |
| Location | -$10,000 | +$5,000 | $0 |
| Size (SF) | -$8,000 | +$3,000 | -$2,000 |
| Condition | -$5,000 | $0 | +$8,000 |
| Adjusted Price | $362,700 | $365,663 | $366,000 |
Sample comp adjustment grid for a residential property
Sensitivity Analysis: How Value Changes with Cap Rate
Cap rate sensitivity analysis reveals how much property value changes for small movements in capitalization rates. This is critical because cap rates are not precise—they are estimated from limited market data. A property with $100,000 NOI is worth $1,428,571 at a 7.0% cap rate but only $1,250,000 at an 8.0% cap rate—a $178,571 difference (12.5% decline) from just a 100 basis point shift. Build a two-variable sensitivity table with cap rates on one axis and NOI scenarios on the other to understand the full range of possible values.
| NOI \ Cap Rate | 6.0% | 6.5% | 7.0% | 7.5% | 8.0% |
|---|---|---|---|---|---|
| $80,000 | $1,333K | $1,231K | $1,143K | $1,067K | $1,000K |
| $90,000 | $1,500K | $1,385K | $1,286K | $1,200K | $1,125K |
| $100,000 | $1,667K | $1,538K | $1,429K | $1,333K | $1,250K |
| $110,000 | $1,833K | $1,692K | $1,571K | $1,467K | $1,375K |
Value sensitivity to cap rate and NOI changes (in thousands)
Case Study: Sensitivity Analysis for Acquisition Decision
A 12-unit property is offered at $1,350,000 with $105,000 NOI (implied 7.8% cap). You believe market cap rates could range from 7.0% to 8.5%.
- 1At 7.0% cap: value = $1,500,000 → 11% upside from asking price.
- 2At 7.8% cap: value = $1,346,154 → approximately at asking price.
- 3At 8.5% cap: value = $1,235,294 → 8.5% below asking.
- 4Assess likely cap rate direction: are rates compressing (favorable) or expanding (risky)?
- 5Decision: offer $1,280,000 to build in margin of safety if cap rates expand to 8.5%.
Sensitivity analysis reveals the asking price is only supportable if cap rates remain at or below 7.8%. Offering 5% below asking creates protection against cap rate expansion.
Key Takeaways
- ✓Build reusable spreadsheet templates for income analysis, comp grids, and sensitivity tables.
- ✓Color-code inputs (blue) vs. formulas (black) for clarity.
- ✓A 100 basis point cap rate change can shift value by 12-15%.
- ✓Two-variable sensitivity tables reveal the full range of possible outcomes.
Sources
- Appraisal Institute — Valuation Standards(2025-03-15)
- CoreLogic — Property and Market Data(2025-03-15)
Common Mistakes to Avoid
Using a single valuation approach without cross-checking with other methods.
Consequence: Each approach has limitations; using only one produces a biased value estimate.
Correction: Use at least two approaches and reconcile results, weighting based on data quality and property characteristics.
Treating property valuation as an exact science rather than an informed estimate.
Consequence: Expecting exact values leads to frustration and potentially flawed decisions when estimates vary.
Correction: Work with value ranges and confidence levels rather than single point estimates.
Test Your Knowledge
1.For Building Valuation Models in Spreadsheets, which valuation approach is typically given the most weight?
2.How should investors handle conflicting results from different valuation approaches?
3.What role does market knowledge play in property valuation accuracy?