Internal Rate of Return (IRR): Handling #NUM! in multi-phase construction draws.

In development and opportunistic real estate models, Excel’s standard IRR and XIRR functions frequently throw a #NUM! error during multi-phase construction projects. This error occurs because the iterative calculation engine cannot converge on a mathematical solution, or because the cash flow sequence contains structural flaws such as missing negative values, disconnected phases, or non-conventional cash flow sign changes.

Quick Answer

#NUM! occurs when Excel hits its 100-iteration limit without finding a rate, or when no sign change exists. Supply a starting estimate in the [guess] argument:
=IRR(Cash_Flow_Range, 0.08)
For irregularly dated draws across multi-phase timelines, always use XIRR:
=XIRR(Cash_Flow_Range, Date_Range, 0.10)
If all numbers are positive or all negative, correct your cash flow inputs first.

---

Why Multi-Phase Construction Breaks the IRR Algorithm

Excel calculates IRR through an iterative search. Starting from an initial guess (defaulting to 10% / 0.10), it tests discount rates until the Net Present Value (NPV) equals zero:

$\text{NPV} = \sum_{t=0}^{N} \frac{C_t}{(1 + \text{IRR})^t} = 0$

Multi-phase development projects introduce cash flow patterns that break this numerical solver:

1. Absence of Sign Changes (The Unfunded Phase): If a construction sheet models capital expenditures in one table and equity inflows in another, an analyst might feed a series of entirely negative cash flows (draws) into =IRR(). Mathematically, if all cash flows are negative (or all positive), the rate of return is infinite or undefined. Excel stops and returns #NUM!.
2. Multiple Sign Changes (Non-Conventional Cash Flows): In multi-phase projects, Phase 1 might complete, lease up, and throw off positive cash flow (+), followed immediately by deep capital calls for Phase 2 infrastructure (-), followed by another exit (+). According to Descartes’ Rule of Signs, each change of sign can introduce an additional valid IRR solution. Excel’s solver often oscillates indefinitely between these solutions until it exceeds its iteration limit.
3. Empty Month Arrays: If multi-year construction schedules leave months with $0 entries formatted as empty text strings (""), IRR misreads cell contents, distorting the time index exponent $t$.

---

Step-by-Step Troubleshooting Workflow

Follow this diagnostic sequence to resolve #NUM! in multi-phase construction models:

[#NUM! Error in IRR/XIRR]
         │
         ▼
Do cash flows have AT LEAST ONE positive and ONE negative value?
   ├── NO  ──► Fix cash flow logic: Include initial equity/terminal sale.
   └── YES
         │
         ▼
Are cash flows spaced at irregular, multi-year dates?
   ├── YES ──► Switch from IRR() to XIRR() with accurate date headers.
   └── NO
         │
         ▼
Does the cash flow switch signs multiple times (+ / - / + / -)?
   ├── YES ──► Supply an explicit [guess] (e.g., 0.12) or use MIRR().
   └── NO
         │
         ▼
Are empty periods formatted as text strings ("")?
   └── Replace "" with 0 to maintain continuous period indexing.

---

Method 1: Injecting an Algorithmic Guess

When complex multi-phase draws cause Excel to cycle out, supply a seed value in the formula’s optional argument.

Assuming your net periodic cash flows are in row 25 from column B to AF:

=IRR(B25:AF25, 0.08)

By supplying 0.08 (8%), you shift the initial calculation point close to the true rate of return, allowing the solver to converge within its hard iteration threshold.

If 0.08 still returns #NUM!, try negative or larger guesses based on project realities:

• For distressed developments with heavy cost overruns: =IRR(B25:AF25, -0.05)
• For high-leverage merchant build projects: =IRR(B25:AF25, 0.25)

---

Method 2: Fixing Non-Periodic Draws with XIRR

Multi-phase construction loans rarely fund on uniform 30-day monthly cycles. Equity is called as architectural milestones are met, permits are issued, and subcontracts are drawn.

Using standard IRR on non-periodic rows introduces timeline distortion that triggers calculation failures. Switch to XIRR and pair the cash flows with exact calendar dates:

=XIRR(B25:AF25, B4:AF4, 0.10)

(Where row 4 contains true Excel serial date values).

Important XIRR Data Formatting Rules:

• The first cash flow (cell B25) must be negative (the initial equity injection or land deposit) and must align with the project start date (B4).
• Cells containing no draw activity must contain a numerical 0, not empty quotation marks (""). Passing "" in an XIRR array can cause Excel to reject the pairing between the date array and the cash flow array.

---

Method 3: Handling Alternating Cash Flows with MIRR

When Phase 1 cash flows are distributed to investors while Phase 2 draws are actively running, a project may have multiple IRRs. In corporate finance, standard IRR is mathematically unsuited for alternating signs.

Use the Modified Internal Rate of Return (MIRR), which resolves non-conventional cash flows by financing negative draws at a borrowing cost and reinvesting positive distributions at a hurdle rate:

=MIRR(B25:AF25, Finance_Rate, Reinvestment_Rate)

• Finance Rate: The interest rate paid on the construction facility or mezzanine line (e.g., 8%).
• Reinvestment Rate: The rate at which early Phase 1 proceeds can be redeployed (e.g., 5%).

MIRR is mathematically guaranteed to return a single, unique percentage, eliminating #NUM! entirely.

---

Diagnostic Next Steps

• If terminal equity distribution is missing: Development models that omit property sale proceeds at the end of the timeline will almost always show negative returns or #NUM!. Ensure your final month includes the net exit proceeds.
• If returns flow through an investor waterfall: Complex partnership hurdles can cause secondary #NUM! or #REF! errors when passing project-level returns down to GP/LP tiers. To trace equity distribution breaks across hurdles, see Equity Waterfalls: Troubleshooting #REF! errors in “Hurdle Rate” distributions..