Depreciation calculations

[dcardno]

When forecasting future capital spending, one often has to reflect depreciation calculations for future periods. This can get tedious if there are several asset classes involved (which usually require different depreciation terms), and if the spending extends over many years. The typical approach is to lay out something like this (this assumes a "Half Year" rule, where Straight-Line depreciation is applied, but only at one half the usual rate in the year an asset goes into service):

Excel 2007
	A	B	C	D	E	F	G	H	I	J
3	Half-Year Rule	0.5		Year -->
4				1	2	3	4	5	6	7
5	SL Depreciation Rate	16.7%	Term	6
6
7	Purchases			900	0	0	100	0	0	600
8
9	Depreciation Calculations
10
11	purchases yr 1			(75)	(150)	(150)	(150)	(150)	(150)	(75)
12	purchases yr 2				0	0	0	0	0	0
13	purchases yr 3					0	0	0	0	0
14	purchases yr 4						(8)	(17)	(17)	(17)
15	purchases yr 5							0	0	0
16	purchases yr 6								0	0
17	purchases yr 7									(50)
Sheet1

Cell Formulas
Range	Formula
B3	=IF(INT(D5) - D5 = 0, 0.5, 1)
B5	=1/D5
D11	=-D$7*$B$5*$B$3
E11	=-MIN($D$7*$B$5,$D$7+SUM($D11:D11))
E12	=-E$7*$B$5*$B$3
F11	=-MIN($D$7*$B$5,$D$7+SUM($D11:E11))
F12	=-MIN($E$7*$B$5,$E$7+SUM($D12:E12))
F13	=-F$7*$B$5*$B$3
G11	=-MIN($D$7*$B$5,$D$7+SUM($D11:F11))
G12	=-MIN($E$7*$B$5,$E$7+SUM($D12:F12))
G13	=-MIN($F$7*$B$5,$F$7+SUM($D13:F13))
G14	=-G$7*$B$5*$B$3
H11	=-MIN($D$7*$B$5,$D$7+SUM($D11:G11))
H12	=-MIN($E$7*$B$5,$E$7+SUM($D12:G12))
H13	=-MIN($F$7*$B$5,$F$7+SUM($D13:G13))
H14	=-MIN($G$7*$B$5,$G$7+SUM($D14:G14))
H15	=-H$7*$B$5*$B$3
I11	=-MIN($D$7*$B$5,$D$7+SUM($D11:H11))
I12	=-MIN($E$7*$B$5,$E$7+SUM($D12:H12))
I13	=-MIN($F$7*$B$5,$F$7+SUM($D13:H13))
I14	=-MIN($G$7*$B$5,$G$7+SUM($D14:H14))
I15	=-MIN($H$7*$B$5,$H$7+SUM($D15:H15))
I16	=-I$7*$B$5*$B$3
J11	=-MIN($D$7*$B$5,$D$7+SUM($D11:I11))
J12	=-MIN($E$7*$B$5,$E$7+SUM($D12:I12))
J13	=-MIN($F$7*$B$5,$F$7+SUM($D13:I13))
J14	=-MIN($G$7*$B$5,$G$7+SUM($D14:I14))
J15	=-MIN($H$7*$B$5,$H$7+SUM($D15:I15))
J16	=-MIN($I$7*$B$5,$I$7+SUM($D16:I16))
J17	=-J$7*$B$5*$B$3

As you can appreciate, one of these tables is needed for each asset class, and it must extend for at least as many rows as there are years as the capital spending program. I am working on a template that will reflect a 20-year capital plan - this approach is cumbersome.

I have written a UDF that does the calculations in one line for each asset class - I would appreciate it if any accounting or finance types (or anyone else whose pulse races when talking about depreciation) could try it out and see if they find any errors. The code is below, and I can e-mail a file, if you would like to send a message with your e-mail address. This calculation either applies the half-year rule, or for non-integer terms (we have some stuff with 30% SL depreciation, which represents a 3.33 year term) it applies full depreciation for the term + a stub year.

Option Explicit

Function SLDeprn(Purchases As Range, Years As Double) As Double
'  Function returns SL depreciation for all capital expenditures in a string of annual
'  purchases in a (horizontal) range, given a depreciation term (in years). Depreciation for each
'  year is calcuated assuming that CapEx and depreciation calculations are aligned vertically.

