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

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

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.

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

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!

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:

How to Defer Payments and Interest to give your Borrowers some Breathing Room Because of the COVID 19 Pandemic

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:

  1. Change interest rate to 0.00% (or other rate)
  2.  In the “Post Payment” tool, export the payments to be “cancelled” (deferred)
  3. Post payments to 0.00 (or other amount) in the “Post Payment” tool
  4. 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.

Is there an Alert that we can add that will pop up when we attempt to add more principal than what we have set as the maximum credit?

Q:  Is there an Alert that we can add that will pop up when we attempt to add more principal than what we have set as the maximum credit?

A: Yes, this is called a Conditional Alert.

In the Main window go to Tools > Settings > Set Alerts > Conditional:

When the window opens, click on “New” and the window below will appear allowing you to name the Alert and its condition.

Your condition is quite simple: warn the user when Initial Principal + any additional Principal (as a Line status) added is greater that the amount entered in the Maximum Credit field. Go to the themes on the left to get the proper fields.

You then enter the message that should be displayed to the user when this condition is met.

Save the Alert and you will get back to the list of Conditional Alerts page. Highlight the newly created Alert (the one in blue) and press on both buttons: Apply to existing Active Records and the Apply to Records as they become Active.

You could also use Custom fields for extra criteria and even Equations to, for example, “add these 4 fields” that must be less than this other field.


Now, in this example, we have Credit limit of 2 million and the user tried adding another million to the existing 1.5 million and got this warning when saving…

Another maximum credit tool allows you to set a maximum by Borrower – this is useful if a Borrower has multiple loans in the portfolio:

 

Mass data entry / Global database changes / Adding new data in the database in bulk / Mass database changes in Margill Loan Manager

Q:  We have added some custom fields for additional loan information.  Is there a way we can mass import only those specific custom fields in Loan Manager?

A: Yes you can mass import data into Margill Loan Manager. This is with what we call “Global changes”.

This can be done for the loan, mortgage, line of credit, lease, etc. (the Record) or for the Borrower.

For adding new information or changing data in many Records at once, sort these in the Main window, choose the desired Records, highlight these and right click with the mouse. Choose Global changes:

This window will appear showing the various fields that can be changed.

There are over 30 fields that can be changed plus all Custom fields.

Select the field you wish to add data to (or change data) and press on Refresh. You can only add data to one field at a time.

Your can then highlight the Records and with the right mouse click add/change the data in bulk. Case being, you will see existing data and can replace these or not. Use the Ctrl or Shift key and mouse to pick and choose the desired lines.

Below is the option when a scroll menu exists for the Custom field. If the field was a Text field for example, you would simply enter any text (no menu).

If the data is never the same, for example, adding the date of birth for Borrowers, you can add the data line by line.

Once the data is entered or changed, press on Save (bottom right). The changes will be made.

Adding data via spreadsheet (Sorry not yet… but coming soon):

  • In version 5.0.x coming up in a few weeks, you will be able to make these Global changes with a spreadsheet (Excel). All you will need are two columns (a loan Identifier – our “MLM Record ID” or one of the two “Unique Identifiers”. “File”, “File Number” and “Accounting ID” are not allowed since these may not be unique identifiers). It is strongly recommended to start using the Unique Identifiers offering much more versatility.

This is not the same as adding a new loan or Borrower in the database – this can be done through Tools, Settings, Special and:

See http://www.margill.com/en/mass-importing-existing-loans-and-borrowers-in-margill-loan-manager/


You can also use the Global changes for these practical changes:

  • Change Active Records to Closed after your fiscal year end
  • Activate Automatic fees
  • Enable or disable the sending of email reminders to your Borrowers
  • Activate the Electronic Funds Transfer for a bunch of Records at once
  • Add banking data to your Borrowers
  • Make corrections in bulk
  • Add Metro 2 credit reporting compulsory data to the loans and Borrowers
  • Update and change most Borrower data and their Custom fields

 

Mass importing existing loans and borrowers in Margill Loan Manager

Q: I wish to change from my current loan servicing platform to Margill Loan Manager. Can I import my existing loans or will I have to enter these one by one?

A: Mass import can be done easily with simple spreadsheets (Excel).

You can import:

  • Borrower data
  • Creditor data
  • Employer data
  • Basic loan information (loan type, loan amount, interest rates, dates, amortization, method, custom fields, etc.)
  • Individual historical payments (paid, partial and late payments, additional advances, etc.)

Go to Tools > Settings > Special section >

Your Excel sheet must list all the data column by column. This is a sample spreadsheet for importing loan information.

Select the spreadsheet and then map the spreadsheet columns to the proper Margill fields:

You could have one single spreadsheet with Loan and Borrower information and map some columns but not others depending on where the data fits (Loan or Borrower).

You can save this format to use over and over to add more loans and Borrowers in bulk.

Please contact Margill Support to obtain a sample sheet with more import information…


You can also import individual transactions with an Excel sheet:

 

The Transaction type columns uses a number to identify the transaction type: payment, advance, etc. Comments and a host of other data can also be added such as Check number…


Importing loans and Borrowers takes no time at all. The challenge lies in getting the proper information from your existing system into the Excel sheet. The Margill team is there to help in this transition.

See also how to add data in bulk once the loan or Borrower is entered in the database: https://www.margill.com/en/mass-data-entry-global-database-changes-adding-new-data-in-the-database-in-bulk-mass-database-changes-in-margill-loan-manager/

PS: Good idea switching from your other system to Margill 😉

Setting up automatic emails (reminders) to your borrowers

Margill Automatic Emails…

  • Allow you to have Margill send out emails automatically a few days before a payment is due, to remind your borrower to pay.
  • You can send reminders to all your borrowers or only the ones that don’t pay so well or on time…
  • The system can automatically email your borrower that a payment was returned NSF
  • The system can automatically email your borrower that the last payment of the loan is coming up and that the renewal date is close
  • Or emails can be sent based on any other “Line statuses” that you have set up