Chapter 15: Using Formulas for Financial Analysis
15
function that has that logic built in. Figure 15.18 shows a depreciation schedule using the
following VDB-based formula:
=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,VDB($C3,
0,$E3*2,IF(YEAR(F$2)=$D3,0,IF(YEAR(F$2)=$D3+$E3,$E3*2-1,
(YEAR(F$2)-$D3)*2-1)),IF(YEAR(F$2)=$D3,1,IF(YEAR(F$2)=$D3+$E3,
$E3*2,(YEAR(F$2)-$D3)*2+1))))FIGURE 15.18
An accelerated depreciation schedule
You might have noticed that this formula is a little more complicated than the SLN formula
from the previous example. Don’t worry, we’ll step through it piece by piece so that you
understand it:
=IF(OR(YEAR(F$2)<$D3,YEAR(F$2)>$D3+$E3),0,VDB(...))))The first part of the formula is identical to the SLN formula shown earlier. If the date in
row 2 is not within the useful life, the depreciation is zero. If it is, the VDB function is
evaluated:
VDB($C3,0,$E3*2,starting_period,ending_period)The first three arguments to VDB are the same as the SLN arguments: cost, salvage value,
and life. SLN returns the same value for every period so that we don’t have to tell SLN
which period to calculate. But VDB returns a different amount depending on the period.
The last two arguments of VDB tell it which period to compute. The life in E3 is doubled,
which will be explained in the next section.
Starting_period
IF(YEAR(F$2)=$D3,0,IF(YEAR(F$2)=$D3+$E3,$E3*2-1,(YEAR(F$2)-$D3)*2-1))None of Excel’s depreciation functions take into account the convention. That is, Excel cal-
culates depreciation as if you bought all of your assets on the first day of the year. That’s
not very practical. In this section, we’re assuming a half-year convention so that only half
of the depreciation is taken in the first and last years. To accomplish this with VDB, we
have to trick Excel into thinking that the asset has twice its useful life.