Margill Loan Manager – Explanatory document for Transition from “Perceptech” to “VoPay”

/in , /by

September 4, 2024

The transition from Perceptech to VoPay is done in a few minutes in Margill. Simply follow the instructions below and follow the short “Transition Wizard” in Margill.

1) Before the transition, it is important to know that the following fields are mandatory for the Borrower:

  • Name, First name (or Company), Address, City, Province, Country, Postal Code

The formats below are also mandatory, but some exceptions have been programmed in Margill, see paragraph ** below.

  • Province: Must be a valid two-letter provincial or territorial code (alpha code)
    • Provinces and territories : AB, BC, MB, NB, NL, NT, NS, NU, ON, PE, QC, SK, YT
  • Country: Must be a valid two-letter country code as defined by ISO 3166-1 alpha-2 – Canada = CA
  • City: These special characters are not allowed : Comma, underline and parentheses
  • Postal Code: Must be in standard A9A 9A9 or A9A9A9 format

** For Province and Country, MLM performs the necessary transformations before transmitting payments to VoPay when Provinces and Countries are entered in various ways. For example, if in the Borrower’s file it is written Ontario, Ont, Ont., etc., the Province will automatically be converted to ON in the VoPay file. Therefore, you would not have to modify your data in the database, however it is strongly recommended that you do.

To avoid last minute problems, it is STRONGLY suggested to check all this data relating to the Borrower BEFORE making the transition.  You should produce a report with these fields in all Records, check them and you then can re-import them with the corrections via an Excel sheet and Global Changes.  See our article to carry out this operation: https://www.margill.com/en/margill-loan-manager-update-of-borrowers-data-with-the-global-changes-and-an-excel-sheet/. Note that accents are allowed.

2) Download and install the latest version of Margill Loan Manager (version 5.6 from September 2024 – many minor but important corrections have been made in previous version 5.6) on  www.margill.com/get

  • If you use the SAAS version (cloud hosted), the update will be carried out for you starting in July. Please contact us ([email protected]) to advise us of your desired transition date to VoPay.

3) A Margill Admin must go to Tools > Settings > Modules > Electronic Funds Transfer.

The current provider is “Perceptech (Canada)”.

In the scroll menu, change the provider to “VoPay International Inc.”

You will get this message:

Follow the instructions. Enter the date of the last collection with Perceptech when prompted in Margill and send this date to Perceptech at [email protected]. The message is simply:

  • Our company XYZ will make its last debit with Perceptech on September XX, 2024.

If you deposit to many bank accounts, please advise them.

NOTE: VoPay also offers a service to actually credit your Borrower bank accounts. You can activate this service by checking the box:

3) You will then need, for each of the Creditors with EFT accounts, to update the data in the EFT tab. This data was provided to you by VoPay.

4) You can submit you EFTs exactly the same way you used to do with Perceptech.

