Engineering Economic Analysis

r

Spreadsheets and Depreciation 363

replaced by MACRS in 1986,they are not covered in detail here. All three functions include

parameters forcost(initial book value),salvage(final salvage value), andlife(depreciation

period). Both DDB and SYD change depreciation amounts every year, so they include a

parameter to pick theperiod(year). Finally, DDB includesafactor.The default value is 2

for 200% or double declining balance, but another commonly used value is 1.5 for 150%.

Using VDB for MACRS

The Excel function VDB is a flexible or variable declining balance method. It includes the

ability to specify the starting and ending periods, rather than simply a year. It also includes

an optional nO-8witchfor problems where a switch from declining balance to straight-line

depreciation is NOT desired.

To use VDB to calculate MACRS depreciation, the following are true.

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

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

3. First period runs from 0 to 0.5, 2ndperiod from 0.5 to 1.5, 3rdfrom 1.5 to 2.5, tth
   fromt- 1.5 tot- 0.5, and last period fromlife-0.5 tolife.

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

of 15 or 20 years.

5. Since MACRS includes a switch to straight line, no_switchcan be omitted.

The starLperiod and end_periodargumentsare fromt- 1.5tot- 0.5, because MACRS

uses a half-year convention for the first year. Thus the first year has 0 to 0.5 year of

depreciation, and the second year starts where the first year stops. When one is writing the

Excel function, either the first and last periods must be edited individually, or start_period

must be defined with a minimum of 0 and end_periodwith a maximum of life. This prevents

the calculation of depreciation from -0.5 to 0 and fromlifetolife +0.5.

The results of using the VDB function match Table 11-4, except that the VDB func-

tion has more significant digits rather than being rounded to 2 decimals. Example 11-14

illustrates the use of the VDB function.

Return to the data of Example 11-5 which had $150,000 of office equipment, which is 7-year

MACRS property. Use VDB to compute the depreciation amounts.

SOLUTION.

The spreadsheet in Figure 11-7 de:finest:!lestart...periodwith a minimum of Q¥1d tpe end_pefiog

~lthfnfaximum of life. Thus this formula couldbe used for any year of any recoveryschedule.No-

tice that the VDBformula uses the value0 for thesalvage value,rather than referring to the data cell

for thes~v~g~ v'tlue.~A~RSoa§sumesca§ill,v~gy.valueot:zer~nomatter what the value truly is:

-- ---- -- --- -.. .. -- -.. - ..-

--- -,- ........