User-defined Function in VBA: MACRS Depreciation


New Member
Oct 5, 2020
Office Version
  1. 2013
  1. Windows
Hi, this is my first post but this website has been saving me since I was an analyst almost 10 years ago - and I'm very, very grateful. Having been annoyed with cumbersome and unnecessarily large depreciation schedules in financial models, I decided to write a UDF. I'm admittedly a novice when it comes to VBA, but I think I'm just missing a few things that someone more experienced might pick up on. This problem probably makes the most sense to someone who is familiar with the VDB() native excel function.

I utilize the following conventions to keep my code organized: dbl_ = double; var_ = variant; lng_ = long; bln_ = boolean; str_ = string; _One_ = single item; _Arr_ = array. For context and disambiguation, _Life = Depreciable life of the asset or the recovery period (ie. 5 years for 5 years MACRS property); _Years = {1,2,...,n} to represent the timing of costs; _Salvage = Salvage value for the property; _Factor = decline rate; _BonusRate = Bonus depreciation rate, taken in the first year; _Convention = MACRS convention {HY=Half-Year; MQ=Mid-Quarter; MM=Mid-Month}; _PIS_ = Placed in Service {qtr=1-4; mth=1-12}.

The code is written as two loops. The first loops across the periods for which there is cost (or capex) that needs to be depreciated, from 1-p, and skips the second loop when there is no cost in that year. The second loop calculates the applicable depreciation expense for the cost incurred in year p (1st year = bonus depreciation+regular depreciation) and every year until the end of that asset's life by passing arguments into the .VDB function. To expand on the unique timing here, a $100 cost incurred in year 2 (p=2) for 5 year property and 50% bonus depreciation and a 100% factor (or straight line), will have no depreciation in p=1, ($100*50%)+($100-$50)/(5) = $60, ignoring the convention (HY, MQ, MM) for simplicity. p=3 depreciation expense would be ($100-$50)/(5) = $10, etc. These expenses are summed across all p's and the output will be an array formula with dimensions (1,p) (R,C).

Thank you for your help. While I've done my best to keep it organized, please let me know if you have any questions!


VBA Code:
Option Explicit

' STEP 1: Declare function with range parameters to read sheet data.
Function Depreciation_UDF( _
    Input_One_Life As Range, _
    Input_Arr_Years As Range, _
    Input_Arr_Cost As Range, _
    Input_Arr_Salvage As Range, _
    Input_Arr_Factor As Range, _
    Input_Arr_BonusRate As Range, _
    Input_Arr_Convention As Range, _
    Optional Input_One_PIS_qtr As Range, _
    Optional Input_One_PIS_mth As Range _
    ) As Variant
' STEP 2(i): Declare <Input> arrays/variables in order to read on-sheet range data.
    Dim dbl_Input_One_Life() As Double
    Dim var_Input_Arr_Years() As Variant
    Dim var_Input_Arr_Cost() As Variant
    Dim var_Input_Arr_Salvage() As Variant
    Dim var_Input_Arr_Factor() As Variant
    Dim var_Input_Arr_BonusRate() As Variant
    Dim var_Input_Arr_Convention() As Variant
    Dim lng_Input_One_PIS_qtr As Long
    Dim lng_Input_One_PIS_mth As Long
' STEP 2(ii): Declare other arrays/variables to in order to utilize VDB function.
    Dim dbl_VDB_Cost As Double
    Dim dbl_VDB_Salvage As Double
    Dim dbl_VDB_Life As Double
    Dim dbl_VDB_Start As Double
    Dim dbl_VDB_End As Double
    Dim dbl_VDB_Factor As Double
    Dim bln_VDB_NoSwitch As Boolean
    Dim dbl_VDB_BonusAmount As Double
    Dim str_VDB_Convention As String
    Dim dbl_Depreciation As Double
' STEP 2(iii): Declare <Aux> arrays/variables.
    Dim p As Long 'for
    Dim i As Long
    Dim bln_FLAG_FirstYear As Boolean