Note that data pre-validation is carried out by VoPay allowing you to correct certain errors before the collection date. See the Margill Loan Manager User Manual on page 231 (https://www.margill.com/margill-loan-manager/user/MLM-User-Guide.pdf) and see page 254 for explanations of pre-validation.

Happy transition!

Margill Team

[email protected] / [email protected] / [email protected]

Margill Loan Manager – How to create a report which identifies all of the unpaid payments

/in , , , /by

Q: How can we create a report which identifies all of the unpaid payments?

A: I would create and run a Transaction report.

1. Create a New report template. On the top right, pick up at least the Unpaid Pmt Line status (there could be other Unpaid Pmt Line statuses).

2. On the left from the “Transaction Data” theme, pick up at least Line Status, Pmt Date and Expected Pmt. I also included the Outstanding amount balance that will appear on each transaction.

3. Then add the loan identifiers such as loan ID, Borrower data, etc. (I moved them up for the report with the arrows on the right). You could also add an email, phone number, etc. so collectors can easily reach these clients. You could also email these clients directly in Margill (or automate the process, but I will not cover this here).

I called my report “Unpaid Pmts”. Run it for the desired loans and period. I ran mine for January 2024:

I have 4 unpaid payments. Notice the Outstanding amount on loans 10104 and 10392. This shows that these are bad payers since the Outstanding (balance) is higher than the current unpaid payment. You can show totals and export to Excel or other.

 

Margill Loan Manager – How to eliminate an Outstanding amount when the final loan balance is 0.00

/in , , /by

Question: I’m trying to apply a payment in order to elimintat the outstanding balance in a loan. Can you explain how to do it?

Answer: The outstanding amount is somewhat of a theoretical amount in more complex or irregular loans. The amount is based on what is actually paid (“Payment” column in the Payment schedule) versus what was to be paid (“Expected Pmt” column in the Payment schedule).

If the ending balance (at the end of the loan) in the Payment schedule is 0.00, then the outstanding amount is no longer really relevant. If you absolutely want to eliminate this amount, change, for one of more payments, the “Expected Pmt” amount to 0.00.

In the following example, a final payment of 18,450.37 was made. Depending on the order of operations to enter this amount, it is possible that the amount in the Expected Pmt column was modified to 18,450.37 when the expected payment was actually 8793.68.

So simply change the amount of the Expected Pmt to 8793.68 or to 0.00 and the outstanding amount will be eliminated:

The Expected Pmt amount may need to be modified for one or more previous lines if the outstanding amount is too high and the Expected Pmt amounts have been mismanaged in the past.

In order to change an Expected Pmt amount, you must have these rights:

Also note that in System settings > Line Payment Statuses, the Expected Pmt can be set to always be 0.00. This is the case for compensatory payments (which compensate for unpaid or partial payments) or for additional payments not included in the contract:

Margill Loan Manager – Update of Borrower’s data with Global Changes and an Excel sheet

/in /by

Margill Loan Manager – Update of Borrower’s data with Global Changes and an Excel sheet

Question: I must make sure that all my loans include a name, address, city, province (or state), postal code (or Zip) and country for the Borrowers in a specific format.  How can I do that in Margill?

Answer: This operation can be done quickly with Global Changes via an Excel sheet.

 Steps:

  • Create a personalized template in Reports > Record List with all the data that needs to be verified and updated, making sure to include the Unique ID for the Borrower in the first column:

2) Create the report for all Records (you can exclude the archived or closed loans – Use Advanced Queries to include the desired loans in order to reduce the size of the report if you have thousands of loans.). Produce the report.

Export data to Excel:

3) Clean up the Excel sheet.

  • Lines 5 and 6 do not have Borrowers. Either add a Borrower or eliminate the lines.
  • Duplicated lines (we can easily see them in column A) can all be eliminated – these people have many loans – we only want Borrowers, not loans.
  • Here is the result after the clean-up including the State and Country that now have 2 standard letters:

In my situation, I only need to update the State/Province, the Country and the Postal Code. I therefore eliminate everything that should not be updated in my Excel sheet while absolutely keeping the Borrower Unique Identifier which is the “key”, allowing me to link my Excel sheet to the correct Borrower.

You would have tens, hundreds or thousands of Borrowers for real:

4) Update the columns one by one via Global Changes for Borrowers:

In the Main Margill window, go to Borrower under File > right click on the mouse > Global Changes:

Click on the Excel icon. The ? gives you additional instructions about the simple Excel sheet required.

Column A is the Borrower Unique Identifier (not the loan) and Column B is the data to be imported or updated. In the first import, we want to change the State/Province and therefore in the right menu, choose “Borrower State, Province”. Then choose the Excel file by clicking on the orange file icon.

Note that for Borrower 10001, no update is required since the data is unchanged (the “Submit” column is therefore not checked).

Click on “Save” and the data will be updated.

Afterwards, we want to update the Country.  In Excel, copy the Country data into column B, save the sheet, then import (I simply deleted the State/Province column but it is wise not to destroy the columns or make a copy of the Excel sheet before deleting data). Do the same for the Postal Code/ZIP and other data as needed, one by one.

Note that the “Automated Imports” (API) would allow you to update all this data in one operation. There is also a Salesorce API available (not covered here):

Is it possible to issue a refund to a customer who overpaid, directly from Margill?

/in , , , /by

Q: Is it possible to issue a refund to a customer who overpaid, directly from Margill?

A: Do you mean issuing a credit to the customer directly in Margill like when you do a pre-authorized debit with Perceptech / Acceo / Transphere (in Canada)?

The answer is no with Perceptech / Transphere but with our other electronic payment partner, VoPay, yes it is possible by eTransfer (Interac credit). Credits to borrowers can be up to $25,000.

For the Payment schedule, if the borrower has overpaid, then you can create an Additional principal type Line status – which you would rename to Refund (will only refund principal, but you can also refund interest ) and the amount would be negative to increase the principal (and interest if needed) and the balance.

Don’t forget to add these to your reports and mathematical equations (for reporting) as needed as these become new transactions types.

Automated invoice numbering

/in , /by

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].

How can I mass import “Unpaid” payments with an Excel sheet in Margill Loan Manager? I need to obtain the Outstanding payment amounts.

