AMORDEGRC

What it does

AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

Two ideas define it, and the second is what separates it from its sibling:

1. Proration of the first period. If the asset is bought part-way through the first accounting period, the first charge is scaled by the fraction of that period the asset was held. That fraction is a day-count computation on the chosen basis, from date_purchased to first_period.
2. A declining-balance charge with a statutory coefficient. The annual rate is not rate but rate × coefficient, where the coefficient is a step function of the asset's life; and in the last two periods the rate is forced up so the book value is fully written off.

Sketching the schedule the way the documentation describes it:

effective rate  = rate × coefficient(1 / rate)
first charge    = cost × effective rate × first-period fraction
later charges   = book value × effective rate
second-to-last  = book value × 50 %
last            = the whole remaining book value

Microsoft states the last two lines directly: "The depreciation rate will grow to 50 percent for the period preceding the last period and will grow to 100 percent for the last period." And it states the stopping rule: "This function will return the depreciation until the last period of the life of the assets or until the cumulated value of depreciation is greater than the cost of the assets minus the salvage value."

period is 0-based here — period 0 is the prorated first period — which is unusual in Excel's depreciation category and is the single most common source of an off-by-one answer.

Arguments

AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

rate carries two jobs at once: it is the base depreciation rate and, through 1/rate, the asset's life in periods. Changing rate therefore moves the schedule length and the coefficient band simultaneously, which is why small changes to it can produce large, discontinuous changes to the answer.

The basis table for this function is not the usual one. Microsoft's Learn page gives:

Date arguments are Excel date serials in numeric slots; see Coercion and lifting.

Try it

Quirks

The depreciation coefficient

Microsoft's Learn page publishes this table:

and immediately below it, this sentence:

"If the life of assets is between 0 (zero) and 1, 1 and 2, 2 and 3, or 4 and 5, the #NUM! error value is returned."

Read together, the documentation says the function is defined only for lives in [3, 4], [5, 6] and (6, ∞), and errors everywhere else — including the whole band between 4 and 5 years, which is a hole in the middle of the domain rather than an edge case.

The two disagree on two named bands, and the disagreement is not subtle:

Result and edge cases

Returns a Number: the depreciation charge for the requested period, in the units of cost.

• period = 0 is the first period, and it is the only prorated one. Asking for period 1 on an asset bought mid-period returns a full-period charge, not the prorated one.
• date_purchased after first_period is rejected; the proration fraction would be negative.
• Empty, missing and error arguments follow the shared call model; see The value universe.

Errors

Documented on Microsoft's Learn page — one condition only, and it is the coefficient-band sentence quoted above: a life between 0 and 1, 1 and 2, 2 and 3, or 4 and 5 returns #NUM!. No other error condition is documented there.

How to implement

Numerical notes

1. The coefficient is a step function, so the result is discontinuous in rate. Crossing a band boundary changes the answer by a finite amount, not by an epsilon. Any implementation that compares 1/rate against 3, 5 or 6 in floating point is deciding those boundaries with a comparison that a rate like 0.2 (life exactly 5) will exercise — and 1.0/0.2 is exactly 5 in binary64, while 1.0/0.15 is not exactly 6.667. Pin the comparison, and state whether the boundary is inclusive.
2. The stopping rule needs the whole schedule. There is no closed form for period n: the book value depends on every rounded charge before it, so the implementation must iterate. That makes the function O(period) and makes early-period errors permanent.
3. 1/rate is used both as a coefficient selector and, ceilinged, as the schedule length. Those two uses can disagree for a rate whose reciprocal is barely above an integer.

Rust code

27 amordegrc_fn "AMORDEGRC" (6, 7) [V "cost", V "date_purchased", V "first_period", V "salvage", V "period", V "rate", V "basis"] |s, a, sv| {
    let purchased = date_1900(s, a, 1, sv)?;
    let first = date_1900(s, a, 2, sv)?;
    let basis = if given(a, 6) { num(s, a, 6)? } else { 0.0 };
    amor_out(s, amor::amordegrc_kernel(num(s, a, 0)?, purchased, first, num(s, a, 3)?, num(s, a, 4)?, num(s, a, 5)?, Some(basis)))
}

