Financial functions

XIRR

Matches all 7 recorded answers

Recorded: 7 workbook cells. How we measure this · See the Rust code

Local names in 40 languages

Language	Name in Excel
Arabic	`XIRR`
Bulgarian	`XIRR`
Czech	`XIRR`
Danish	`INTERN.RENTE`
German	`XINTZINSFUSS`
Greek	`XIRR`
English (Australia)	`XIRR`
English (United Kingdom)	`XIRR`
English (United States)	`XIRR`
Spanish	`TIR.NO.PER`
Estonian	`XIRR`
Finnish	`SISÄINEN.KORKO.JAKSOTON`
French	`TRI.PAIEMENTS`
Hebrew	`XIRR`
Croatian	`XIRR`
Hungarian	`XBMR`
Indonesian	`XIRR`
Italian	`TIR.X`
Japanese	`XIRR`
Korean	`XIRR`
Lithuanian	`XIRR`
Latvian	`XIRR`
Norwegian	`XIR`
Dutch	`IR.SCHEMA`
Polish	`XIRR`
Portuguese (Brazil)	`XTIR`
Portuguese (Portugal)	`XTIR`
Romanian	`XIRR`
Russian	`ЧИСТВНДОХ`
Slovak	`XIRR`
Slovenian	`XIRR`
Serbian (Latin)	`XIRR`
Swedish	`XIRR`
Thai	`XIRR`
Turkish	`AİÇVERİMORANI`
Ukrainian	`XIRR`
Vietnamese	`XIRR`
Chinese (Simplified)	`XIRR`
Chinese (Hong Kong)	`XIRR`
Chinese (Traditional)	`XIRR`

Names from Microsoft's support site.

What it does

XIRR(values, dates, [guess])

XIRR(values,dates,[guess]) finds an annualized rate that makes the date-discounted cash-flow sum zero. It accommodates irregular dates and defaults guess to 0.1.

Try it

Put -1000,1250 in B2:B3 and enter =DATE(2027,1,1) and =DATE(2028,1,1) in C2:C3. =XIRR(B2:B3,C2:C3) is about 0.25: one year’s growth takes 1000 to 1250.

Quirks

Values and dates must align; a date before the first supplied date or differing counts gives #NUM!, while invalid dates give #VALUE!. Date serials are truncated. At least one positive and one negative flow are required. Later dates may be out of order, but the first supplied date remains the anchor.

Several sign changes can admit multiple roots. Guess can affect the selected result and its last digits even when the mathematical root is unique. Microsoft documents an iterative result accuracy of 0.000001 percent and a limit of 100 tries; failure gives #NUM!. That public stopping description does not identify every private residual or iteration step.

How to implement

The mathematical equation is sum(value_i/(1+r)^t_i)=0, with t_i=(date_i-date_0)/365. It provides a residual, not a complete rule for choosing one root.

The current reconstruction uses the following routes after validating signed flows and aligned dates:

Its internal residual forms base=1+r with ordinary binary64 arithmetic. For each input in supplied order it calculates the binary64 year fraction, calls the internal runtime power leaf crt_pow(base,t_i), divides the flow by it, and adds to a binary64 total. Rates at or below -0.999999999 are outside this current solver’s accepted domain.
For exactly two flows, compute the candidate (-value_1/value_0)^(1/t_1)-1. Reject a nonpositive date difference or an invalid ratio. A negative candidate can return directly. For a nonnegative candidate, a negative or zero guess is rejected by the current path; it does not simply publish the closed form.
For the two-flow positive route, evaluate the residual at zero and at the guess. If they have the same sign, repeatedly move the lower endpoint up to the upper one and double the upper rate, with a limit of 128 expansions. Return an endpoint whose residual is exactly zero.
Bisect the bracket. Before testing the midpoint residual, the current route returns the midpoint when (upper-lower)/abs(midpoint)<=2e-8; otherwise replace the endpoint with the same residual sign. It permits at most 100 bisections.
For more than two flows, the positive-guess route also brackets and bisects, but uses width <=1e-8*(1+2*abs(midpoint)). A failed positive route can fall back to the source’s bounded secant/Newton solver. That fallback has its own derivative and bounds; it is not established by the two-flow recipe.

For the example, the residual is -1000+1250/(1+r). Starting at guess 0.1 gives a positive residual, as does 0.2; 0.4 gives a negative one. Bisection tests 0.3 and then 0.25, where this simple example reaches zero. On less convenient inputs the width test can publish an approximation before an exactly zero residual appears.

Do not call the public XNPV wrapper as this internal residual: it uses worksheet POWER, stored x87 operations and a different rate guard. The route above describes the cited implementation and its retained controls. Full root-selection coverage, runtime-power last bits and transfer of every stopping boundary remain open.

Rust code

Show the CalcFunc source of this function

Quoted from the public repository DnaCalc/DnaCalc, crates calc-func and calc-fml, at commit 9e1884369a67. Comments are the source's own. A kernel shared with other functions appears once, on its module page.

XIRR · 14 lines · calc-func/src/catalogue/financial.rs on GitHub

