Q: How can I provide my borrowers a temporary payment and interest break in Margill Loan Manager?
A: With normal loans, those with regular payments, at regular intervals, and no regular fees or taxes (the case with leases), this can easily be done in Margill and in bulk. So you need not do this individually, loan by loan unless you are dealing with irregular scenarios where these would have to be done partially in bulk and on an individual basis in many instances.
In the latest version 5.1 (released March 20 or 23, 2020) we created a tool to specifically allow the interest rate to be changed to 0.00% or reduced by x% for a determined period of time.
NOTE: If you do not wish to give borrowers a break with the interest rate, skip step 1. The interest rate will remain unchanged and accrued interest will be compounded if you are charging Compound interest. If you are using Simple interest you can also capitalize that interest with the right mouse click on the last line with payment of 0.00 :
Steps:
- Change interest rate to 0.00% (or other rate)
- In the “Post Payment” tool, export the payments to be “cancelled” (deferred)
- Post payments to 0.00 (or other amount) in the “Post Payment” tool
- Add new (deferred) payments at end of loan
1. Change interest rate to 0.00% (or other rate)
Let’s do an example:
Normal payment schedule before the 0.00% interest for 3 months:
We wish to stop charging interest for the months of April, May and June for the loans highlighted in blue below.
In the Main Margill window, select (highlight) the desired loans. This shortcut must then be used: “Ctrl Alt Shift i”. This window will appear with a new option “Add End date of rate change”. In normal situations this is kind of difficult to understand since a little illogical… We will probably delete this option in version 5.2…
Enter as such:
You will be prompted to do a backup – PLEASE do this just in case you enter the wrong dates – you don’t want to have to change back your interest rate loan by loan!
In the example, notice two new lines with Rate change. So rate of 0% as of 04-01-2020 and back to 7.98% on 07-01-2020 (we temporarily displayed the “Start Date” column which is normally hidden):
2. In the Post Payment tool, export the payments to be cancelled
We first wish to find all payments to be deferred. Go to Tools > Post Payments (a very powerful tool!), check the box called “Use date interval” and enter the proper dates to find all such payments. Press on Refresh.
These are all the payments we wish to defer (so a total amount of 26,149.20 in this scenario -“Interval” amount):
Right click in the window and “Export to Excel” – save the file which we will be using in step 4 to re-import the deferred payments.
3. Post payments to 0.00 (or other amount) in the “Post Payment” tool
Stay in the “Post Payment” tool with the current list of payments. Right click with the mouse and choose this option:
You also could have clicked the “Select All” button and the option to change the Line statuses would have been there as with the right mouse click.
Notice the special Line status that was created: “Deferred Pmt (0.00)”. This would have been created previously through Tools > Settings > System Settings (Administrators) > Line Payment statuses (this is a “No payment” type Line status that must be = 0.00):
No Automatic fees will apply to this Line status – contrary to the usual “Unpaid Pmt” Line status if such a fees rule had been activated.
NOTE: If you are monitoring the Outstanding amounts, you should change the “Expect. Pmt” column amounts to 0.00 for each payment, otherwise these amounts will become outstanding. A slight pain to do one by one if you have hundreds or thousands of loans! As of version 5.2 you can specify that the “Expected Pmt” for specific Line statuses must = 0.00. This will save a huge amount of time since the “Expect. Pmt” will automatically become 0.00 (go to Tools > Settings > Line payment statuses > check the “Expected Pmt = 0” column for the “Deferred Pmt (0.00)” (or named as you wish) Line status).
If you do not do this in version 5.2, then double click in each cell and delete:
Now press on “Apply”. The window will become blank after the payment processing stating the number of Records updated. They are now managed and payments 0.00. In our example, notice now an ending balance because of the three deferred payments:
4. Add new (deferred) payments at end of loan
Adding the deferred payments at the end of the loan is the more arduous step because of the date issues.
To import the payments back into Margill, we will be using the “Post Payment” tool once again but with the “Bulk Payment Import” option that allows us to import new payments via an Excel sheet.
All we need to import the deferred payments back to each loan individually are 3 columns (and other optional ones):
- Column A – the Record identifier
- Column B – the date of the deferred payment
- Column C – the amount of the deferred payment
We will now modify our Excel sheet. We can delete most columns and keep the three important ones even if the Payment dates are the payment dates of the original payments to be deferred. Notice a column to the far right called “Last Payment > 0,00” – keep this column in order to know the very last line of the schedule (with a payment greater than 0.00) as a reference to add the next payment date (I moved and changed to red since will be temporary):
In our sample loan 10035, the last payment would have been on 05-07-2021 so I added my first deferred payment one month after and added another month for each of the next two payments. Someone who’s good in Excel can probably create some way to get the dates in there quicker by using the EDATE function (see how to months below) or, for weekly payments by adding 7, 14, 21 days to the Last payment date.
To see the payment frequency (although one can tell by the original payment date sequence) I showed the Period of payments in my Main Margill window.
==================================================================
Using the EDATE function in Excel
If you have a relatively large number of payments to add at the end of each of the loans,
it is really worth using the EDATE function which allows you to quickly add
one or more months at the end of the payment schedule
Loan 101 below ends on 12-22-2021. We must therefore add a month after this date, hence the formula:
=EDATE(B2; 1) where B2 is the cell and 1 is + 1 month
Then we must add 2 months for the second to last payment
=EDATE(B3; 2) 2 being + 2 months
Finally add 3 months for the new last payment
=EDATE(B4; 3) 3 being + 3 months
Column F is not relevant – only used to show the number of months that need to be added
The formulas can then be copied for each of the “trios”. BE CAREFUL to always have
1, 2, 3 and not 5, 6, 7, 8, etc. in the subsequent formulas for adding months.
Once the 3 new dates are all calculated, simply copy
the “Formula” column and “Paste Special” > “Values” to column B
==================================================================
My Excel file is now clean and ready for import. Notice I added a Comment in column E and column F should be taken out.
I also purposely made an error in Record 106 – entered 2020 instead of 2022… There’s also a date error in Record 10028.
Now go to Tools > Post Payment tool > Bulk Payment Import > Import New Payments. Select the Excel file in the “File to import” section. Use this button 
.
You will see a list of all the new deferred payments and dates.
Notice Record 106 shows as an error since these are “Due payments” in the past and before “Paid payments” which is not allowed. Record 10028 date should also be fixed.
Fixes done in Excel sheet. Re-import. Notice the total amount desired (26,149.20) is re-imported. Press on “Insert lines”.
If all good, we get this message:
Let’s look at our sample loan with lines 27-29 now added. Notice a difference of 0.71 (the balance). This is probably due to the interest rate change at the start and end of month as opposed to the exact payment date on the 7th. You could adjust the last payment but no big deal (check then uncheck Balance = 0.00)
So there you have it. You gave your clients a break…. Many may need it because of the pandemic.
Please stay safe!
Marc Gelinas, CEO
NOTES:
As of version 5.2 Column fees (such as monthly fees or taxes) can also be re-imported with the bulk payment import via Excel but as amounts only (not as formulas). These can be entered in columns S, T, U and V.
