Financial Modeling
Variable Declining Balance (VDB) Schedule with Optimal Switch to Straight-Line
Model accelerated declining-balance depreciation that automatically switches to straight-line as soon as straight-line yields a larger charge, ensuring book value reaches salvage on schedule.
Why unswitched declining balance leaves stranded book value
Pure double-declining balance multiplies opening book value by Factor / Life every year. When salvage value is low (or zero), multiplying by a constant fraction never reaches Salvage by the final year—leaving unamortized book value stranded at retirement.
Variable declining balance (VDB) compares the declining-balance charge Book_Open * (Factor / Life) against the remaining straight-line charge (Book_Open - Salvage) / Remaining_Years in each period, taking MAX of the two (capped so book value never drops below Salvage).
| Year | Opening Book | DDB (40%) | Remaining SL | Switched VDB Charge | Closing Book |
|---|---|---|---|---|---|
| 1 | 50,000 | 20,000 | 9,600.00 | 20,000 | 30,000 |
| 2 | 30,000 | 12,000 | 7,000.00 | 12,000 | 18,000 |
| 3 | 18,000 | 7,200 | 5,333.33 | 7,200 | 10,800 |
| 4 (Switch!) | 10,800 | 4,320 | 4,400.00 | 4,400 | 6,400 |
| 5 | 6,400 | 2,560 | 4,400.00 | 4,400 | 2,000 (Salvage) |
Period VDB Charge with Salvage Floor & SL Crossover
For opening book value E2, salvage $B$3, life $B$4, factor $B$5, and year index D2, this LET formula selects the larger of accelerated declining balance or remaining straight-line while clamping total write-off at E2 - $B$3.
=LET(remCap, MAX(0, E2 - $B$3), ddbAmt, E2 * ($B$5 / $B$4), slAmt, remCap / ($B$4 - D2 + 1), ROUND(MIN(remCap, MAX(ddbAmt, slAmt)), 2))Detecting the Exact Straight-Line Switchover Year
Comparing the remaining straight-line charge against the declining-balance charge flags the exact fiscal year when the asset transitions from accelerated to straight-line depreciation.
=IF((E5 - $B$3) / ($B$4 - D5 + 1) > E5 * ($B$5 / $B$4), "SL Switch", "Declining")Try the example
Use the original values or change them to test your understanding.