User-defined Function in VBA: MACRS Depreciation

[turbinetime]

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!

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, _
                                        bln_VDB_NoSwitch)
            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

 

[kcmuppet]

Hello,

I'd like to try your UDF, but I'm new to MACRS (and not a VBA expert), and have some questions about the function:

1. What is the 2nd input "Years" "to represent the timing of costs?" Does that mean what is the cost in a given Year n? or is that the MACRS "Property Class", e.g. applicable under GDS?
   
   
2. I can see use of the VDB function (so assume declining balance is somewhere), but how do we specify if its declining balance or straight line method?
   
   
3. For the input "Factor" we specify 150% or 200%, right?
   
   
4. What do you mean by "bonus depreciation"? I don't see that in the MACRS guidance I've read. Can this be ignored or must be zeroed or...?
   
   
5. Is the "PIS" (Placed in Service) an alternative to the convention (Half-Year/ Mid-Quarter/ Mid-Month)? If not what happens if we specify permutations that aren't compatible?