Depreciation Vintage

This page demonstrates how you can apply a user defined function instead of one of those diagonal matrices that has a diagonal thing. You can use this idea for many different applications that are similar to the depreciation problem. Please note that this depreciation problem is not at all necessary if you have straight line depreciation. The screenshot below illustrates the result of the depreciation function compared to creating a matrix

The second example illustrates a case with remaining life straight line depreciation

Sub A_Depreciation_Functions()
End Sub
Function depreciation(capital_expenditure, depreciation_rate) As Variant ' When the output is an array define as Variant
asset_life = depreciation_rate.Count ' Find Life from the depreciation rate array
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
ReDim Depreciation_Expense(cap_exp_periods) As Single ' Make a new array variable that is the output
For model_year = 1 To cap_exp_periods ' loop around each period

For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal) If (age > 0 And age <= asset_life) Then ' Only when asset is alive Depreciation_Expense(model_year) = _ capital_expenditure(vintage) * depreciation_rate(age) + Depreciation_Expense(model_year) End If Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year
depreciation = Depreciation_Expense
End Function


Function depreciation_remaining_life(capital_expenditure, remaining_life) As Variant ' When the output is an array define as Variant
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
For model_year = 1 To cap_exp_periods ' loop around each period and make a square with columns and rows
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal) If (age > 0 And remaining_life(vintage) <> 0) Then ' Only when asset is alive Depreciation_Expense(model_year) = _ capital_expenditure(vintage) / remaining_life(vintage) + Depreciation_Expense(model_year) End If Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year
depreciation_remaining_life = Depreciation_Expense
End Function


Function depreciation_remaining_life_1(capital_expenditure, remaining_life, max_life) As Variant ' When the output is an array define as Variant
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
Dim remaining_life1(5000) As Single
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
If remaining_life(vintage) >= max_life Then
remaining_life1(vintage) = max_life
Else
remaining_life1(vintage) = remaining_life(vintage)
End If
Depreciation_Expense(vintage) = remaining_life1(vintage)
Next vintage
depreciation_remaining_life_1 = Depreciation_Expense
Exit Function
For model_year = 1 To cap_exp_periods ' loop around each period and make a square with columns and rows
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal) If (age > 0 And remaining_life1(vintage) <> 0) Then ' Only when asset is alive Depreciation_Expense(model_year) = _ capital_expenditure(vintage) / remaining_life1(vintage) + Depreciation_Expense(model_year) End If Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year
depreciation_remaining_life_1 = Depreciation_Expense
End Function


Function depreciation_remaining_life_2(capital_expenditure, remaining_life, max_life) As Variant ' When the output is an array define as Variant
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
Dim remaining_life1(5000) As Single
' Determine whether to use remaining life or something shorter
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
If remaining_life(vintage) >= max_life Then
remaining_life1(vintage) = max_life
Else
remaining_life1(vintage) = remaining_life(vintage)
End If
Next vintage
For model_year = 1 To cap_exp_periods ' loop around each period and make a square with columns and rows
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal) If (age > 0 And remaining_life1(vintage) <> 0 And remaining_life1(vintage) >= age) Then ' Only when asset is alive Depreciation_Expense(model_year) = _ capital_expenditure(vintage) / remaining_life1(vintage) + Depreciation_Expense(model_year) End If Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year
depreciation_remaining_life_2 = Depreciation_Expense
End Function


Function depreciation_remaining_life_3(capital_expenditure, remaining_life, max_life, factr) As Variant ' When the output is an array define as Variant
cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled
Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
Dim dep_rate(5000, 5000) As Single
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
If remaining_life(vintage) >= max_life Then
adjusted_life = max_life
dep_rate(vintage, 1) = 1 / adjusted_life * factr
For j = 2 To adjusted_life dep_rate(vintage, j) = WorksheetFunction.Vdb(1, 0, adjusted_life, j - 1, j, factr) Next j Else adjusted_life = remaining_life(vintage) dep_rate(vintage, 1) = 1 / adjusted_life * factr For j = 2 To adjusted_life dep_rate(vintage, j) = 1 / adjusted_life dep_rate(vintage, j) = WorksheetFunction.Vdb(1, 0, adjusted_life, j - 1, j, factr) Next j End If
Next vintage
For model_year = 1 To cap_exp_periods ' loop around each period and make a square with columns and rows
For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal)
If (age > 0 And remaining_life(vintage) <> 0) Then ' Only when asset is alive
Depreciation_Expense(model_year) = _
capital_expenditure(vintage) * dep_rate(vintage, age) + Depreciation_Expense(model_year)
End If
Next vintage ' Note that the vintage is usef for the capital expenditure
Next model_year
depreciation_remaining_life_3 = Depreciation_Expense
End Function


Function dep(capexp, life) As Variant
num = capexp.Count ' find the length of the cap exp array
ReDim dep1(num) As Single ' create a flexible array as the output
For model_year = 1 To num
For vintage = 1 To num
age = model_year - vintage + 1
If (age > 0 And age <= life) Then dep1(model_year) = capexp(vintage) / life + dep1(model_year) End If Next vintage
Next model_year
dep = dep1
End Function