Importing batch payments in Margill Loan Manager (CSV, Excel)

/1 Comment/in /by

Question: Our company collects weekly (bi-weekly or monthly – no matter) payments drawn directly from their bank accounts in some cases and in others as payroll deductions. We receive CSV files from the bank and processors. Can these transactions then be imported back to Margill Loan Manager?

Answer: As you guessed, since I blogged this, Margill does this no problem.

Each and every line of a payment schedule includes a unique line identifier. If this line identifier can be sent back to Margill from your processor (or accounting or ERP system), the software will automatically change a “Due payment” to a Paid, Late or Partial payment or even an Unpaid payment if your system can send back the Line ID with a payment of 0.00 (most systems don’t actually, so a human may have to decide when this payment eventually is considered Unpaid).

To send the transactions to the bank or to the payroll deduction processor, use the Transaction report with any data you want. Let’s keep it simple by supplying the Name, Transaction date, Transaction amount and Unique Line ID.

This report can then be sent to the processor in Excel, CSV or Text format.

Please note. The file created by Margill is not in NACHA format. We work with third party processors Intrix (US) and Perceptech (Acceo)(Canada) for electronic funds transfers to debit your borrower accounts.

Notice the “Line Unique ID” above – your provider must be able to accept this data and return it with the paid payments in the format below. We highlighted special payments (need not for real…):

  • In C2  we have a Partial payment
  • C4 – Unpaid payment
  • B7 – Late payment
  • B15 – Partial Late
  • C23- Unpaid payment
  • The rest are all normal Paid payments – on time, right amount…

This Excel file is imported to Margill and all payments will be updated in seconds… Errors will be flagged the case being.

See also: How can I enter multiple payments and advances in a loan without having to enter them one by one? I have over 100 in one loan. I have the data in Excel.

Margill Loan Manager: Is there a way for me to see if users are logged on to the system?

/in /by

Question: Is there a way for me to see if users are logged on to the system?

Answer:

Go to Tools > Users and see this:

Can Loan Manager be used to collect invoices with payments here an there? Each invoice only starts accruing interest after 30 days.

/in /by

Q: Can Loan Manager be used to collect invoices with payments here an there? Each invoice only starts accruing interest after 30 days.

We are a medium-sized law firm and send out invoices to our clients irregularly. Many of our clients do not pay within the  30 days allotted (invoices are Net 30 days otherwise the monthly interest is 1% per month (12% annually)). We charge simple interest, not compound.

Example:

  • First invoice: Dec. 1 2016 for $2950.00
  • Second invoice: Jan 4, 2017 for $4381.25
  • Third invoice: Nov. 6, 2017 for $5000.00
  • Payment was made Jan 26, 2017 for $1000.00
  • We are now November 7, 2017 and the assistant must easily see how much is due today or any day.

A. This is done easily but a little thinking must go into it. Below will show you what has to be done…

In Margill Loan Manager, a Record must be created for each of your clients. Each Record will contain many invoice and (we hope) many payments. This is very similar to a line of credit with one exception, that interest starts accruing only 30 days after the invoice date. You will thus enter the invoice on the day it was sent BUT with a payment date 30 days (or one month) after the invoice date. So for the Dec. 1 invoice, the payment date will be Jan 1.

Click on File > New Record > Data

The Compounding Period is grayed out since in Advanced we changed to Simple interest

With an Irregular payment frequency, this window (optional) allows you to enter the invoices and payments quickly and these need not follow the chronological order in which invoice are sent and payments received. Any transaction entered in the window can be changed afterwards in the actual payment schedule.

Press on Save.

Now press on Compute to produce the payment schedule (called Payment Table in Margill). A comment could have added or can be added now in the Comment column (there is also a Check number column to the right that can be moved).

Fees can even be added in the Column Fees (Called “Admin – Accrued” below) which could be added above and beyond the interest on the invoices (we will not add any here).

I would first change the “Due Pmt” Line status to “Paid Pmt” since this was already paid. Notice “Add. Princ. (Loan)” – this is where each invoice is sent. These could be changed to a more user friendly name such as “Invoice” (you can rename many Line statuses – take “Add. Princ (2)” and rename as desired)).

I now wish to know how much is owed today Nov 7, 2017. This step is not required since the data is all computed an accessible directly in the Main window but for the sake of learning, let’s do it. We insert a line (right mouse click or  ). Change the date to today’s date. So this client owes the law firm $6937.95 + $5000 = $11937.95 but does not YET owe the $60.59 in accrued interest.

Now let’s get this information on to the Main window for very easy access.

I created an Equation in Margill that takes the balance today and adds the Principal (my invoices) that is added after today (so the 5000 billed yesterday but that will start bearing interest only 30 days from Nov 6).

We now see the “Total due (maybe should have been or “owed” not “due”) TODAY” that is the number we are looking for. The amount will increase tomorrow by $2.09 which is the daily interest on the interest bearing portion (not on the 5000 that only starts bearing interest on Dec. 6).

Here is the calculation for the daily interest on the proper amount:

If you are dealing with a large volume of transactions (invoices and payments) on a daily basis, you can mass import the invoices and payments manually for all your accounts in one window or through a very simple Excel sheet containing the account number, the date and the amount and whether the transaction is an invoice or payment. This is done in Tools > Post Payments > Bulk Payment Import > Import New Payments.

Example below of manual entry for multiple accounts at once.

Contact our Support for more information.

Margill Loan Manager – Features inquiry…

/in /by

Is there a limit on the number of notes that can be tracked at one time?

Not really. The number of loans you can enter in the system is based on your license package. Our largest users have 30,000 loans, our smallest maybe 5? Price is based on number of loans and users.

Can the interest rates adjust as the Prime rate (or other) adjusts and payments recalculate automatically? 

You can update all your loans at once with the Prime rate (or other). You can have interest-only payments recalculated. You cannot have P&I payments recalculated automatically although this feature could be added.

Can the system handle balloon payments?

Yes

Terms of the notes can be different? Interest rates fixed vs variable, length of loan

Yes

Types of reports available?

You create your own with over 1000 fields. Various types are available:

  • Executive summary including graphs
  • Record list (where you chose the fields to report on – name, interest, principal paid, balances, outstanding amounts, etc.)
  • Record list with period breaks (totals by month, quarter or year)
  • Transactions report
  • Accounting report to send transactions directly to your General ledger with Excel, CSV, TXT, QuickBooks, Sage.
  • Merge documents to create invoices, statements, contracts, letters

Reports customizable? 

Yes, see above

Able to customize reports on own or does the software company have to do it?

You customize, see above.

The Margill team can also create very specialized customized reports if needed.

Able to track short term vs long term portion of notes?

Yes

Able to assess late payment fees automatically after a specific grace period?

No and yes. You can configure Automatic fees but a human has to tell the system that the payment is paid on that date (10 days late for example) and human must change the transaction from “Due Payment” to “Paid late > 10 days” so $x fees apply. You create your own fees rules. Not very complicated actually.

Able to upload payments by csv or excel to multiple accounts at one time?

Yes but you need not do this since the schedule already exists. You change a payment from Due to Paid (or Unpaid, Late , Partial….). This can be done in bulk and there is also an option to post these payments via an Excel sheet since every single payment has a unique ID – BUT you have to give this ID to your ERP system or Accounting package in  order to post back to Margill after… To be discussed…

Is there a notes section available for collections, etc?

Yes – even automatic emails if, for example there is an unpaid payment, system sends out an auto email: “Dear John, Your payment of $123.45 bounced on November 3, 2017. Please call us ASAP.”

Auto print invoices? Automated email delivery of bills?

Yes but not Auto – a human has to tell the system to send these every month – but yes sent automatically by email with a subject, text and PDF attachment.

Is the software server-based? Cloud based or both?

Server-based but about 25% of our clients use on THEIR cloud. We do not store your data.

Number of users allowed per license?

No set limit.

Automated payment notification to debtor when payment is received?

Yes by email

Able to create amortization tables within the software?

Of course!

Able to create loan docs in the software? Example: Promissory Note Agreement   If so, able to use our format or required to use software company’s format?

Yes. RTF, DOCX and PDF – this is called Document Merge. You can copy/past your Word document and add images and merge fields.

ACH payment processing available within the software?

Yes

US partner: Intrix (based in Colorado and California)

Canada partner: Perceptech (Acceo) (based in Montreal)

Contract required when purchasing the software?

No except the standard End-User Agreement

Cost of software?  Set up fees?  Monthly or annuals fees?  Per user cost?

Please contact us by email ([email protected])

Here’s a little more information:

Presentation of the software: http://www.margill.com/margill-loan-manager/Margill_Loan_Manager_Overview.pdf

More detail: http://www.margill.com/margill-loan-manager/Margill-loan-servicing-en.htm

Short videos: https://www.margill.com/en/support-center/video-guides

You can also try it for 30 days at http://www.margill.com/ncm

See our client comments: https://www.margill.com/en/client-testimonials/

We can also set up a live demo. Please contact [email protected] or call at 1-877-683-1815 or 450-621-8283.

How to enter loan number, Federal Tax ID, set up automatic late fees, create a line of credit, etc.?

/in , /by

Questions:

In Margill Loan Manager:

1) How/where do I enter my loan #? Can I change the identifier number automatically assigned by Margill?

2) Most of my clients are businesses, and I do not see a place to enter the Federal Tax ID number. I would also like to add fields for type of company, state of formation, year of formation.

3) When posting payments, most are not made on the exact due date, so I need to right click and post the payment into the schedule, is that correct?  Also, where can I enter the type of payment and identifier, like the check number?

4) How to set up a Line of Credit account where all the payments are interest-only except the last one

5) How to automatically set up Late Fees?

Answers:

1) How/where do I enter my loan #? Can I change the identifier number automatically assigned by Margill?

A unique identifier is created automatically for each loan. You an also enter your own under Data > General:

And your loan number can show up on top of the loan window (set this up under Tools > Settings):

 

2) Most of my clients are businesses, and I do not see a place to enter the Federal Tax ID number. I would also like to add fields for type of company, state of formation, year of formation.

You can create as many custom fields as you want. You would create these for the Borrower and others for the Loan – you tie the field to the closest – Borrower or Loan (and even Creditor but you may not need these custom fields).

Tools > Settings:

 

3) When posting payments, most payments are not made on the exact due date, so I need to right click and post the payment into the schedule, is that correct?  Also, where can I enter the type of payment and identifier, like check number?

You do not need to right click. You change you Line status column from a Due Pmt to a Paid Late Pmt. This small window will then appear to enter the true payment date – in this example Borrower was supposed to pay on the 1st but paid on the 22nd. The date can also be changed directly in the Pmt Date column.

The Post payment Tool (no screenshot below) allows you to post payments in batches as opposed to one by one. They can be Paid, Unpaid, Late, Partial and you can add the check number and add fees manually if you wish (or change the automatically added fees). Very flexible…

If you did not wish to charge extra interest if the payment was only a few days late, you could have entered the true payment date in the “True Pmt Date (Grace/EFT)” column without changing the “Pmt Date.” Transaction reports would pick up this special date.

You can also add a Comment to each payment line and multiple other elements that you customize.

4) How to set up a Line of Credit account where all the payments are interest-only except the last one

You would first create a Record type called Line of credit (Tools > Settings).

You then create a Record and in the Data tab enter this information. I advanced 50,000 on 9/5/17.

 

Notice “Irregular” payments (a screen will follow allowing you to enter advances and payments if this was an existing line of credit – ignore this window if this is a new line of credit – click on:

Once the payment schedule is calculated (one line schedule at first), you simply add lines at the end depending on whether they are Advances or Payments. Here there was a second $15,000 advance and a $3,000 payment. I also added an Information line in the year 2020 to keep the interest accruing.

You can add or insert line with the right mouse click or with these icons on the far right:

 

When a payment is made, column fees and then interest are paid first, then principal. In the example above, the $3,000 payment will pay principal. You could have had Margill compute the total interest to be paid (right mouse click).

If monthly payments MUST be made by the Borrower to pay interest, then you would have set up a line of credit with Monthly payments (not Irregular) and would have specified that these pay interest. This is a more advanced feature called “Line Behavior.” Let me know if this is what you want, and I can explain this in more detail.

5) How to automatically set up Late Fees?

You can create one or multiple Automatic fees.

Go to Tools > Settings > Column Fees: Automatic. You then select the Line status and create your own rules. You can charge an amount, a percentage of the balance, a percentage of the unpaid portion, etc.

 

Intercompany Loan Management with a Loan Servicing Software

/in /by

intercompany loans

Intercompany loans

Very often, when a company has many subsidiaries, branches or franchises, the head office will afford loans to these other entities. These subsidiaries in turn can lend to other entities and so forth. These intercompany loans often represent quite a challenge to the accountants and controllers since the loans are not part of the company’s core business and are most often very different from run-of-the-mill personal loans and mortgages.

