MIRR

What it does

MIRR(values, finance_rate, reinvest_rate)

MIRR(values, finance_rate, reinvest_rate) returns the modified internal rate of return of a series of periodic cash flows: the constant rate at which the discounted cost of the negative flows grows into the compounded value of the positive flows over the life of the project.

Let n be the number of periods (one less than the number of cash flows), and split the series:

PV_neg  =  Σ_{k: CF_k < 0}  CF_k / (1 + finance_rate)^k          — costs, discounted to time 0
FV_pos  =  Σ_{k: CF_k > 0}  CF_k · (1 + reinvest_rate)^(n−k)     — returns, compounded to time n

Then

MIRR  =  ( −FV_pos / PV_neg )^(1/n)  −  1

Read as a sentence: gather every outflow at the start at the rate you actually borrow at, gather every inflow at the end at the rate you actually reinvest at, and ask what single compound rate takes the one into the other. That is a well-posed question with exactly one answer.

Why it exists: the two things it fixes in IRR

MIRR is not a variant of IRR; it is a correction of two specific defects in it.

1. The reinvestment assumption. IRR implicitly assumes every interim inflow is reinvested at IRR itself. For a project with a 40% internal rate that assumption is heroic. MIRR makes the reinvestment rate an explicit argument, so the answer says what it assumes.
2. Multiple roots. IRR solves a degree-n polynomial, so a series with several sign changes can have several real answers and the one you get depends on guess. MIRR has no polynomial and no root-finding at all: it is a single n-th root of a ratio of two sums, so it is always unique, always exists on the admissible domain, and takes no guess argument. That is the structural difference, and it is the reason to prefer MIRR whenever the cash flows are not conventional.

Special case worth knowing: when finance_rate = reinvest_rate = IRR(values), MIRR returns that same value. MIRR is a generalization of IRR, not a competitor to it.

Domain and range: the result is real whenever FV_pos > 0 and PV_neg < 0, which the sign requirement guarantees. It is bounded below by −1 and unbounded above.

Sign convention

The family convention holds: negative values are payments, positive values are income. The split of the series into two sums is by sign of the cash flow, so miscoding a cost as positive does not merely change a number — it moves that flow into the other sum and compounds it forward instead of discounting it back. A sign error here is qualitatively worse than a sign error in FV.

A zero cash flow belongs to neither sum. It still consumes a period.

Arguments

• All three are required. Unlike IRR there is no optional argument, because there is nothing to guess.
• Order is meaning. Like IRR, MIRR has no dates: position in the array is the time index.
• values must contain at least one positive and at least one negative number, otherwise one of the two sums is empty and the ratio is undefined.
• The rates are per period, in the same period as the spacing of values.
• n is the number of periods, one less than the count of cash flows — the exponent 1/n in the final root uses that count, and it counts every entry the collector kept, including zeros.

Try it

Quirks

Result and edge cases

Returns Number — a periodic rate as a decimal fraction.

• No iteration, no guess, no non-convergence. Every failure mode is a domain failure.
• finance_rate = reinvest_rate = IRR(values) reproduces IRR; a useful sanity anchor.
• reinvest_rate = 0 compounds nothing forward: the positive flows are simply summed. Likewise finance_rate = 0 sums the negatives undiscounted. Both are admissible and meaningful.
• finance_rate = −1 or reinvest_rate = −1 makes a compounding base of zero; the reference engine rejects a base within a small tolerance of zero as #DIV/0!.
• Zero entries consume a period without joining either sum, so padding a series with trailing zeros lengthens n and lowers the result. This is not a rounding effect; it changes the exponent.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

How to implement

Numerical notes

MIRR is closed form and well conditioned, and that is its main numerical virtue.

No root-finding means no conditioning problem. Everything said on the IRR page about flat roots, iteration budgets, guess sensitivity and publication rules for ULP plateaus simply does not apply here. MIRR's answer is a deterministic function of its inputs evaluated in a fixed number of operations.