fn normalize_amordegrc_period(value: f64) -> Result<i64, AmorDepreciationEvalError> {
    if !value.is_finite() || value < 0.0 {
        return Err(AmorDepreciationEvalError::Domain(WorksheetErrorCode::Num));
    }
    Ok(value.floor() as i64)
}

fn round_half_away_from_zero(value: f64) -> f64 {
    let rounded = (value.abs() + 0.5).floor().copysign(value);
    if rounded == -0.0 { 0.0 } else { rounded }
}

fn amordegrc_coefficient(rate: f64) -> Result<f64, AmorDepreciationEvalError> {
    if !rate.is_finite() || rate <= 0.0 || rate >= 0.5 {
        return Err(AmorDepreciationEvalError::Domain(WorksheetErrorCode::Num));
    }
    let life = 1.0 / rate;
    if life < 3.0 {
        Ok(1.0)
    } else if life < 5.0 {
        Ok(1.5)
    } else if life <= 6.0 {
        Ok(2.0)
    } else {
        Ok(2.5)
    }
}

pub fn amordegrc_kernel(
    cost: f64,
    date_purchased: f64,
    first_period: f64,
    salvage: f64,
    period: f64,
    rate: f64,
    basis: Option<f64>,
) -> Result<f64, AmorDepreciationEvalError> {
    if !cost.is_finite() || !salvage.is_finite() || !rate.is_finite() {
        return Err(AmorDepreciationEvalError::Domain(WorksheetErrorCode::Num));
    }
    if cost <= 0.0 || salvage < 0.0 || salvage > cost {
        return Err(AmorDepreciationEvalError::Domain(WorksheetErrorCode::Num));
    }

    let purchase = parse_date_serial(date_purchased)?;
    let first = parse_date_serial(first_period)?;
    let basis = parse_basis(basis.unwrap_or(0.0))?;
    let normalized_period = normalize_amordegrc_period(period)?;
    if period > 0.0 && period < 1.0 {
        return Ok(0.0);
    }
    let coefficient = amordegrc_coefficient(rate)?;
    let depreciation_rate = rate * coefficient;
    let initial_fraction = first_period_fraction(purchase, first, basis)?;
    let first_depreciation = round_half_away_from_zero(cost * depreciation_rate * initial_fraction);

    if normalized_period == 0 {
        return Ok(first_depreciation);
    }

    let total_periods = (1.0 / rate).ceil() as i64;
    let mut book_value = cost - first_depreciation;
    for current in 1..=normalized_period {
        if book_value < salvage || book_value <= 0.0 {
            return Ok(0.0);
        }
        let remaining_periods = total_periods - current;
        let depreciation = if remaining_periods > 2 {
            round_half_away_from_zero(book_value * depreciation_rate)
        } else if remaining_periods == 2 {
            round_half_away_from_zero(book_value * 0.5)
        } else {
            book_value
        };
        if current == normalized_period {
            return Ok(depreciation.max(0.0));
        }
        book_value = (book_value - depreciation).max(0.0);
    }
    Ok(0.0)
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• AMORLINC — the same function without the acceleration. Microsoft describes the pair explicitly: AMORDEGRC "is similar to AmorLinc, except that a depreciation coefficient is applied in the calculation depending on the life of the assets." Same arguments, same proration, same four-row basis table; different charge per period.
• DB — the Anglo-American fixed-declining-balance function. It also prorates a partial first year, but through a month argument rather than a pair of dates, and its rate is derived from the salvage ratio rather than supplied. DB is period-1-based; AMORDEGRC is period-0-based.
• DDB and VDB — declining balance with an explicit factor. VDB is the closest in spirit, since it also handles partial periods, but it prorates by period number rather than by calendar date.
• SLN, SYD — the straight-line and sum-of-years-digits schedules, with no proration at all.
• Confused with: AMORLINC (above), and with DDB — the coefficient here is not the factor argument of DDB; it is derived from rate and cannot be supplied.

Named variants

No named variants are recorded yet.