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 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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 01 | 091000022 | 123456789 | 240610 | 1600 | A | 094 | 10 | 1 | 1 | 1 | 1 | 091000022 | 0 | 1000 |
| 5 | 200 | 091000022 | 123456789 | 240610 | |||||||||||
| 6 | |||||||||||||||
| 8 | |||||||||||||||
| 9 |
Example Filled NACHA File in Excel
Here is a more detailed example with filled data for a specific transaction.
| 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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 01 | 091000022 | 123456789 | 240610 | 1600 | A | 094 | 10 | 1 | 1 | 1 | 1 | 091000022 | 0 | 1000 |
| 5 | 200 | 091000022 | 123456789 | 240610 | |||||||||||
| 6 | 27 | 091000022 | 123456789 | 091000022 | 0 | 1000 | |||||||||
| 8 | |||||||||||||||
| 9 |
Instructions for Use:
- 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).
- Priority Code: Typically “01”.
- Immediate Destination: ACH Operator’s routing number.
- Immediate Origin: Your bank’s routing number or your company’s identifier.
- File Creation Date: Date of file creation (YYMMDD format).
- File Creation Time: Time of file creation (HHMM format).
- File ID Modifier: Single character to distinguish multiple files created on the same date (A-Z).
- Record Size: Always “094”.
- Blocking Factor: Always “10”.
- Format Code: Always “1”.
- Batch Count: Total number of batches in the file.
- Block Count: Number of 10-record blocks in the file.
- Entry/Addenda Count: Total number of Entry Detail and Addenda records in the file.
- Entry Hash: Hash total of all entry detail records.
- Total Debit Entry Dollar Amount: Sum of all debit entries.
- 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