'  If the term is an integer number of years the value reflects 'half-year' rule in the year of
'  expenditure and a catch-up in year following normal term. If the term is non-integer, no
'  half-year is applied; instead each year up to the 'stub year' gets a full year of depreciation
'  and the stub year gets the fractional year of depreciation.
'
'  For example, with a single purchase of $100, and a depreciation term of 3.3333 years (30% SL
'  depr) depreciation in each of year 1-3 would be $30, (ie, 30% SL) and the fourth year would be
'  $10, representing the 0.3333 of a year remaining.


Dim CurrCol    As Integer
Dim PurRow     As Long
Dim PurCol     As Integer
Dim StrtCol    As Integer
Dim FullYr     As Long
Dim PartYr     As Double
Dim WFS        As WorksheetFunction

Set WFS = Application.WorksheetFunction

FullYr = Int(Years)
PartYr = Years - FullYr
PurRow = Purchases.Row
PurCol = Purchases.Column
CurrCol = Application.Caller.Column
StrtCol = WFS.Max(PurCol, CurrCol + 1 - FullYr)
'  Capital subject to depreciation is the total from past "X" full years including current year -
'  StrtCol represents the first year of the still-depreciating capital expenditure

If StrtCol > CurrCol Then
    SLDeprn = 0
    '  CapEx and depr. must be vertically aligned by year: return zero if to the left of first expenditure
Else
    SLDeprn = WFS.Sum(Range(Cells(PurRow, StrtCol), Cells(PurRow, CurrCol)))
    '  total CapEx for "FullYr" number of years

    If PartYr > 0 Then                                '  Depreciation term is non-integer - part-year rules
        If CurrCol - FullYr >= PurCol Then
            '  ensure we don't refer to cell to left of purchase data
            If IsNumeric(Cells(PurRow, CurrCol - FullYr).Value) Then
                SLDeprn = SLDeprn + Cells(PurRow, CurrCol - FullYr).Value * PartYr
                '  Adds the stub amount from N+1 years ago to current depreciable CapEx
            End If
        End If
    Else                                              '  Depreciation term is integer - apply half-year rules
        SLDeprn = SLDeprn - Cells(PurRow, CurrCol).Value / 2    '  deduct 1/2 of current year CapEx
        If CurrCol - FullYr >= PurCol Then
            If IsNumeric(Cells(PurRow, CurrCol - FullYr).Value) Then
                SLDeprn = SLDeprn + Cells(PurRow, CurrCol - FullYr).Value / 2
                ' add 1/2 of N-1 year's CapEx
            End If
        End If
    End If
    SLDeprn = SLDeprn / Years                         '  Divide depreciating CapEx by depr term
End If

End Function

Thanks in advance,

 

[delaneyjm]

What about the SLN function? That does straight line amortization as well.

 

[dcardno]

No - the SLN function just divides depreciable value (cost - salvage) by life to determine the period depreciation charge - it doesn't calculate the depreciation charge by year (ie, the pattern over time), nor will it accept multiple assets put in service at different times.

It goes without saying, but if anyone finds the UDF useful, please feel free to paste it into your own work.

 

[dcardno]

I should probably make it clearer that the UDF would sit in a single cell (or cells in a row) to return what would otherwise be the totals in the columns showing annual depreciation, like this (using the same values as in the original post):

Excel 2007
	A	B	C	D	E	F	G	H	I	J
3	Half-Year Rule	0.5		Year -->
4				1	2	3	4	5	6	7
5	SL Depreciation Rate	16.7%	Term	6
6
7	Purchases			900	0	0	100	0	0	600
8
9	Depreciation UDF			75.00	150.00	150.00	158.33	166.67	166.67	141.67
Sheet1

Cell Formulas
Range	Formula
B3	=IF(INT(D5) - D5 = 0, 0.5, 1)
B5	=1/D5
D9	=sldeprn($D$7:$W$7,$D$5)
E9	=sldeprn($D$7:$W$7,$D$5)
F9	=sldeprn($D$7:$W$7,$D$5)
G9	=sldeprn($D$7:$W$7,$D$5)
H9	=sldeprn($D$7:$W$7,$D$5)
I9	=sldeprn($D$7:$W$7,$D$5)
J9	=sldeprn($D$7:$W$7,$D$5)