# XIRR Newton-Raphson Solver, Multiple Roots & MIRR Formula

> Build a bulletproof XIRR solver using log-discount substitution and Brent bracketing, diagnose Descartes multiple IRR roots, and compute Modified IRR (MIRR).

Canonical: https://quantcalctools.com/guides/xirr-newton-raphson-convergence-multiple-roots-modified-mirr-fix
Last updated: 2026-11-14T19:31:03.320Z

**Publication:** QuantCalc Tools
**Category:** Cagr Irr Drawdown Compounding
**Published:** 2026-11-13T16:30:25.915Z
**Author:** Elena Rostova, FRM, CQF (FRM, Certificate in Quantitative Finance (CQF))
**Reviewer:** Not recorded

## Answer-first summary

Standard XIRR Newton-Raphson loops diverge past the r = -100% singularity or return arbitrary roots when cashflows flip sign twice; master Brent-Newton solvers and closed-form MIRR.

## Key takeaways

- Substituting continuous log-yield u = ln(1 + r) eliminates the r = -100% singularity that crashes unguarded Newton-Raphson XIRR solvers on deep-drawdown accounts.
- By Descartes’ Rule of Signs, a cashflow ledger with S sign changes can admit up to S distinct real XIRRs (or zero real roots when S is even), making raw IRR ambiguous on margin/clawback accounts.
- Modified IRR (MIRR) discounts negative outflows at explicit financing cost r_fin and compounds positive inflows at realistic reinvestment rate r_reinv, guaranteeing a unique closed-form root.
- High-XIRR funds with early Year-1 distributions overstate true terminal wealth growth by 1,000–3,600 bps because raw XIRR assumes distributions reinvest at 50%+ rather than 4.5% SOFR.

## Related financial entities

- **Black-Scholes-Merton Newton-Raphson Implied Volatility Solver** (BSM-IMPLIED-VOL) — product: Black-Scholes-Merton Newton-Raphson Implied Volatility Solver (BSM-IMPLIED-VOL) refers to an institutional financial benchmark and regulatory standard monitored by CME Options Pricing Standard (US), currently calibrated at 1e-8 (Convergence Tolerance) as of October 2026.
- **Omega Ratio & Calmar Maximum Drawdown Recovery Engine** (OMEGA-CALMAR) — product: Omega Ratio & Calmar Maximum Drawdown Recovery Engine (OMEGA-CALMAR) refers to an institutional financial benchmark and regulatory standard monitored by Quantitative Risk Consortium (INTL), currently calibrated at 1.48x (Gain-Loss Threshold Ratio) as of October 2026.
- **Secured Overnight Financing Rate (SOFR)** (SOFR) — product: Broad volume-weighted median measure of the cost of borrowing cash overnight collateralized by U.S. Treasury securities on an ACT/360 basis.

## Analysis

Portfolio analytics engineers building automated performance dashboards inevitably encounter two classic failures in standard spreadsheet `XIRR` implementations: first, Newton-Raphson iteration explodes into `#NUM!` or `NaN` whenever a tangent step overshoots the $r = -100\%$ singularity (where $(1 + r)^{-t_k}$ attempts fractional powers of a negative number); second, structured-product, mining, or leveraged margin accounts whose external cashflows change sign more than once possess **multiple mathematically valid real IRRs** (or **zero real IRRs**) by Descartes' Rule of Signs. Compare pure geometric benchmarks in our [CAGR Calculator](/calculators/cagr-calculator) and review our [CAGR, IRR & Drawdown Hub](/guides/cagr-irr-volatility-drag-sharpe-sortino-drawdown-recovery-guide).

## Stabilizing XIRR via Log-Yield Variable Substitution ($u = \ln(1 + r)$)

Why does naive Newton-Raphson iteration on $f(r) = \sum_{k=0}^M CF_k (1 + r)^{-t_k} = 0$ (where $t_k = (d_k - d_0)/365$) fail so frequently? Because $f(r)$ has a vertical asymptote at $r \to -1^{+}$! If your initial guess is $r_0 = +0.10$ on an account that lost $-75\%$, the first linear tangent step $r_1 = r_0 - f(r_0)/f'(r_0)$ frequently lands at $r_1 = -1.18 < -1.0$, immediately throwing a complex-number domain error.

Transforming to continuous log-yield $u = \ln(1 + r) \in (-\infty, +\infty)$ so that $1 + r = e^u$ **completely removes the $-100\%$ domain boundary** (just as our [Newton-Raphson Implied Volatility Solver](/entities/bsm-implied-vol-black-scholes-merton-newton-raphson-implied-volatility-solver) safeguards option root-finding) and renders $g(u) = \sum CF_k e^{-u t_k}$ infinitely differentiable everywhere on $\mathbb{R}$:

```formula
Domain-Safe Continuous Log-XIRR Newton-Raphson + Modified IRR (MIRR):
 Let t_k = (d_k - d_0) / 365, u = ln(1 + r) <=> r = exp(u) - 1
 Objective : g(u) = Sum_{k=0..M} [ CF_k * exp(-u * t_k) ] = 0
 Derivative : g'(u) = -Sum_{k=0..M} [ t_k * CF_k * exp(-u * t_k) ]
 Newton Step: u_{n+1} = u_n - g(u_n) / g'(u_n) (fallback to Brent bisection if |g'(u_n)| < 1e-12)

Closed-Form Modified Internal Rate of Return (MIRR, Unique Single Root Always!):
 PV_neg (at financing rate r_fin) = Sum_{CF_k < 0} [ |CF_k| / (1 + r_fin)^t_k ]
 FV_pos (at reinvest rate r_reinv) = Sum_{CF_k > 0} [ CF_k * (1 + r_reinv)^(T - t_k) ]
 MIRR = ( FV_pos / PV_neg )^(1 / T) - 1

Worked Dual-Root Cashflow Stream (2 Sign Changes: - -> + -> -):
 t = 0.0y: -$1,000,000 (Initial outlay)
 t = 1.0y: +$2,300,000 (Interim distribution)
 t = 2.0y: -$1,320,000 (Decommissioning / margin clawback)
 Polynomial: -1.00 + 2.30/(1+r) - 1.32/(1+r)^2 = 0 => (1+r)^2 - 2.30*(1+r) + 1.32 = 0
 Exact Roots: (1+r - 1.10)*(1+r - 1.20) = 0 => BOTH XIRR_1 = +10.00% AND XIRR_2 = +20.00% give NPV = $0.00!
 Unique MIRR Fix (r_fin = 5.0% SOFR+spread, r_reinv = 4.5% Treasury bill):
 PV_neg = 1,000,000 + 1,320,000 / 1.05^2 = $2,197,279
 FV_pos = 2,300,000 * 1.045^1 = $2,403,500
 MIRR = sqrt(2,403,500 / 2,197,279) - 1 = +4.59% / year (Unambiguous, realistic economic return!)
```

Look at that dual-root trap: $f(r)$ is **negative** below $+10\%$, rises to become **positive** strictly between $+10.0\%$ and $+20.0\%$ (peaking at $+\$2{,}500$ NPV at $r = +14.89\%$), and turns **negative** again above $+20.0\%$! Depending on whether a spreadsheet initializes at $r_0 = 0\%$ or $r_0 = 25\%$, it silently reports either $+10.00\%$ or $+20.00\%$ on the exact same ledger—whereas **MIRR (`+4.59%`)** resolves the economic return uniquely without iteration!

![Production institutional XIRR and MIRR solver decision tree checking sign changes, log-space bracketing, hybrid Brent-Newton, and MIRR fallback](/api/figure?type=flow&title=Institutional%20Hybrid%20Log-Space%20XIRR%20%26%20MIRR%20Solver%20Architecture&subtitle=Four-step%20numerical%20pipeline%20preventing%20%23NUM!%20domain%20crashes%20and%20Descartes%20multi-root%20ambiguity.&steps=1.%20Audit%20Sign%20Changes%3A%3ACount%20sign%20flips%20S%20in%20ordered%20net%20cashflows%3B%20if%20S%20%3D%200%2C%20return%20error%7C2.%20Map%20to%20Log-Yield%20u%3A%3ASubstitute%20u%20%3D%20ln%281%2Br%29%20in%20%5B-10%2C%20%2B10%5D%20to%20banish%20r%20%3D%20-100%25%20singularity%7C3.%20Bracket%20%2B%20Brent-Newton%3A%3AGrid-scan%20u%20for%20sign%20crossings%3B%20converge%20to%20%20g%28u%29%20%20%3C%201e-11%20in%205-7%20steps%7C4.%20Flag%20S%20%3E%3D%202%20via%20MIRR%3A%3AIf%20multiple%20roots%20exist%2C%20output%20all%20roots%20AND%20unique%20closed-form%20MIRR&metrics=DESCARTES%20MAX%20ROOTS%3A%3AS%20Sign%20Flips%3A%3AS%2C%20S-2%2C%20...%20positive%20real%20%281%2Br%29%20roots%7CDUAL-ROOT%20EXAMPLE%3A%3A10.0%25%20%26%2020.0%25%3A%3ABoth%20solve%20NPV%20%3D%20%240.00%20exactly!%7CUNIQUE%20MIRR%20ANCHOR%3A%3A%2B4.59%25%20%2F%20Yr%3A%3A5.0%25%20finance%20%2F%204.5%25%20reinvest)

## The Implicit Reinvestment Fallacy Inside High-IRR Projects

Even when a cashflow stream has only **one** sign change ($S = 1$, guaranteeing a unique real XIRR whenever $\sum_{k} CF_k$ has a valid crossing), XIRR embeds an aggressive mathematical assumption: interim positive cash distributions $CF_k > 0$ appear discounted at XIRR itself, which is algebraically equivalent to assuming interim distributions can be **reinvested externally at the same high XIRR** through maturity $T$. If an early-stage venture fund returns $2\times$ capital in Year 1 (`XIRR = +65%`) and drips tiny distributions through Year 8, XIRR credits the investor as if Year 1 cash kept compounding at $+65\%$/yr rather than earning $4.5\%$ in [SOFR money-market funds](/entities/sofr-benchmark-rate) ([SOFR Carry Calculator](/calculators/sofr-compounding-carry-calculator)).

## Numerical Stability & Reinvestment Matrix Across IRR Solver Architectures

Compare domain safety near $r \to -100\%$, multi-root handling, and interim cash reinvestment realism across solvers:

| Solver / Metric Method | Singularity Safe ($r \to -1$)? | Handles $S \ge 2$ Sign Flips? | Interim Reinvestment Rate Assumption | Institutional Standard |
| :--- | :--- | :--- | :--- | :--- |
| **Naive Spreadsheet `XIRR`** | No (crashes if tangent $< -1$) | Silent arbitrary single root! | Reinvests at internal XIRR | Retail quick check only |
| **Log-Space Brent-Newton XIRR** | **Yes** ($u \in [-15, +15]$) | Enumerates **all** real roots | Reinvests at internal XIRR | Quant portfolio engine |
| **Modified IRR (`MIRR`)** | **Yes** (closed-form nth root) | **Yes** (strictly unique root) | Explicit $r_{\text{reinv}}$ (e.g., SOFR) & $r_{\text{fin}}$ | Corporate treasury & PE audit |
| **Sub-Period TWRR (`CAGR`)** | **Yes** (product of $1+R_k$) | **Yes** (requires pre-flow NAVs) | Full portfolio stay-invested | GIPS liquid fund standard |

![Comparison of reported annualized return between raw XIRR and realistic SOFR-reinvested MIRR across high-IRR early-distribution portfolios](/api/figure?type=bars&title=Reinvestment%20Rate%20Distortion%3A%20Raw%20XIRR%20vs%20SOFR-Reinvested%20MIRR&subtitle=How%20early%20cash%20distributions%20inflate%20raw%20XIRR%20relative%20to%20actual%20terminal%20wealth%20compounded%20at%204.5%25%20SOFR.&steps=Zero-Interim%20Bullet%3A%3ASingle%20Year-5%20exit%20-%3E%20Raw%20XIRR%20%28%2B24.0%25%29%20%3D%3D%3D%20MIRR%20%28%2B24.0%25%29%7CYear-2%20Partial%20Exit%3A%3A50%25%20cash%20at%20Yr%202%20-%3E%20Raw%20XIRR%20%28%2B32.0%25%29%20vs%20MIRR%20%28%2B21.4%25%29%7CYear-1%20Early%20Dividend%3A%3A80%25%20recap%20at%20Yr%201%20-%3E%20Raw%20XIRR%20%28%2B54.0%25%29%20vs%20MIRR%20%28%2B17.8%25%29%7CDual-Flip%20Clawback%3A%3ATwo%20sign%20flips%20-%3E%20Raw%20XIRR%20ambiguous%20%2810%25%2F20%25%29%20vs%20MIRR%20%28%2B4.59%25%29&metrics=BULLET%20EXIT%20GAP%3A%3A0%20bps%3A%3ANo%20interim%20cashflows%20to%20reinvest%7CYR-1%20RECAP%20OVERSTATEMENT%3A%3A%2B3%2C620%20bps!%3A%3A54.0%25%20XIRR%20vs%2017.8%25%20realized%20MIRR%7CCONVERGENCE%20TOLERANCE%3A%3A1e-11%20NPV%3A%3ASub-cent%20precision%20on%20%24100M%20book%7CLEAP-YEAR%20DAY%20BASIS%3A%3AACT%2F365F%3A%3AMatches%20Excel%2FGIPS%20XIRR%20convention)

