Using Google Sheets function SYD( )
To setup a Sum of the Years Depreciation Schedule for your fixed assets, you need to list your assets (column A). Then, include the item’s cost (column B), include each asset’s useful life in years (column C). Last, determine any residual value (optional), if not include $0 (column D).
Next, I included in each column a consecutive number representing the useful life. In this example, since the largest useful life is 8 years, I numbered row 3, from E to L, 1 to 8.
The function used: =if(ISERROR(SYD($B4,$D4,$C4,E$3)),0,SYD($B4,$D4,$C4,E$3))
This is a nested function, consisting of three functions, one inside the other. First, I created a conditional statement using =if(test criteria, true, false). The tested criteria use the function iserror(criteria); which results on a true or false result. The criteria is SYD(), the sum of the years function, by Google Sheets.
The SYD() function will use the values entered on columns B, C, D and E to calculate the depreciation per year using the sum of the years method. Remember, that if an asset has a three year life, the the first year is a 3/6 depreciation, the second year 2/6 and the third year 1/6. Where the numerator is the descending order of 3, 2, 1 years of life. The denominator represent the sum of 3 + 2 + 1 = 6.
The rationale of the formula
The list contains various length of useful years. Therefore, if you try to use the function without testing for invalid periods you will get errors. Instead, you use the if statement to test for an error, if true place $0. If false, then it will not calculate the sum of the years depreciation. This statement will hold true for every year, as stated in the useful life column. Once the useful life ends, the formula stops calculating depreciation.
Please note – this schedule is a simple version; a more complete version must have the fixed asset acquisition date and the reporting date. As a short version, I included only the useful life, but with date manipulation you can get the exact result for your schedule.
Conclusion:
Several businesses present their depreciation schedules using poor mathematical calculations. Using the sum of the years and other available functions allows you to make better presentations. Your schedules look clean and well prepared with easy-to-follow details.
I understand that sometimes, you do not have the time to explore and put together spreadsheet solutions. To ease up things, I have included a spreadsheet with this methodology. It is only $3 to cover the costs of running this website and keep adding new material. You can modify, update, or change the spreadsheet to accommodate your needs.
Please add some comments, requests of new material or spreadsheets. I want to improve your site experience and keep you as a regular visitor.