Loan/Lease Import

General

Importing loans or leases into LoanPro can seem very daunting.  This article will walk you through the import process and attempt to give comprehensive information on best practices for this type of import.  For a better understanding of loan terms and advanced settings, see  Loan Terms and  Advanced Configuration.

Note: 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.

Example

In this example, we will import a single loan into LoanPro, but we will look at some of the different approaches you might take when importing loans.  We will import a loan because lease calculations are simpler, so there aren’t as many considerations when importing a lease.

To start your loan import, navigate to Settings > Company > Import > Instructions & Samples inside your company account.

Find Setup in the instruction tree.  You can do this by searching for it using the Search by keyword field, or you can expand sections of the tree by clicking  .  Once you’ve located the Setup section, click   to load the instructions for that section into the Instructions pane.  In the Samples pane, click loanSetupSample.csv to download the sample file.

Once you’ve downloaded the sample file, use any spreadsheet application to open it.  The most common spreadsheet applications include Microsoft Excel, Apple Numbers, and OpenOffice Calc.  If you don’t have a spreadsheet program, we recommend you download OpenOffice  here because it’s free.

If you use OpenOffice to open the sample file, you will first see a screen that will need information about how to use the file.

Make sure your Character set is one of the Western Europe options.  Make sure the separator option you choose is Separated by and that Comma is the only box checked.  Click OK to open the file.  Your file should look something like this:

There are really two options you have when importing loans into LoanPro.  You can either import the original loan complete with all of its transactional history (we will call this the historical method), or you can import a snapshot of the loan as it currently exists with all the current values, but no history (we will call this the snapshot method).

You should enter a value for each loan you want to import under each required header and any other header you have information for in the spreadsheet.  We will look at the values needed for both import options.  Here are the available headers:

  • Action – This is the action you would like the system to perform using the data you are providing.  The options include add and update.  Since we are adding a new account, we will enter “add”.
  • loanId – This is the ID of a loan that is set by the system.  This is one way you can identify a loan if you are trying to perform an update.  Since we are adding a loan, we will leave this blank.
  • displayId – This is the ID that you want the system to display for this loan.  This ID can be used to identify the loan for an update import as well.  The ID you enter here should be unique to this loan only.  This is not required, but is recommended when adding a loan, so we will enter “ABC-123”.
  • loanAmount – This is the loan amount.  If you are entering the the loan using the historical method, this number will be the original loan amount.  If you are using the snapshot method, this number should be the current principal balance of the loan.  We will enter 10000.
  • discount – This is the lenders fee or discount on the loan.  If you acquired the loan for less than the face value of the loan, the difference in the amount you paid vs the amount of principal that will be repaid is the discount.  If you are using the historical method, this will be the original discount on the loan.  If you are using the snapshot method, this will be the unpaid discount amount on the loan.  We will enter 1000.
  • underwriting – This is the underwriting fee charged on the loan.  If you are entering the loan using the historical method, this will be the original underwriting fee.  If you are using the snapshot method, this amount should be included in the current principal balance, so you can leave this blank.  We will enter 100.
  • interestRate – This is the rate used to calculate interest on the loan.  We will enter 25.
  • interestRateFrequency – Most interest rates are annual interest rates, but in LoanPro you can enter the rate for a different period of time.  Your options are annually, semiannually, monthly, semimonthly, biweekly, and weekly.  We will enter “annually”.
  • contractDate – This is the date when interest will start to accrue on the loan.  If you are entering the loan using the historical method, this will be the original contract date for the loan.  If you are using the snapshot method, this could be today’s date, or, if there is accrued but unpaid interest on the loan, you will need to calculate the date in order to accrue that much interest.  For example, if there is $150 outstanding interest on the loan at the time we want to import it, we can calculate the best contract date in the following way.  Our loan is a $10,000 loan that accrues 25% interest each year.  We can use this to calculate how much interest it accrues in a day.  Take the annual rate of 25 as a decimal (.25) and divide it by the number of days in the year.  .25/365 = 0.0006849315.  This is the daily interest rate.  Now, we will multiply this by the principal balance, which equals the loan amount of $10,000 plus the underwriting fee of $100.  This gives 10100 x 0.0006849315 = 6.917808.  So we know the loan will accrue $6.92 of interest per day.  If we divide the outstanding interest of $150 by the daily interest of $6.92, this will tell us how many days ago we should put the contract date in order to accrue the appropriate amount of interest.  In this case, it is 150 / 6.92 = 21.68.  So we would probably put the contract date 21 days ago just to make sure we didn’t accrue too much interest.  We will enter 12/30/2015.
  • firstPaymentDate – If you are entering the loan using the historical method, this will be the original first payment date from the loan.  If you are using the snapshot method, this will be the next date when a payment will come due on the loan.  We will enter 01/15/2016.
  • term – This is the number of payment periods on the loan.  If you are using the historical method, this is the original loan term.  If you are using the snapshot method, the term must be correctly calculated so you will end up with correct payment amount.  Solve for the term and enter that number.  Since we are entering a new loan, we will enter 12.  The formula to calculate the term is:

 n = term
