SYD in Excel: Descending Depreciation Weights and Schedule Checks

=SYD(cost, salvage, life, per)

All four arguments are required. Cost and salvage are amounts in the same currency. Life is the full number of periods, and per identifies the requested period in the same units. The examples use a positive whole-number life and per from 1 through life.

The independent model is (cost−salvage)×(life−per+1)/(life×(life+1)/2). With life six, the denominator is 21 and the weights are 6, 5, 4, 3, 2 and 1. This is not a fixed percentage of remaining book value.

For cost 18000, salvage 3300 and life six years:

=SYD(18000,3300,6,1)
=SYD(18000,3300,6,6)

Expected expenses are 4200 and 700. The depreciable base is 14700. The first period receives 6/21 of it; the last receives 1/21.

The first expense is six times the last because of the weights. The amounts decline by a fixed 700 each period, rather than by a fixed percentage.

Put 18000 in B2, 3300 in B3 and 6 in B4. Enter period numbers 1 through 6 in A10:A15. In B10 use =SYD($B$2,$B$3,$B$4,A10) and fill down.

Expected charges are 4200, 3500, 2800, 2100, 1400 and 700. They sum to 14700. An expanding closing-balance formula, =$B$2-SUM($B$10:B10), gives 13800, 10300, 7500, 5400, 4000 and 3300 as it is filled down.

Keep the asset inputs fixed while the period reference moves. If every copied row still refers to period 1, each row can contain valid syntax while the overall schedule is wrong.

The first three weights total 6+5+4=15. Therefore cumulative depreciation is 14700×15/21=10500, and closing book value is 7500.

For k completed periods in a life n, the sum of the first k descending weights is k×(2n−k+1)/2. At k=3 and n=6, that equals 15. This independent control can catch omitted or duplicated periods without relying only on a second SUM of the same rows.

The formula is intended here for whole k within the lifespan. Do not extrapolate beyond the final scheduled period and interpret the algebra as additional authorized depreciation.

A six-year schedule uses life=6. A 72-month sum-of-years schedule uses life=72, with denominator 2628. For the same asset:

=SYD(18000,3300,72,1)

Expected first-month expense: 402.739726027. This is different from dividing the first annual charge of 4200 by twelve, which gives 350. The two schedules allocate weights at different granularities.

Decide which model the analysis requires before converting units. The function does not establish an accounting convention or automatically prorate dates.

Straight-line depreciation for this asset is =SLN(18000,3300,6), expected 2450 per year. Both complete six-year models allocate 14700 and retain 3300. SYD changes the allocation’s timing, not the total base.

Keep depreciation amounts distinct from book values and cumulative depreciation. Those three quantities can all appear in the same schedule, but they answer different questions.

Validate positive life, a period within the intended lifespan, and plausible cost/salvage inputs before calculating. The dedicated reference defines the parameters but does not supply a comprehensive invalid-input error table; exact native errors and fractional-period behavior are not asserted here.

If the result is constant across rows, inspect the per reference. If the closing balance fails to equal salvage, check missing periods, duplicate rows and rounding. If cents differ, determine whether each charge was rounded before summation. Native coercion, blanks and numerical boundaries remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft SYD reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.