MACRS DEPRECIATION WITH A SPREADSHEET FUNCTION: A TEACHING AND PRACTICE NOTE.

ABSTRACT

While Excel[TM] and Quattro Pro[TM] do not have a function MACRS(cost, class life, year), they do have a variable declining balance function, VDB, that can be used to compute annual MACRS depreciation amounts. A literature search of journals and texts did not find examples using this function nor did it find other shortcut approaches to MACRS calculations. A built-in spreadsheet function is clearly superior to relying on hard coding percentages copied from texts or IRS tables or building tables to calculate MACRS percentages. This article 1) describes the underlying definition of the variable declining balance function (VDB), 2) notes some quirks and errors in other spreadsheet depreciation functions and accompanying explanatory material, 3) shows how VDB can be used to calculate MACRS depreciation, 4) provides an example of its use, and 5) suggests useful further developments.

INTRODUCTION

The spreadsheet functions for straight-line (SLN), double declining-balance (DDB), and sum-of-the-years'-digits (SYD) depreciation are straightforward. They have been used in a large variety and volume of engineering economy examples. They even have analogues in the buttons contained on financial calculators. Because these techniques are simple, and they were replaced by ACRS in 1981 and MACRS in 1986, they are not covered in detail here.

However, it is worth noting that the simple form of these functions has created an unfilled demand for a spreadsheet function like MACRS (cost, class life, year). All three functions (SLN, DDB, SYD) include parameters for cost (initial book value), salvage (final salvage value), and life (depreciation period). Note that the salvage value could be the market value minus removal costs or the value in an alternative use that is not part of the economic evaluation. Both DDB and SYD change depreciation amounts every year, so they include a parameter for period (year). Finally, DDB includes an optional factor (default equals 2 for 200% or double-declining balance, but values such as 1.5 for 150% can be entered). To use these functions, one only need know the values of these parameters and not how the techniques are calculated.

The variable declining balance (VDB) function is intended to accommodate declining balance depreciation with or without a switch to straight line depreciation. The general depreciation schedule (GDS) of MACRS is based on declining balance with a switch to straight line, so VDB falls into this general class of techniques. However, to properly specify the VDB function a detailed understanding of MACRS is required.

A thorough literature search of journals, texts, and spreadsheet material did not reveal any articles or examples where the VDB function has been used to calculate MACRS depreciation. On-line searches were also fruitless. Therefore, this note was written so that economic analyses could be conducted and taught more effectively.

In the balance of this short note, we,

1) very briefly discuss some quirks or errors of present spreadsheet depreciation functions,

2) link the definition of MACRS to the specification of VDB terms,

3) provide an example of its use,

4) complete the example by including the role of recaptured depreciation,

5) suggest another possible spreadsheet depreciation function that would be very useful in engineering economic analysis.

To ensure a concise presentation, this note assumes a general familiarity with depreciation, MACRS, and spreadsheets.

ERRORS OR QUIRKS IN SOME "PRESENT" SPREADSHEET DEPRECIATION FUNCTIONS

The DDB function works correctly in Excel and Quattro Pro, however, the formula given in the help utility of Excel is wrong. In the help utility, the formula is incorrectly given as:

((cost - salvage) - total depreciation from prior periods) * (factor / life) (1)

The salvage value is not part of the annual depreciation calculation, as claimed in the incorrect Eq. (1). Instead, it is used as a lower bound on the book value, so that too much depreciation is not claimed. Thus if the normal declining balance depreciation amount would "overshoot" the salvage value, then the salvage value becomes part of the calculation and all subsequent depreciation amounts are zero.

The correct result is given by the following formula. This formula or its equivalent is used in Excel and Quattro Pro to calculate the depreciation in period t.

Let total [depreciation.sub.t-1] = total depreciation from prior periods, then

min [(cost - total [depreciation.sub.t-1]) * (factor / life), cost - salvage - total [depreciation.sub.t-1]] (2)

Equation (2) uses the salvage value as a minimum value for the book value, but the salvage value is not subtracted from the cost to determine each year's depreciation. With this function, there is no switch to straight line, so a low salvage value may be below the final book value. The @DDB function in Quattro Pro is similar, but it does not have the optional factor, and it does not list any formula in its help utility.

Both Excel and Quattro Pro include a second declining balance function called DB or @DB. This is not a "traditional" depreciation function, in that it has not been part of the U.S. tax code. This function sets the rate so that the book value equals the salvage value at the specified horizon or life. If we let this rate equal d, then the formula is:

salvage = [(1 - d).sup.life] * cost (3)

The quirk in Eq. (3) is that if the salvage value equals zero, as it is with MACRS, then all depreciation is assigned to the first year. This is most easily seen by rearranging the formula.

d = 1 - [(salvage / cost).sup.1/life] (4)

Thus when salvage equals 0, then d equals 1. Once the asset is fully depreciated in year 1, all subsequent depreciation amounts are zero. Clearly this effect is not desirable for use in engineering economic analysis, since it is neither part of the tax law nor a good approximation.

SPECIFYING VDB TERMS TO CALCULATE MACRS PERCENTAGES

The following introduction to the variable declining balance function of Excel and Quattro Pro assumes a basic familiarity with the rules for MACRS depreciation in the U.S. Tax Code. The Excel VDB function is defined as:

VDB (cost, salvage, life, start_period, end_period, factor, no_switch)

The @ VDB function of Quattro Pro has the same parameters and order. The cost, salvage, life, and factor terms are found in the other spreadsheet depreciation functions, which were described earlier, and their meaning is quite intuitive. The optional no-switch term is equally intuitive, however, it is unnecessary here, since MACRS does switch to straight line depreciation. In contrast, the definitions of the starting and ending periods for MACRS are not intuitive. The basic difficulty is that the MACRS GDS percentages are calculated with a half-year assumption for the first and last years. As a result, 1) there is an extra year beyond the end of the class life that has a non-zero depreciation percentage, and 2) the first year's depreciation is halved.

While the VDB terminology is "life," in the context of MACRS this is really the "class life" or "recovery period" not the asset's life or expected usage period. Thus life is the total number of years over which the asset's first cost will be recovered or depreciated. As noted above, one of these years is split between the first and last periods. The second shift in terminology is that for MACRS the salvage value is 0 and not the expected salvage value of the asset.

To use VDB to calculate MACRS depreciation the following conditions exist:

1. Salvage = 0, since MACRS assumes no salvage value

2. Life = recovery period of 3, 5, 7, 10, 15, or 20 years

3. The first year runs from period 0 to 0.5, second year from period 0.5 to 1.5, third from period 1.5 to 2.5, t th from period (t - 1.5) to (t - 0.5), and the last year or year (life + 1) from period (life - 0.5) to life

4. Factor = 2 for recovery periods of 3, 5, 7, or 10 years and = 1.5 for recovery periods of 15 or 20 years

5. No_switch argument is omitted, since MACRS includes a switch to straight line.

In Eq. (5) the start_period and end_period arguments are from (t - 1.5) to (t - 0.5), because MACRS uses a 1/2 year convention for the first year. Thus the first year has 0 to 0.5 year's worth of depreciation, and the second year starts where the first year stops. When writing the spreadsheet function either the first and last periods must be edited individually, or start_period must be defined with a minimum of 0 and end_period with a maximum of life. Otherwise, the function attempts to calculate depreciation from - 0.5 years to 0 years and from life to (life + 0.5) years.

Note: when explaining this to students it may be worth clarifying that to achieve a minimum value of 0 years, the MAX function with one value of 0 is used. Similarly, to achieve a maximum value of life, the MIN function with one value of life is used.

The resulting function that can be used to calculate MACRS depreciation for a given year is:

VDB(cost, 0, life, MAX(0, year - 1.5), MIN(year-.5, life), factor) (5)

COMPARING VDB RESULTS WITH IRS TABLES

When Eq. (5) is used, the results match the IRS tables of MACRS percentages [1] for all recovery periods. However, the VDB function has more significant digits rather than being rounded to 2 or 3 decimal points as are the IRS tables. TABLE 1 summarizes the percentages calculated with the VDB function. The highlighted entries emphasize the differences between the "rounded" values shown in the IRS tables and the values calculated using Eq. (5). For example, in TABLE 1 the printed values for a 15-year class life sum to 99.96%, and the IRS tables "round" four 5.90% values up to 5.91%. The VDB function maintains more digits of accuracy than are printed, and the sum is still 100%.