SYD Function Example

Using the sum-of-years' digits method, this example returns the depreciation of an asset for a specified period given the asset's initial cost (InitCost), the salvage value at the end of the asset's useful life (SalvageVal), the total life of the asset in years (LifeTime), and the period for which the depreciation is calculated (PDepr), also in years.

Const YEARMONTHS = 12                    ' Number of months in a year.
Fmt = "###,##0.00"                       ' Define money format.
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("What's the asset's value at the end of its life?")
MonthLife = InputBox("What's the asset's useful life in months?")
Do While MonthLife < YEARMONTHS          ' Ensure period is >= 1 year.
   MsgBox "Asset life must be a year or more."
   MonthLife = InputBox("What's the asset's useful life in months?")
Loop
LifeTime = MonthLife / YEARMONTHS        ' Convert months to years.
If LifeTime <> Int(MonthLife / YEARMONTHS) Then
   LifeTime = Int(LifeTime + 1)          ' Round up to nearest year.
End If
DepYear = CInt(InputBox("For which year do you want depreciation?"))
Do While DepYear < 1 Or DepYear > LifeTime
   MsgBox "You must enter at least 1 but not more than " & LifeTime
   DepYear = CInt(InputBox("For what year do you want depreciation?"))
Loop
PDepr = SYD(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & Format(PDepr, Fmt) & "."