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 periodFor 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 rowsFor 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 IfDepreciation_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 rowsFor 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 rowsFor 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 * factrFor 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 + 1If (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