Excel Challenge 49: Compute Straight-Line Asset Depreciation Schedule

Challenge Scenario

In fixed asset accounting, physical assets (like vehicles, machinery, and computer fleets) lose value over time due to wear and tear. Under the Straight-Line Depreciation method, an asset depreciates by an equal dollar amount every year over its estimated useful life.

As a Fixed Assets Accountant, you are maintaining the corporate asset registry:

Annual Depreciation Expense (Column E): Calculate the annual straight-line depreciation using the standard function =SLN(B2, C2, D2) (or =(B2-C2)/D2).
Current Net Book Value (Column G): Subtract total accumulated depreciation to date from the original purchase cost: =B2-(E2*F2).
Total Annual Depreciation (Cell E7): Sum annual depreciation across all assets using SUM(E2:E5).
Total Portfolio Book Value (Cell G7): Sum current net book values using SUM(G2:G5).

Your Sheet Layout

Understanding the fixed asset schedule layout:

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:D5`	Asset Details, Cost, Salvage, Life	Asset Valuation	Purchase cost, salvage value, and useful life	Read-only asset parameters.
`E2:E5`	Annual Depr ($)	Depreciation Zone	`=SLN(B2, C2, D2)`	Calculates yearly write-off amount.
`F2:F5`	Age (Yrs)	Asset Age	Years asset has been in service	Read-only age in years.
`G2:G5`	Book Value ($)	Balance Sheet Value	`=B2-(E2*F2)`	Current remaining asset valuation.
`E7`	Total Annual Depreciation	Summary	`=SUM(E2:E5)`	Combined yearly depreciation ($17,400.00).
`G7`	Total Book Value	Summary	`=SUM(G2:G5)`	Current balance sheet valuation ($77,400.00).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: E2

In cells E2:E5, calculate the annual straight-line depreciation using SLN.

Objective #2

Cell: G2

In cells G2:G5, calculate the current net book value (= Cost - (Annual Depr * Age)).

Objective #3

Cell: E7

In cell E7, sum the total annual depreciation expense across all assets.

Objective #4

Cell: G7

In cell G7, sum the total current net book value of the asset portfolio.

Solution & Formula Breakdown

Straight-line depreciation calculates consistent yearly write-offs:

1. Annual Straight-Line Depreciation with SLN (E2:E5)

=SLN(B2, C2, D2)

The SLN(cost, salvage, life) function subtracts salvage value from cost and divides by useful life:

Row 2 (Delivery Van): ($35,000 - $5,000) / 5 years = $6,000.00/yr.
Row 3 (CNC Milling Unit): ($60,000 - $6,000) / 10 years = $5,400.00/yr.
Row 4 (Office Laptops): ($12,000 - $0) / 3 years = $4,000.00/yr.
Row 5 (Executive Desks): ($14,000 - $2,000) / 6 years = $2,000.00/yr.

2. Current Net Book Value (G2:G5)

=B2-(E2*F2)

Row 2: $35,000 - ($6,000 × 2 yrs) = $23,000.00.
Row 3: $60,000 - ($5,400 × 4 yrs) = $38,400.00.
Row 4: $12,000 - ($4,000 × 1 yr) = $8,000.00.
Row 5: $14,000 - ($2,000 × 3 yrs) = $8,000.00.

3. Portfolio Balance Sheet Totals (E7 & G7)

Cell E7 (Total Depreciation): =SUM(E2:E5) → $17,400.00.
Cell G7 (Total Net Book Value): =SUM(G2:G5) → $77,400.00.

Salvage value (also called scrap or residual value) is the estimated amount an asset is worth at the end of its useful life. If an asset is expected to be discarded with zero resale value, enter 0 for salvage!