' STEP 2(iv): Declare <Output> arrays/variables.
    Dim var_Arr_Depreciation(1 To 2) As Variant
    Dim var_Arr_FunctionOutput As Variant
' STEP 3(i): Read data from function arguments into local variables/arrays.
    dbl_Input_One_Life = Input_One_Life.Value2
    var_Input_Arr_Years = Input_Arr_Years.Value2
    var_Input_Arr_Cost = Input_Arr_Cost.Value2
    var_Input_Arr_Salvage = Input_Arr_Salvage.Value2
    var_Input_Arr_Factor = Input_Arr_Factor.Value2
    var_Input_Arr_BonusRate = Input_Arr_BonusRate.Value2
    var_Input_Arr_Convention = Input_Arr_Convention.Value2
    lng_Input_One_PIS_qtr = Input_One_PIS_qtr.Value2
    lng_Input_One_PIS_mth = Input_One_PIS_mth.Value2
' STEP 3(ii): Assign data into local variables/arrays.
    bln_VDB_NoSwitch = False
    dbl_VDB_BonusAmount = 0
    dbl_VDB_Salvage = 0
' STEP 4: Loop procedures.
    For p = 1 To UBound(var_Input_Arr_Years, 1)
        If var_Input_Arr_Cost(p) <> 0 Then
            dbl_VDB_BonusAmount = var_Input_Arr_Cost(p) * var_Input_Arr_BonusRate(p)
            dbl_VDB_Cost = var_Input_Arr_Cost(p) - dbl_VDB_BonusAmount
            dbl_VDB_Salvage = var_Input_Arr_Salvage(p)
            dbl_VDB_Life = dbl_Input_One_Life(p)
            dbl_VDB_Factor = var_Input_Arr_Factor(p)
            str_VDB_Convention = var_Input_Arr_Convention(p)
        For i = 1 To UBound(var_Input_Arr_Years, 1)
            If dbl_VDB_Life + 1 = i Then
                Exit For
            End If
                Select Case str_VDB_Convention
                    Case i = 1: bln_FLAG_FirstYear = True
                    Case i <> 1: bln_FLAG_FirstYear = False
                End Select

            With Application.WorksheetFunction
                Select Case str_VDB_Convention
                    Case "HY"
                        dbl_VDB_Start = .Max(0, i - 1.5)
                        dbl_VDB_End = .Min(dbl_VDB_Life, i - 0.5)
                    Case "MQ"
                        dbl_VDB_Start = .Max(0, i - 1 - lng_Input_One_PIS_qtr / 4 + 0.125)
                        dbl_VDB_End = .Min(dbl_VDB_Life, i - lng_Input_One_PIS_qtr / 4 + 0.125)
                    Case "MM"
                        dbl_VDB_Start = .Max(0, i - 1 - lng_Input_One_PIS_mth / 12 + 1 / 24)
                        dbl_VDB_End = .Min(dbl_VDB_Life, i - lng_Input_One_PIS_mth / 12 + 1 / 24)
                End Select
                dbl_Depreciation = dbl_VDB_BonusAmount * bln_FLAG_FirstYear + _
                                   .Vdb(dbl_VDB_Cost, _
                                        dbl_VDB_Salvage, _
                                        dbl_VDB_Life, _
                                        dbl_VDB_Start, _
                                        dbl_VDB_End, _
                                        dbl_VDB_Factor, _
            End With
            var_Arr_Depreciation(1)(p - 1 + i) = var_Arr_Depreciation(2)(p - 1 + i)
            var_Arr_Depreciation(2)(p - 1 + i) = var_Arr_Depreciation(1)(p - 1 + i) + dbl_Depreciation
        Next i
        End If
    Next p
' STEP 5: Pass outputs to output array.
    var_Arr_FunctionOutput = var_Arr_Depreciation(2)
' STEP 6: Assign output array as return variable.
    Depreciation_UDF = var_Arr_FunctionOutput

End Function

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back