Using Microsoft Excel
Businesses acquire several types of fixed assets or plant assets for their operations. Those long term assets benefit more than one period; thus, the cost must be allocated between reporting periods. As we know, various methods for allocating exist such as straight line, sum of the years, double declining and others.
As we learned in school, these systematic allocations involve the use of simple math. In this blog, I will show you how to use Microsoft Excel (Google Sheets) to perform the Straight Line Method calculation. Remember, use depreciation methods for property, plant and equipment and other long term assets, excluding land.
Cost Allocation
Depreciation processes represent a cost allocation, not an asset valuation. Therefore, the book value (Cost less Accumulated Depreciation) does not represent the fair market value of the fixed asset. The theory behind cost allocation, tell us that over time the useful life on a depreciable asset will decline due to wear and tear or obsolescence. Thus an accounting journal entry must be recorded to reflect this.
Straight Line Method
The Straight Line Method expense the same amount of depreciation over the useful life of the asset. The depreciable cost represents the Cost minus any residual value (salvage value). The depreciable cost is then divided by the asset’s useful life. The result becomes the yearly expense.
In the example, using Microsoft Excel we can calculate the straight line depreciation. As you can see, formulas such as =IF(E$3<=$C4,SLN($B4,$D4,$C4),0) assist us to calculate the result in uneven situations. On this example, four assets with different useful life, and different residual values resulted in our depreciation table under the straight line method. The table headings show the years from 1 to 8, the residual value must be excluded for this calculation.
The formula used will stop calculating depreciation once the year exceeds the useful life of the asset. Therefore, year 3 has $0 depreciation for the computer asset with two years of useful life.
Excel Function – SLN()
The built in function SLN() allow us to calculate the straight line depreciation fairly easy. It consist of the following parameters: cost, residual value, and useful life. We use on the table this function and we use the If function statement to test if the year exceeds the useful life. If exceeds, we stop using the calculation from the SLN() function.
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.
Straight Line Spreadsheet
This Excel-based Fixed Assets Register is a comprehensive and user-friendly tool designed to help businesses of any size track, manage, and report on their capital assets. It supports multiple assets and automates straight-line depreciation calculations over the asset’s useful life.
Please add some comments, requests for new material, or spreadsheets. I want to improve your site experience and keep you as a regular visitor.
Please consider subscribing to receive notifications when new posts are available!