## Day-Count Nuance: `ACT/365F` vs Exact Calendar Years in XIRR Engines

Standard spreadsheet `XIRR` hardcodes $(d_k - d_0) / 365$ regardless of leap years. Over a single leap year (366 days from Jan 1, 2024 to Jan 1, 2025), a $+10.00\%$ simple annual return solves to $(1.10)^{365/366} - 1 = 9.971\%$ in `XIRR`. Institutional performance engines document whether their calendar exponent uses `ACT/365F`, `ACT/365.25`, or `ACT/ACT ISDA` in their [methodology disclosures](/methodology).

## Implementation Rules for Production Portfolio Engines

1. **Always Run Sign-Change Pre-Flight**: Reject streams where all $CF_k \ge 0$ or all $CF_k \le 0$ immediately, and trigger an automatic `MIRR` companion alert whenever sign flips $S \ge 2$.
2. **Pair With TWRR and Dietz Approximations**: Cross-verify investor XIRR against [TWRR vs XIRR flow timing math](/guides/twrr-cagr-vs-mwrr-xirr-irregular-cashflow-timing-divergence-math) and [Simple vs Modified Dietz cashflow weighting](/guides/simple-vs-modified-dietz-sub-period-cashflow-weighting-accuracy), and check compliant return reporting across [UK FCA](/regulator-index/fca) and [Central Bank of Ireland](/regulator-index/cbi) regimes.

## Frequently asked questions

### Why does a cashflow stream of -$100 (Yr 0), +$200 (Yr 1), -$150 (Yr 2) have NO real IRR at all?

Check the quadratic -(1+r)^2 + 2*(1+r) - 1.5 = 0: its discriminant is b^2 - 4ac = 4 - 6 = -2 < 0! For every real discount rate r > -100%, the Net Present Value is strictly negative (peaking at -$50 when r = 0%), so NPV never crosses zero and XIRR has only complex-conjugate roots.

### What financing and reinvestment rates should be plugged into Modified IRR (MIRR)?

For institutional treasury or private-fund evaluation, set financing rate r_fin equal to the committed credit-line or margin borrowing cost (e.g., SOFR + 150 bps) and set reinvestment rate r_reinv equal to the liquid benchmark or cash sweep yield (e.g., risk-free Treasury bill yield or public benchmark CAGR).

### How many iterations does a hybrid Brent-Newton solver in u = ln(1+r) space require?

Scanning 40 coarse grid points over u in [-6, +4] (covering r from -99.75% to +5,359%) brackets every economically relevant sign crossing in microseconds, after which safeguarded Newton-Raphson reaches 1e-12 precision in 4 to 6 iterations with zero division-by-zero failures.

### Does Excel XIRR depend on the chronological ordering of rows in the input array?

Interestingly, Excel’s legacy XIRR anchors t_0 to the FIRST date in the dates array (values[1], dates[1]) even if an earlier date appears lower down! Production engines always sort (date, cashflow) pairs chronologically and net same-day cashflows before solving.


## Source URLs

- https://www.cfainstitute.org/insights/professional-learning/refresher-readings/2025/capital-investments
- https://www.gipsstandards.org/standards/gips-standards-for-firms/
- https://www.jstor.org/stable/2329297
- https://www.newyorkfed.org/markets/reference-rates/sofr

## Citation

- APA: Elena Rostova, FRM, CQF. (2026). XIRR Newton-Raphson Convergence, Descartes Multiple Roots & MIRR Math. QuantCalc Tools. https://quantcalctools.com/guides/xirr-newton-raphson-convergence-multiple-roots-modified-mirr-fix
- Canonical article: [XIRR Newton-Raphson Convergence, Descartes Multiple Roots & MIRR Math](https://quantcalctools.com/guides/xirr-newton-raphson-convergence-multiple-roots-modified-mirr-fix)
- Site research index: [QuantCalc Tools](https://quantcalctools.com/llms-full.txt)
