Table of Contents

Depreciation

Excel offers five different depreciation functions. We consider an asset with an initial cost of $10000 a salvage value (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 explained separately in the next 5 paragraphs.

Depreciation Results in Excel

Most assets lose more value in the beginning of their useful life. The SYD DB DDB and VDB functions have this property.

Depreciation Chart

SLN

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

SLN (Straight Line) Function

The SLN function performs the following calculation. Depreciation Value = (10000 – 1000) / 10 = 900.00. If we subtract this value 10 times the asset depreciates from 10000 to 1000 in 10 years (see first picture bottom half).

SYD

The SYD (Sum of Years’ Digits) function is also easy. As you can see below this function also requires the period number.

SYD (Sum of Years' Digits) Function

The SYD function performs the following calculations. A useful life of 10 years results in a sum of years of 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1 = 55. The asset loses 9000 in value. Depreciation value period 1 = 10/55 * 9000 = 1636.36. Depreciation value period 2 = 9/55 * 9000 = 1472.73 etc. If we subtract these values the asset depreciates from 10000 to 1000 in 10 years (see first picture bottom half).

DB

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

DB (Declining Balance) Function

The DB function performs the following calculations. Fixed rate = 1 – ((salvage / cost) ^ (1 / life)) = 1 – (1000/10000)^(1/10) = 1 – 0.7943282347 = 0.206 (rounded to 3 decimal places). Depreciation value period 1 = 10000 * 0.206 = 2060.00. Depreciation value period 2 = (10000 – 2060.00) * 0.206 = 1635.64 etc. If we subtract these values the asset depreciates from 10000 to 995.88 in 10 years (see first picture bottom half).

Note: the DB function has a fifth optional argument. You can use this argument to indicate the number of months to go in the first year (If omitted it is assumed to be 12). For example set this argument to 9 if you purchase your asset at the beginning of the second quarter in year 1 (9 months to go in the first year). Excel uses a slightly different formula to calculate the depreciation value for the first and last period (the last period represents an 11th year with only 3 months).

DDB

The DDB (Double Declining Balance) function is easy again. However sometimes you don’t reach the salvage value when you use this function.

DDB (Double Declining Balance) Function

The DDB function performs the following calculations. A useful life of 10 years results in a rate of 1/10 = 0.1. Because this function is called Double Declining Balance we double this rate (factor = 2). Depreciation value period 1 = 10000 * 0.2 = 2000.00. Depreciation value period 2 = (10000 – 2000.00) * 0.2 = 1600.00 etc. As said earlier sometimes you don’t reach the salvage value when you use this function. In this example if we subtract the depreciation values the asset depreciates from 10000 to 1073.74 in 10 years (see first picture bottom half). However read on to fix this.

Note: the DDB function has a fifth optional argument. You can use this argument to use a different factor.

VDB

The VDB (Variable Declining Balance) function uses the DDB (Double Declining Balance) method by default. The 4th argument indicates the starting period the 5th argument indicates the ending period.

VDB (Variable Declining Balance) Function

The VDB function performs the same calculations as the DDB function. However it switches to Straight Line calculation (yellow values) to make sure you reach the salvage value (see first picture bottom half). It only switches to Straight Line calculation when Depreciation Value Straight Line is higher than Depreciation Value DDB. In period 8 Depreciation Value DDB = 419.43. We still have 2097.15 – 1000 (see first picture bottom half) to depreciate. If we use the Straight Line method this results in 3 remaining depreciation values of 1097.15 / 3 = 365.72. Depreciation Value Straight Line is not higher so we do not switch. In period 9 Depreciation Value DDB = 335.54. We still have 1677.72 – 1000 (see first picture bottom half) to depreciate. If we use Straight line method this results in 2 remaining depreciation values of 677.72 / 2 = 338.86. Depreciation Value Straight Line is higher so we switch to Straight Line calculation.

Note: the VDB function is much more versatile than the DDB function. It can calculate the depreciation value of multiple periods. In this example =VDB(CostSalvageLife03) reduces to 2000 + 1600 + 1280 = 4880. It contains a 6th and 7th optional argument. You can use the 6th argument to use a different factor. If you set the 7th argument to TRUE it does not switch to Straight Line calculation (the same as DDB).

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog