Database - Loan Setup (loan_setup_entity)

Description

This table is associated to a loan, and holds the numbers and  settings that define the loan. If your looking for almost any number or calculation setting used to create a loan, it will be found here.

Common Uses

This table is commonly used to find:

  • Loan Values
    • Loan Amount
    • Interest Rate
    • APR
    • Loan Term
    • Payment Amount
    • Contract Date
    • First Payment Date
    • Payment Frequency
    • Underwriting Fee
    • Grace Period
    • Late Fee

It is common to join the loan_entity table with the loan_setup entity table. A query like this will join each loan_entity record with the currently used loan_setup_entity record:

SELECT * 
FROM loan_entity le
JOIN loan_setup_entity lse
ON le.setup_id = lse.id;

Table Fields

Column

Data Type

Column Info

Value Notes

Other

id

int(11)

The  loan setup entity ID. This ID is used to associate this loan setup entity with a specific loan. The ID is used in the loan_entity table.

loan_id

int(11)

This is the ID of the Loan with which this Loan Setup is associated.

mod_id

int(11)

This is the ID of the loan modification this Loan Setup is associated to. Loans can be modified multiple times, and each modification has a different Loan Setup. If this is the first loan setup on a loan, the modId will be 0.

active

tinyint(1)

This field indicates whether or not the loan is activated

1 - yes, 0 - no

til_finance_charge

decimal(11,2)

This is the dollar amount of the finance charge as calculated by LoanPro. The finance charge is the amount the borrower will pay to get the loan in addition the the loan amount. This does not include fees. This is the number disclosed as part of the Truth-In-Lending-Act requirements.

til_total_of_payments

decimal(11,2)

The total of payments is the amount the borrower will pay back on the loan, if they pay on time and in the right amounts. This is the number disclosed as part of the Truth-In-Lending-Act requirements.

til_loan_amount

decimal(11,2)

The initial amount of the loan. This is the initial principal advanced, but doesn't include the underwriting fee. This is the number disclosed as part of the Truth-In-Lending-Act requirements..

til_sales_price

decimal(11,2)

This is specifically the sales price of loan collateral. This field will only be used if the loan is secured by collateral. This is the number disclosed as part of the Truth-In-Lending-Act requirements.

til_payment_schedule

text

This is not a full amortization schedule, but is a simple payment schedule formatted like this:35 Monthly payment(s) of $177.471 Monthly payment(s) of $177.45This is the schedule disclosed as part of the Truth-In-Lending-Act requirements.

regz_custom_enabled

tinyint(1)

You have the option to manually enter the Truth-In-Lending-Act disclosure numbers. This is not recommended, but this field contains a 1 if manually-entered numbers are overriding the calculated numbers, and a 0 if the calculated numbers are being used. Manually-entered numbers are not used in calculations, they will simply be shown in the UI and can be shown on forms.

1 - yes, 0 - no

regz_apr

decimal(11,4)

This is a manually-entered value for APR on the loan. If regz_custom_enabled is set to 1, this number is overriding the calculated  APR for the loan. This number will not be used in calculations, but will be shown in the UI and can be shown on forms.

regz_finance_charge

decimal(11,2)

This is a manually-entered value for Finance Charge on the loan. If regz_custom_enabled is set to 1, this number is overriding the calculated Finance Charge (til_finance_charge) for the loan. This number will not be used in calculations, including what the borrower will actually pay, but will be shown in the UI and can be shown on forms.

regz_amount_financed

decimal(11,2)

This is a manually-entered value for Amount Financed (loan amount) on the loan. If regz_custom_enabled is set to 1, this number is overriding the calculated Amount Financed (til_loan_amount) for the loan. This number will not be used in calculations, but will be shown in the UI and can be shown on forms.

regz_total_of_payments

decimal(11,2)

This is a manually-entered value for Total of Payments on the loan. If regz_custom_enabled is set to 1, this number is overriding the calculated Total of Payments (til_total_of_payments) for the loan. This number will not be used in calculations, including what the borrower will actually pay, but will be shown in the UI and can be shown on forms

