SYD

What it does

SYD(cost, salvage, life, per)

SYD(cost,salvage,life,per) returns depreciation for a specified period by the sum-of-years’ digits method. Earlier periods receive larger weights.

Try it

=SYD(1200,200,4,1) returns 400. The four weights are four, three, two and one; the first period gets 4/10 of the depreciable 1000. The last period gets 100.

Quirks

Life and period use the same time unit and are not automatically truncated to whole years. The current kernel admits negative cost, but rejects negative salvage, nonpositive life or period, and a period above life when their positive difference is at least the minimum normal binary64 value. The very-small-difference condition is an explicit compatibility guard.

The output is one period's depreciation. It is not the total through that period. The denominator's overflow/subnormal handling differs from the numerator's: the current denominator becomes zero and gives #DIV/0!, while a nonfinite basis or final result gives #NUM!. Microsoft's opened page describes the method without spelling out these numerical branches.

How to implement

Let R be the stored 64-bit-significand operation, and let D mean ordinary binary64 arithmetic. First validate finite inputs and the guards above.

1. Store L=R(life+1). Compute den=D(R(life*L)/2). If den is nonfinite or has magnitude below the minimum normal value, replace it with zero; reject a zero denominator with #DIV/0!.
2. Compute basis=R(cost-salvage). Reject a nonfinite basis and flush a subnormal basis to positive zero.
3. Compute remaining=R(L-per), then numerator=R(basis*remaining). Keep (life+1)-per in that order: life-per+1 can lose different low bits.
4. Compute result=R(numerator/den). Reject nonfinite output with #NUM! and publish a subnormal result as positive zero.

Worked at cost=1200,salvage=200,life=4,per=1: L=5, den=4*5/2=10, basis=1000, remaining=4, numerator=4000, result 400. At periods two, three and four the remaining weights are three, two and one, giving 300,200,100. These four exact small examples total the depreciable 1000.

A ratio-first implementation of the same mathematical equation has different rounded intermediates. The current source's selected store discriminants support this operation order; they do not settle every data-admission or workbook route.

Rust code

22 syd_fn "SYD" (4, 4) [V "cost", V "salvage", V "life", V "per"] |s, a, sv| {
    out(s, dep::syd_kernel(num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?, num(s, a, 3)?))
}

pub fn syd_kernel(cost: f64, salvage: f64, life: f64, per: f64) -> Result<f64, WorksheetErrorCode> {
    validate_finite(&[cost, salvage, life, per])?;
    if salvage < 0.0 || life <= 0.0 || per <= 0.0 || (per > life && per - life >= f64::MIN_POSITIVE)
    {
        return Err(WorksheetErrorCode::Num);
    }
    // SYD admits negative cost. Its denominator publishes nonfinite/subnormal
    // values as zero; a zero denominator is DIV/0. Basis overflow remains NUM.
    // Keep (life + 1) - period in that order, including lost low bits for
    // large life. Selected store discriminators require x87 multiply/divide.
    let stage_or_zero = |value: f64| {
        if !value.is_finite() || value.abs() < f64::MIN_POSITIVE {
            0.0
        } else {
            value
        }
    };
    let life_plus_one = crate::ported::excel_numeric::excel_x87_add(life, 1.0);
    let denominator = stage_or_zero(crate::ported::excel_numeric::excel_x87_mul(life, life_plus_one) / 2.0);
    if denominator == 0.0 {
        return Err(WorksheetErrorCode::Div0);
    }
    let basis = publish_depreciation(crate::ported::excel_numeric::excel_x87_sub(cost, salvage))?;
    let remaining = crate::ported::excel_numeric::excel_x87_sub(life_plus_one, per);
    let numerator = crate::ported::excel_numeric::excel_x87_mul(basis, remaining);
    publish_depreciation(crate::ported::excel_numeric::excel_x87_div(numerator, denominator))
}

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

SLN uses equal allowances; DB and DDB use a declining balance.

Named variants

No named variants are recorded yet.