Good Spreadsheet Practice Pays Off
Excel’s Depreciation Functions

By Tina M. Loraas and Jennifer M. Mueller

E-mail Story Print Story

Good Practice

The authors suggest three spreadsheet practices to help ensure that Excel functions are working appropriately from an accounting standpoint:

• Understand the purpose of the function you are going to use.
• Understand the inputs or arguments of the function.
• Check the method of calculation to ensure it is operating as expected.

These practices 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 above.

The first 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 most appropriate?”

Second, it 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.

The third, 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.

Checking Calculations

The significance 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.

Declining 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) 1/Useful Life].

Although 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 asset base.

Double-declining 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. Exhibit 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 in methods.

Variable-declining 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 2.

The Bigger Picture

In reality, 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.

©2008 The New York State Society of CPAs. Legal Notices