/in , , , , /by

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):

In the Loan Manager, is it possible to change a payment date for all loans at the same time?

/in , , , /by

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.

Easily create and manage Covid 19 (Coronavirus) Emergency Business Loans with Margill Loan Manager Software

/in , , , , /by

Federal, state and provincial governments, townships, cities and towns all over the world have created very generous loan programs to help businesses as they struggle with the global pandemic and the effect of confinement.

These loans can take many shapes and finding the right software to properly create and manage these is not always easy. Excel, for all the respect I have for this great software, can do part of the job but struggles with many interest calculation items and exceptions that are the normal for these loans.

Here are various scenarios these loans can take and how Margill Loan Manager can be used to create payment plans adapted to the loan programs or to the borrower’s needs. Then Margill can easily manage or service the actual payments as they are paid… or not not paid…

Typical Covid 19 Emergency Business Loan scenarios:

  • Interest throughout, deferred payments
  • No interest for a number of months, no payments for a number of months, deferred payments
  • Interest-only for a number of months followed by principal and interest payments
  • Above options + seasonal industry cash flow (tourism, agriculture, etc.)

Interest throughout, deferred payments

  • Loan amount: 25,000
  • Interest rate: 3%
  • Loan starts May 15, 2020
  • Deferred payments for 6 months
  • 36 months to pay back principal and interest

Result – notice first payment is December 1, so no payments from June 1 to November 1 inclusively:

+++++++++++

We could have done this slightly different to see the first 6 months with no payments but this is not required since Margill extracts the accrued interest and balance at any date…

I would have entered 42 payments (36 + 6) and changed the first payments to 0.00 and recomputed the next 36 payments. A 10 second process.

A Comment can be added in the Comment column or we (you, the Margill Administrator) could have created a special Line status called “Deferred – Covid 19”, for posterity… Hmmm…


No interest for a number of months, no payments for a number of months, deferred payments

  • Loan amount: 25,000
  • No interest first 3 months
  • Interest rate thereafter: 3%
  • Deferred payments for 6 months
  • 36 months to pay back principal and interest

We can take the results from the example above. Right mouse click to change the interest rate for the first 3 months to 0.00%:

Select the 36 payments (as of line 7), right click and recompute the payments to give  a 0.00 ending balance:

Final result (top half of 36 payment schedule only):

Notice the borrower saves about 5,00 per payment because of the 3 months with no interest.


Interest-only for a number of months followed by principal and interest payments

  • Loan amount: 25,000
  • Interest rate: 3%
  • Deferred principal payments for 6 months
  • 36 months to pay back principal and interest

Entered 42 payments since 6 months are interest-only and 36 months P&I:

Select Lines 7 to 36 and “Payments Adjusted for Balance = X” where X will be 0.00

Final result (top of 42 payment schedule only):


Catering to seasonal industries with irregular cash flows

High cash flow months (next year we hope!) are June, July, August and September so borrower will pay 1250 per month:

Remaining payments adjusted for Balance = 0

Final payment schedule:

A host of other possibilities and mixes are available including fixed principal payments, interest-only payments in between lump sum payments, extra lump sum payments over time, early payoff, etc.

Adapt the payment schedule to the true needs of our struggling entrepreneurs!

12,000 Reasons why to use Loan Servicing Software as opposed to Spreadsheets!

/in , , /by

I love spreadsheets, they are absolutely amazing for doing so many things. They are hugely flexible, powerful and allow you do to just about anything (almost). However, as we all know, they are also very dangerous because of human error!

I recently implemented Margill Loan Manager with a new client who had been using spreadsheets (Excel) to manage its loans. As loans were imported we noticed that the interest calculated in Margill Loan Manager was $6000 higher for a quarter (3 months) that in was in Excel.

When we looked at the formulas used to calculate interest, we immediately found the error. Notice the top calculation 245,000 x 14.5% is interest for 1 year, then divided by 12 for 1 month and multiplied by 3 for three months (very basic calculation method):

In the second calculation in the spreadsheet, notice that instead of a multiplication sign in the formula (12*3), a comma was inserted by error (12,3), thus the almost $6000 difference in total interest for those three months!

The same error was replicated a couple of times in the same loan and in other loans since this was used as a template.

This error could have cost the lender tens of thousands of dollars before being spotted.

Had the error been done the other way around, so to the advantage of the lender, even more serious legal repercussions could have resulted had this been a consumer loan.

So, if you use spreadsheets, do be double cautious. even better, invest in a proper loan servicing solution. Simply finding this error paid many times for the user’s Margill Loan Manager.

See also: