NetLoan - As of Date Fields
NetLoan As of Date Fields
Use Case: The NetLoan "As of Date" fields allow users to view various loan balances and payment information fields as of any date in the loan’s life. These can be pulled into reports and are primarily used to provide borrowers with accurate balances as of today's date.
Step by Step Process:
1. The following fields are listed on the Management tab and Summary subtab on every loan record:
| Field | Field ID | Description |
| As Of Date | custrecord_da_loan_as_of_date | The date through which all calculations are performed. This is the cut-off date for determining what has happened versus what will happen in the future. |
| As Of Balance | custrecord_da_loan_as_of_balance | The outstanding principal balance on the loan as of the As Of Date. Calculated by: (1) Start with the beginning balance (what was owed at the start of the period), (2) Add all principal changes during the period (typically negative, reducing the balance), (3) The result is what the borrower still owes on the original loan amount. |
| As Of Accumulated Accrued Interest | custrecord_da_loan_as_of_accum_accr_int | Interest that has been earned but not yet paid by the As Of Date. Calculated by: (1) Start with accumulated accrued interest at the beginning of the period, (2) Add any new accrued interest that accumulated during the period, (3) This represents interest that is owed but isn't due yet. |
| As Of Deferred Accumulated Interest | custrecord_da_loan_as_of_def_accum_int | Interest that has been deferred (postponed) and accumulated over time. Calculated by: (1) Start with deferred accumulated interest at the beginning of the period, (2) Add any new deferred interest from the current period, (3) This tracks interest that won't be paid immediately but will be paid later. |
| Days Overdue | custrecord_da_loan_days_overdue | How many days past due the most overdue payment is. For revolving loans: Uses the "in arrears days" field from the loan schedule. For fixed installment loans: Looks at all unpaid invoices with due dates before the As Of Date, finds the invoice with the earliest due date that still has an unpaid balance, calculates the number of days between that due date and the As Of Date. |
| Earliest Bill Date | custrecord_da_loan_earliest_bill_date | The transaction date of the oldest unpaid invoice. Calculated by: Searches all invoices that still have unpaid balances and were due before the As Of Date, returns the transaction date (not due date) of the earliest one. This helps identify how long a borrower has had outstanding bills. |
| Outstanding Total | custrecord_da_loan_outstanding_total | The total amount of unpaid invoices that are currently due. For revolving loans: Uses the "in arrears balance" field from the loan schedule. For fixed installment loans: Adds up the unpaid balance from all invoices where the due date is on or before the As Of Date. This includes both overdue amounts and amounts due today. |
| Fees Charge Total | custrecord_da_loan_fees_charge_total | The total amount of all fees charged on the loan. Calculated by: (1) Identifies "fee" invoices by looking for schedule lines with a payment amount of $0 or early payoff fees from loan modifications, (2) Adds the original loan origination fees (if any), (3) Sums all these fee amounts together. |
| Overdue Fees | custrecord_da_loan_overdue_fees | Fees that have been charged but not yet paid. Calculated by: (1) Takes the total fees charged, (2) Subtracts all fee payments that have been made, (3) The difference is what's still owed in fees. |
| Fees Paid Total | custrecord_da_loan_fees_paid_total | The total amount of fees that have been paid. Calculated by: (1) Looks at all payments applied to fee-type invoices, (2) Adds the original loan origination fees (since those are typically paid upfront), (3) Sums these amounts. |
| Interest Paid Total | custrecord_da_loan_interest_paid_total | The total amount of interest paid through the As Of Date. Calculated by: (1) For each invoice that had a payment, payments are applied to interest first. If the payment fully covers the interest due, all that interest is counted as paid. If the payment is less than interest due, only the payment amount counts. (2) Sums up the interest paid across all invoices. (3) Does not include the initial loan setup transaction. |
| Overdue Interest | custrecord_da_loan_overdue_interest | Interest that was due but hasn't been paid yet. Calculated by: (1) For each invoice due before the As Of Date: If no payment - all interest is overdue. If partial payment less than interest - the difference is overdue. If payment covers interest - no interest is overdue. (2) Sums up overdue interest across all qualifying invoices. |
| Last Payment Date | custrecord_da_loan_last_pmt_date | The most recent date when any payment was received. Calculated by: Looks at all payments made on or before the As Of Date, returns the latest payment date found, excludes the initial loan setup transaction. |
| Next Payment Date | custrecord_da_loan_next_pmt_date | When the next payment is expected. Calculated by: Looks for the earliest date that is either a schedule line period end date that is after the As Of Date and has a payment amount, OR an invoice transaction date that is after the As Of Date and has an unpaid balance. Returns whichever date comes first. |
| Overdue Payments | custrecord_da_loan_overdue_payments | The total amount of payments that are past due. For revolving loans: Uses the "in arrears balance" from the loan schedule. For fixed installment loans: Takes all invoices with due dates before (not including) the As Of Date, sums up their unpaid balances. If the result is negative (meaning overpayment), it's set to zero. |
| Overpayment Total | custrecord_da_loan_overpayment_total | Payments received that exceeded what was owed. Calculated by: Looks at schedule lines marked as "adjustment payments" where the borrower paid more than required, sums up the payment amounts from these adjustment entries. |
| Payment Paid Total | custrecord_da_loan_payment_paid_total | The total of all payments received (including fees, principal, and interest). Calculated by: Sums all payments applied to any invoice related to this loan, includes payments made on or before the As Of Date, excludes the initial loan setup transaction. This is the gross total of all money received. |
| Payoff Amount | custrecord_da_loan_payoff_amt | The amount needed to completely pay off the loan today. Calculated by: (1) Start with the current loan balance, (2) Add accumulated accrued interest, (3) Add any payments currently due, (4) Subtract any prepayments already made, (5) Adjust for custom fee balances based on early maturity preferences (some fees may be forgiven on early payoff), (6) If the result is negative but there's still a loan balance, set to zero. |
| Total Prepayments | custrecord_da_loan_total_prepayments | Payments made ahead of schedule toward future invoices. Calculated by: Looks for payments where the invoice due date is AFTER the As Of Date (it's a future payment) but the payment was made ON OR BEFORE the As Of Date (already paid early). Sums these early payment amounts. Note: These prepayments "disappear" when the As Of Date reaches their invoice due date (they become regular payments). |
| Prepayment Balance | custrecord_da_loan_prepayment_balance | Balance of payments made as of the As of Date. Calculated by: Looks for payments where the invoice due date is AFTER the As Of Date (it's a future payment) but the payment was made ON OR BEFORE the As Of Date (already paid early). Sums these early payment amounts. Note: These prepayments "disappear" when the As Of Date reaches their invoice due date (they become regular payments). |
| Overdue Principal | custrecord_da_loan_overdue_principal | The principal portion of payments that are past due. For each invoice due before the As Of Date: If there's no invoice, invoice wasn't due yet, or scheduled payment was $0 (fee only), or payment fully covers invoice: principal overdue = $0. If payment only covers interest or less: principal overdue = full principal amount. If payment covers interest plus some principal: principal overdue = remaining principal unpaid. The logic: payments apply to interest first, then principal. Whatever principal remains unpaid is overdue. |
| Principal Paid Total | custrecord_da_loan_principal_paid_total | The total amount of principal paid down through the As Of Date. For each invoice with a payment made on or before the As Of Date: (1) Payments apply to interest first, (2) If payment exceeds interest, any amount over the interest goes to principal (up to the full principal amount scheduled), (3) Sums up principal paid across all invoices, (4) Excludes the initial loan setup transaction. |
| Remaining Interest | custrecord_da_loan_remaining_interest | Interest that will be due in the future on scheduled payments. Calculated by: Takes the total interest scheduled on all future invoices (invoices with due dates after the As Of Date). This represents interest the borrower will owe on future payments. Does not include interest already paid. |
| Remaining Payment | custrecord_da_loan_remaining_payment | The total amount of future scheduled payments. Calculated by: (1) Sums up all scheduled payments from the loan schedule, (2) Subtracts all payments already received through the As Of Date, (3) The difference is what's left to be paid in the future. |
| Refund Total | custrecord_da_loan_refund_total | The total amount of refunds issued on the loan. Calculated by: Looks at schedule lines during the statement period, sums up any refund amounts recorded on those schedule lines. This tracks money returned to the borrower. |
| XNPV | custrecord_da_loan_xnpv | The net present value of all future loan payments using a specified valuation rate. Calculated by: (1) Only calculated if global configuration allows it and a valuation rate is specified, (2) Gets all future scheduled payments (after the As Of Date), (3) For each future payment, calculate days from As Of Date to payment date and discount the payment amount by: Payment ÷ (1 + Valuation Rate)^(days/365), (4) Sum all discounted payment values. This gives you the "present day value" of all future payments. In simple terms: If the loan has $10,000 in future payments spread over 5 years, the XNPV tells you what those future payments are worth in today's dollars (accounting for the time value of money). This needs to be enabled in the system setup. |
| Update Summary Fields Flag | custrecord_da_loan_update_summary_fields | A flag that controls whether the summary should be automatically recalculated. Always set to false after the summary is updated. This prevents the system from continuously recalculating the same values. |
The calculation of all of the fields above depends on the As of Date entered. The method used to update this field depends on two global settings related to the NetLoan Summary Field functionality. These global settings can be found by navigating to NetLoan > NetLoan Setup > System Setup under System Settings:
As of Date Reporting: Allow UI Update: Check this box to allow saving of Loan Summary fields if user edits the “As of Date” field on Loan Records in the User Interface. If this box is unchecked, the loan summary values will be reset upon saving the loan.
As of Date Reporting: Auto Update on Billing & Payment: Check this box to automatically update the “As of Date” field on loans when Bills/Invoices are created or paid.
Based on the settings chosen above, users have the following options in changing the As of Date:
Change the As of Date by clicking Edit on the loan record and manually changing the date in the User Interface. This is only an option if the As of Date Reporting: Allow UI Update box is checked in the NetLoan System Setup.
Change the As of Date with a csv upload. All that is needed for this is a csv file with the Internal ID of all loans needing to be updated and the new As of Date desired.
Automatically update the As of Date on billing and payment by checking the box in the system settings.