Lesson 175: Making a depreciation table in Excel

Excel provides five different amortization functions. We will consider an asset with an initial value of $10,000, a residual value of $1000, and a useful life of 10 periods (years). Below you can find the results of all five functions. Each function will be analyzed separately.

Most assets will lose value over time. The SYD, DB, DDB, and VDB functions will help you calculate this loss.

Make a depreciation table in Excel

SLN

The SLN (Straight Line) function is very easy to use. Each year the depreciation value is the same.

The SLN function does the following calculation. Deprecated value = (10,000 – 1,000) / 10 = 900.00. If we subtract this value 10 times, the property will depreciate between 10,000 and 1000 in 10 years (see first picture, bottom half).

SYD

The SYD (Sum of Years’ Digits) function is also very easy to use. As you can see below, this function also requires the number of cycles.

The SYD function performs the following calculations. The useful life of 10 years produces a total of 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1 = 55. Assets lose 9000 value. Depreciation value of period 1 = 10/55 * 9000 = 1,636.36. 2nd period depreciation = 9/55 * 9000 = 1,472.73, etc. If we subtract these values, the asset depreciates from 10,000 to 1000 in 10 years (see first figure, bottom half).

DB

The DB (Declining Balance) function is a bit more complicated. It uses a fixed rate to calculate the depreciation value.

The DB function performs the following calculations. Fixed rate = 1 – ((salvage / cost) ^ (1 / life)) = 1 – (1000/10,000)^(1/10) = 1 – 0.7943282347 = 0.206 (round to 3 decimal places) . Depreciation value of period 1 = 10,000 * 0.206 = 2,060.00. 2nd period depreciation = (10,000 – 2,060.00) * 0.206 = 1635.64… If we subtract these values, the asset depreciates from 10,000 to 995.88 in 10 years (see first figure) , lower half).

DDB

The DDB (Double Declining Balance) function is also very easy. However, sometimes you won’t get salvage using this function.

The DDB function performs the following calculations. A useful life of 10 years results in a ratio of 1/10 = 0.1. Because this function is called Double Declining Balance, we double this ratio (factor = 2). Depreciation value of period 1 = 10,000 * 0.2 = 2,000.00. 2nd period depreciation = (10,000 – 2,000.00) * 0.2 = 1600.00,.. In this example, if we subtract depreciation, the asset will depreciate from 10,000 to 1073.74 for 10 years (see first picture, bottom half).

VDB

The VDB (Variable Declining Balance) function uses the DDB (Double Declining Balance) method by default. The 4th parameter represents the start period, the 5th parameter represents the end period.

The VDB function is a function that helps you calculate the depreciation of fixed assets using the declining balance method. Use this calculation function to return the asset’s depreciation for the period you specify, including incomplete maturities, using the double declining balance method or some other method you specify. You can find out more details about this function here.

In addition, you can also view many other excel articles here.