Import Example – Payments

LoanPro has updated our Import process in the latest release. For more info, see our articles on The Import Process, Importing through the API, and Import Reports.

General

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. The process requires that you know the loan ID for any loans you are importing, so you may want to brush up on Finding Loan and Customer IDs.

The import system is not a real-time import. When you choose to import data, the import is queued and run when it gets to the front of the queue.

Similarly, the payments will not be calculated on the loan the minute they're imported. They will apply during Daily Maintenance. You can, however, force them to calculate immediately with a Calc Import, one of the options in a Loan Tools and Actions Import.

Instructions and Samples

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.

Building the File

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:

Uploading the Import

To import the file, make sure it is saved. Now, navigate to Settings > Company > Import > Upload & History inside your company account.

Click the Add button in the top right.

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.

You can drag and drop multiple files of the same kind.

New to LoanPro's June 2021 Release: There are two other options on this page as well, Validation Type and Import Progression.

  • Validation type
    • File rejection – If the file has any errors, LoanPro will reject it entirely, and tell you which rows have problems.
    • Line rejection – LoanPro will accept all valid rows, but reject any rows that have issues.
  • Import Progression
    • Manual – A human being will need to manually click through each step of the import.
    • Automatic – LoanPro will automatically start the next import step when it reaches the end of the previous step, including: validating, ingesting, verifying, and reporting.
    For more info, see our article on The Import Process.

When you've made your selections and added your file, click Save in the top right corner.

Click  to verify the data.

If there were errors, you should see a warning message telling you about them (see troubleshooting and best practices).

After validating the data, you may see a button that says "Skip Calculation." This is a vestige from an earlier version of the software, where the system would immediately calculate and apply payments to loans. The Skip Calculation button would delay this calculation until daily maintenance, speeding up the import process. Since then, however, the software has been updated. Now, skipping this calculation is the default; imported payments will be applied to their loans during daily calculation. The Skip Calculation button will be removed in a future update, but for now it does nothing. If you'd like the system to calculate those payments before daily maintenance, you can perform a Calc Import, one of the options for Loan Tools and Actions Imports.
If you had no errors, click  to import the data.

Now you can check the account to see the payments we just imported.

Column Values

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

id

paymentId

Number

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”


How did we do?


Powered by HelpDocs (opens in a new tab)