20 xirr_fn "XIRR" (2, 3) [R "values", R "dates", V "guess"] |s, a, sv| {
    let values = resolved(s, a, 0, sv)?;
    let dates = resolved(s, a, 1, sv)?;
    let values = match cash::collect_numeric_vector_from_eval(&values) {
        Ok(v) => v,
        Err(error) => return cash_err(error),
    };
    let dates = match cash::collect_serial_vector_from_eval(&dates) {
        Ok(d) => d,
        Err(error) => return cash_err(error),
    };
    let g = guess(s, a, 2, sv)?;
    out(s, cash::xirr_kernel(&values, &dates, g))
}

validate_xcashflow_inputs · 11 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn validate_xcashflow_inputs(values: &[f64], dates: &[i64]) -> Result<(), WorksheetErrorCode> {
    validate_cashflows(values)?;
    if values.len() != dates.len() || dates.is_empty() {
        return Err(WorksheetErrorCode::Num);
    }
    let anchor = dates[0];
    if dates.iter().any(|date| *date < anchor) {
        return Err(WorksheetErrorCode::Num);
    }
    Ok(())
}

xnpv_kernel_raw · 18 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn xnpv_kernel_raw(rate: f64, values: &[f64], dates: &[i64]) -> Result<f64, WorksheetErrorCode> {
    validate_xnpv_inputs(values, dates)?;
    if !rate.is_finite() || rate <= MIN_VALID_RATE {
        return Err(WorksheetErrorCode::Num);
    }
    let base = 1.0 + rate;
    let anchor = dates[0];
    let mut total = 0.0;
    for (value, date) in values.iter().zip(dates.iter()) {
        let years = (*date - anchor) as f64 / 365.0;
        total += *value / crt_pow(base, years);
    }
    if total.is_finite() {
        Ok(total)
    } else {
        Err(WorksheetErrorCode::Num)
    }
}

xnpv_derivative · 21 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn xnpv_derivative(rate: f64, values: &[f64], dates: &[i64]) -> Result<f64, WorksheetErrorCode> {
    validate_xnpv_inputs(values, dates)?;
    if !rate.is_finite() || rate <= MIN_VALID_RATE {
        return Err(WorksheetErrorCode::Num);
    }
    let base = 1.0 + rate;
    let anchor = dates[0];
    let mut total = 0.0;
    for (value, date) in values.iter().zip(dates.iter()) {
        let years = (*date - anchor) as f64 / 365.0;
        if years == 0.0 {
            continue;
        }
        total += -years * *value / crt_pow(base, years + 1.0);
    }
    if total.is_finite() {
        Ok(total)
    } else {
        Err(WorksheetErrorCode::Num)
    }
}

xirr_two_cashflow_root · 21 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn xirr_two_cashflow_root(values: &[f64], dates: &[i64]) -> Result<f64, WorksheetErrorCode> {
    if values.len() != 2 || dates.len() != 2 {
        return Err(WorksheetErrorCode::Num);
    }
    let first = values[0];
    let second = values[1];
    let years = (dates[1] - dates[0]) as f64 / 365.0;
    if years <= 0.0 || first == 0.0 {
        return Err(WorksheetErrorCode::Num);
    }
    let ratio = -second / first;
    if !ratio.is_finite() || ratio <= 0.0 {
        return Err(WorksheetErrorCode::Num);
    }
    let root = crt_pow(ratio, 1.0 / years) - 1.0;
    if root.is_finite() && root > MIN_VALID_RATE {
        Ok(root)
    } else {
        Err(WorksheetErrorCode::Num)
    }
}

xirr_two_cashflow_positive_root_excel_like · 57 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn xirr_two_cashflow_positive_root_excel_like(
    values: &[f64],
    dates: &[i64],
    guess: f64,
) -> Result<f64, WorksheetErrorCode> {
    if !guess.is_finite() || guess <= 0.0 {
        return Err(WorksheetErrorCode::Num);
    }

    let mut lower = 0.0;
    let mut lower_value = xnpv_kernel_raw(lower, values, dates)?;
    let mut upper = guess;
    let mut upper_value = xnpv_kernel_raw(upper, values, dates)?;

    if lower_value == 0.0 {
        return Ok(lower);
    }
    if upper_value == 0.0 {
        return Ok(upper);
    }

    let mut expansions = 0usize;
    while lower_value.signum() == upper_value.signum() {
        lower = upper;
        lower_value = upper_value;
        upper *= 2.0;
        if !upper.is_finite() || upper <= MIN_VALID_RATE {
            return Err(WorksheetErrorCode::Num);
        }
        upper_value = xnpv_kernel_raw(upper, values, dates)?;
        expansions += 1;
        if expansions > XIRR_TWO_CASHFLOW_MAX_BRACKET_EXPANSIONS {
            return Err(WorksheetErrorCode::Num);
        }
    }

    for _ in 0..ROOT_MAX_ITERATIONS {
        let midpoint = (lower + upper) / 2.0;
        let relative_width = (upper - lower) / midpoint.abs();
        if relative_width <= XIRR_TWO_CASHFLOW_RELATIVE_BRACKET_TOLERANCE {
            return Ok(midpoint);
        }

        let midpoint_value = xnpv_kernel_raw(midpoint, values, dates)?;
        if midpoint_value == 0.0 {
            return Ok(midpoint);
        }
        if lower_value.signum() == midpoint_value.signum() {
            lower = midpoint;
            lower_value = midpoint_value;
        } else {
            upper = midpoint;
        }
    }

    Err(WorksheetErrorCode::Num)
}

