Excel amortization: The SYD function

Your company just acquired a new machine for $10,000. This machine has a useful life of ten years. It has been estimated that it will then have a residual value of $2,500. Bookkeeping asked you to use the sum-of-years’ digits depreciation method to calculate how much it should be depreciated each year. Excel amortization and the SYD function use this method to calculate depreciation. In this learning module, you will be shown how to use the excel amortization and the SYD function.


Capital assets, such as machines, lose their value over time because of wear and tear. In a business, bookkeeping writes off a certain portion of the value of such capital assets each year, until only a salvage value remains. Tax authorities generally regulate how various kinds of capital goods can be written off.
In accounting, this bookkeeping procedure is generically known as depreciation. One of several depreciation methods is known as the sum-of-years’ digits depreciation method. Excel amortization SYD function uses this method to calculate depreciation.

The table in the above illustration contains all the information required to calculate the depreciation. To begin, click in cell C7, where the first result, the depreciation for the first year, is to appear. Then start the Function Wizard by clicking the button next to the formula bar. Find and select the excel amortization SYD function in the Financial category. After you click OK, the Function Arguments dialog box will open:

Cost is the initial cost of the asset. You can enter the cost here or, as in the current example, a reference to a cell containing the cost. If you enter a cell reference, make certain it is an absolute reference; in the example: $B$3
Here you enter the expected Salvage value or a reference to a cell containing that value; in the example: $B$4
Life indicates the useful life of the asset or the simply the number of years over which the asset is to be depreciated. In the current example, this value was entered in $B$5
The Per argument indicates the specific period, i.e., the specific year, for which depreciation is to be calculated. In the example, this is B7
The dialog box with the arguments filled in looks like this for the current example:

Note that the cell reference to depreciation year 1 is a relative cell reference. That is because you are going to copy the function down the cells in column C for years 2, 3, 4 and so on.
When satisfied with your arguments, finish by clicking OK. Now you can use AutoFIll to copy the function in C7 down to C16.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231