Module 04 — Single-Loan Cash-Flow Model

Goal

A clean, single-loan bridge underwriting cash-flow + returns engine — the workhorse of private-credit RE.

Inputs (the core deal — reused in 06)

Value-add multifamily. As-stabilized value $40,000,000; total project cost $30,000,000. Senior loan = lower of 70% LTC ($21,000,000) and 65% LTV ($26,000,000) → $21,000,000 (LTC binds). Rate = 1-mo Term SOFR 4.30% + 350 bps = 7.80%, floating, Actual/360. Term 36 months, full-term I/O. Origination 1.00%, exit 0.50%. In-place NOI $1,400,000; stabilized NOI $2,250,000. Payoff at month 36 at par + exit fee.

Concepts featured

• Sources & Uses (Sources = Uses check), single draw at close.
• Actual/360 interest: Beg Bal × rate × days/360 × IO-flag. Never PMT for a floating IO leg.
• Balloon via a flag: principal repaid = 0 until the term month, then the full balance. One formula copies down.
• Fees: origination (netted from funding) and exit (added at payoff).
• Credit metrics: DSCR (in-place & stabilized), debt yield (both), LTV, LTC.
• Returns: lender XIRR and MOIC on the dated cash-flow column.

Common mistakes

• Sizing to one constraint and forgetting the other — use MIN and show which binds.
• Dividing interest by 12 (or using PMT) on an Actual/360 floating loan.
• Forgetting the exit fee in the payoff cash flow.

Your assignment

Open 04_LoanCashFlow_ASSIGNMENT.xlsx: build S&U, the Actual/360 schedule, fees, payoff, and all metrics + returns. Self-Check grades the metrics and lender XIRR.