The VBA SYD Function

Related Functions:
VBA DDB
VBA SLN

Description

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:

SYD( Cost, Salvage, Life, Period )

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.

VBA SYD Function Example

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 DoubleDim syd_yr2 As DoubleDim syd_yr3 As DoubleDim syd_yr4 As DoubleDim syd_yr5 As Double' Calculate the depreciation during year 1syd_yr1 = SYD( 10000, 1000, 5, 1 )' syd_yr1 is now equal to 3000.' Calculate the depreciation during year 2syd_yr2 = SYD( 10000, 1000, 5, 2 )' syd_yr2 is now equal to 2400.' Calculate the depreciation during year 3syd_yr3 = SYD( 10000, 1000, 5, 3 )' syd_yr3 is now equal to 1800.' Calculate the depreciation during year 4syd_yr4 = SYD( 10000, 1000, 5, 4 )' syd_yr4 is now equal to 1200.' Calculate the depreciation during year 5syd_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.

VBA SYD Function Error

The VBA SYD function produces the Run-time error '5': Invalid procedure call or argument if either:

• The supplied Salvage is < 0
or
• The supplied Life or the supplied Period is ≤ 0
or
• The supplied Period > Life.