apr

decimal(11,4)

This is the calculated APR on the loan.

apr_force_single_freq

tinyint(1)

If the payment frequency on the loan is single (meaning there will be a single payment) this field will be set to 1. Otherwise the value of this field will be 0. APR is calculated differently for a payment frequency of Single than it would be for 1 payment of a different frequency.

1 - yes, 0 - no

payment

decimal(11,2)

This is the most common payment amount on the loan (some payments may be for a different amount). If each payment is for a different amount, this will be the first payment amount.

orig_final_payment_date

date

This is the originally-calculated final payment date on the loan. The date can potentially be different if the borrower pays early or late, or misses payments, causing the term to shorten or extend. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.

orig_final_payment_amount

decimal(11,2)

This is the originally-calculated amount of the final loan payment. This amount will change if the borrower pays early or late, relative to the originally-calculated amortization schedule.

loan_amount

decimal(11,2)

This is the loan amount that is entered by the user.

discount

decimal(11,2)

This is the discount amount that is entered by the user.

underwriting

decimal(11,2)

This is the underwriting fee that is entered by the user.

money_factor

char(20)

This is the money factor as entered by the user. Money factor is calculated as Interest Rate/2400. Money factor is used to calculate the rental or lease fee in a  lease.

residual

decimal(11,2)

The residual amount as entered by the user. Residual amount is the amount that leased property will be worth at the end of the lease term. The initial value of the property minus the residual amount equals the total amount the value of the property depreciated over the lease term.

loan_rate

decimal(11,4)

This is the interest rate as entered by the user. This rate is used in loan calculations. APR is a calculated value, but is NOT used to calculate interest or loan payments. It is important not to confuse the two.

loan_rate_type

char(30)

This is the frequency the interest rate is related to. Most loans use an annual interest rate, but LoanPro allows for biweekly, monthly, semi-annually, semi-monthly, and weekly rates. This field holds an enumerated value from one of our loan setup collections.

loan.rateType.annually, loan.rateType.biweekly, loan.rateType.monthly, loan.rateType.semiannually, loan.rateType.semimonthly, loan.rateType.weekly

loan_term

decimal(11,4)

The term of the loan as entered by the user. This is the number of payment periods. The length of a payment period is determined by the payment frequency (e.g. monthly, biweekly).

schedule_round

decimal(11,2)

This field holds the amount that the last payment on the payment schedule will be adjusted by. This will be 0 if there is no round on the account. A negative number will adjust the payment amount down, while a positive number will adjust it up.

contract_date

date

The contract date is the date the loan was given, and also is usually the date when interest starts accruing on the loan. It may start to accrue on the following day depending on the first_day_interest setting. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.

first_payment_date

date

This is the date the first payment will come due on the loan. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.

due_date_on_last_dom

tinyint(1)

This tracks whether payments will come due on the last day of the month. If a payment is on the 30th, for example, and the value of this field is 0, payments will come due on the 30th, even in months that have 31 days.

1 - yes, 0 - no

due_dates_on_business_days

varchar(40)

This setting specifies whether payments can come due on non-business days "loan.businessduedates.disabled" or not. If the field value is "loan.businessduedates.addDays" a due date that falls on a non-business day will be pushed into the future to the next business day. If "loan.businessduedates.subtractDays" is selected, a due date that falls on a non-business day will be pushed into the past to the nearest prior business day.

loan.businessduedates.disabledloan.businessduedates.addDaysloan.buisnessduedates.subtractDays

amount_down

decimal(11,2)

This is the amount of the down payment the borrower made on the loan collateral. This field is only relevant to collateralized loans.

reserve

decimal(11,2)

A reserve amount is sometimes kept to insure against bad loans from a loan acquisition source. The reserve is built up by holding back a portion of loan funds from the acquisition source. This is the amount the lender held in reserve when funding the loan.

sales_price

decimal(11,2)

This is the sales price of the loan collateral. This field is only relevant to collateralized loans.

gap

decimal(11,2)

This is the amount the borrower paid for GAP insurance on the loan collateral. This field is only relevant to collateralized loans.