xirr_general_positive_root_excel_like · 76 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

fn xirr_general_positive_root_excel_like(
    values: &[f64],
    dates: &[i64],
    guess: f64,
) -> Result<f64, WorksheetErrorCode> {
    if !guess.is_finite() || guess <= 0.0 {
        return Err(WorksheetErrorCode::Num);
    }

    let zero = 0.0;
    let zero_value = xnpv_kernel_raw(zero, values, dates)?;
    if zero_value == 0.0 {
        return Ok(zero);
    }

    let mut lower = guess;
    let mut lower_value = xnpv_kernel_raw(lower, values, dates)?;
    if lower_value == 0.0 {
        return Ok(lower);
    }

    let mut upper;

    if zero_value.signum() != lower_value.signum() {
        lower = zero;
        lower_value = zero_value;
        upper = guess;
        let upper_value = xnpv_kernel_raw(upper, values, dates)?;
        if upper_value == 0.0 {
            return Ok(upper);
        }
    } else {
        upper = guess * 2.0;
        if !upper.is_finite() || upper <= MIN_VALID_RATE {
            return Err(WorksheetErrorCode::Num);
        }
        let mut upper_value = xnpv_kernel_raw(upper, values, dates)?;

        let mut expansions = 0usize;
        while lower_value.signum() == upper_value.signum() {
            lower = upper;
            lower_value = upper_value;
            upper *= 2.0;
            if !upper.is_finite() || upper <= MIN_VALID_RATE {
                return Err(WorksheetErrorCode::Num);
            }
            upper_value = xnpv_kernel_raw(upper, values, dates)?;
            expansions += 1;
            if expansions > XIRR_GENERAL_POSITIVE_ROOT_MAX_BRACKET_EXPANSIONS {
                return Err(WorksheetErrorCode::Num);
            }
        }
    }

    for _ in 0..ROOT_MAX_ITERATIONS {
        let midpoint = (lower + upper) / 2.0;
        let bracket_width = upper - lower;
        let brent_tol = XIRR_GENERAL_POSITIVE_ROOT_BRENT_EPS * (1.0 + 2.0 * midpoint.abs());
        if bracket_width <= brent_tol {
            return Ok(midpoint);
        }

        let midpoint_value = xnpv_kernel_raw(midpoint, values, dates)?;
        if midpoint_value == 0.0 {
            return Ok(midpoint);
        }
        if lower_value.signum() == midpoint_value.signum() {
            lower = midpoint;
            lower_value = midpoint_value;
        } else {
            upper = midpoint;
        }
    }

    Err(WorksheetErrorCode::Num)
}

xirr_kernel · 30 lines · calc-func/src/ported/cashflow_rate_family.rs on GitHub

pub(crate) fn xirr_kernel(
    values: &[f64],
    dates: &[i64],
    guess: Option<f64>,
) -> Result<f64, WorksheetErrorCode> {
    validate_xcashflow_inputs(values, dates)?;
    let guess = guess.unwrap_or(0.1);
    if values.len() == 2 && dates.len() == 2 {
        let root = xirr_two_cashflow_root(values, dates)?;
        if root >= 0.0 && guess < 0.0 {
            return Err(WorksheetErrorCode::Num);
        }
        if root >= 0.0 {
            return xirr_two_cashflow_positive_root_excel_like(values, dates, guess);
        }
        return Ok(root);
    }

    if guess > 0.0 {
        if let Ok(root) = xirr_general_positive_root_excel_like(values, dates, guess) {
            return Ok(root);
        }
    }

    bounded_rate_solve(
        guess,
        |rate| xnpv_kernel_raw(rate, values, dates),
        |rate| xnpv_derivative(rate, values, dates),
    )
}

Kernels shared with other functions

resolved catalogue/financial
cash_err catalogue/financial
guess catalogue/financial
numeric_from_cell ported/cashflow_rate_family
scalar_number_from_eval ported/cashflow_rate_family
collect_numeric_vector_from_eval ported/cashflow_rate_family
serial_from_number ported/cashflow_rate_family
collect_serial_vector_from_eval ported/cashflow_rate_family
validate_cashflows ported/cashflow_rate_family
validate_xnpv_inputs ported/cashflow_rate_family
bounded_rate_solve ported/cashflow_rate_family
polish_rate ported/cashflow_rate_family
map_cashflow_rate_error_to_ws ported/cashflow_rate_family

Argument and result plumbing

fail catalogue/kit
given catalogue/kit
grid catalogue/kit
out catalogue/kit

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

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

XNPV is the paired dated calculation; NPV and IRR use equally spaced periods.

Named variants

No named variants are recorded yet.

Suggest a correction or contribute an implementation · For agents