Webinar – What’s new in version 5.4
See the cool new features in version 5.4 released from January 22 to September 15, 2022.
For more details on the new additions, please visit the Release Notes page.
Automated invoice numbering
Q: I created an invoice in Margill (via Document Merge) and would like to know if it can number the invoices automatically.
A: A few options are available in Margill
First method:
By using the Global Changes function import your invoice numbers directly into your Records.
Let’s start by creating a Custom field to import your invoice numbers.
Go to Tools > Settings > Custom Fields > Record > Unlimited Fields (Table format)
Please note that if you wish to keep historical invoice numbers, you will have to create a new field for each invoicing cycle (ex. Invoice number 2023) (this is less practical if the invoices are sent every month).
Once this field has been created, you can import your invoice numbers. To do this, you will need a list of your Record unique identifiers. You can create this list in Reports > Record List and export in Excel format.
Once in Excel, adding your invoice numbers to the document will be easy.
Now let’s update your invoice numbers with the Global Changes tool. To do this, right-click in the Main Margill window and select the Global Changes option.
When the window opens, click on the Excel icon at the top right of the window.
In the new window, simply select the Invoice number field and the file to import.
Now, you can add this field to your invoices and account statements.
Before the next billing cycle, you must import the new invoice numbers in a new field (with year and/or month) or in the existing field (the invoice number will thus be updated at every new billing cycle).
Second method:
You will find that this method is faster and requires fewer manipulations. On the other hand, you will not have the same flexibility as the previous method.
This method consists of creating a unique invoice number using the MLM ID and, for example, the number of documents attached to the Record. Several other options would be possible such as adding a date or other.
To do this, add the following fields to your invoice:
1- MLM Record Identification
2- Total number of files attached to the Record
When you produce your invoices, it will be important to check “Attach each file produced to the Record”, so that the numbering continues correctly.
It is up to you to see which technique is preferred to obtain the desired results. Note that it is possible to combine the two techniques. Do not hesitate to contact our team if you have any questions regarding the Document Merge function at [email protected].
On a $1000 loan at 20% interest, why is my interest not $200 for one year?
Q: On a $1000 loan at 20% interest, why is my interest not $200 for one year?
A: This is a common question that we often get and some information is missing to answer the question so we’ll analyse this, taking into account various scenarios and how to manage this in Margill Loan Manager.
There is a misunderstanding as to the concept of “amortization”.
Here is how we get to $200 in interest on a loan. It must have ONE (1) lump sum payment at the end (one year later) of 1200 to get a balance of 0.00. So there is no amortization in this loan:
Compute to get the Results table:
Let’s look at this with bi-weekly $0.00 payments just to see the interest accrued (so 26 payments and the last payment on Jan. 1, 2023 to give exactly one year). This is Compound interest (not Simple interest), so the interest keeps on increasing:
So you get exactly 200 (+ or – a few cents due to rounding) as the interest amount.
However, when you add true payments that pay interest and principal (every 2 weeks, so 26 for a full year approximately), you are not lending 1000 for 1 year since principal gets paid back every 2 weeks, thus reducing the interest accrued.
“Compute” to get a real amortization schedule at 20% annual (APR). Notice my balance goes down so the fortnightly interest (every 2 weeks) goes down and so does the interest per period. So for an amortized loan, the interest is very far from 200 total, only about half (96.96) because of the amortization effect.
There are two ways to get the desired $200 in accrued interest for 1 year when there are true principal and interest (P&I) payments:
Method 1): Calculate the REAL interest rate
- Desired Interest per payment: 200 / 26 = 7.69
- Principal per payment: 1000 / 26 = 38.46
- So 26 payments of 46.15 each (26 x 46.15 = 1199.90)
Leave the Annual Nominal Rate blank and enter the Payment of 46.15. Margill will compute the rate.
“Compute” and notice the real interest rate (APR) is now 43.97% (APR). We are at 199.90 in interest (almost 200).
2) Use Fees, not true interest
Other option is to use Column fees (that are not computed on a daily basis but entered once and no matter what, you will have 200 in “finance costs”, not real interest). Click on Add Fees (I called them Admin fees – you can rename them to anything you want) and add 7.69 (200 / 26) in “interest” (Admin Fees here) per payment.
Here are the results. I added a few cents in Admin Fees at the end and increased my payment to get exactly 200 as my finance cost. Notice my interest rate is 0% since I am now using Column fees, not real interest.
I also invite you to consult our White Paper on interest. It explains basics and more advanced issues with interest: https://www.margill.
Margill Loan Manager: Amount Due at current date or any date to “get back on track”
A most appreciated feature in Margill Loan Manager (MLM) is its quick access to four variables, accessible in the reports or in the Main window, that allow the user to instantly see the amount that must be paid by the Borrower to “get back on track” if one or several payments are missed, partial or late.
Variables:
- Amount due at Current Date (For final balance = 0.00)
- Amount due at Current Date (For final balance = original balance)
- Amount due report End Date (For final balance = 0.00)
- Amount due report End Date (For final balance = original balance)
Example:
- Loan amount: 25,000
- Principal and interest payments for 18 months
- Regular payment should be 1487.08 with a last payment of a few cents less.
Below is the payment schedule based on contract that would yield a balance of 0.00 if full payments were made on time:
Let’s suppose payment 4 is missed and payment 5 is partial, leading a hypothetical final balance of 2731.16 (in principal, interest and maybe fees had these been added):
Borrower calls you up today January 10, 2022 to know how much he must pay to be back on track. The amount can be seen in the Main window with the appropriate variable. In this case “Amount due at Current Date (For final balance = 0.00)”. So the Borrower would have to pay 2490.25 (today) so that the final balance of 2731.16 (in the year 2023) becomes 0.00. The difference is due to interest accrued on a higher amount if the outstanding amount is paid in the future as opposed to today.
If there had been a residual value, the proper variable would have been “Amount due at Current Date (For final balance = original balance)”
If Borrower wished to know the amount due at another date than today, then a report (Record List) would have been produced to get the data with one of the two variables “Amount due report End Date”.
Or you could have gone in the loan itself, inserted a line on the date, right click > Payments > Payments Adjusted for Balance = 0.00 (or Balance = X).
Activate this option in Tools > Settings > System Setting (Admin…)
For “up to current date” calculations, it is strongly advised to use the Automatic / Overnight tasks which compute totals during the night as opposed to when launching Margill in the morning.
Webinar – What’s New in Version 5.3
In this webinar, we are looking into what’s new in version 5.3 including increased automation and customization.
This is followed by a sneak peek at upcoming version 5.4 (available in November 2021), again with new automation aspects as well and many features you asked for.
Finally, we go over some aspects of the software that you may not know exist and that could help you in your day-to-day with Margill. Don’t miss this!
How can I mass import “Unpaid” payments with an Excel sheet in Margill Loan Manager? I need to obtain the Outstanding payment amounts.
Question: How can I mass import “Unpaid” payments with an Excel sheet in Margill Loan Manager? I need to obtain the Outstanding payment amount too.
Answer: Usually, when payments are NOT made (so were skipped or the payments returned for non sufficient funds (NSF), on a historical basis, these would simply be ignored and only the Paid payments entered (even partial and late payments)
However, in order to count the number of Unpaid payments and to obtain the Outstanding amounts, it may be a good idea to enter payments lines of 0.00 and include the payment that SHOULD have been paid, thus allowing Margill to calculate the Outstanding payment amounts.
One would go through the “Post payment” tool under “Tools”. On the far right is the “Bulk Payment Import” button. You need “Import new payments”.
This mass (or bulk) import tool allows you to import payments (Paid pmt, partial pmt, late pmt, etc.) (as well as additional principal – a negative amount – and column fees and other information in the Results or payment table) but does not allow the import of Unpaid payments of 0.00. So we must be a little creative…
The tool does allow the import of what are called “Other” Line statuses. “Other” Line statuses never pay interest or principal – they are made to manage special scenarios and allow you to add more data in bulk such as Column Fees or other information in columns to the right. If the Outstanding amount was not important you could rename, for example, “Other 3” to “Unpaid” and mass import these. However, when “Other” is added, since this is not a real “payment”, no matter how it is renamed, an amount in the “Expected Pmt” column will not affect the Outstanding as an Unpaid Pmt does (see example below where Other 3 does not increase the Outstanding to 1000):
In the question at hand, the Outstanding amount is required, so we cannot use an “Other” Line status with a payment of 0.00.
What can be done however, and this will be our solution, is to use a “Paid Pmt (x)” Line status, rename it to “Unpaid…” (renamed to “Unpaid Special” below) and mass import this Line status with a payment of 0.00 and an “Expected Pmt” for the amount that was supposed to be paid.
Margill allows “Paid” type Line statuses with a payment of 0.00. A little odd I agree, but this allows for greater flexibility. Even with the name “Unpaid”, the payment must not necessarily be 0.00 as in a real “Unpaid” Line status (line 6 below “Unpaid Visa” where must =0)
Once this Line status is created, in Bulk Payment Import > Import new payments, find the appropriate number for “Unpaid Special” (6 in this case – this is not the Line status order as in Line status Settings that vary depending on the order you desire). The Excel sheet must contain data and a header in columns A, B, C, D and L.
Here is the Excel sheet with only 2 loans. Notice I also added fees (column T for my Admin Fees)
Bulk import window:
Final result in Record 10003 after pressing on “Insert lines” with an Outstanding of 1300:
You can even get the number of each and every Line status through “Personalized Reports” > “Record List” (“Tally” theme):
What’s criminal? Lenders beware – amendments to Criminal Code under Bill C-274
An Act to amend the Criminal Code Bill-C-274 (the Bill) entered first reading in the House of Commons on May 11, 2021. The Bill would amend1 the definitions of “criminal rate” and “interest” in subsection 347(2) of the Criminal Code, while also repealing section 347.1, which had allowed certain exceptions for payday loans.
The Bill will be of interest to lenders, especially payday lenders and other non-traditional lenders, as the amendments proposed would lower the criminal rate at which interest charged or received is under the Criminal Code from 60% to 30%.
To continue to read the text by Me Joyce M. Bernasek and Me Ramz Aziz from the law firm Osler, follow this link.
In the Loan Manager, is it possible to change a payment date for all loans at the same time?
Q: In the Loan Manager, is it possible to change a payment date for all loans at the same time? For example, I want to change the date from March 26 to March 27?
A: This can be done in batch but each date will have to be modified. You can do this for “Due Pmt” and “Paid Pmt” lines only.
- Go to Tools > Post Payments
- Check “Use Date interval”
- Enter dates between March 26, 2021 and March 26, 2021 (or other dates)
In theory, you would change only the Due Pmt lines so therefore you don’t have to check “Include all Payment Line statuses”. In the following example, I checked the option but this is usually not necessary…
Afterwards, you need to copy and paste the March 27 date (the new date) and modify line by line (faster with Ctrl C and Ctrl V (copy/paste) compared to manually entering teh date):
You will then be able to modify the dates and the lines will become light green. The chronological order of the lines must be followed:
Once the changes are done, click “Apply” and dates will be modified.
Webinar – What’s new in Margill Loan Manager 5.2
Last February 11, 2021, the Margill Team held a webinar to introduce the new features of the Margill Loan Manager software, version 5.2.
For those who have missed it or if you wish to see it again, here it is:
I would like to convert 7500 of accrued interest to principal. Can this be done in Margill Loan Manager?
Question: I would like to convert 7500 of accrued interest to principal. Can this be done in Margill Loan Manager?
Answer: Certainly with special Line statuses.
First, go to Tools > Settings:
Make sure “Interest paid” is available (not checked to Hide from menu) as well as an “Add. Principal X” Line status.
We will rename Add. Princ. (3) to “Capitalized interest” (or another name that fits your needs). We cannot however rename “Interest Paid” so you must be careful when using this. If it is already used to pay, on a cash basis, pure interest in other loans (as opposed to using it as we will do now), then you will have to note this in your reports not to mix up cash and non-cash items.
Normal scenario where Interest remains interest (in Simple interest no interest is generated on interest – Day count is 30/360 for equal interest every month):
We will “pay” 7500 in interest and add 7500 in this new “principal” (non cash). Insert 2 lines (right mouse click)
Since interest is now capitalized (so really brought to Principal), the new monthly interest amount increases. You could have said no interest on the 7500 but this becomes a little strange (right mouse click on the line).
When reporting you will need to isolate these special transactions as not to mix them up as cash transactions.
Personally, I would not have converted interest to principal since I believe from an accounting perspective interest must remain interest, not be converted to principal, but you are doing this for a good reason…
I would have done it this way by telling the system to capitalize the 7500 (thus there would be interest on this amount- goes to Computational Balance):
Comes up to the same mathematical results but interest remains interest:
After more than 2 years of work, the latest version 5.1 of the User Guide is now available. In it you will learn all you need to know about the new features.