warranty

decimal(11,2)

This is the amount the borrower paid for a warranty on the loan collateral. This field is only relevant to collateralized loans.

dealer_profit

decimal(11,2)

This is the amount of profit the dealer who sold the loan collateral made on the sale. This field is only relevant to collateralized loans.

taxes

decimal(11,2)

This is the amount of sales tax paid on the loan collateral. This field is only relevant to collateralized loans.

credit_limit

decimal(11,2)

This is the credit limit on this loan. Funds should not be advanced on the loan that exceed this amount.

reporting_credit_limit

decimal(11,2)

If the loan is reported to the credit bureaus, this is the amount that will be reported as the credit limit on the loan.

loan_class

char(30)

This is the category of the loan: automobile, real estate, consumer, or other. This field holds an enumerated value from one of our loan setup collections.

loan.class.carLoanloan.class.mortgageloan.class.consumerloan.class.other

loan_type

char(30)

This is the loan type: installment, credit limit, flooring, or lease. This field holds an enumerated value from one of our loan setup collections.

loan.type.installmentloan.type.creditLimitloan.type.flooringloan.type.lease

discount_split

tinyint(1)

If the loan was acquired for less than the face value of the loan (e.g. the borrower will repay $5,000 principal, but only $4,000 was paid to acquire the loan), the difference in the loan value and the amount paid to acquire it is discount. This setting specifies whether the discount portion will be shown as a separate line item for each payment.

1 - yes, 0 - no

payment_frequency

char(30)

The frequency at which payments come due. The options include monthly, weekly, biweekly, semi-monthly, annually, semi-annually, quarterly, single, weekly, single, and custom. This field holds an enumerated value from one of our  loan setup collections.

loan.frequency.annuallyloan.frequency.biWeeklyloan.frequency.customloan.frequency.monthlyloan.frequency.quarterlyloan.frequency.semiannuallyloan.frequency.semiMonthlyloan.frequency.singleloan.frequency.weekly

calc_type

char(30)

This is the  calculation type for the loan. This field holds an enumerated value from one of our  loan setup collections.

loan.calcType.interestOnlyloan.calcType.rule78loan.calcType.simpleInterestloan.calcType.simpleIntLocked

days_in_year

char(30)

This setting specifies whether the number of days in the year used in calculating loan interest will be the actual number of days in the year, or an adjusted number of days based on loan frequency. This field holds an enumerated value from one of our  loan setup collections.

loan.daysInYear.actualloan.daysInYear.frequency

interest_application

char(50)

This setting specifies the interest application (how interest is calculated/comes due) for the loan. This field holds an enumerated value from one of our  loan setup collections.

loan.interestApplication.betweenPeriodsloan.interestApplication.betweenTransactions

beg_end

char(20)

This setting determines if payments come due at the beginning of a payment period or the end of a period. End is the most common setting. This field holds an enumerated value from one of our  loan setup collections.

loan.begend.begloan.begend.end

first_period_days

char(40)

This setting determines how interest in the first payment period will be calculated, if the first payment period is longer or shorter than the other payment periods on the loan. This field holds an enumerated value from one of our  loan setup collections.

loan.firstPeriodDays.actualloan.firstPeriodDays.forceRegularloan.fristPeriodDays.frequency

first_day_interest

tinyint(1)

This setting determines whether interest will accrue on the loan contract date.

1 - yes, 0 - no

discount_calc

char(40)

This setting determines how the  discount portion of each payment will be calculated. This will only apply if the value for discount_split is 1. This field holds an enumerated value from one of our  loan setup collections.

loan.discountCalc.fullloan.discountCalc.percentageloan.discountCalc.percentFixedloan.discountCalc.rebalancingloan.discountCalc.straightLine

diy_alt

tinyint(1)

This field is no longer supported

1 - yes, 0 - no

days_in_period

char(30)

If the value of the payment_frequency field is "loan.frequency.custom", the value of this field will determine the length of the payment frequency in days. The option "loan.daysinperiod.1B" means that a payment comes due every business day. This field holds an enumerated value from one of our  loan setup collections.

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

