0) Why this modeling matters in practice

• Investment Memos: an IC (Investment Committee) needs transparent logic: where does NOI come from, how sensitive is value to exit cap and rent growth, and what happens under downside?
• Debt & Banking: lenders focus on DSCR, ICR, and refinance feasibility at maturity. You must show a defendable debt schedule.
• Valuation & Negotiation: direct cap is fast; DCF is richer. You’ll use both—cap for market sense check, DCF for deal structuring and scenarios.
• Communication: numbers must be reproducible. Your model is a map—others should be able to retrace your steps.

1) Core Cash-Flow Concepts (definitions you actually use)

• GPR (Gross Potential Rent): rent at 100% occupancy before losses.
• Vacancy & Collection Loss: allowance for downtime and bad debt.
• Other Income: parking, storage, bill-backs, service fees.
• OpEx (Operating Expenses): taxes, insurance, utilities, repairs, management (excludes debt service and most CapEx).
• NOI: NOI = GPR − Vacancy + Other Income − OpEx.
• CapEx: non-recurring improvements (roof/MEP) and leasing costs (TI/LC).
• FCFF: NOI − recurring CapEx (and reserves) (unlevered cash flow).
• FCFE: equity cash flow after debt service and net borrowing/repayments.

Why: Lenders and buyers price the asset on expected NOI and the reliability of that NOI (lease quality, rollover risk, OpEx stability, CapEx drag).

2) NOI & Direct Capitalization (when to use, when not)

Direct Cap is a snapshot valuation method for stabilized assets:

Value ≈ NOI / CapRate

• Use it when: asset is near-stabilized, growth is modest, and CapEx is not lumpy.
• Avoid as sole method when: you have heavy lease-up, major CapEx, or volatile income—then DCF is more faithful.

Example: NOI = 1,200,000; Cap = 6.00% ⇒ Value ≈ 20,000,000.

3) DCF & Terminal Value (Exit Cap vs Growth Model)

1. Timeline: pick annual/quarterly/monthly and stick with it.
2. Forecast: revenue growth (rents/indexation), vacancy, OpEx growth, recurring CapEx.
3. Discount Rate: unlevered asset rate for FCFF; equity rate for FCFE.
4. Terminal Value (TV) options:
   • Exit Cap Method: TV = (NOIN+1 / ExitCap) × (1 − SaleCosts)
   • Gordon Growth: TV = (FCFN+1) / (r − g) (sanity check; rarely used as the primary TV for property but useful intuition)

Why: DCF captures the timing of lease events, capex, and debt much better than a single-period cap.

4) NPV (exact-date vs periodic) + Mid-Year convention

• XNPV (preferred): discounts using exact dates (realistic).
• NPV: assumes equal periods and discounts one period forward (include C0 separately).
• Mid-Year convention: reduces bias by assuming cash flows arrive through the year.

Formulas:

=XNPV(CashFlows, Dates, DiscountRate)               /* exact-date NPV */
=C0 + NPV( DiscountRate, C1:C5 )                      /* periodic NPV: include C0 separately */
=SUMPRODUCT( C1:C5, 1/(1+Rate)^(ROW(C1:C5)-ROW(C1)+1) )        /* manual NPV */
=SUMPRODUCT( C1:C5, 1/(1+Rate)^((ROW(C1:C5)-ROW(C1)+1)-0.5) )  /* mid-year */

Why: NPV tells you value in today’s money versus the price you pay. If NPV > 0 at your required return, value is being created.

5) IRR, XIRR, MIRR: usage, pitfalls, interpretation

• IRR: internal rate for periodic cash flows.
• XIRR: IRR using dates (use this in practice).
• MIRR: sets finance and reinvestment rates explicitly (more realistic than IRR’s implicit reinvestment at IRR).
• Pitfalls: multiple sign changes can produce multiple IRRs; check the NPV profile and MIRR.

Formulas:

=IRR( EquityCF )                          /* periodic */
=XIRR( EquityCF, Dates )                    /* exact dates (preferred) */
=MIRR( EquityCF, FinanceRate, ReinvestRate )/* resolves reinvestment assumption */

Interpretation: IRR is the discount rate that sets NPV=0. Compare IRR vs your required return; also check NPV at that required return.

6) Unlevered vs Levered Returns + Deal Metrics

• Unlevered (asset) view: FCFF discounted at an asset rate; compares the property itself.
• Levered (equity) view: FCFE than includes debt; magnifies upside/downside.
• Metrics: LTV = Loan/Value; LTC = Loan/Total Cost; EM (Equity Multiple) = Distributions / Equity Invested.

7) Debt Module: IO → amortization, balloon, refi, DSCR

Why: Most assets are financed. Lenders test DSCR and refi feasibility. You must model interest-only (IO) periods, amortization, and maturity outcomes.

Key pieces

• Interest-only phase: interest = rate × outstanding; no principal reduction.
• Amortization phase: standard payment with IPMT + PPMT.
• Balloon: remaining principal at maturity if not fully amortized.
• Refi test: new payment at market rate/term; check DSCR and LTV against covenants.
• DSCR: NOI / Debt Service (often covenant ≥ 1.20×).

