Excel SYD Function
last modified October 5, 2026
The SYD function returns the sum-of-years' digits depreciation of an
asset for a specified period. It is one of Excel's accelerated depreciation
methods: it charges more in the early periods and less in the later ones, which
matches assets that lose most of their value soon after purchase. This tutorial
explains the exact formula, works through verified examples with cell references
and monthly periods, builds a full depreciation schedule, compares
SYD with the SLN, DB, and
DDB methods, and lists the errors you are most likely to encounter.
SYD Function Basics
The SYD function calculates depreciation with the sum-of-years'
digits method. It requires four arguments: cost, salvage value, life, and the
period to depreciate. Each period receives a shrinking fraction of the
depreciable amount cost - salvage, so the charge declines steadily
over the life of the asset.
| Component | Description |
|---|---|
| Function Name | SYD |
| Syntax | =SYD(cost, salvage, life, per) |
| Arguments | cost, salvage, life, per (all required) |
| Return Value | Depreciation for the specified period |
This table breaks down the essential components of the SYD
function. All four arguments are required, there are no optional arguments, and
the function returns the depreciation amount for a single period.
| Argument | Meaning |
|---|---|
| cost | The initial cost of the asset. |
| salvage | The value at the end of the depreciation, called the salvage value. It may be zero. The salvage portion of the cost is never depreciated. |
| life | The number of periods over which the asset is depreciated, sometimes called the useful life. It must be positive. |
| per | The period you want the depreciation for. It must be between 1 and life, and it uses the same time unit as life. |
life and per must use the same unit: a life in years
requires a period in years, and a life in months requires a period in months.
The arguments must be numbers; text that does not look like a number produces a
#VALUE! error, and a period outside the range 1 to
life or a non-positive life produces a
#NUM! error.
How SYD Calculates Depreciation
depreciation = (cost - salvage) * (life - per + 1) / (life * (life + 1) / 2)
The denominator is the sum of the digits from 1 to life. For a
five-year asset that is 5 + 4 + 3 + 2 + 1 = 15. In period per the
numerator uses the remaining digits life - per + 1, so period 1
receives 5/15 of the depreciable amount, period 2 receives 4/15, and so on down
to 1/15 in the final period. The fractions add up to exactly 1, so a complete
schedule writes the asset down from cost to salvage with no rounding gap.
Basic SYD Example
This example demonstrates the simplest use of the SYD function with hard-coded values. We'll calculate the first year's depreciation for a $10,000 asset.
=SYD(10000, 1000, 5, 1)
This formula calculates first-year depreciation for a $10,000 asset with $1,000
salvage value over 5 years. The result is $3,000.00, which is
(10000 - 1000) * 5 / 15. SYD allocates more depreciation in early
periods than the straight-line method would.
Microsoft's documentation uses the same logic for a $30,000 asset with a $7,500
salvage value and a ten-year life: =SYD(30000, 7500, 10, 1) returns
$4,090.91, which is 10/55 of the $22,500 depreciable amount, and
=SYD(30000, 7500, 10, 10) returns $409.09, which is 1/55 of it.
SYD Depreciation Schedule
Running the function once per period builds a full depreciation schedule. This table lists the formula for each of the five years.
| Year | Formula |
|---|---|
| 1 | =SYD(10000, 1000, 5, 1) |
| 2 | =SYD(10000, 1000, 5, 2) |
| 3 | =SYD(10000, 1000, 5, 3) |
| 4 | =SYD(10000, 1000, 5, 4) |
| 5 | =SYD(10000, 1000, 5, 5) |
=SYD(10000, 1000, 5, 1) // Year 1: $3,000 =SYD(10000, 1000, 5, 2) // Year 2: $2,400 =SYD(10000, 1000, 5, 3) // Year 3: $1,800 =SYD(10000, 1000, 5, 4) // Year 4: $1,200 =SYD(10000, 1000, 5, 5) // Year 5: $600
The amounts decrease every year, and each charge is exactly $600 less than the
previous one, which is the depreciable amount divided by the digit sum
(9000 / 15). The five charges total $9,000
(cost - salvage), so the book value ends at exactly the $1,000
salvage value.
SYD with Cell References
A more practical approach uses cell references instead of hard-coded values. This makes the worksheet dynamic and easier to maintain.
| A | B |
|---|---|
| Cost: | 15000 |
| Salvage: | 2000 |
| Life (years): | 7 |
| Year 3 Depreciation: | =SYD(B1, B2, B3, 3) |
=SYD(B1, B2, B3, 3)
This formula calculates year 3 depreciation for a $15,000 asset with $2,000 salvage value over 7 years. The result is $2,321.43. The digit sum for a seven-year life is 7 + 6 + 5 + 4 + 3 + 2 + 1 = 28, and period 3 receives 5/28 of the $13,000 depreciable amount. Using cell references allows easy parameter changes without modifying formulas.
SYD with Monthly Depreciation
Because per must use the same unit as life, the same
function produces monthly charges when both arguments are expressed in months.
This example converts the asset from the first example to a monthly schedule.
| A | B |
|---|---|
| Cost: | 10000 |
| Salvage: | 1000 |
| Life (months): | 60 |
| Month 1 Depr.: | =SYD(B1, B2, B3, 1) |
| Month 12 Depr.: | =SYD(B1, B2, B3, 12) |
=SYD(10000, 1000, 60, 1) // Month 1: $295.08 =SYD(10000, 1000, 60, 12) // Month 12: $240.98
The digit sum for a 60-period life is 60 * 61 / 2 = 1830. Month 1
receives 60/1830 of the $9,000 depreciable amount, and month 12 receives
49/1830. All 60 monthly charges add up to $9,000, so a monthly schedule ends at
salvage just like the yearly one.
SYD with Named Ranges
Using named ranges makes SYD formulas more readable and maintainable. This example shows SYD calculations with defined names for each parameter.
| A | B |
|---|---|
| AssetCost | 25000 |
| SalvageValue | 4000 |
| AssetLife | 8 |
| Year4Depr | =SYD(AssetCost, SalvageValue, AssetLife, 4) |
=SYD(AssetCost, SalvageValue, AssetLife, 4)
This formula calculates year 4 depreciation using named ranges instead of cell references. An eight-year life has a digit sum of 36, so period 4 receives 5/36 of the $21,000 depreciable amount, which is $2,916.67. The names make the formula's purpose clearer and reduce errors when modifying the worksheet structure.
SYD Compared to Other Depreciation Methods
The SLN function charges the same amount every period. The
accelerated methods DB, DDB, and SYD
charge more in the early years and less later. The table below compares all four
methods for the $10,000 asset with a $1,000 salvage value and a five-year life
used in the first example.
=SLN(10000, 1000, 5) =DB(10000, 1000, 5, 1) =DDB(10000, 1000, 5, 1) =SYD(10000, 1000, 5, 1)
| Year | SLN | DB | DDB | SYD |
|---|---|---|---|---|
| 1 | 1800.00 | 3690.00 | 4000.00 | 3000.00 |
| 2 | 1800.00 | 2328.39 | 2400.00 | 2400.00 |
| 3 | 1800.00 | 1469.21 | 1440.00 | 1800.00 |
| 4 | 1800.00 | 927.07 | 864.00 | 1200.00 |
| 5 | 1800.00 | 584.98 | 296.00 | 600.00 |
All four methods allocate the same $9,000 depreciable amount
(cost - salvage) over the five years. SLN charges
$1,800 every year. SYD declines steadily from $3,000 to $600.
DDB starts at $4,000 and, in year 5, is clamped to $296 so that the
book value stops exactly at the $1,000 salvage value. DB applies a
fixed rate of 0.369 to a shrinking book value, so its charges fall from $3,690
to $585 and its five charges total about $8,999.66, a few cents below $9,000,
because DB rounds its rate to three decimal places.
Choose the method that matches how the asset is consumed. Use SLN
when the benefit is spread evenly, which gives flat, predictable expense in
every period. Use DDB or DB when the asset loses value
fastest in the early years, which is common for tax schedules. Use
SYD for a moderate acceleration that is gentler than
declining-balance, with charges that are simple fractions of the depreciable
amount and are easy to verify by hand.
SYD Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| per is less than 1 or greater than life | #NUM! error | Keep per in the range from 1 to life |
| life is zero or negative | #NUM! error | Life must be a positive number of periods |
| salvage is negative | #NUM! error | Use a non-negative salvage value |
| An argument is text that is not a number | #VALUE! error | Make sure the referenced cells contain numbers; ISNUMBER can verify |
| Referenced cell is empty | Empty cost or salvage counts as 0; an empty life or per gives #NUM! | Fill in all four inputs before using the formula |
| life and per use different units | Plausible but incorrect amounts | Express life and per in the same time unit |
| salvage is greater than cost | Negative depreciation | Check the values; the asset must cost more than its salvage value |
| Referenced cell contains an error | The same error is passed through | Fix the source cell, or wrap the formula in IFERROR |
Most SYD errors come from a period outside the range 1 to
life or from a non-positive life. Note that Excel
raises #NUM!, not #DIV/0!, when life is
zero. Wrap the formula in IFERROR to substitute a friendly value
when inputs may be incomplete.
The SYD function gives you a predictable accelerated depreciation
method: every period receives a plain fraction of cost - salvage,
the fractions decline one step at a time, and a complete schedule ends exactly
at the salvage value. Keep per between 1 and life in
the same unit, express life in months when you need monthly
charges, and remember how the four methods differ: SLN is flat,
SYD accelerates gently, and DB and DDB
accelerate hardest. With these rules in hand you can build accurate depreciation
schedules and choose the method that fits your accounting or tax needs.
Author
List all Excel Formulas.