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

How can I set up multiple Global Initial Fees in Margill Loan Manager?

/in /by

Question:

How to set Global Initial Fees which are a) a flat $100, b) a Dealer Fee of 7% of the loan amount and c) an Admin one time initial fee of 7% of the loan amount. These fees are financed, not paid up front…

How and where do I set the Initial 3 fees described above and can I have them only apply to “Auto” loans, but not to other types of loans?

Answer:

There are many ways to do this in Margill. I would recommend the APR tab. In this example we are looking at a $2000 loan (so the percentage fees are computed to $140 each):

Notice on the bottom left (circled in red) that you can set this up as your Default for all new loans.

To answer the second part of your question, can some fees apply only to some loans? Yes. You can save what are called “Profiles”. So these fees would not be set up as the Default but as a Profile for Auto loans.

 

APR fees financed and accounting

Since these three fees are financed, they become “principal” like (part of the total amount financed).

So when they are paid, from an accounting perspective, they are paid according to a proportion of 380/2000 (total fees divided by principal):  380/2000 = 0.19

So for a $100 payment, interest is first paid, followed by principal in the following proportion: 50.70 x 0.19 = 9.63

The $9.63 in paid APR fees can even be subdivided by the 3 fee types you entered. This is done in the reports.

 

Fees could also have been added as Line Fees and paid when you want then to be paid. You charge fees (“Setup Fees”: -$100 in the Payment column) and you tell the system when you want them to be paid (“Setup Fees Paid”: +$100 or other amount in the Payment column). Each of the fees below can be renamed.

 

They can also be added as Column Fees that are paid within the normal payment. I personally would keep my 3 column fees for regular monthly fees or for automatic NSF fees…

Conclusion:

Where to add these fees? I personally would add them under the APR tab. You must provide the APR to your borrower anyways…

From an accounting perspective though, although a little less important, you should ask your accountant.

How do we update the interest rates for multiple records at once?

/in /by

Question: 

The Prime rate changed. How do we update the interest rates for multiple records at once? Read more

Real-estate

/in /by
Read more

Finance

/in /by
Read more