Depreciation schedules in fixed asset registers often fail when assets near the end of their useful lives. The two most common errors are salvage value overshoot—where the formula reduces an asset’s book value below its contractual salvage value—and terminal residual drift, where declining-balance formulas leave a residual balance that fails to equal the salvage value in the final year. These discrepancies violate accounting standards (GAAP/IFRS) and throw balance sheet fixed asset roll-forwards out of balance.
Quick Answer
Excel’s
=SLN()does not account for mid-year asset purchases, while=DB()uses an internal three-decimal fixed-rate algorithm that rarely hits the salvage value exactly. To fix these errors, wrap periodic calculations in a boundary control:=MAX(Salvage_Value, Prior_Book_Value - Current_Depreciation). For straight-line models with mid-year additions, use=AMORDEGRC()or explicit day-count logic.
Core Behavioral Differences: SLN vs. DB vs. DDB
To diagnose why your depreciation schedule is miscalculating salvage values, you must first identify how each Excel function handles asset boundaries:
Depreciation Function Characteristics:
├── =SLN() ──> Linear, ignores mid-year placement, stops exactly at (Cost - Salvage).
├── =DB() ──> Fixed declining rate, rounds rate to 3 decimals, often leaves terminal drift.
└── =DDB() ──> 200% declining balance, ignores salvage in rate, can overshoot salvage early.
| Function | Method | How Salvage Value Is Handled | Primary Source of Error |
|---|---|---|---|
=SLN(Cost, Salvage, Life) | Straight-Line | Subtracted upfront: (Cost - Salvage) / Life. | Cannot handle assets placed in service mid-year (no partial-period support). |
=DB(Cost, Salvage, Life, Period, [Month]) | Fixed Declining Balance | Built into the internal rate calculation. | Internal rate is rounded to 3 decimal places, creating a mismatch in the final year. |
=DDB(Cost, Salvage, Life, Period, [Factor]) | Double Declining Balance | Excluded from the initial rate; serves only as a hard floor. | Halts depreciation abruptly when floor is reached; does not switch to straight-line. |
Problem 1: The Mid-Year Addition Trap in =SLN()
The =SLN() function divides depreciable base by asset life evenly. It has no argument for placement date or in-service month:
=SLN(Cost, Salvage, Life)
If an asset costing $120,000 with a $20,000 salvage value and a 5-year life is purchased on October 1:
- Full-year depreciation is $20,000 per year.
- In Year 1, the true depreciation should only be 3 months ($5,000).
- If an analyst manually enters $5,000 in Year 1 and then uses
=SLN()for the next five years, the model applies five full years of depreciation ($100,000) on top of the first partial year ($5,000). - Total depreciation hits $105,000, driving ending book value down to $15,000—violating the $20,000 salvage floor by $5,000.
The Fix: Explicit Depreciable Base Cap
Never allow an asset schedule to calculate straight-line depreciation without checking remaining depreciable capacity:
=MIN(Annual_Depreciation_Rate, Prior_Book_Value - Salvage_Value)
This guarantees that once cumulative depreciation equals Cost - Salvage, subsequent periods output $0.00.
Problem 2: Terminal Rate Drift in =DB()
The =DB() function calculates depreciation using a fixed percentage rate:
Per the underlying Excel specification, this calculated rate is rounded to three decimal places.
Example:
Cost: $10,000 | Salvage: $1,000 | Life: 5 Years
Unrounded Mathematical Rate: 1 - (1,000 / 10,000)^(1/5) = 0.3690426... (36.904%)
Excel DB Formula Rounded Rate: 0.369 (36.9%)
Because that three-decimal rate is applied over the life of the asset, cumulative depreciation diverges from the target:
Year Beginning Book Value Depreciation (36.9%) Ending Book Value
-------------------------------------------------------------------------
1 $10,000.00 $3,690.00 $6,310.00
2 $6,310.00 $2,328.39 $3,981.61
3 $3,981.61 $1,469.21 $2,512.40
4 $2,512.40 $927.08 $1,585.32
5 $1,585.32 $584.98 $1,000.34 <-- ERROR
At the end of Year 5, the ending book value is $1,000.34, leaving a lingering $0.34 variance above the target $1,000.00 salvage value.
The Permanent Solution: Implementing Dynamic Book Value Floors
To prevent rounding errors and premature overshoots across any depreciation schedule, construct the asset register with explicit boundary logic:
Prior Period Book Value
│
Calculate Periodic Depreciation
│
Is (Prior BV - Depreciation) < Salvage?
│
┌──────────────┴──────────────┐
YES NO
│ │
Current Depreciation = Current Depreciation =
Prior BV - Salvage Full Calculated Amount
(Ending BV = Salvage) (Ending BV > Salvage)
In your annual depreciation column, apply this formula:
=MAX(0, MIN(Calculated_Raw_Depreciation, Prior_Period_Book_Value - Salvage_Value))
And in the Ending Book Value column:
=MAX(Salvage_Value, Prior_Period_Book_Value - Current_Period_Depreciation)
This ensures two accounting guarantees:
- Periodic depreciation automatically trims itself in the terminal year to step down exactly to the salvage value.
- The asset will never depreciate past the salvage floor, regardless of whether you use accelerated methods, leap year day counts, or custom tax conventions.
For models linking fixed asset registers into automated three-statement cash flow forecasts, confirm your error-trapping reconciliations are set up to flag any unlinked asset write-downs. See Check-Sum Failures: Building “Error Dashboards” that flag when a model is out of balance..