It is easy to make a depreciation worksheet in Excel without using complex and conditional formulas. You only need to familiarize yourself with the SLN Straight-line method , DDB Double-declining balance method , and SYD Sum-of-the-years digits method formulas to compute depreciation.
In this article, we’ll teach you how to make a depreciation worksheet in Excel, from assembling column headers to entering formulas, and explain the usage and arguments in each depreciation formula. We’ve also prepared a downloadable depreciation worksheet template that’s ready to use.
Thank you for downloading!
Hopefully, by walking you through how to make a depreciation worksheet in Excel from scratch, you’ll learn how to further customize our downloadable template to suit your needs.
Step 1: Assemble the Column Headers in Row 1 of the Spreadsheet
Create a new Excel spreadsheet file and assemble the following information in Row 1 of the spreadsheet.
- Asset’s Name
- Historical Cost
- Salvage Value
- Useful Life
- Current Period
- Depreciation Expense
- Accumulated depreciation, beginning
- Accumulated depreciation, ending
Your spreadsheet should look like this:
For values in Columns B, C, F, G, and H, remember to format it as Currency so that it shows the dollar sign. You do this by right-clicking on a cell and selecting Format Cells. From there, navigate to Currency and choose your formatting style as shown below:
Here’s what the worksheet looks like in our downloadable template. The blue cells are the areas where you need to enter information, whereas the gray cells contain formulas. If you use our template, don’t enter information in the gray cells to preserve the formulas.
The easiest way to get fixed asset data is to generate a report in your accounting software that shows all activity in your fixed asset balance sheet accounts. By analyzing the activity in these accounts, you should be able to determine the cost of the asset and when it was purchased. For the useful life and salvage value, check if you have documentation that sets the useful lives and salvage values of different fixed asset classes.
Step 2: Enter the Depreciation Expense Formulas
The depreciation formula may differ depending on what method you use. Regardless of the method, enter the formula in Column F of the worksheet.
Here are the syntaxes of the different depreciation formulas you can use:
Syntax: =SLN(cost, salvage, life)
- Cost (required) is the historical cost of the asset.
- Salvage (required) is the salvage value of the asset. If none, place zero instead.
- Life (required) is the useful life of the asset.
In the spreadsheet, here’s how you insert the arguments:
The cost of the asset, salvage value, and useful life are in cells B2, C2, and D2, respectively. Click Enter after completing all arguments.
There are two common variations of the declining balance method. You can either use double-declining or 150% declining by using just one syntax.
Syntax: =DDB(cost, salvage, life, period, [factor])
- Cost (required) is the historical cost of the asset.
- Salvage (required) is the salvage value of the asset. If none, place zero instead.
- Life (required) is the useful life of the asset.
- Period (required) is the point in time for which you want to compute depreciation. It must be the same units as life, such as if life is in years, then the period can be 1 for first year, 2 for second year, and so on.
- Factor (optional) is the rate at which depreciation declines. If omitted, Excel uses 2 or 200% declining rate. If you want to use 150% declining, add 1.5 to the factor argument.
For 200% declining balance, here’s how you’ll do it in the spreadsheet:
By default, the factor is set at 2, so you can ignore the factor argument if you want to use the double-declining balance method. But if you want to depreciate using 150% declining balance, you have to insert 1.5 in the factor argument, as shown below:
Syntax: =SYD(cost, salvage, life, per)
- Cost (required) is the historical cost of the asset.
- Salvage (required) is the salvage value of the asset. If none, place zero instead.
- Life (required) is the useful life of the asset.
- Per (required) is the point in time for which you want to compute depreciation, such as 1 for first year or 10 for 10th year.
In the spreadsheet, the formula looks like this:
The current period is 2, which means we want to know the sum-of-the-years depreciation for the second year.
In our downloadable template, the worksheet automatically determines the depreciation formula based on a series of conditions. There’s no need to enter the formula manually for each row. However, our template also follows the same formulas we explained above. We highly recommend that you familiarize yourself with the three depreciation formulas above just in case you want to modify our template.
Step 3: Enter the Accumulated Depreciation Formulas
You need to get your previous year’s depreciation worksheet, copy the ending Accumulated Depreciation per fixed asset, and paste it to this year’s beginning Accumulated Depreciation.
If you don’t have a depreciation worksheet, generate a report from your accounting software that shows the beginning Accumulated Depreciation per fixed asset. Otherwise, you have to compute the beginning balance manually.
This is what it looks like in the spreadsheet.
In Cell H2, enter =F2+G2 to add Depreciation Expense and Accumulated Depreciation, beginning to arrive at Accumulated Depreciation, ending. Use the autofill feature to copy the formula in Cell H2 automatically to succeeding rows.
To do this, click Cell H2 and hover your cursor around the bottom right corner of the cell. You’ll see a small square there. Click it, and drag it down.
Frequently Asked Questions (FAQs)
A depreciation worksheet is a document that shows the depreciation expense and accumulated depreciation of an asset. It lets you track depreciation for the current year and serves as an internal reference document for depreciation journal entries.
The formulas you need to familiarize yourself with are SLN, DDB, and SYD. The syntax of each formula is as follows:
- SLN formula: =SLN(cost, salvage, life)
- DDB formula: =DDB(cost, salvage, life, period, [factor])
- SYD formula: =SYD(cost, salvage, life, per)
Bottom Line
We hope we provided you with enough instructional information on how to create a depreciation worksheet in Excel. Having a depreciation worksheet can serve as a basis for making depreciation journal entries. Besides that, you can use it as an internal document that shows important depreciation information.
