Since the import feature can be difficult to understand and use, this article will go through an example of importing two payments so you can see how the process works.
In this example, we will import two payments. The first thing we need to know is the ID of the loan into which the payments should be imported. If you don’t have the ID, you can search for the loan in the Loan Manager to find its ID.
You can also see the ID for a loan in the loan header.
Once we have the loan ID we can start preparing the import file. Navigate to Settings > Company > Import > Instructions & Samples inside your company account. Search for payments using the keyword search, or navigate to payments by expanding the Loan Transactions section.
Click to load the instructions and download for payments.
Click the download link to download the sample import file. Once the file has downloaded, open it in any spreadsheet application you have on your computer (e.g. Microsoft Excel, Apple Numbers, OpenOffice Calc). If you don’t have a spreadsheet application, you can download OpenOffice free here.
Now we will need to enter the information for each of our payments. Here are the available columns of the sample file:
- action – This field lets the import system know what to do with this row of data. In this case, we are adding a new payment, so we will enter the value “add”.
- loanId – This is the ID of the loan into which the payments should be imported. The ID we will use is 1686.
- id – This is the ID of the payment if it already exists. Since we are adding new payments, we will leave this column blank.
- amount – This is the amount of the payment. We will enter 100.00 for a $100.00 payment.
- date – This is the payment date. The date should be formatted as YYYY-MM-DD. We will enter 2015-12-21.
- type – This is the payment type. Since you can customize your payment types, this will be the ID of one of the payment types in your company account. To get the ID, navigate to Settings > Loan > Payments > Types inside your company account. The ID is located in the ID column to the left of each payment type listing. We will enter 1.
- method – This is the ID for the payment method for the payment. This works in the same way as the payment type. Payment methods can be found at Settings > Loan > Payments > Methods. We will enter 1.
- status – This is the payment status of either active or reversed. Since we will be importing active payments, we can leave this column blank because it will default to active.
- early – This corresponds to the early payment setting, which is only applicable if the loan the payment will be imported into has an interest application of between periods. Since our loan has a between transactions interest application, we will leave this blank.
- extraTowards – This is the extra towards setting for the payment. There are four options you can choose from, but the option you choose must be appropriate to the interest application on the loan you will be importing the payment into. The options are: periods.next, periods.principalonly, tx.principal, tx.principalonly. Since our loan is a between transactions loan, we could choose either tx.principal or tx.principalonly. We will enter the value tx.principal.
- cashDrawer – This is the ID of a cash drawer you want the payment to be deposited into. You can find the IDs of your cash drawers by navigating to Tools > Cash Drawer inside your company account. The IDs are located in the ID column on the left of each cash drawer listing. We will leave this blank.
- info – For info, enter any information you want to record for this payment. We will enter 12/21/2015 – Cash.
- chargeoff – This setting lets you choose if the payment being imported should be a charge-off payment. We will leave this blank.
- reverseReason – This lets you give a reason why the payment was reversed. Since we are importing an active payment we will leave this blank.
- nachaReturnCode – This option lets you specify the NACHA error code for reversed payments. Since we are importing an active payment we will leave this blank.
The rest of the fields let you specify a custom payment application. We chose a payment type of 1 (regular) instead of custom. If we had chosen custom, we could have used the fields below to specify how the payment would apply by entering an amount for each field.
- customFees – Amount of the payment that should apply towards fees.
- customInterest – Amount of the payment that should apply towards interest.
- customDiscount – Amount of the payment that should apply towards discount.
- customPrincipal – Amount of the payment that should apply the should apply towards principal.
- customEscrow1 – Amount of the payment that should apply towards escrow bucket 1. If you want to change which escrow bucket the payment will apply towards, replace 1 with the ID of any other escrow bucket. You can also add columns to the sample file to make the payment apply towards more than one escrow bucket (e.g. customEscrow2). If you don’t want to apply the payment towards specific buckets, but simply towards due escrow, remove the 1 from this header.
See the table below for more instructions on the payments import.
Your import file should now look something like this:
We will now follow a similar process to add a second payment. The finalized sheet should look like this:
To import the file, make sure it is saved. Now, navigate to Settings > Company > Import > Upload & History inside your company account.
Select Loan Transactions and Payments from the drop-down menus provided. Drag and drop the file you created onto the drop zone, or click the drop zone to browse for the file on your computer.
Click Save to upload the file.
Click to verify the data.
If there were errors, you should see a warning message telling you about them (see troubleshooting and best practices).
You should see a checkbox labeled “Skip Calculation”. This box is not available for all imports, because loans will only be recalculated after an import of some type of transaction. Checking this box means that when the payments are imported, LoanPro will not run the calculations that update the loan. This will make things much faster because it will take an extra few seconds per payment otherwise. If you choose to skip the calculation step, the loan will be updated when daily maintenance runs overnight, or when the loan is accessed by any user.
If you had no errors, click to import the data.
Now you can check the account to see the payments we just imported.
Payment Import Instructions
|Field||Description||Expected Values||Data Format||Requirements|
|action||Command Action instructions. What are you trying to do?||add, update||Text||Required|
|loanId||The identifier for the account.||Text||Required|
|amount||The payment amount||Number||Required if status is other than “reversed”|
|date||The date the payment will apply on the account||Date: MM/DD/YYYY||Required if status is other than “reversed”|
|type||Identifier used to represent the type of payment being posted, and it’s corresponding waterfall application.||Id of the type; These can be found in Settings>Loan>Payments>Types. Expected value “1”||Number||Required if status is other than “reversed”|
|method||This is the associated method that was used to make the payment, such as Cash, or Check, etc..||Id of the method. These can be found in Settings>Loan>Payments>Methods. Expected value “4”||Number||Required if status is other than “reversed”|
|status||The Status of the Payment.||active, reversed||Text||Not Required. If omitted “Active” will be defaulted|
|early||This controls for the calculation on betweenPeriods accounts if the payment is applying before the due date for the calculation of the ADB for interest calculations only.||True/False||Text||Not Required. If omitted False will default|
|extraTowards||Identifier used to determine where funds in excess of the due amount are applied. Accepted Values of: next, principal,||periods.next, periods.principalonly, tx.principal, tx.principalonly||Text||Required if status is other than ‘reversed’|
|cashDrawer||Id of the cashdrawer to associate the payment with. Make sure that the method used is an acceptable method for the cash drawer selected.||This can be found in Tools>Cash Drawer||Number||Not Required|
|info||Title of the Payment.||Text||Required|
|chargeoff||True/False||If omitted will set to False|
|reverseReason||This is the reverse reason you want place on the payment.||The Reversal Reason. Accepted Values: checkBounce, clericalError, insufficientFunds, nachaErrorCode, other||Text||Required if Status is “Reversed”|
|nachaReturnCode||The NACHA error code for the reverse reason.||The NACHA error code for The Reversal Reason. get valid values in /Settings/Company/Merchant/NACHA Returns||Text||Required if reverseReason is “nachaErrorCode”|
|customFees||Custom Amount applied towards dueFees. Expected value of number 50.00||Number||Required if type = # for Custom and Status is other than “reversed”|
|customPayoffFees||Custom Amount applied towards Payoff Fees. Expected value of number 40.00||Number||Required if type = # for Custom and Status is other than “reversed”|
|customInterest||Custom Amount applied towards dueInterest. Expected value of number 100.90||Number||Required if type = # for Custom and Status is other than “reversed”|
|customDiscount||Custom Amount applied towards dueDiscount. Expected Value of number 20.00||Number||Required if type = # for Custom and Status is other than “reversed”|
|customPrincipal||Custom Amount applied towards duePrincipal. Expected value of number 100.00||Number||Required if type = # for Custom and Status is other than “reversed”|
|customEscrow1||Replace the 1 for any particular escrow bucket that is desired for payment to apply towards. Each would require its own column. Omit the 1 in the header, if desired to apply to the parent field of Escrow & have the software determine the allocation. This is the amount to be applied towards the dueEscrow1. Expected value as number such as 21.10||Number||Required if type = # for Custom and Status is other than “reversed”|