Creating a NACHA (National Automated Clearing House Association) file in Excel involves several steps. NACHA files are used for electronic funds transfers (EFTs) and follow a specific format. Below is a guide on how to create a basic NACHA file in Excel.

Steps to Create a NACHA File in Excel

1. Open Excel:
Open a new Excel workbook.

2. Define the Columns:
Create the necessary columns for the NACHA file. The basic NACHA file format includes various record types, each with its specific layout. For simplicity, we’ll focus on the essential columns for a basic ACH credit transaction.

3. Populate the Data:
Enter the required data into the Excel sheet. Below is an example layout with the necessary fields:

  • Record Type
  • Priority Code
  • Immediate Destination
  • Immediate Origin
  • File Creation Date
  • File Creation Time
  • File ID Modifier
  • Record Size
  • Blocking Factor
  • Format Code
  • Batch Count
  • Block Count
  • Entry/Addenda Count
  • Entry Hash
  • Total Debit Entry Dollar Amount
  • Total Credit Entry Dollar Amount

4. Save as CSV:
Save the Excel file as a CSV (Comma-Separated Values) file.

5. Convert CSV to NACHA Format:
Open the CSV file in a text editor (like Notepad) and manually adjust it to match the NACHA format. Ensure each line adheres to the NACHA specifications.

Example NACHA File in Excel

Below is an example of how you might structure a NACHA file in Excel for an ACH credit transaction.

Record TypePriority CodeImmediate DestinationImmediate OriginFile Creation DateFile Creation TimeFile ID ModifierRecord SizeBlocking FactorFormat CodeBatch CountBlock CountEntry/Addenda CountEntry HashTotal Debit Entry Dollar AmountTotal Credit Entry Dollar Amount
1010910000221234567892406101600A09410111109100002201000
5200091000022123456789240610
6
8
9

Example Filled NACHA File in Excel

Here is a more detailed example with filled data for a specific transaction.

Record TypePriority CodeImmediate DestinationImmediate OriginFile Creation DateFile Creation TimeFile ID ModifierRecord SizeBlocking FactorFormat CodeBatch CountBlock CountEntry/Addenda CountEntry HashTotal Debit Entry Dollar AmountTotal Credit Entry Dollar Amount
1010910000221234567892406101600A09410111109100002201000
5200091000022123456789240610
62709100002212345678909100002201000
8
9

Instructions for Use:

  1. Record Type: Indicates the type of record (1 for File Header, 5 for Batch Header, 6 for Entry Detail, 8 for Batch Control, 9 for File Control).
  2. Priority Code: Typically “01”.
  3. Immediate Destination: ACH Operator’s routing number.
  4. Immediate Origin: Your bank’s routing number or your company’s identifier.
  5. File Creation Date: Date of file creation (YYMMDD format).
  6. File Creation Time: Time of file creation (HHMM format).
  7. File ID Modifier: Single character to distinguish multiple files created on the same date (A-Z).
  8. Record Size: Always “094”.
  9. Blocking Factor: Always “10”.
  10. Format Code: Always “1”.
  11. Batch Count: Total number of batches in the file.
  12. Block Count: Number of 10-record blocks in the file.
  13. Entry/Addenda Count: Total number of Entry Detail and Addenda records in the file.
  14. Entry Hash: Hash total of all entry detail records.
  15. Total Debit Entry Dollar Amount: Sum of all debit entries.
  16. Total Credit Entry Dollar Amount: Sum of all credit entries.

This is a simplified example and may need to be adjusted based on the specific requirements and rules of your bank or ACH operator. Always refer to the official NACHA file format guide for detailed specifications.


0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *