Excel 2019 Bible

Chapter 15: Using Formulas for Financial Analysis

15

Calculating depreciation

Excel provides a number of depreciation-related worksheet functions including DB, DDB,

SLN, and SYD. In this section, we’ll look at calculating straight line (SLN) and variable-

declining balance (VDB) depreciation.

The depreciation for the first and last years of an asset’s life is usually different than for the middle year. A conven-
tion is employed so that a full year’s depreciation is not taken for the first year. Common conventions are half-year,
mid-month, and mid-quarter. For the half-year convention, the asset is assumed to have been purchased at the half-
way point of the year, and consequently one-half of a normal year’s depreciation is recorded for that year.

Figure 15.17 shows a depreciation schedule for five assets using the straight-line method

and a half-year convention.

FIGURE 15.17

A straight-line depreciation schedule

Columns B:E contain the following user-entered data:

Asset No. A unique identifier for each asset. It’s not necessary for the schedule, but it is

handy for keeping track of assets.

Cost The amount paid to put the asset in service. This includes the price paid for the

asset, any taxes associated with purchase, the cost to ship the asset to its place of service,

and any costs to install the asset so that it’s ready for use. This is also known as basis or

cost basis.

Year Acquired The year the asset was put into service. This may be different than the

year the payment was made to purchase the asset. It determines when depreciation starts.

Useful Life The number of years you estimate the asset will provide service.

The formula shown in F3:N7 follows:

=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,SLN($C3,0,$E3))*IF(OR(YEAR

(F$2)=$D3+$E3,YEAR(F$2)=$D3),0.5,1)