The challenge of intercompany loan administration stems from multiple factors:

  • These loans are often irregular with sporadic payments that are not due at set dates as in regular loans.
  • They can also be interest-only, fixed-principal, principal-only, and of course, principal and interest (P&I).
  • They often include irregular capital advances, so are somewhat like lines of credit adding yet another level of complexity that most loans servicing platforms cannot handle.
  • Changing or variable interest rates are another challenge faced by the parent company. Often the interest rate will be x basis points above or below central bank rates or LIBOR rates (Overnight, 1 week, 3 months, etc.). Updating loans as the rates change can be a time consuming task where errors are easily made.
  • In some situations, there are multiple entities involved in the same loan: multiple creditors (participating loans) and even multiple borrowers (co-borrowers) for these loans. Each has a stake as a percentage of the total loan amount or dollar amount.
  • We have seen many examples where the borrower pays back more than the principal, so the lender actually becomes the borrower. The lender now owes the borrower. So negative interest is actually calculated. The roles can change regularly as the lender provides new capital advances and as the borrower pays back…
  • Multinational organizations often have loans in multiple currencies.

If the people responsible for setting up loans and payment schedules are not from the banking sector, the calculation method specified in the loan agreement may not have been followed or is simply guessed based on the calculation and loan servicing tools available. This in turn may not correspond to the parties’ true contractual intent.

  • Some loans in a lender’s portfolio may use one method (Simple interest) while other loans use Compound interest. Even in Compound interest, two methods are commonly seen: the Banking or Effective Rate method that uses a special formula with an exponential component; and what we term Simple Interest Capitalized where simple interest is used to calculate interest on a daily basis. And if interest is not paid at the end of the month (for monthly compounding) then the new balance (taking into account the unpaid interest portion) now generates interest. The US government often uses this method.
  • The compounding frequency (annually, semi-annually, monthly, daily, etc.) must also be factored in.
  • Finally, the Day count (so number of days to be used in the calculation – 365, leap year 366 and 360) may vary from loan to loan or may not even have been considered.

Since these lenders and borrowers are not professional lenders, the companies are often ill equipped to deal with these loans. Spreadsheets are the most common solution. Excel does a great job for a few loans but when the volume increases, the spreadsheets become practically unmanageable! Exceptions are simply ignored, interest improperly calculated, etc.

Our Loan Servicing Software, Margill Loan Manager offers a solution for all these problems with the capacity to easily:

  • Create regular payment schedules
  • Create irregular payment schedules
  • Add principal advances (additional principal to a loan)
  • Compute interest-only payments
  • Compute principal-only payments
  • Compute fixed-principal payments
  • Add automatic fees for late or missed payments (although this is less common in intercompany loans)
  • Create schedules using historical variable interest rates
  • Increase or decrease the interest rates for one or multiple loans (in batches) based on the rate type and the new interest rate (LIBOR, Base rates, etc.)
  • Include multiple entities as creditors (holding  company, bank, subsidiary) and multiple entities as borrowers, co-borrowers and guarantors
  • Create Participation loans (percentage ownership for creditors and for co-borrowers)
  • Include not only intercompany and bank loans, but also distinguish these from unrelated third-party loans
  • Allow a loan to eventually yield a negative balance with interest computed on this negative balance. The rate could even be changed or set to 0.00% when the loan becomes negative
  • Create loans in various currencies, and then convert these currencies back to a unique currency based on the desired exchange rate
  • When monthly or even daily transaction volumes become important, a very simple Excel file can be used to enter new payments or advances or to post set due payments directly to Margill
  • Electronic Funds Transfers (EFT) (ACH) directly in Margill (US and Canada)
  • Extract, in seconds, borrowing activity, P&I balances, accrued interest, paid interest, etc., for the whole loan portfolio or a part of it
  • Produce the accounting Debit and Credit report which can then be imported to the company’s General Ledger (GL) in QuickBooks and Sage. The report can also be exported to generic formats: Excel, CSV and TXT

See also our White Paper on Interest calculations: https://www.margill.com/en/interest-calculation-white-paper/

 

Margill Loan Manager

You can also try our software for 30 days for free: https://www.margill.com/en/margill-loan-manager-free-trial/

Or Schedule a demo: https://www.margill.com/en/schedule-a-demo/

