PDURATION

What it does

PDURATION(rate, pv, fv)

PDURATION(rate,pv,fv) returns the number of periods for a positive amount to grow to a positive target at a positive constant rate, without intervening payments.

Try it

=PDURATION(0.25,64,100) is about 2: 64*1.25^2=100. The result can be fractional; a target below the starting value gives a negative mathematical duration at a positive rate.

Quirks

The documented arguments must all be positive; invalid values give #NUM!, and invalid data types give #VALUE!. A target below the starting amount is still permitted and gives a negative duration at a positive rate.

The current helper has no equal-value shortcut before its logarithm calculation. Equal positive amounts normally give zero, but a rate so small that stored 1+rate equals one makes the denominator zero; the resulting nonfinite calculation gives #NUM!. Replacing the denominator by a stable log1p(rate) would change this boundary behaviour.

How to implement

Let D denote ordinary nearest/even binary64 storage. The current graph uses three separate public worksheet LN results:

1. Validate finite, strictly positive rate, present value and future value.
2. Obtain Lf=LN(fv) and Lp=LN(pv), each stored to binary64.
3. Form base=D(1+rate) and obtain the stored Lb=LN(base).
4. Store difference=D(Lf-Lp), then duration=D(difference/Lb). Return #NUM! if the result is nonfinite.

For rate 0.25, pv 64 and fv 100, the current x87-LN reconstruction gives these intermediate numbers:

The mathematical duration is exactly two; the table is a worked reconstruction of the cited operation tree, rather than an additional Excel measurement. Its split logarithms can lose low bits in the subtraction. Do not first compute fv/pv and take one logarithm: the source’s retained 80-input control distinguishes that ratio-log candidate from the split graph. The LN instruction approximation and transfer to other CPUs remain separately scoped.

Rust code

12 pduration_fn "PDURATION" (3, 3) [V "rate", V "pv", V "fv"] |s, a, sv| {
    tv_out(s, tv::pduration(num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?))
}

pub fn pduration(
    periodic_rate: f64,
    present_value: f64,
    future_value: f64,
) -> Result<f64, FinancialError> {
    validate_finite(&[periodic_rate, present_value, future_value])?;
    if periodic_rate <= 0.0 || present_value <= 0.0 || future_value <= 0.0 {
        return Err(FinancialError::Num);
    }
    // Live Excel 16.0 b20326 Value2: PDURATION is the split worksheet-LN
    // graph (LN(fv)-LN(pv))/LN(1+rate), not LN(fv/pv)/LN(1+rate).
    // 80/80 exact on a fresh mixed grid; the fused-ratio LN form is 20/80.
    use crate::ported::excel_numeric::excel_log;
    let result =
        (excel_log(future_value) - excel_log(present_value)) / excel_log(1.0 + periodic_rate);
    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

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

NPER includes periodic payments; RRI solves a growth rate from a period count; FV solves the resulting balance.

Named variants

No named variants are recorded yet.