Depreciation schedule (multiple methods)
AdvancedCompanies can choose different depreciation methods for tax and accounting purposes, each producing different expense patterns over an asset's life. Understanding how to calculate and compare these methods is essential for financial analysis and decision-making.
Excel provides several depreciation functions. Here are three common ones:
- SLN: Straight-line depreciation (equal amounts each year)
- DDB: Declining-balance depreciation (defaults to double-declining with factor = 2; accelerated, front-loaded)
- SYD: Sum-of-years' digits (accelerated, but less aggressive than DDB)
All three functions require the same core arguments: cost, salvage value, and useful life. DDB and SYD also require the period number to calculate depreciation for a specific year.
Your task:
Build a depreciation comparison table for a $120,000 asset with a $20,000 salvage value and 5-year useful life.
For each year (1-5):
- Calculate straight-line depreciation in column B
- Calculate double-declining balance depreciation in column C
- Calculate sum-of-years' digits depreciation in column D
Then calculate the totals for each method in row 16.
Use absolute references for the asset parameters so your formulas can be copied down. Reference the year number from column A for the period argument in DDB and SYD.
Need some help?
Hint 1
All three functions share the same first three arguments: cost, salvage, life. For DDB and SYD, add the period number as the fourth argument.
Hint 2
SLN returns the same value each year, but you still need to copy it down. DDB and SYD change each period based on the year number in column A.
Hint 3
Make sure to use absolute references ($B$4, $B$5, $B$6) for the asset parameters, but use a relative reference (A10) for the period so it updates when copied down.
Answer
Exercise
Depreciation schedule (multiple methods)
AdvancedCompanies can choose different depreciation methods for tax and accounting purposes, each producing different expense patterns over an asset's life. Understanding how to calculate and compare these methods is essential for financial analysis and decision-making.
Excel provides several depreciation functions. Here are three common ones:
- SLN: Straight-line depreciation (equal amounts each year)
- DDB: Declining-balance depreciation (defaults to double-declining with factor = 2; accelerated, front-loaded)
- SYD: Sum-of-years' digits (accelerated, but less aggressive than DDB)
All three functions require the same core arguments: cost, salvage value, and useful life. DDB and SYD also require the period number to calculate depreciation for a specific year.
Your task:
Build a depreciation comparison table for a $120,000 asset with a $20,000 salvage value and 5-year useful life.
For each year (1-5):
- Calculate straight-line depreciation in column B
- Calculate double-declining balance depreciation in column C
- Calculate sum-of-years' digits depreciation in column D
Then calculate the totals for each method in row 16.
Use absolute references for the asset parameters so your formulas can be copied down. Reference the year number from column A for the period argument in DDB and SYD.
Need some help?
Hint 1
All three functions share the same first three arguments: cost, salvage, life. For DDB and SYD, add the period number as the fourth argument.
Hint 2
SLN returns the same value each year, but you still need to copy it down. DDB and SYD change each period based on the year number in column A.
Hint 3
Make sure to use absolute references ($B$4, $B$5, $B$6) for the asset parameters, but use a relative reference (A10) for the period so it updates when copied down.