Excel building blocks

/* debt service in month t (amortizing) */
=IPMT( Rate/12, t, Nper, -LoanAmt ) + PPMT( Rate/12, t, Nper, -LoanAmt )

/* DSCR per period */
=NOI_t / DebtService_t

/* Refinance payment at maturity */
=PMT( NewRate/12, NewTermMonths, -OutstandingAtMaturity )

8) Sensitivities: one-way, two-way, and tornado

• One-way: vary exit cap by ±50 bps and show Value/IRR change.
• Two-way: exit cap (rows) × NOI growth (columns) → Equity IRR table.
• Tornado: rank drivers by absolute impact on IRR/NPV (manual or with simple SCENARIO + SORT).

Why: Decision makers want to see what breaks and how quickly.

9) Excel Formula Library (expanded)

Timelines & growth

=SEQUENCE(6,1,0,1)                                 /* Years 0..5 */
=ROUND( NOI_Y1 * (1 + GrowthRate)^(YearIndex), 0 )    /* annual growth */

Direct cap & terminal value

=NOI / CapRate
=(NOI_Next / ExitCap) * (1 - SaleCosts)              /* terminal value */

NPV (various)

=XNPV( CashFlows, Dates, DiscountRate )           /* exact dates */
=C0 + NPV( DiscountRate, C1:C10 )                      /* periodic */
=SUMPRODUCT( C1:C10, 1/(1+Rate)^(ROW(C1:C10)-ROW(C1)+1) )
=SUMPRODUCT( C1:C10, 1/(1+Rate)^((ROW(C1:C10)-ROW(C1)+1)-0.5) ) /* mid-year */

IRR / XIRR / MIRR

=IRR( EquityCF )
=XIRR( EquityCF, Dates )
=MIRR( EquityCF, FinanceRate, ReinvestRate )

Rate conversions & annuity math

=EFFECT(NominalRate, 12)     /* effective annual from nominal monthly */
=NOMINAL(EffectiveAnnual, 12) /* nominal from effective annual */
=PV(rate, nper, pmt, [fv], [type]) /* present value */
=FV(rate, nper, pmt, [pv], [type]) /* future value */

Debt & DSCR

=IPMT( Rate/12, t, Nper, -LoanAmt ) + PPMT( Rate/12, t, Nper, -LoanAmt )
=NOI_t / DebtService_t
=PMT( NewRate/12, NewTermMonths, -OutstandingAtMaturity )

Parsing & QA helpers (make data sheet-ready)

=TEXTAFTER(A2, "≥ ")            /* e.g., pull DSCR threshold from text */
=VALUE(TEXTBEFORE(TEXTAFTER(A2,"+"),"%"))/100  /* parse "+3%" rent step */
=FILTER( Leases, Leases[MonthsToExpiry] <= 12 ) /* expiring soon */

Named mini-functions (clarity & reuse)

/* Name Manager → New: TV(noi, cap, sale) = (noi / cap) * (1 - sale) */
=LAMBDA(noi, cap, sale, (noi/cap)*(1-sale))

/* Name Manager → New: DF(rate, t) = 1/(1+rate)^t */
=LAMBDA(rate, t, 1/(1+rate)^t)

10) Mini Case Walkthrough (numbers you can test)

Scenario: Buy an office for 19,000,000. Year-1 NOI = 1,200,000. Rent growth 2.0%/yr, OpEx stable, recurring CapEx 50,000/yr. Exit in Year-5, sale costs 1.5%. Unlevered discount rate 8.5%. Exit cap 6.25%.

Steps

1. Forecast NOI (Years 1-5): 1,200,000 × 1.02^(t-1)
2. FCFF = NOI − CapEx: subtract 50,000 each year.
3. TV at Year-5: compute NOI at Year-6 = NOI₅ × 1.02, then:
   TV = (NOI_6 / 0.0625) × (1 − 0.015)
4. Unlevered NPV: discount FCFF(Years 1-5) + TV at 8.5% and compare to Price 19,000,000.

Excel (periodic: put FCFF in C1:C5, TV in T5)

=C0 + NPV( 0.085, C1:C5 ) + T5/(1+0.085)^5    /* if C0 = -19,000,000 */

Excel (exact dates: put all cash flows & dates)

=XNPV( AllCF, AllDates, 0.085 )

Interpretation: If NPV > 0 at 8.5%, the price is attractive on an unlevered basis. If not, you need better terms: lower price, better growth, or a sharper exit cap (market evidence required).

11) Common Pitfalls & Sanity Checks

• IRR ≠ always better: compare NPV at a required return; IRR can mislead with odd timing or sign flips.
• NPV timing: use XNPV/XIRR for real calendars; don’t mix periodic functions with irregular dates.
• TV dominance: if terminal value > ~60–70% of total PV, your model is highly sensitive to Exit Cap—show a 1-way sensitivity.
• DSCR realism: check DSCR path after rates rise; refi tests at maturity are crucial.
• Inputs one-sheet rule: keep all key assumptions together with sources, dates, and a change log.