Calculating double-declining balance depreciation for tax purposes in Excel

Q. I was excited to see the article about ways to calculate depreciation in Excel, especially when I saw one of them was double-declining balance (DDB). As tax professionals, we’re always trying to calculate DDB to conform to the tax rules and end up doing this manually with VLOOKUPs and depreciation tables.

As I read through the article, I noticed that Excel’s version of DDB is not the tax version. We use a half-year or midquarter convention, and it takes one more year to depreciate an asset past its life (e.g., six years to depreciate a five-year asset, eight years to depreciate a seven-year asset). As an example, the following percentages are used for a five-year asset:

1:20%

2:32%

3:19.2%

4:11.52%

5:11.52%

6:5.76%

Is there a way to leverage the DDB formulas to achieve the correct formulas for tax purposes?

A. You can view the original article this reader is referring to here. You can use a built-in Excel function to calculate the modified accelerated cost recovery system (MACRS) depreciation, but you need to use the variable-declining balance function instead of the double-declining balance function. Variable-declining balance uses the double-declining factor but also initiates the automatic switch to straight-line depreciation once that is greater than double-declining.

You can download the Excel workbook used in this article here.

The syntax for the variable-declining balance method of depreciation in Excel is =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch]). The first five arguments are required, and the last two are optional. The arguments are defined as follows:

• cost: Original cost of the asset.
• salvage: Salvage value of the asset (the book value of the asset after it is fully depreciated).
• life: Useful life of the asset (how long the asset is estimated to be used in operations).
• start_period: Period to start calculating the depreciation (the unit used for the period must be the same as the unit used for the life; e.g., years, months, etc.).
• end_period: Period to end calculating the depreciation (the unit used for the period must be the same as the unit used for the life; e.g., years, months, etc.).
• factor: By what factor to multiply the rate of depreciation (this argument is optional; if it is left blank, Excel will assume the factor is 2, the straight-line depreciation rate times two, which is double-declining balance depreciation).
• no_switch: Value to specify whether to switch to straight-line depreciation when that calculation is greater than the calculation for declining-balance depreciation (this argument is optional; if it is left blank or the value is FALSE, Excel will switch; if the value is TRUE, Excel will not switch).

See the screenshot below for the facts of the asset we will depreciate using the variable-declining balance for the MACRS half-year convention.

Note that the useful life is five years, but I have six periods listed in the schedule. This is to account for the half-year in the first and last periods. Create your first formula in cell B8, which is =VDB($C$2,0,$C$4,A8-1,A8-0.5). The first argument is the cost, which is referenced as C2. The second argument is the salvage value, but instead of referencing C3, I insert a zero since that is not part of the MACRS calculation. The third argument is the useful life and is referenced as C4. The fourth argument is the starting period. We want the depreciation to start in period zero, so we must include minus 1 in the calculation: A8-1. The fifth and final required argument is the ending period. If our first period had been a full year, I would have only referenced cell A8 (period 0 to 1). However, this is a half-year convention, so the first year is only a half-year. Therefore, my fifth argument will be A8-0.5, to only capture half of the period. I did use absolute references in my first and third arguments. Absolute references instruct Excel not to change the rows and/or columns as a formula is being copied down or across a spreadsheet.

The second formula will be slightly different since it is for the second period and will calculate depreciation for a full year instead of a half-year. The formula for period two is =VDB($C$2,0,$C$4,A9-1.5,A9-0.5). Besides the fact that this formula now references A9 instead of A8 because we are calculating the depreciation for the next period, now we subtract 1.5 from the starting period instead of one. This is because we actually want to start the period in the middle of period zero instead of period one. Our ending period is in the middle of period one (period two minus 0.5), calculating a full year of depreciation.

You can drag this formula down to period five without making any changes as long as you use absolute references.

The last period is only a half-year, so the formula is =VDB($C$2,0,$C$4,A13-1.5,A12). The difference is that the ending period is defined as period 5. With the starting period being 1.5 years prior to period six, this calculates only a half-year of depreciation (middle of period four to period five).

See the screenshot below for the formulas used in the spreadsheet and the results of the MACRS half-year depreciation calculations.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.