XNPV

What it does

XNPV(rate, values, dates)

XNPV(rate,values,dates) discounts cash flows from their actual dates to the first supplied date. Annual exponents use day differences divided by 365, rather than a selectable bond basis.

Try it

Put -1000,1250 in B2:B3 and enter =DATE(2027,1,1) and =DATE(2028,1,1) in C2:C3. =XNPV(0.25,B2:B3,C2:C3) is near zero: the first amount is undiscounted and the second divides by 1.25.

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. The first date is an anchor, rather than an instruction to sort the inputs.

The current XNPV kernel requires a positive rate and returns #NUM! at zero or a negative rate, even when the mathematical dated sum is finite. Its source records that guard as measured; the opened Microsoft page does not explicitly state it. This differs from the internal residual used by XIRR, whose iterates can go negative.

Microsoft describes both a positive and a negative cash flow as required. The current helper and its retained all-positive source control accept 100,200 at dates 45000,45100 and rate 0.1, giving about 294.8451203808644. Keep this source-scoped observation separate from the documented sign requirement.

How to implement

Use D for ordinary binary64 storage and R for the stored x87-style operation.

1. Validate finite numeric flows, aligned truncated dates and the first-date anchor. Require a finite positive rate in the current public route.
2. Calculate base=R(1+rate) and initialize total=0.
3. For each flow in its supplied order, calculate years=D((date-date_0)/365). Call the public worksheet POWER route on (base,years); integer year counts can select its integer-exponent path.
4. Store term=R(value/power), then total=R(total+term). Preserve every store and the forward accumulation order. Apply the final finite-result and worksheet publication rules.

For the example, the year counts are zero and one, the powers one and 1.25, and the terms minus 1000 and plus 1000. The stored sum is zero. By contrast, putting the first payment into NPV would discount it by one period.

This current arithmetic chain is distinct from XIRR’s runtime-power, ordinary-binary64 residual. POWER’s complete fractional-exponent behaviour and transfer to other environments are not solved by the dated-sum loop.

Rust code

19 xnpv_fn "XNPV" (3, 3) [V "rate", R "values", R "dates"] |s, a, sv| {
    let rate = match cash::scalar_number_from_eval(&a[0]) {
        Ok(n) => n,
        Err(error) => return cash_err(error),
    };
    let values = resolved(s, a, 1, sv)?;
    let dates = resolved(s, a, 2, 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),
    };
    out(s, cash::xnpv_kernel(rate, &values, &dates))
}

/// Worksheet `XNPV` — bit-exact to 64-bit Excel on `x86_64` (W109 pilot,
/// identified by calculation-graph search and validated on 1,650 live-Excel
/// witnesses, build 20131, max ULP 0):
///
/// * guard: `rate <= 0` (including `-0.0`) publishes `#NUM!` — live-Excel
///   probed; `+1e-300` evaluates;
/// * `base = RN53(RN64(1 + rate))` — x87 double-rounded add;
/// * `years = (date - anchor) / 365` — strict binary64 division (proven
///   observationally equivalent to the double-rounded form over the entire
///   integer-serial date domain: no delta in `1..2.9M` hits a
///   double-rounding window);
/// * `pow = POWER(base, years)` — the FULL worksheet POWER kernel
///   (`power_kernel`), including the exact-integer binary-exponentiation
///   publication (integer year counts take that path, not `exp(y·ln x)`);
/// * `term = RN53(RN64(value / pow))` — x87 double-rounded divide;
/// * `total = RN53(RN64(total + term))` per step, forward order — the legacy
///   x87 memory-spill accumulation loop.
///
/// The internal solver substrate `xnpv_kernel_raw` (used by XIRR, whose
/// iterates legitimately go negative) is intentionally NOT this path; its
/// staging is a separate identification (catalog G6 solver lane).
pub fn xnpv_kernel(rate: f64, values: &[f64], dates: &[i64]) -> Result<f64, WorksheetErrorCode> {
    use crate::ported::excel_numeric::{excel_x87_add, excel_x87_div};
    use crate::ported::power_fn::power_kernel;

    validate_xnpv_inputs(values, dates)?;
    if !rate.is_finite() || rate <= 0.0 {
        return Err(WorksheetErrorCode::Num);
    }
    let base = excel_x87_add(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;
        let pow = power_kernel(base, years)?;
        let term = excel_x87_div(*value, pow);
        total = excel_x87_add(total, term);
    }
    if total.is_finite() {
        Ok(total)
    } else {
        Err(WorksheetErrorCode::Num)
    }
}

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

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

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

Named variants

No named variants are recorded yet.