Walkthrough: Scheduled Loan pay back with actual interest invoice
Step 1: Register the loan
Search for Loans and click on New to create a loan. Alternatively, you can create a new loan from a customer or vendor card, FastTab Contract Management, by drilling down on the No. of Loans field.
On General FastTab, select a customer or vendor in fields Source Type and Source No. Select a Loan Category Code, it will default fields Annuity, Interest Invoice Type, Capitalized Pay Back, Scheduled Pay Back Type, Pay Back period Type and Pay Back Period Formula (in case the Pay Back Period Type = Custom). See also Setup Loan Categories for an explanation on these fields.
For a loan that is paid back by a schedule where actual interest is charged afterwards by an interest invoice select a loan category set with
- Interest Invoice Type = Non-capitalize
- Capitalized Pay Back = Yes
- Pay Back Period Type: filled with the scheduled pay back period: Month,Quarter,Year or Custom.
Fill in the Duration in months, Starting Date, Ending Date, Loan Pay Out Date and First Pay Back Date.
Fill in the Loan Amount (LCY), the Interest rate (annual). It is optional to fill field Additional Charges (LCY), but when filled, it will be added to the periodic interest invoice.
Select Calculate the Scheduled Pay Back Amount action and Scheduled Pay Back Amount (LCY) field is calculated, but you can fill this field also manually.
On Contract Object FastTab, fill the Contract Object field if you want to link the loan to an object. It is not mandatory.
On Payments FastTab, fields have been filled by default from the customer or vendor card, but you can alter the Bill-to/Pay-to field (eg. for beverage invoices an other bill-to customer pays for, but for the loan the customer pays back himself), the Payment Terms Code and the Payment Method Code (eg. the loan is paid back by a direct debit mandate, where beverage invoices are not).
Select Create Loan Lines action. For new loans, turn off the Reschedule toggle.
Loan lines are created according to schedule. The first line is for the pay out, the rest for the pay back, according to the Pay Back Period Type per pay back date a line. Column Date shows the scheduled pay out date and pay back dates. Columns Pay Back amount (LCY) and Capital Amount (LCY) have the same value as the pay back is capitalized and the interest (+ additional charges) is charged separately, afterwards by an invoice. Further columns are No. of Interest Days, Interest Rate, Interest Amount (LCY), Additional Charges Amount (LCY) and Scheduled Capital Balance (LCY). On the last line the Scheduled Capital Balance (LCY) shows zero.
The Interest Amount (LCY) is calculated from the Interest Rate (annual, divided by 360 days) multiplied by the No. of Interest Days over the previous Scheduled Capital Balance (LCY).
On Loan Invoicing FastTab make sure to fill Last Interest Invoice Ending Date field with the Loan pay out date, it will recalculate the Next Interest Invoice Ending Date based on the Pay Back Period type to the end date of the next period. When posting a Loan Interest Invoice the fields will be updated automatically.
Make sure to fill field Last Pay Back Ending Date with the Loan pay out date, it will recalculate the Next Pay Back Ending Date based on the Pay Back Period type to the next period. When posting a pay back journal the fields will be updated automatically.
On General FastTab, change Status for your information, but in the end select a status that allows for the next steps.
Step 2: Pay out the loan
Post the Loan pay out through a general journal, like the cash receipt journal or a bank journal. Select the Customer, the Loan No. and set the Loan entry Type to Loan Pay Out, it will update the (Customer) Posting Group accordingly and set the due date to the end date of the loan. Fill in the loan amount and select a balancing account. Post the Journal.
Open the Loan card. The Balance Loan (cap.) (LCY) field now shows on the Balance Fact Box and in the loan lines. Drill down to see the customer ledger entry.
It also shows on the customer card on the Contract Management FastTab in field Balance Loan (Cap) Total(LCY). In addition factbox Customer Balances by Posting group (from Drink-it Foundation) shows balances for all posting groups, including the ones selected in the Loan Category.
Step 3: Pay back the loan by journal
For scheduled pay back loans you run the Create Loan Pay Back journal lines function from the loan card (with default filter on the loan no.), or in general for all applicable loans (search for this function) to create an open due entry. Select an As per Date and Posting Date.
The function only takes valid loans into consideration
- Status: allows for pay back
- Scheduled Pay back Type: unequal None
For loans with Scheduled Pay Back type = Scheduled Amount, it will create journal lines for all Loan lines with
- Date before or on the As Per date
- Pay Back Status = New
After that the Pay Back Status of the Loan line is set to Progress.
Find the general journal that was selected for this (see also Contract Management Setup).
For non annuity loans it creates 2 journal line per loan line, for customer loans a debit and a credit line where the credit line gets the end date of the loan as due date and the debit line gets the due date from the journal line’s document date (from posting date) + the payment terms date formula of the loan. Both lines have the same loan no., loan entry type (Pay Back (Cap)) and thus the same posting group.
The credit journal line is applied to the oldest loan pay out entry. The debit journal line will post the open entry that is supposed to be collected (direct debit, apply a bank transfer to this entry, remind it when overdue...).
For vendor loans it is the opposite, the debit line is applied to the loan and the credit line is due, to be paid.
Post the journal.
This will update the Pay back Status of the loan line to Posted.
Open the Loan card. The balance of the loan is not changed, but it has an open entry to be collected with the payment method from the loan card on the due date from the loan line and payment terms. Drill down on the Balance Loan (cap.) (LCY) field to view the open entries.
When actual payment is done (direct debit, cash, bank transfer) from the customer, you post that in your cash/bank/payments book. When you select the open entry in field Applies-to Doc. No. fields Loan, Loan Entry type and Posting Group are updated from that and cannot be changed. When you apply by the Apply Entries action the journal line will be updated from there, possibly even split when multiple entries were applied with different posting groups, loans or loan entry types.
Post the payment in the journal for the due open entry.
You would likely run the Create Loan Pay Back Journal Lines batch job for all loans at period end. Then it could run into an error for a loan, e.g. because a customer card was set to blocked. It will skip the loan and continue with the next. At the end you will be notified on the loans for which no journal lines could be created. Open it and view which loans and the reason.
Step 4: Interest Invoice
Run the Create Loan Interest Invoice function from the loan card (with default filter on the loan no.), or in general for all applicable loans (search for this function). Select an Invoice to Date, Posting Date and Document Date.
The function only takes valid loans into consideration
- Interest Invoice type: unequal None
- Next Interest Invoice Ending Date before or on the Invoice to Date
When you run the Create Loan Interest Invoices batch job for all loans it could run into an error for a loan, e.g. because a customer card was set to blocked. In that case it will skip it and continue with the next. At the end you will be notified on that. Open it and view which loans and the reason.
Search for Sales invoices and open the invoice. First it shows general loan information, the loan interest period and the last balance in comment lines.
When there are no net changes in the period, it will create one invoice line for all interest days of the period. When there are net changes in the period, it will create multiple lines, based on actual balances and changes. The Quantity is based on the number of days between two last changes. The Unit price is the daily interest amount calculated over the previous balance.
Net changes are included in the balance for the next day. In this example the loan pay out was done on 1-11-21, so the remaining no. of days in the month is 29 (interest invoices calculate with 30 days per months, fixed).
When the invoice is posted field Last Interest Invoice Ending Date and therewith Next Interest Invoice Ending Date is updated on the loan (only one invoice can be posted for the same period, the check is based on the next interest invoice date of the loan vs the invoice).
On Contract Management FastTab, the Loan No. is filled, the Loan entry type is set to Non-Capitalized (from the Loan category code) and the Customer Posting group is set from that.
Post the invoice.
Open the Loan card. Balance Loan (Non-Cap) (LCY) field now shows a value for the interest invoice, both in the Balance Fact Box and in the loan lines. On Loan Invoicing FastTab, Last Interest Invoice Ending Date and Next Interest Invoice Ending Date fields have been updated.