round_decimals

int(11)

This setting determines the number of decimal places that will be held for loan numbers, during calculations. This is only when calculations are taking place. Numbers that are displayed will be rounded to 2 decimal places if they are currency values, and usually 4 at the most.

1 - 7

last_as_final

tinyint(1)

This option specifies whether the  final payment on the loan will grow to accommodate extra interest accrued because of late or missed payments, or if the payment schedule should have extra payments added to it instead.

1 - yes, 0 - no

ndd_calc

char(30)

This option specifies whether the amount past due should be calculated based on all loan components, or on interest only. This field holds an enumerated value from one of our loan setup collections.

loan.nddCalc.interestOnlyloan.nddCalc.standard

end_interest

char(30)

This setting specifies whether interest accrual should cease after the originally-calculated, final payment date on the loan.  This field holds an enumerated value from one of our loan setup collections.

loan.endInterest.loanExploan.endInterest.no

fixed_term_pmt

tinyint(1)

This field is no longer supported.

1 - yes, 0 - no

calc_history_enabled

tinyint(1)

If history is enabled, loan transaction history will be taken into account in calculated loan numbers, even if the loan is not activated.

1 - yes, 0 - no

calc_dates_enabled

tinyint(1)

If dates are enabled, calculated loan numbers will take dates into account, even if the loan is not activated.

1 - yes, 0 - no

schedule_template

int(11)

This is the ID of the schedule roll template the loan is using. If no template is used, the value of this field will be 0.

curtailment_template

int(11)

This is the ID of the curtailment template the loan is using. If no template is used, the value of this field will be 0.

fees_paid_by

char(30)

This setting determines if fees will be considered due (and payments applied to them) if their due date falls within the current payment period, or if it is in the past.

loan.feesPaidBy.dateloan.feesPaidBy.period

use_interest_tiers

tinyint(1)

This setting determines whether interest tiers will set the loan interest rate.

1 - yes, 0 - no

grace_days

int(11)

This is the number of days after a payment comes due before a late fee can be assessed.

1-30

late_fee_type

char(30)

The late fee type. The options are: fixed dollar amount, percentage of the unpaid portion of the payment, lesser of a fixed dollar amount or percentage, greater of a fixed dollar amount or percentage. This field holds an enumerated value from one of our  loan setup collections.

loan.lateFee.1loan.lateFee.2loan.lateFee.3loan.lateFee.4

late_fee_amount

decimal(11,2)

Dollar amount of the late fee. This is also used in the calculation of the greater or lesser late fee options.

late_fee_percent

decimal(11,2)

Percentage of the late fee. This is also used in the calculation of the greater or lesser late fee options.

late_fee_calc

char(30)

This setting determines whether the loan must be past due, past due with no payment made in the current period, or more than the late fee amount past due in order to receive a  late fee. This field holds an enumerated value from one of our loan setup collections.

loan.lateFeeCalc.currentloan.lateFeeCalc.standardloan.lateFeeCalc.standardFee

latefee_percent_base

char(40)

This setting determines whether the  escrow amount should be considered when calculating the late fee as a percentage of the unpaid portion of the payment. This field holds an enumerated value from one of our  loan setup collections.

loan.latefeepercentbase.escrowloan.latefeepercentbase.regular

roll_last_payment

int(11)

This field is no longer supported.

payment_date_app

char(30)

This setting determines if payments will apply on the apply date or on the oldest missed-payment date. This field holds an enumerated value from one of our  loan setup collections.

loan.pmtdateapp.acutal, loan.pmtdateapp.last

suspend_fc_to

date

Sometimes when calculating numbers like a future payoff, it is best to calculate them assuming  future payments won't be made. This field contains a date up to which it won't be assumed that future payments will be made when calculating loan numbers. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.

deleted

tinyint(1)

This shows whether the loan setup has been marked for deletion.

1 - yes, 0 - no

lastUpdated

timestamp

Timestamp of when the loan setup values were last updated. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.


How did we do?


Powered by HelpDocs (opens in a new tab)