Can Margill Loan Manager do progressive Advances to my clients?

/in , /by

Question:

Can Margill Loan Manager do progressive Advances to my clients?

For example, my borrower was authorized for a $100,000 loan but this will be disbursed in stages. So 15,000 one day, 10,000 another and so forth…

Answer:

Short answer… very easily…

You first create a new Record. In this case the first advance of 15k is on 06/06/2017 with regular payments on the first of each month starting July 1. To be repaid over 5 years (60 months).

You can Compute and the following preliminary schedule is created. If we were to leave it at that, we would have 60 payments of $305.59.

For information purposes, let’s enter that the loan is for a maximum of 100,000 (General tab):

Now for the next draws. Do you know when they are to be paid of not? If so, you can enter them on the set Advance dates as Additional Principal (Loan). Notice below there are 2 more Advances, the first for 10k and the second for 25k. We include these as negative amounts to increase the Balance.

I also used the right mouse click to recompute the payments to get 0.00 as my ending balance after 60 months.

So the new payments become 1099.54. You could recompute the payments to give 0.00 at any time or stretch out the loan (add more payment months). As you wish….

If you do not know when the money is to be advanced to your borrower, then you enter the Additional Principal as the information comes in and your recompute your payments (increase them) as more principal is advanced.

Margill Loan Manager – Principal and Interest forecast

/in /by

Question: 

I need to break down the due payment for the next fiscal into Due payment interest portion total and Due payment principal portion.

Answer:

If you are on version 4.3 and above (go to https://www.margill.com/get to download) go to Reports > Personalized Reports > Record List (Customized) with Period Breaks.

1) Report template

Click on New, name your report and select the fields from the left.

In the example below I selected the Borrower Business and Loan ID to identify each loan.

Then I selected, under the “Interest” theme, the “Interest Accrued (for period)”. We call it “Accrued” but in fact, for projections it is TO BE accrued. I will rename my column header to “Interest – Forecast” (see below).

Finally, select, under the “Principal’ theme “Principal Accrued (including any transaction on the report Start Date)(for period)” – renamed to “Principal – Forecast”.

Report template is now complete.

2) Actual report

First select the desired Records from the Main window and go to Reports > Personalized Reports > Record List (Customized) with Period Breaks.

This report will break down the principal and interest by month, quarter or year. So you can do short and long term projections – short term for 12 months broken down by month and short/medium/ long term over 5 years.

Now run the report which may take  few minutes (thousands of calculations are done!). You then get results that can be shown in a variety of ways (horizontal, vertical and summaries). You can even show Totals.

Summary view below:

 

Margill Loan Manager – General Ledger (GL) Accounts

/in /by

Questions:

1. In terms of the accounting report and accounting identifiers, are these captured at the borrower level,  or the record level? How is this selection made and where is it used/displayed on the accounting report?

2. In our accounts we split interest accrued and other fees by product type, i.e. a separate GL account by product, is it possible to do this in the accounting report or would we need select and run it for each product type?

Answers:

1. You can have as many General Ledger (GL) accounts as you want in Margill and report for  each. These can be assigned to the Loan (Record), the Borrower and/or the Creditor.

These can be included as Custom fields by checking the “#GL” column. You can even create a scroll menu if there aren’t hundreds of these.

You can populate these very quickly with the Global changes (select Records in the Main window, right click with the mouse).

The GL accounts created are then used in the Accounting Entries report (only one Record has an account below…).

When you run the report, the GL number will show up here:

This detail can then be exported to Sage, QuickBooks, Excel, CSV, TXT. Or you can get a summary – “By Account” tab.

2. Yes you can have a GL account by product type. If each Record (or loan) is for one product type, then you would create a scroll menu for the GL by product type and assign to each Record individually. You can then run one report with all your records and the proper GL number will be assigned for each debit and credit.

Typical questions before software purchase – Margill Loan Manager

/in /by

Questions:

  • We are a secured lender, our loans are secured against properties. Does the system allow us to input details of the property and loan to value against each loan? How do I do this?
  • Will the system give me an overview of our loan book as a whole?
  • Some clients pay monthly, some clients we deduct the interest payments from the loan on completion. Can I input these options?
  • From an Excel spreadsheet can I input the loan book /data how do I do this /what information is needed?
  • How do I start is there someone I can speak to (I am based in the UK)? Read more