r = period interest rate
PV = present value
P = payment amount.
  • frequency – This is the frequency with which payments come due.  The options are monthly, semiMonthly, biWeekly, weekly, custom, and single.  We will enter “monthly”.
  • customFrequency – This is required if you chose custom as the frequency.  The options are 1,1b, 2, 3, 4, 5, 6, 8, 9, 10, 12, 13, 15, 18, 20, 26, 28, 30, 36, 40, 45, 52, 60, 72, 73, 90, 91, 120, 180, 182, 360, 364, 365.  Most of the options represent the number of days in each payment period.  1b means that a payment will come due each business day.  We will leave this blank.
  • residual – This is only applicable to leases.  This is the amount a vehicle will be worth at the end of the lease term.  We will leave this option blank.
  • moneyFactor – This is only applicable to leases.  This is the interest rate divided by 2400.  For more information see  Typical Lease Calculation.  We will leave this blank.
  • forceSingleFrequency – This option lets you choose to have LoanPro calculate APR using a single payment frequency if the term of a loan is 1.  We will enter “true”.
  • interestTiers – This lets you choose to have your interest rate assigned based on  interest tiers in your company account.  We will enter “false”.
  • creditLimit – This is only required if your loanType is creditLimit.  This is the dollar amount of the credit limit for the loan.  We will leave this blank.
  • creditReporting – This is the credit limit that will be reported to the credit agencies.  This will only be reported if you are using LoanPro’s credit reporting feature.  We will leave this blank.
  • amountDown – This is the amount the customer put down on the loan.  We will leave this blank.
  • salesPrice – This is the sales price of the collateral if the loan is secured by collateral.  We will leave this blank.
  • taxes – This is the sales tax paid on the collateral if the loan is secured by collateral.  We will leave this blank.
  • GAP – This it the amount paid for a GAP insurance policy on a vehicle if the loan is secured by a vehicle.  We will leave this blank.
  • dealerProfit – This is the dealer profit on the sale of the collateral if the loan is secured by collateral.  We will leave this blank.
  • warranty – This is the amount paid for a warranty on the collateral if the loan is secured by collateral.  We will leave this blank.
  • reserve – This is the amount held in reserve to insure against bad loans from a  source company.  We will leave this blank.
  • loanType – This is the type of loan.  The options are installment, creditLimit, flooring, and lease.  We will enter installment.
  • loanCategory – This is the category of the loan collateral.  The options are auto, realestate, consumer, and other.  If the loan is not secured by collateral, other is the typical choice.  We will enter “other”.
  • calcType – This is the interest calculation for the loan.  The options are lease, leaseAlt if the loanType is lease, otherwise the options are simpleInterest, simpleIntLocked, interestOnly, rule78.  We will enter “simpleInterest”.
  • discountSplit – This option lets you choose whether to recognize discount income over the life of the loan.  We will enter “true”.
  • discountCalc – This option lets you specify the  calculation type that will be used to determine what portion of each payment should apply towards discount.  The options are straightLine, rebalancing, percentage, percentFixed, and standard.  We will enter “percentFixed”.
  • daysInYear – This lets you specify whether the number of days in the year should be the actual number (365 or 366) or calculated based on the payment frequency (360 for monthly and semi-monthly and quarterly, 364 for weekly and bi-weekly).  The options are frequency and actual.  We will enter “actual”.
  • loanFirstPeriodDays – This lets you specify how LoanPro should treat an odd number of days in the first period (a number that isn’t the typical number of days in a payment period).  If interestApplication is betweenPeriods then the options are actual, frequency or forceregular. If interestApplication is betweenTransactions and daysInYear is actual the only option is actual. If daysInYear is frequency and interestApplication is betweenTransactions your options are actual, frequency, or forceregular.  We will enter “actual”.
  • interestApplication – This selection determines the way interest is calculated and how it comes due.  The options are betweenPeriods and betweenTransactions.  We will enter “betweenTransactions”.
  • begEnd – This option lets you choose whether payments should be made at the beginning of the payment period, before interest accrues in that period, or at the end of the period.  We will enter “end”.
  • lastIsFinal – This lets you choose whether the originally calculated last payment date will always be the last payment date.  If that is what you decide, any extra interest accrued and the amount of missed payments will be added to the final payment.  We will enter false.
  • RoundDecimals – This is the number of decimals that values will be rounded to during the calculations that take place in LoanPro.  You can enter integers 1 – 7.  We will enter 7.
  • scheduleRound – This is an amount added to or rounded off of the payment schedule starting with the final payment.  We will leave this blank.
  • firstDayInterest – This option lets you choose whether to charge interest on the first day of the loan.  We will enter true.
  • endInterest – This option lets you choose whether to stop interest accrual when the originally calculated final payment date on the loan passes.  The options are no or loanExp.  We will enter “no”.
  • rollScheduleTemplate – This option lets you specify whether a  schedule roll template should be used when calculating the payment schedule.  The options are 0 – none, 1 – Prorate 1st Pmt Long Only, 2 – Prorate 1st Pmt Short Only, 3 – Prorate 1st Pmt All, 4 – Smooth Payment Advanced, 5 – Smooth Payment Basic, 6 – Smooth Payment Advanced (No Round), or you can also create your own templates and enter the ID of any template here.  IDs can be found at Settings > Loan > Setup New Loan > Schedule Roll Templates.  We will leave this blank.
  • feesPaidBy – This option lets you specify how fees should come due.  Fees can either come on the date entered for the fee, or be paid by any payment made in the same payment period that the fee falls in.  The options are date and period.  We will enter “date”.
  • paymentDateApplication – This option lets you choose whether payments will apply on the date of the first missed payment on the loan or on the date entered for the payment.  The options are last and actual.  Actual is by far the most common option.  We will enter “actual”.
  • loanNddCalc – This setting will control how the system calculates the next due date. Standard will take the amounts that have come due & subtract off what has been paid (except “only” payments, such as Principal Only). When that number becomes positive & the next payment date is in the future of the current date, then that will be the next due date. Interest Only will work the same way, however it will only take into consideration the interest that has come due & subtract off the interest that has been paid.  The options are standard and interestOnly.  We will enter “standard”.
  • curtailmentPercentageBase – This is the amount used to calculate the curtailment percentage.  The current option is loanAmount.  We will leave this blank.
  • gracePeriod – This is the number of days after a payment comes due before a late fee is assessed on the account.  Enter an integer between 0 and 30.  We will enter 10.
  • lateFeeType – This determines whether automatic late fees will be a percentage of the outstanding payment amount a flat fee or the greater or lesser of those options.  You can enter an integer 1 – 5.  1 – $30.00 flat fee, 2 – custom flat fee, 3 – custom percentage, 4 – greater of custom flat fee or custom percentage, 5 – lesser of custom flat fee or custom percentage.  We will enter 2.
  • lateFeeCalc – This selection determines when a late fee is assessed.  The options are standard, standardFee, and current.  Standard will assess a fee if the customer is even $0.01 past due.  The standardFee option will assess a late fee if the customer is past due an amount greater than the late fee.  The current option will assess a late fee only if the borrower hasn’t made their full payment in the current payment period regardless of any outstanding balance from previous payment periods.  We will enter “standard”.
  • LateFeeAmt – This is the custom late fee amount.  This is required if the lateFeeType is 2, 4, or 5.  We will enter 10.
  • LateFeePercent – This is the custom percentage used to calculate the late fee.  This will be a percentage of the unpaid portion of the payment.  This is required if lateFeeType is 3, 4, or 5.  We will leave this blank.
  • curtailmentTemplate – This is the ID of the curtailment template you want to add to the loan. The ID should be numeric and this value is not required. We will leave this blank.
  • dueOnBusinessDays – This option lets you elect what to do if a  customer’s payment due date falls on a non-business day. The options are to add days to make the payment due on the following business day, subtract days to make the payment due on the previous business day, or disable this feature and let the payments fall as they may. The options are addDays, disabled, subtractDays. We will enter “disabled”.
  • primaryCustomerId – This is the ID of the customer that should be added to the primary position of this loan.  If you previously imported customers and assigned them import IDs, the import ID should be entered here.  You can find the ID for a customer by navigating to Users > Customer Manager inside your company account.  We will leave this blank.
  • secondaryCustomerId – This works similarly to primaryCustomerId.  We will leave this blank. 
  • additionalCustomerId – This works similarly to primaryCustomerId.  We will leave this blank.

At this point, your import file should look something like this:

Save the file and then navigate to Settings > Company > Import > Upload & History.

Click  .

Choose Loan Account and Setup from the drop-downs provided.  Either click the drop zone to browse for your import file, or drag and drop the file on the drop zone.

Click  .

Click   to verify the data.

If there are errors in the file, click E to see what they are.

If the data verifies correctly, click  to complete the import.

Now you can verify that the loan is in the system by navigating to Loans > Loan Manager and using the keyword search to find loan ABC-123.  You are done!

Import Instructions

Samples  (Download)

  1. loanSetupSample.csv

Field

Description

Expected Values

Data Format

Requirements

action

Command Action instructions. What are you trying to do?

add, update

Text

Required

loanId

This is a value set by the system at time of creation of the account. It is a unique identifier of the account.

Alpha/Numeric

Required for Update, Ignored/Omit on Add

displayId

Client set Account Number, as the Display ID. If a value is not set in this field then the loanId will be the Account Number. If a value is set here then this will be the Account Number. This should be a unique value, as many problems can occur if you so choose to duplicate the Account numbers including import items being mapped to a different account that has the same Account Number.

Alpha/Numeric

Recommended on Add, Not Required

loanAmount

Amount Financed

Number

Required

discount

Total Discount

Number

Not Required. If Omitted will set at 0

underwriting

Total Underwriting Fee (this value is included in the total principal balance, and is interest bearing).

Number

Not Required. If Omitted will set at 0

interestRate

Interest Rate for the provided Interest Rate Frequency. This is NOT APR, it is the nominal interest rate. Most common is to have an annual interest rate. Which is similar or close to the APR but may differ depending on the odd days in the 1st period, pre-paid finance charges, and other values. APR is an ouput value, nominal interest rate is an input value.

Number, such as 19.90 = 19.90%

Required.

interestRateFrequency

This is the frequency at which the interestRate field occurs. For example if I provided a 10% monthly rate, this is equal to 120% annual rate. This is NOT APR. It is the frequency at which the interestRate occurs.

annually, semiannually, monthly, semimonthly, biweekly, weekly

Text

Not Required. If Omitted will set to Company Defaults.

contractDate

Date of the Contract.

Date: MM/DD/YYYY

Required

firstPaymentDate

Date that the 1st payment is contractually due.

Date: MM/DD/YYYY

Required

term

The number of periods on the account.

Number

Required

frequency

The frequency in which payments come due on the account.

monthly, semiMonthly, biWeekly, weekly, custom, single

Text

Required

customFrequency

For custom frequency this is the number of days in the custom frequency

1,1b, 2, 3, 4, 5, 6, 8, 9, 10, 12, 13, 15, 18, 20, 26, 28, 30, 36, 40, 45, 52, 60, 72, 73, 90, 91, 120, 180, 182, 360, 364, 365

Number

If frequency is “custom” then Required. Otherwise Ignored

residual

The residual on the Lease account. This is the remaining depreciation outstanding after the original term of the lease expires.

Number

Required if LoanType is Lease

moneyFactor

This is the lease money factor. It has relation to the interest rate, however it is not interest rate.

Number

Required if LoanType is Lease

forceSingleFrequency

If term is 1, then there is an option to force the APR calculator to use the frequency of single regardless of the frequency selection. If True is selected this is the equivalent of checking that box. If False then the box is not selected. This is only valid if the term is something greater than 1 & the frequency is something other than single.

TRUE/FALSE

TRUE/FALSE

Not Required. If Omitted will set to FALSE

interestTiers

TRUE/FALSE — this would have the interest rate be a calculated value based off the settings selected in the tenant, rather than an input value

TRUE/FALSE

TRUE/FALSE

Not Required. If Omitted will set to FALSE

creditLimit

The amount of the credit limit

Number

Required if ‘creditLimit’

creditReporting

The credit limit that will be reported to the credit agencies

Number

Required if ‘creditLimit’

amountDown

The Total Amount Down. This is reported in the TIL disclosures but does not have any effect on the calculations of the account.

Number

Not Required

salesPrice

Sales Price, if applicable. This does not effect calculations on the account

Number

Not Required

taxes

taxes, if applicable. This does not effect calculations on the account

Number

Not Required

GAP

GAP, if applicable. This does not effect calculations on the account

Number

Not Required

dealerProfit

Dealer Profit, if applicable. This does not effect calculations on the account

Number

Not Required

warranty

Warranty, if applicable. This does not effect calculations on the account

Number

Not Required

reserve

Reserve, if applicable. This does not effect calculations on the account

Number

Not Required

loanType

The Type of the Loan

installment, creditLimit, flooring, lease

Text

Required

loanCategory

A classification as to what the account category is.

auto, realestate, consumer, other

Text

Required

calcType

The calculator method that determines the calculations on the account.

If loanType is Lease then acceptable values are: lease, leaseAlt. If loanType is installment, creditLimit, or flooring then acceptable values are: simpleInterest, simpleIntLocked, interestOnly, rule78

Text

Required

discountSplit

Setting that determines if the discount amount is broken out over the term of the account to realize income in installments rather than at origination of the contract.

TRUE/FALSE

TRUE/FALSE

Required. If omitted will pull from company defaults

discountCalc

Method used to calculate what amount of discount will be realized as income in what period.

straightLine, rebalancing, percentage, percentFixed, standard

Text

Required. If omitted will pull from company defaults

daysInYear

A setting that determines a component in calculating interest.

frequency, actual

Text

Required. If omitted will pull from company defaults

loanFirstPeriodDays

Determines the number of days that are counted for interest accrual in the first period of the loan between the loan contract date and the first due date.

(If interestApplication is ‘betweenPeriods’ then it can be ‘actual’, ‘frequency’, ‘forceregular’. If interestApplication is ‘betweenTransactions’ and daysInYear is ‘actual’ then it can only be ‘actual’. If daysInYear is ‘frequency’ and interestApplication is ‘betweenTransactions’ then it can be ‘actual’, ‘frequency’, or ‘forceregular’)

Text

Required. If omitted will pull from company defaults

interestApplication

Interest application selection for the loan

betweenPeriods, betweenTransactions

Text

Required. If omitted will pull from company defaults

begEnd

In the calculation of the payment amount formula, do you want to have it assume the payment is made at the beginning or the end of the period.

beg, end

Text

Required. If omitted will pull from company defaults

lastIsFinal

Balloon the final payment to accommodate missed or late payments (instead of increasing the loan term)

TRUE/FALSE

TRUE/FALSE

Required. If omitted will pull from company defaults

RoundDecimals

The number of decimal places before rounding in the calculations on the account. This affects interest accrual, balances, due amounts, etc..

2,3,4,5,6,7

Number

Required. If omitted will pull from company defaults

scheduleRound

Amount to apply as an adjustment to the final payment. A negative amount will round the final payment amount down, or a positive amount will round the final payment amount up. (Please Note: Will only work on updates to existing configured loans)

Number

Not Required

firstDayInterest

This setting will determine if you charge interest on the Contact date or not. True is yes, charge interest on the contract date, False is no, don’t charge interest on the contract date

TRUE/FALSE

TRUE/FALSE

Required. If omitted will pull from company defaults

endInterest

This is a selection if you wish to stop interest accrual on the account. This is a bit of a short cut as you also have the ability to specifically submit a particular date to that entity in the software. This selection will allow you to choose from existing pre-determined date values & upon the calculation of the account have it stop interest accrual as of that date.

no, loanExp

Text

Required. If omitted will pull from company defaults

rollScheduleTemplate

This is the preselection of the applied Roll Schedule Template to use when calculating the account.

0 – none, 1 – Prorate 1st Pmt Long Only, 2 – Prorate 1st Pmt Short Only, 3 – Prorate 1st Pmt All, 4 – Smooth Payment Advanced, 5 – Smooth Payment Basic, 6 – Smooth Payment Advanced (No Round). For Company created Template use the Id associated to them found here:Settings>Loan>Setup New Loan>Schedule Roll Templates.

Number

Required. If omitted will pull from company defaults

feesPaidBy

This setting determines that when payments apply, if fees due are taken into consideration by the period that they are inside of, or by the date of the charge. If fees are set to period it is possible to have a payment apply towards a charge that did not exist until after that payment applies.

date, period

Text

Required. If omitted will pull from company defaults

paymentDateApplication

This setting only has any relevance to interest calculation accounts of “between Periods”, it does not affect “between Transaction” accounts. This setting will determine the application date of how payments apply. If you select “Actual/Next” on a account that is “between Periods” for interest application, then when that payment is logged to the account, it will calculate its application date as follows: If the account is current 0 past due then it will apply the payment as if it was paid on the next due date, if the account is past due >0 (in any amount) then it will apply the actual date entered into the payment. If the payment date application setting is “Last/Next” this means that if the account is past due then it will apply as of the earliest missed payment date (as if it was paid on time). If the account is current then it will apply towards the next payment date.

actual, last

Text

Not Required. If omitted will pull from company defaults

loanNddCalc

This setting will control how the system calculates the next due date. Standard will take the amounts that have come due & subtract off what has been paid (exempting “only payments, such as Principal Only”). When that number becomes positive & that date is in the future of the current date, then that will be the next due date. Interest Only will work the same way, however it will only take into consideration the interest that has come due & subtract off the interest that has been paid.

standard, interestOnly

Text

Not Required. If omitted will pull from company defaults

gracePeriod

The number of days after a payment comes due before a late fee is assessed

Number 0 – 30

Number

Not Required. If omitted will pull from company defaults

lateFeeType

The amount or pecentage that is assessed as a late fee. (1 = Flat $30.00 Late Fee; 2 = Custom Flat Fee; 3 = Percentage of Payment; 4 = Greater of Flat Fee or Percentage of Payment); 5 = Lesser between flat amount and percentage

1, 2, 3, 4, 5

Number

Not Required. If omitted will pull from company defaults

lateFeeCalc

The method used to calculate if a late fee should be assessed. [standard = Late fees are calculated on past due interest and principal; standardFee = Late fees are calculated based on past due interest, principal and fees; current = Calculates late fees only on unpaid balances in the current payment period]

standard, standardFee, current

Text

Required

LateFeeAmt

This is the flat amount that the late fee is.

Number

Required if lateFeeCalc is 2, 3, or 4.

LateFeePercent

This is the flat late fee percentage.

Number formatted as 10.50 = 10.50%

Number

Required if ‘3’ is entered for lateFeeType

curtailmentTemplate

Curtailment Template Id

Number

Curtailment Template Id, 0 if none.

dueOnBusinessDays

Due dates fall only on business days?

addDays, disabled, subtractDays

Text

Not Required

primaryCustomerId

The identification number associated with the Primary Customer that will be linked this account (must match a customerId or importId for an existing customer). For an update import, use 'null' in this field to unlink the customer.

Text

Required if Flooring Account.

secondaryCustomerId

The identification number associated with the Secondary Customer that will be linked this account (must match a customerId or importId for an existing customer). For an update import, use 'null' in this field to unlink the customer.

Text

Not Required

additionalCustomerId

The identification number associated with additional customers that will be linked this account (must match customerIds or importIds for existing customers). To link multiple additional customers, list the IDs separated by semicolons. For an update import, use 'null' in this field to unlink the customer.

Text

Not Required


How did we do?


Powered by HelpDocs (opens in a new tab)