As Per Nepal Rastra Bank’s directive no 2 the loan are categorized based on the due days. The categories are good (30 day due), watchlist (30-90 days due), substandard (90-180 days due), substandard (upto 365 days due) and Bad loan (above 365 days due). The Excel UDF tool simplifies loan category determination based on overdue days and the reporting date entered by the user.

What Does the Add-in Do?

Available Functions

The add-in contains a VBA module that introduces a User Defined Function (UDF) called classify. This function helps to determine the loan category based on the number of days a loan has been overdue. The Code also fetch the loan category if the user provide the date in yyyymmdd format on UDF assuming the client remains unpaid till that date.

1. 1. =classify(duedays)
      – Returns the NRB loan category based on the current number of overdue days.
      – Example: =classify(45) → Would return “Watchlist” as per the  NRB rules.
2. 2. =classify(duedays, date)
      – Projects the loan category on a future date assuming the loan remains unpaid.
      – Example: =classify(45, 20251231)) → Returns the category as of December 31, 2025.

The classification logic is based on NRB guidelines, which typically include categories like Pass, Watchlist, Substandard, Doubtful, and Bad Loan. The date file is here which contain the Month end, Quarter end and year end date on excel.

How to Install the Add-in

Follow these steps to install and enable the macro add-in in Excel:

• 1. Download the Add-in .xlam VBA File from here
     – Once available, download the .xlam or .xlsm file containing the macro.
• 2. Unblock the File
     – Right-click the file
     – Select Properties
     – Check Unblock at the bottom (if available)
     – Click Apply
• 3. Install the Add-in in Excel
     – Open Excel
     – Go to File > Options > Add-ins
     – At the bottom, choose Excel Add-ins and click Go
     – Click Browse, locate the file, and add it
     – Ensure the checkbox next to add-in is checked
• 4. Enable Macros
     – If prompted, enable macros to allow the VBA code to run.

How to Use the UDFs in Excel

Once installed, you can use the functions directly in any worksheet.

• Example 1: Classify Based on Current Due Days
  =classify(30)
• Example 2: Classify Based on Future Date
  =classify(30, 20251231)

Function Classify(dueDays As Integer, Optional futureDate As String = "") As String
    ' =========================================================================
    ' Function: Classify
    ' Purpose:  Classifies accounts based on current or projected due days
    '
    ' Parameters:
    '   dueDays     - Current number of days past due (Integer)
    '   futureDate  - Optional: Future date in YYYYMMDD format (String)
    '                 Example: "20251225" for December 25, 2025
    '                 If blank/empty, calculates bucket using current dueDays
    '
    ' Returns: Classification bucket (Good/Watchlist/Substandard/Doubtful/Bad)
    ' =========================================================================
    
    Dim revisedDueDays As Long
    Dim yearPart As Integer
    Dim monthPart As Integer
    Dim dayPart As Integer
    Dim bucket As String
    Dim futureDateTime As Date
    
    ' Check if futureDate is provided and not empty
    If Len(Trim(futureDate)) = 0 Then
        ' No future date provided - use current dueDays
        revisedDueDays = dueDays
    Else
        ' Future date provided - validate and calculate
        If Len(Trim(futureDate)) <> 8 Then
            Classify = "Invalid Date"
            Exit Function
        End If
        
        ' Fast parsing with validation
        On Error GoTo DateError
        yearPart = CInt(Left$(futureDate, 4))
        monthPart = CInt(Mid$(futureDate, 5, 2))
        dayPart = CInt(Right$(futureDate, 2))
        
        ' Validate month and day ranges
        If monthPart < 1 Or monthPart > 12 Or dayPart < 1 Or dayPart > 31 Then
            Classify = "Invalid Date"
            Exit Function
        End If
        
        ' Create date and calculate revised due days
        futureDateTime = DateSerial(yearPart, monthPart, dayPart)
        revisedDueDays = dueDays + (futureDateTime - Date)
        On Error GoTo 0
    End If
    
    ' Validate that revised due days is not negative
    If revisedDueDays < 0 Then
        Classify = "Error: Negative Due Days"
        Exit Function
    End If
    
    ' Classify based on revised due days
    If revisedDueDays <= 30 Then
        bucket = "Good"
    ElseIf revisedDueDays <= 90 Then
        bucket = "Watchlist"
    ElseIf revisedDueDays <= 180 Then
        bucket = "Substandard"
    ElseIf revisedDueDays <= 365 Then
        bucket = "Doubtful"
    Else
        bucket = "Bad"
    End If
    
    Classify = bucket
    Exit Function
    
DateError:
    Classify = "Invalid Date"
End Function