The VBA SYD function calculates the sum-of-years' digits depreciation for a specified period in the lifetime of an asset.
The syntax of the function is:
Where the function arguments are:
| Cost | - |
The initial cost of the asset. |
| Salvage | - | The value of the asset at the end of its useful life. |
| Life | - | The number of periods over which the asset is to be depreciated. |
| Period | - | The period number for which you want to calculate the depreciation. |
In the example below, the VBA SYD function uses the sum-of-years' digits depreciation method to calculate the yearly depreciation of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.
|
' Calculate the yearly depreciation of an asset that cost $10,000 at
' the start of year 1, and has a salvage value of $1,000 after 5 years. Dim syd_yr1 As Double Dim syd_yr2 As Double Dim syd_yr3 As Double Dim syd_yr4 As Double Dim syd_yr5 As Double
' Calculate the depreciation during year 1
syd_yr1 = SYD( 10000, 1000, 5, 1 ) ' syd_yr1 is now equal to 3000.
' Calculate the depreciation during year 2
syd_yr2 = SYD( 10000, 1000, 5, 2 ) ' syd_yr2 is now equal to 2400.
' Calculate the depreciation during year 3
syd_yr3 = SYD( 10000, 1000, 5, 3 ) ' syd_yr3 is now equal to 1800.
' Calculate the depreciation during year 4
' Calculate the depreciation during year 5syd_yr4 = SYD( 10000, 1000, 5, 4 ) ' syd_yr4 is now equal to 1200. syd_yr5 = SYD( 10000, 1000, 5, 5 ) ' syd_yr5 is now equal to 600. |
The above VBA code calculates that:
|
Depreciation during year 1 = $3,000; Depreciation during year 2 = $2,400; Depreciation during year 3 = $1,800; Depreciation during year 4 = $1,200; Depreciation during year 5 = $600. |
Note that the five calculated depreciation values add up to $9,000, which, as expected, is the difference between the cost, $10,000 and the salvage value, $1,000.
The VBA SYD function produces the Run-time error '5': Invalid procedure call or argument if either: