### VBA Projects

###### Full Access with Source Code
• Designed and Developed by PNRao

• Full Access with VBA Source Code

• Well Commented Codes Lines

• Creative and Professional Design

#### 120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

###### 120+ PM Templates Includes:
• 50+ Excel Templates

• 50+ PowerPoint Templates

• 25+ Word Templates

Share Post

VBA SYD function is a financial function used to calculate the depreciation of an asset for a specific period using the Sum-of-the-Year-Digits method. This method is commonly used in accounting and finance to determine the value of an asset over its useful life. The SYD function takes into consideration the total cost of the asset, its useful life, and the specific period for which the depreciation is being calculated.

## VBA SYD Function – Purpose, Syntax and Arguments

### Purpose

The purpose of the SYD (Sum-of-the-Year-Digits) function is to provide a simple and efficient way to accurately calculate the depreciation of an asset over a specified period. This function is especially useful for accounting and financial professionals who need to calculate the value of assets for tax and financial reporting purposes.

## What is the SYD Function?

The SYD function stands for ‘Sum of Years’ Digits. It is a financial function used in calculating depreciation. Depreciation is a method of allocating the cost of a tangible asset over its useful life. The SYD function calculates the depreciation expense for an asset for a specific period using the sum of years’ digits method.
The sum of years’ digits method is based on the principle that the depreciation expense for an asset is higher in the earlier years of its useful life and decreases as the asset gets older. This method is commonly used for tax purposes to provide a higher depreciation expense in the early years, which reduces the tax burden.

### Syntax

The syntax for the SYD function is as follows:

```SYD(cost, salvage, life, period)
```

Cost: The initial cost of the asset.
Salvage: The value of the asset at the end of its useful life.
Life: The useful life of the asset.
Period: The period for which you want to calculate depreciation.
The SYD function returns the depreciation expense for an asset for a specific period. The returned value is a negative number, as it represents an expense.

### Example

Suppose we have purchased a machine for \$10,000 with an expected salvage value of \$2,000 and a useful life of 5 years. We want to calculate the depreciation for the third year using the SYD function.

```SYD(10000, 2000, 5, 3)
```

The result of this calculation would be \$2,400, which is the depreciation for the third year.

### Remarks and Important Notes

• The SYD function is a built-in function in VBA and does not need to be defined or loaded into the code.
• The result of the function is returned as a numeric value.
• The SYD function is not case-sensitive, which means it can be written in uppercase, lowercase, or mixed case.
• If the value for the period argument is less than 1 or greater than the asset’s useful life, the function will return an error.
• If the value for the cost, salvage, or life argument is negative, the function will return an error.

### Important Note for Excel Users

While the SYD function is not available in Excel, the same result can be achieved by using the built-in SYD function in Excel. The syntax and arguments for the Excel SYD function are the same as the VBA SYD function. The only difference is the formula bar in Excel will display a ‘#’ sign before the formula to indicate that it is not a recognized function in Excel.
Overall, the VBA SYD function is a very useful tool for accurately calculating the depreciation of assets using the Sum-of-the-Year-Digits method. It saves time and effort for professionals in the fields of accounting and finance and provides a more efficient way of handling financial calculations.

## Example 1: Straight-Line Depreciation

Let’s say we have a machine with an initial cost of \$20,000, a salvage value of \$5,000, and a useful life of 10 years. We want to calculate the depreciation expense for the first year.
The code for this example would be:

```Sub Example1()
Dim cost As Double
Dim salvage As Double
Dim life As Integer
Dim period As Integer
cost = 20000
salvage = 5000
life = 10
period = 1
Dim depreciation As Double
depreciation = SYD(cost, salvage, life, period)
MsgBox "The depreciation expense for the first year is " & depreciation
End Sub
```

When we run this code, the output would be:  The depreciation expense for the first year is -1500

Let’s break down the code. Firstly, we declare the variables: cost, salvage, life, and period, and assign their respective values. Then, we declare the depreciation variable and use the SYD function to calculate the depreciation expense. Finally, we use a message box to display the result.
In this example, we can see that the depreciation expense for the first year is \$1,500. This means that the company can claim this amount as an expense on their tax return, which ultimately reduces their taxable income.

## Example 2: Double-Declining Balance Depreciation

In this example, we will use the same values as in Example 1, but use the double-declining balance method instead. With this method, the depreciation expense is calculated by doubling the straight-line rate and applying it to the remaining book value of the asset. Let’s calculate the depreciation expense for the first year.
The code for this example would be:

```Sub Example2()
Dim cost As Double
Dim salvage As Double
Dim life As Integer
Dim period As Integer
cost = 20000
salvage = 5000
life = 10
period = 1
Dim depreciation As Double
Dim straightLineRate As Integer
Dim doubleDecliningRate As Integer
straightLineRate = 1 / life
doubleDecliningRate = straightLineRate * 2
depreciation = SYD(cost, salvage, life, period)
'value after first period: cost - depreciation
depreciation = SYD((cost - depreciation), salvage, life - 1, period)
MsgBox "The depreciation expense for the first year is " & depreciation
End Sub
```

When we run this code, the output would be: The depreciation expense for the first year is -2875

Let’s break down the code. In this example, we first declare the variables and assign their values. Then, we use the SYD function to calculate the depreciation expense using the sum of years’ digits method. However, this method calculates the depreciation expense for the entire life of the asset, so we need to make some adjustments.
Firstly, we calculate the straight-line rate, which is the rate at which the asset depreciates each year. Then, we double this rate to get the double-declining balance rate. We then use the SYD function again to calculate the depreciation expense, but this time we use the adjusted cost and life values. This gives us the depreciation expense for the first year.
In this example, we can see that the depreciation expense for the first year is \$2,875, which is significantly higher than the straight-line depreciation method.

## Example 3: Depreciation for Different Periods

In the previous examples, we calculated the depreciation expense for the first period in each case. However, we can also use the SYD function to calculate the depreciation expense for multiple periods. Let’s calculate the depreciation expense for the first five years using the sum of years’ digits method.
The code for this example would be:

```Sub Example3()
Dim cost As Double
Dim salvage As Double
Dim life As Integer
Dim period As Integer
cost = 20000
salvage = 5000
life = 10
period = 5
Dim depreciation As Double
Dim straightLineRate As Integer
straightLineRate = 1 / life
For i = 1 To period
depreciation = SYD(cost, salvage, life, i)
'value after first period: cost - depreciation
cost = cost - depreciation
life = life - 1
MsgBox "The depreciation expense for year " & i & " is " & depreciation
Next i
End Sub
```

When we run this code, the output would be:

```The depreciation expense for year 1 is -1500
The depreciation expense for year 2 is -2300
The depreciation expense for year 3 is -1950
The depreciation expense for year 4 is -1650
The depreciation expense for year 5 is -1400
```

Let’s break down the code. In this example, we use a ‘For’ loop to calculate the depreciation expense for each year (represented by the ‘i’ variable). In each iteration, we use the SYD function to calculate the depreciation expense and then update the cost and life variables for the remaining periods.
In this example, we can see that the depreciation expense decreases each year, which is consistent with the sum of years’ digits method.

## Conclusion

In this blog post, we have explored the SYD function in VBA and its usage in calculating depreciation expense using the sum of years’ digits method. We have provided examples to help you understand the function better. By knowing how to use the SYD function, you can efficiently calculate depreciation expenses for your assets and save valuable time and effort. With continued practice, you can become proficient in using the SYD function and apply it to your specific financial calculations in Excel.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER ###### Project Portfolio Management Templates
Categories: VBA FunctionsLast Updated: September 30, 2023