Two summations and a fractional power. The accuracy budget is: n divisions accumulating into PV_neg, n multiplications accumulating into FV_pos, one division, one x^(1/n), one subtraction of 1.

• The summations are same-sign within each sum — negatives only, and positives only — so there is no cancellation in the accumulation at all. This is a real structural advantage over IRR, whose objective function is a mixed-sign sum by construction.
• The final subtraction of 1 is the one cancellation site. When FV_pos/(−PV_neg) is close to 1 — a project that barely breaks even — the n-th root is very close to 1 and subtracting 1 loses precision proportionally. The stable form is expm1(log1p(ratio − 1)/n), or equivalently expm1(ln(ratio)/n), which holds full relative accuracy for a near-zero result. The reference engine at commit 473efa3 uses the naive powf(ratio, 1/n) − 1.
• The exponent 1/n is inexact for every n that is not a power of two, introducing a rounding before the power is evaluated.

The Handbook does not claim what Excel does internally.

Rust code

10 mirr_fn "MIRR" (3, 3) [R "values", V "finance_rate", V "reinvest_rate"] |s, a, sv| {
    let values = numbers(s, a, 0, 1, sv, Held::Numbers)?;
    tv_out(s, tv::mirr(&values, num(s, a, 1)?, num(s, a, 2)?))
}

pub fn mirr(
    cashflows: &[f64],
    finance_rate: f64,
    reinvest_rate: f64,
) -> Result<f64, FinancialError> {
    validate_finite(&[finance_rate, reinvest_rate])?;
    validate_finite(cashflows)?;
    if cashflows.len() < 2 {
        return Err(FinancialError::Div0);
    }
    let has_positive = cashflows.iter().any(|value| *value > 0.0);
    let has_negative = cashflows.iter().any(|value| *value < 0.0);
    if !has_positive || !has_negative {
        return Err(FinancialError::Div0);
    }
    if (1.0 + finance_rate).abs() < EPSILON || (1.0 + reinvest_rate).abs() < EPSILON {
        return Err(FinancialError::Div0);
    }
    let periods = (cashflows.len() - 1) as f64;
    let mut finance_pv = 0.0;
    let mut reinvest_fv = 0.0;
    for (idx, cashflow) in cashflows.iter().enumerate() {
        let idxf = idx as f64;
        if *cashflow < 0.0 {
            finance_pv += *cashflow / crt_pow(1.0 + finance_rate, idxf);
        } else if *cashflow > 0.0 {
            reinvest_fv += *cashflow * crt_pow(1.0 + reinvest_rate, periods - idxf);
        }
    }
    if finance_pv >= 0.0 || reinvest_fv <= 0.0 {
        return Err(FinancialError::Div0);
    }
    let result = crt_pow(-reinvest_fv / finance_pv, 1.0 / periods) - 1.0;
    if result.is_finite() {
        Ok(result)
    } else {
        Err(FinancialError::Num)
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• IRR — the unmodified measure. MIRR differs in three ways at once: explicit rates, closed form, unique answer. Where IRR returns #NUM! for a series with no sign change, MIRR returns #DIV/0! for the same series.
• XIRR — the dated internal rate of return. There is no dated MIRR; irregular cash flows with an explicit reinvestment assumption have to be built by hand from XNPV.
• NPV — the two sums MIRR forms are NPV-shaped, and a hand-built MIRR is usually written with NPV and FV. The classic single-cell spreadsheet identity is MIRR = (−NPV(reinvest, positives)·(1+reinvest)^n / (NPV(finance, negatives)·(1+finance)))^(1/n) − 1 — worth writing out once to see that the function is genuinely closed form.
• RRI — the compound rate between a present and a future value over n periods. MIRR is RRI(n, −PV_neg, FV_pos): the two gathered sums and one compound rate between them. If RRI is available, that identity is the clearest statement of what MIRR means.
• FV — the compounding FV_pos performs.
• Confused with: IRR with a guess. The guess argument in IRR selects among genuine answers; MIRR has one answer and needs no selection.

Named variants

No named variants are recorded yet.