NPV

What it does

NPV(rate, value1, [value2], ...)

NPV(rate,value1,[value2],...) discounts equally spaced future cash flows to the point one period before the first amount. The first listed amount is at the end of period one, not at time zero.

Try it

=NPV(0.25,125,156.25) returns about 200: 125/1.25=100 and 156.25/1.25^2=100. A separate time-zero outflow of 200 gives a net value near zero when added outside NPV.

Quirks

Order determines timing. Use numeric zero to represent a period with no cash flow; an ignored blank or text entry can remove a position from the numeric sequence. The documented data-admission rules distinguish numerical values from text, logical values and blanks. Rate minus one makes a discount denominator zero.

How to implement

1. Collect admitted numeric flows in worksheet order. Keep this traversal separate from data admission: an ignored cell does not become a zero-valued period.
2. With D denoting ordinary nearest/even binary64 storage, set base=D(1+rate), discount=1 and total=0. A zero base gives #DIV/0! in the current helper.
3. For each admitted flow, store discount=D(discount*base), term=D(flow/discount), then total=D(total+term).
4. After the last flow, reject a nonfinite total with #NUM!; otherwise publish the numeric result. The current helper also rejects an empty numeric sequence.

In the example, the first discount factor is 1.25 and the first term 100. The next factor is 1.5625 and the next term is also 100, giving 200. At rate -2, the base is minus one and factors alternate signs: the current retained control NPV(-2,100) returns -100 rather than rejecting every rate below minus one.

A power-per-term implementation or reverse Horner fold has a different rounding history. The loop above describes current code, including its stored factors; the mathematical definition alone does not identify Excel’s entire numerical implementation.

Rust code

6 npv_fn "NPV" (2, 255) [V "rate", R "value1", R "value2"] |s, a, sv| {
    let rate = num(s, a, 0)?;
    let values = numbers(s, a, 1, a.len(), sv, Held::Numbers)?;
    tv_out(s, tv::npv(rate, &values))
}

pub fn npv(periodic_rate: f64, cashflows: &[f64]) -> Result<f64, FinancialError> {
    validate_finite(&[periodic_rate])?;
    validate_finite(cashflows)?;
    if cashflows.is_empty() {
        return Err(FinancialError::Value);
    }
    let base = 1.0 + periodic_rate;
    if base == 0.0 {
        return Err(FinancialError::Div0);
    }
    let mut total = 0.0;
    let mut discount = 1.0;
    for cashflow in cashflows {
        discount *= base;
        total += *cashflow / discount;
    }
    if total.is_finite() {
        Ok(total)
    } else {
        Err(FinancialError::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

XNPV uses actual dates; PV assumes constant payments; IRR finds a periodic cash-flow root.

Named variants

No named variants are recorded yet.