Page 42 - JoFA_Jan_Apr23

TECHNOLOGY Q&A

MICROSOFT EXCEL
Calculating double-declining balance depreciation for
tax purposes in Excel

Q. I was excited to see the article about ways to calculate depreciation estimated to be used in operations).
in Excel, especially when I saw one of them was double-declining ■ start_period: Period to start calculating the
balance (DDB). As tax professionals, we’re always trying to calculate depreciation (the unit used for the period must
DDB to conform to the tax rules and end up doing this manually with be the same as the unit used for the life; e.g.,
VLOOKUPs and depreciation tables. years, months, etc.).
As I read through the article, I noticed that Excel’s version of DDB ■ end_period: Period to end calculating the
is not the tax version. We use a half-year or midquarter convention, depreciation (the unit used for the period must
and it takes one more year to depreciate an asset past its life (e.g., be the same as the unit used for the life; e.g.,
six years to depreciate a five-year asset, eight years to depreciate a years, months, etc.).
seven-year asset). As an example, the following percentages are used ■ factor: By what factor to multiply the rate of
for a five-year asset: depreciation (this argument is optional; if it is
1: 20% left blank, Excel will assume the factor is 2, the
2: 32% straight-line depreciation rate times two, which
3: 19.2% is double-declining balance depreciation).
4: 11.52% ■ no_switch: Value to specify whether to switch to
5: 11.52% straight-line depreciation when that calculation is
6: 5.76% greater than the calculation for declining-balance
Is there a way to leverage the DDB formulas to achieve the correct depreciation (this argument is optional; if it is left
formulas for tax purposes? blank or the value is FALSE, Excel will switch; if
the value is TRUE, Excel will not switch).
A. You can view the original article this reader is See the screenshot below for the facts of the
Submit a referring to on journalofaccountancy.com. You can asset we will depreciate using the variable-declining
question use a built-in Excel function to calculate the modi- balance for the MACRS half-year convention.
fied accelerated cost recovery system (MACRS)
Do you have depreciation, but you need to use the variable-
technology declining balance function instead of the double-
questions for this declining balance function. Variable-declining
column? Or, after balance uses the double-declining factor but also
reading an answer, initiates the automatic switch to straight-line depre-
do you have a ciation once that is greater than double-declining.
better solution? You can download the Excel workbook used in
Send them to this article and access a video demonstration of how
jofatech@aicpa.org. to calculate depreciation for tax purposes that ac-
We regret companies this article at journalofaccountancy.com.
being unable to The syntax for the variable-declining balance
individually answer method of depreciation in Excel is =VDB(cost,
all submitted salvage, life, start_period, end_period, [factor],
questions. [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

40 | Journal of Accountancy January 2023