Part II: Working with Formulas and Functions
The main part of this formula is SLN($C3,0,$E3). The SLN worksheet function computes
the straight-line depreciation for one period. It takes three arguments: cost, salvage, and
life. For simplicity, the salvage value for this example is set to zero, meaning that the
asset’s cost will be fully depreciated at the end of its useful life.
The SLN function is pretty simple. But this is a depreciation schedule, so there’s more work
to do. The first IF function determines if the column is within the asset’s useful life. If
the year of the date in F2 is less than the year acquired, then the asset isn’t in service yet,
and the depreciation is zero. If F2 is greater than the year acquired plus the useful life, the
asset is already fully depreciated, and the depreciation is zero. Both of these conditionals
are wrapped in an OR function so that if either is true, the whole expression returns TRUE.
If both are FALSE, however, the SLN function is returned.
See Chapter 13, “Using Formulas for Conditional Analysis,” for more examples of using IF with OR.
The second part of the formula is also an IF and OR combination. These conditional state-
ments determine whether the year in F2 is either the first year of depreciation or the last
year. If either is true, the straight-line result is multiplied by 0.5 representing the half-year
convention employed here.
All the cell references in this formula are anchored so that the formula can be copied down
and to the right and the cell references change appropriately. References to row 2 are
anchored on the row so that we’re always evaluating the date in row 2. References to the
columns C:E are anchored on the columns, so Cost, Year Acquired, and Useful Life stay the
same as the formula is copied.
See Chapter 9, “Introducing Formulas and Functions,” for more information on relative and absolute
cell references.
Calculating accelerated depreciation
The straight-line method depreciates an asset equally over all of the years of its useful life.
Some organizations use an accelerated method, a method that depreciates at a higher rate
at the beginning of an asset’s life and a lower rate at the end. The theory is that an asset
loses more value when it is first put in service than in its last year of operation.
Excel provides the DDB function (double-declining balance) for accelerated depreciation.
DDB computes what the straight-line method would be for the remaining asset value and
doubles it. The problem with DDB is that it doesn’t depreciate the whole asset within the
useful life. The depreciation amount gets smaller and smaller, but it runs out of useful life
before it gets to zero.
The most common application of accelerated depreciation is to start with a declining bal-
ance method, and once the depreciation falls below the straight-line amount, the method
is switched to straight line for the remaining life. Fortunately, Excel provides the VDB
