Spreadsheet Practice Pays Off
Excel’s Depreciation Functions
Tina M. Loraas and Jennifer M. Mueller
MAY 2008 - Spreadsheets
support accounting and financial statement preparation on a daily
basis, but few users ever stop to question the reliability of a
Microsoft Excel accounting function. An unspoken assumption is that
the software developer completely understood the calculation that
the function was designed to perform. Given the complexity of some
accounting and finance calculations, this assumption may not always
suggest three spreadsheet practices to help ensure that Excel
functions are working appropriately from an accounting standpoint:
the purpose of the function you are going to use.
the inputs or arguments of the function.
the method of calculation to ensure it is operating as expected.
can be illustrated in the context of Excel’s built-in depreciation
functions (see Sidebar).
They are as follows: straight-line (SLN), sum of the years’
digits (SYD), declining balance (DB), double-declining balance
(DDB), and variable declining balance (VDB). The depreciation
context should be familiar to users because spreadsheets are commonly
used in corporate accounting to calculate and maintain asset-depreciation
schedules for use in planning, budgeting, financial reporting,
or what-if analyses related to asset acquisitions and disposals.
In addition, Excel’s depreciation functions are a likely
point of error without the three spreadsheet practices mentioned
good practice is to understand the intended purpose of the function
to be used. Though this step may seem superficial at first glance,
acronyms used by Excel to represent functions do not always clearly
indicate what calculation a function is performing. For example,
Excel offers three different declining balance functions: DB,
DDB, and VDB. The question for a user is, “Which is the
is important to understand the arguments, or inputs, sought by
a function. For example, the DDB function has five inputs (cost,
salvage, life, period, factor), one of which is optional argument
(factor). While the first three inputs (cost, salvage, and life)
seem straightforward, the period and factor inputs are less clear.
Incorrectly entering any one of the inputs may prohibit Excel
from completing the calculation, resulting in an error message
(e.g., #N/A or #DIV/0) or, worse, the function may yield an apparently
valid result that is actually incorrect.
and perhaps most important, good spreadsheet practice is to check
the function’s method of calculation against the correct
or intended method of calculation when first constructing a spreadsheet.
Doing so allows a user to avoid assuming that the programmer understood
the accounting method. Explanations found in Excel’s help
function are useful for establishing a clear understanding of
a function, its inputs, and its calculations.
of checking a function’s calculation method against the
intended calculation can be clearly demonstrated when considering
the declining balance functions within Excel. The DB and DDB functions
are subject to misuse in both the 2003 and 2007 versions of Excel,
as well as in templates that can be downloaded from Microsoft.
balance. The DB function determines depreciation
amounts on a fixed declining balance schedule by applying a constant
rate to the declining book value of the asset. The rate is a function
of the original asset cost, the salvage value, and the useful
life; specifically, Depreciation Rate = [1 – (Salvage Value/Cost)
this method meets the GAAP requirement of “systematic and
rational,” any asset with a zero-dollar salvage value will
be fully depreciated in the first period using the DB function.
This error occurs because the latter part of the depreciation
rate equation becomes zero, yielding a depreciation rate of 100%.
The formula causes initial depreciation calculations to be extreme
any time the salvage value is less than approximately 10% of the
asset’s acquisition cost. Thus DB should be used only when
the salvage value of the asset is a significant portion of the
balance. Double-declining balance, perhaps a more
commonly used accelerated method, also produces declining depreciation
expenses over time by applying a constant rate to the book value
of the asset. The rate can be up to double the straight-line depreciation
rate. For example, consider an asset with a 10-year life. The
straight-line depreciation rate is 10% per year (100% of depreciable
base/10 years). The DDB rate can be up to 20% per year. This 20%
rate should be applied to the book value of the asset until the
asset reaches the salvage value. This is the point at which Excel’s
DDB function is noticably problematic.
If the salvage
value of the asset is low compared to its acquisition cost, then
DDB will not completely depreciate the asset by the end of the
useful life. The amount of “leftover” depreciation
varies depending upon the length of the asset’s life. For
a $10,000 asset with no salvage value, the unrecorded depreciation
at the end of the useful life will range from $370, if its life
is three years, to $1,262, if its life is 30 years. In practice,
to prevent “leftover” depreciation, a switch to the
straight-line method is necessary in the year that the straight-line
balance method, using the remaining depreciable balance, yields
a higher depreciation expense than the double-declining method.
1 illustrates the need to switch methods for the $10,000 asset
mentioned above, assuming a 10-year useful life. Note that if
the double-declining balance method is used throughout the 10-year
life, $1,073.74 of unrecorded depreciation will remain at the
end of Year 10. A switch to the straight-line method should occur
in the year that it yields a greater depreciation expense than
the double-declining balance method. For this asset, the switch
occurs in Year 7. This adjustment needed to correctly use the
double-declining balance method is not inherent in the DDB functionality
of Excel. A less-well-known function, VDB, provides for the switch
balance. The VDB function is an improvement over
the DDB function because it allows for additional inputs. An optional
VDB input called “no-switch” allows the user to instruct
Excel on whether to switch to the straight-line method as discussed
previously. If a value for “no-switch” is omitted,
Excel will automatically switch to the straight-line method when
appropriate. The depreciation schedule for the same $10,000 asset
illustrated in Exhibit 1 is computed using VDB in Exhibit
financial reporting is supported by various spreadsheets containing
a multitude of built-in functions and user-developed formulas.
In the wake of the Sarbanes-Oxley Act, many public companies and
their auditors have examined the validity of key spreadsheets
used in the financial reporting process. While tests of spreadsheets
should rightly focus on spreadsheet design, including the accuracy
of user-developed formulas and cell references, users may not
be skeptical enough when it comes to Excel’s built-in functions.
Absent a complete understanding of a function, an end user must
accept that the function always produces a correct answer. Such
blind faith could lead to inaccuracies in financial reports. The
authors believe that following three good spreadsheet practices
can eliminate such problems: understanding the purpose of the
function you are going to use; understanding the inputs or arguments
of the function; and checking the method of calculation to ensure
it is operating as expected.
M. Loraas, PhD, is an assistant professor, and Jennifer
M. Mueller, PhD, is an associate professor, both in the
school of accountancy at Auburn University, Auburn, Ala.