Seamless Legal/Litigation Fees Finance Management and Interest Calculation for Law Firms

Seamless Legal/Litigation Fees Finance Management and Interest Calculation for Law Firms with Margill Loan Manager

Many law firms, particularly, personal injury lawyers (accidents, injuries, claims, liability) will finance their client costs and fees until the settlement occurs, at which time they will deduct these fees, costs and now interest, from the settlement amount. With rising interest rates, it becomes increasingly difficult for law firms to finance their clients without charging interest. Money is longer practically free!

Managing this all the while calculating interest can become quite arduous without the proper system. For all the respect and admiration I have for spreadsheets and Excel, these just aren’t made for this (See Loan Servicing with Excel? Pitfalls and alternatives and 12,000 Reasons why to use Loan Servicing Software as opposed to Spreadsheets!).

Margill Loan Manager (MLM) can, using data from with your other systems, create the actual Case (more or less a loan), import case costs and fees (transactions) in bulk, and calculate the interest throughout the lifecycle of the Case. In some situations, a line of credit is afforded to the client where costs and fees (invoices) are added and payments made occasionally by the client to reduce the balance owed. This latter situation does not factor in success/non-success, so is simply client finance.

For litigation finance based on success, if a case is successful, interest will be charged to the client and fees reimbursed. If the case is not successful, no interest or fees will usually need to be paid back by the client, but the law firm will know its cost of capital, case by case. Most often, the cost of capital to the law firm (so the interest rate its bank charges) will be the same rate as the rate charged to the client, but it could also be higher (margin) to generate a little extra revenue. The interest rate could also be fixed or tied to the bank rate (variable interest rate loans) and could include a spread (so Prime + x%). Simple or compound interest can be used as the calculation method.

Main window where all cases are shown:

Some interesting aspects:
  • A graphic Dashboard can show portfolio trends;
  • Balances (principal and interest) are calculated every single day, case by case;
  • Client names need not appear (they could be identified simply by a Unique Identifier for added confidentiality);
  • Extra data can be added such as Case Type, Court Case Number, Region, etc., for data analysis.
Steps in Margill Loan Manager:
1) Line status setup
We recommend using “Additional Principal” type Lines statuses since we will consider the Fees or Costs to be principal (since this is kind of a loan) as opposed to fees as Margill would see them. So change one or many “Add. Princ (X)” Line statuses to the desired names (Tools > Settings > Line Payment Statuses):

You could create only one “Fee” or multiple fees (up to 9 different names if needed).


2) Client and Loan (Case) creation
A Loan / Line of credit or Case in legal jargon, can be created in Margill by entering these data (minimum information needed):
  • Start (Origination) Date and First Payment Date (in litigation loans, these are usually the same date)
  • Interest rate (or Interest Rate table)(could also be a default rate so could not be needed)
  • Unique Loan ID (from other system)(required only for auto import)

Any other data can be entered as required (court case, client names, email, address, mobile, case type, active or draft, and other special data…).

This information is entered in the Data window either:

  • manually;
  • in bulk manually via a simple Excel file;
  • in bulk automatically via an Excel or JSON file (what we call “hot folders” where, when a file is deposited in a specific folder, Margill will automatically grab the file and import the data).

Since law firms can use a wide variety of systems  – practice management solutions and accounting systems, a data conversion app may be required to convert third party data to a format accepted by Margill Loan Manager (JSON and Excel files). This can be developed by our programming team.

Typical Excel sheet for Case creation in Margill:

Principal amount is required but is usually 0.00 as case Costs and Fees will be added as line items (see 2) below)

The Case(s) will then appear in the Main MLM window with nothing really exciting so far – only the basic data. It gets fun when transactions are added!


3) Cost and Fees (transaction) import
Once a Case if created, Costs and Fees (and payments) can be imported at any date after the Start (Origination) Date. They can be entered:
  • manually Case by Case (for very low activity volume)
  • manually for multiple Cases at once
  • in bulk with an Excel sheet manually
  • in bulk with an Excel sheet automatically

For the bulk imports, the Excel sheet must follow this precise mapping:

For any auto imports, the ISO date format MUST be used: YYYYMMDD

3a) Cost and Fees (transaction) import from other systems

If your law firm must import data (transactions) from an accounting software (QuickBooks or other) or a practice management system and this system does not support the production of an Excel sheet as above, a special conversion application can be created by the Margill programming team that converts the file your system produces, to the Margill format which can then be imported manually or automatically. Such an app saves time and avoids errors.


4) Special situations

A host of special situations can be handled by MLM (the numbers correspond to the red numbers in the image below):

  • (1) One or many, up to nine (9) Fee types (Lawyer Fees and Expert Fees – could be named as you wish – Medical Expense, Expert Expense, Medical Expense no interest, etc.).
  • (2) Line 5 shows the $666.36 Lawyer Fees that do not bear interest (blue Line status and Pmt Type column).
  • (3) As of 1/29/2023, interest is no longer charged on all amounts since maximum 3 year Term (optional of course). Line color was changed to red to highlight the End of Term.
  • (4) An Invoice number was added for each invoice.
  • (5) A Comment may be added on any line.
  • (6) Final settlement of $21,250. All interest got paid first but we could have paid the $19,760.83 in Principal (the Fees) and there would have been an interest balance of $39.44 instead of Principal balance for this amount – you have the options with the Line statuses.
  • Interest can start 30 days after the invoice date so the Pmt Date entered would be the invoice date + 30 days. The actual invoice date could be in another column (not shown below).
  • The Case can also be lost in which case, instead of a Payment at the very end, there would be a Line status that could be called “Loss” or “Bad debt” to bring the balance to 0.00 and to isolate the fees and interest as unrecoverable for your accounting (not shown below).
  • Many other options are available…

5) Reporting
You can create your own reports for any time period using over 1000 fields. Typical reports would be daily, weekly, monthly, quarterly, yearly…:
  • Disbursed Costs and Fees
  • Interest generated (accrued)
  • Interest actually paid or collected (as opposed to simply accrued)
  • Fees by Case type
  • Cost and Fees paid back
  • Fees lost (Case was lost)
  • Balances
  • and many more, such as socioeconomic information for decision makers (statistical data).
Please contact Margill support ([email protected]) for more information.

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.com/en/interest-calculation-white-paper/

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.

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.

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.

How can I create a schedule where I can see the interest that accrues on a daily basis, every day?

Q: My law firm must calculate the interest from June 30, 2019 to May 28, 2020 on an amount due by an insurance company. I must be able to see the interest that accrues every day.

Interest rate is 4% annually and amount is 150,250.33.

A: In Margill Law Edition, you would usually use the “Interest on one amount between two dates” calculation:

Data entry:

This would give you the amount as two lines with a split on December 31 at midnight:

I know, you want detail, lots of it, on a daily basis so instead of using “Interest on one amount between two dates”, use the very powerful “Recurring Payments (Amortization)” calculation that can do just about anything, not only loans or mortgages.

Here is how I would enter the data to see the payments every single day. Notice:

  • “First Payment Date” is one date after my “Origination Date” or start date
  • “Payment Method” = “Payments set to 0.00”.
  • For “Number of Payments”, I right clicked with the mouse to enter 05-28-2020 and Margill calculates a cool 333 payments (of 0.00)

We get a 333 line schedule with the daily interest for each day.

We get almost the same amount as in the calculation done with “Interest on one amount between two dates”. We are higher by 0.66 since the calculations below are done line by line and the 2 decimal point pennies leads to this slight difference.

—————————————

In Simple interest, using the Actual/Actual Day count, the interest in 2019 is slightly higher than in leap year 2020. This also could have be done in Compound interest where the daily interest would change almost every day.

Remember the interest for the end date is excluded. So interest does not include the interest for May 28, 2020.

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!

Calculating Interest Distribution to Investors in Complex Participation/Syndicated Loans, Pools or Funds

Maximizing Investor Returns / Reducing Risk through Participation/Syndicated Loans

Investors are constantly looking for new ways to maximize their returns. Investing in higher risk private loans is a method to generate 10 to 20% + annual returns. Of course, one of the objectives is to reduce risk and this can be achieved when multiple investors participate in a loan.

In higher-value commercial loans, bridge loans, loans for entrepreneurs (for starting or expanding a business) and short-term tax credit loans, many investors can participate or pool their investments to finance larger projects. Funds based on industry or loan product or investor pools are often created to reduce the risk of each investor while offering maximum flexibility.

Some loan funds and loan pools allow the investor to invest into or divest from a fund/pool/loan before the loan comes to maturity. An investor can decide for example to invest in a loan when the real-estate project reaches a specific milestone or divest when he/she feels there is a better opportunity elsewhere (usually divest to another fund within the same finance company as opposed to a complete withdrawal).

Again, to minimize risk and reduce cost to the borrower, in some loans, notably, construction loans, principal is extended progressively to the borrower only as required and contingent on achieving project milestones. These new principal advances are financed by existing or new investors that can join a loan or fund. This obviously adds to the complexity of calculating the distribution of income to investors.

Revenue models for the investor vary greatly: some Fund Administrators will pay the investor no matter if the borrower pays or does not pay the accrued interest every month (revenue based on accrued interest) and others will only pay the revenue portion to the investors if the borrower pays at least the accrued interest (revenue based on cash received). Whether based on accrued interest or cash paid, the revenue must be redistributed to each investor on a pro-rated basis of his investment amount and participation time (number of days or months of participation).

The Impossible Job of Interest Distribution to Investors

With this flexibility though, comes a major headache for Fund Administrators who must manage the revenue (interest revenue) for each investor based on the amount invested and on the time period this investor was involved in the loan.

Not many software tools can deal with such complexity, and although spreadsheets can be created to solve part of the problem, the time required to compute accrued or paid interest and investor distribution can take days, not to mention the great chance of error.

Calculating the interest in participation or syndicated loans is one piece of the equation, preparing statements is the next phase, a long and arduous process without the proper tools.

Investor management software to the rescue!

Yes there are solutions out there! Margill Loan Manager, a world-class software product, sold in over 40 countries, offers a highly sophisticated Investor module specifically created to manage irregular investments, divestments, payments and of course calculate revenue distribution by investor.

Let’s do a example and throw in a few curve balls…

Borrower ABC Inc. is given a credit facility of 1,250,000 (could be $, £, €, no matter) at a rate of 12% annually for 12 months. Investors in the participation/syndicated loan receive this return too.

  • If investors were to receive a lower return than the rate charged to the borrower, then this lower rate would be entered or, when reporting, a special custom report or statement could be created to factor in the spread or revenue for the Fund Administrator.

We will be using Compound interest, compounded monthly (we could have used Simple interest or compounding at another frequency (annually, semi-annually or other). We will also be using the banking method called the Effective rate method. Day count will be the most precise: Actual/Actual – could have been Actual/365, 30/360 or Actual/360.

ABC must pay accrued interest every month.

  • First draw of 300,000 on Feb 2, 2019
  • 5 investors wish to finance this first draw:
  • Fund A: 90,000
  • Fund B: 75,000
  • Fund C: 55,000
  • Fund D: 40,000
  • Fund E: 40,000

Initial loan advance of 300,000:

First interest payment on March 1, 12019. Total accrued interest is 2892.34. The accrued interest and individual investor portion is automatically calculated. We allocate to All Funds (investors in this case). Interest is prorated to each investor.

These interest payments can also be mass posted through the Post payment tool (see below).

Special event:

  • Fund C: Withdraws 25,000, April 12
  • Fund A: Replaces Fund C for 25,000, same day

Reallocation from Fund C to Fund A:

May 1 and June 1 interest payments are posted with the Post payment tool. This tool can post interest payments for a single or hundreds of loans, in seconds.

We can see how the 3003.24 interest payment was applied. Reports can show full portfolio totals for each investor/fund:

The Borrower makes a second draw (the operation is not shown in the images below since method was explained in example above…):

  • Second draw of 250,000 on June 17, 2019
  • Fund E: 100,000
  • Fund F: 100,000
  • Fund G: 50,000
  • Fund C: Complete divestment (30,000) July 2
  • Fund E: Replaces Fund C, same date

Borrower inherits from a rich uncle and pays back 65,250 on July 25. Outstanding interest is first paid back, and the balance pays the principal. The payment could also have gone 100% to principal with the “Principal Only” option.

On the lower part of the window, one sees the distribution by fund. Notice Fund C had a 0.00 principal balance but a 10.02 interest balance that will get paid off with the 65k payment.

Finally, rich uncle did not leave enough to ABC so there’s a need for more money (operations for adding draws an interest payments are not shown since we are now experts at adding these):

  • Third Draw of 600,000 on October 3, 2019
  • Fund A: 200,000
  • Fund E: 150,000
  • Fund H: 175,000
  • Fund I: 75,000

For the November 1 payment that was supposed to pay the accrued interest (10,506.48) ABC can only pay 1000 (see bottom portion).

The December 1 interest payment would then be much higher to pay the outstanding and current interest for a total interest payment of 20,479.77.

Construction is now complete and on Jan 18, 2020 ABC decides to pay back only 250,000. Fund Administrator decides that Fund A should get paid back first.

Outstanding interest (1653.76) is first paid to Fund A exclusively:

The remaining payment balance, 248,346.24 is then paid to principal to Fund A exclusively (use the “Principal Only” option):

And finally, the full loan is paid off on February 14, 2020. All interest is paid to the investors as well as the principal bringing the loan balance from 844,402.41 to 0.00 with the Payoff button:

Final schedule:


Multiple other options are available in the module such as:

  • Non-cash principal and interest Adjustments
  • Non-cash partial or complete P&I Transfers OUT of old loan and IN to new loan
  • Bad debt

A loan can include dozens of investors and a portfolio hundreds of investors.


The reporting module allows the production of reports at any date and for any time period for all investors/funds:

  • All transaction types (principal advances, cash payments, reallocations, transfers, adjustments, bad debt)
  • Principal balances
  • Interest accrued
  • Custom-built statements

The reports are produced as spreadsheets (Excel) or PDF.


Note: This module cannot include fees. It is meant to calculate the return to each investor. A second loan type for the borrower interactions, could include extra fees (origination, points, automatic penalties, etc.).


Changes in Winter 2022: Variable interest rates can now be added in the module (fixed rates previously).


For more information on this very special module, please contact Margill Customer support: [email protected] or call at 450-621-8283.


Notes on Line statuses used in module:

Line status (original name) Fund module recommended name (or purpose)
Add. Princ. 2 Additional Principal
Paid Pmt 2 Payment or Payoff
Principal Paid Reallocation Pmt, Principal Paid, Principal Adjustment Reduce
Add. Princ. 3 Reallocation Principal
Add. Princ. 4 Transfer in Principal
Paid Pmt 5 Transfer Out
Paid Pmt 4 Bad dept
Interest Charged Transfer In interest
Interest Paid Interest Adjustment Reduce
Interest Charged 2 Interest Adjustment Add
Add. Princ. 5 Principal Adjustment Add
Information no Impact Information Line
Rate Change Rate Change

Margill Loan Manager – Ageing report – with refinanced loans

Q: If a loan was refinanced and the payments revised based on the refinanced balance, can the loan account still be in arrears?  Doesn’t the refinancing and revised payments take into consideration any prior arrears? 

A: Arrears are always a little tricky with refinanced outstanding amounts since a human must take a decision as to whether the new payments to be added are simply extra payments or are to compensate for the unpaid payments in the past. The examples will help…

A most important column in the schedule is the “Expected Pmt” column. This column indicates how much was expected for this line and subtracts the actual payment amount from this amount to generate the Outstanding amount.

  • On 07-06-2018 I was expecting 439.58 and got a 439.58 payment so Outstanding = 0.00.
  • On 10-06-2018 I was expecting 439.58 and got 0.00 so Outstanding = 439.58 and so forth

If an extra payment (unexpected in the normal scheme of things) is made, then the Expected Pmt should be 0.00 and the Outstanding is thus reduced.

If a loan is refinanced, you must make sure that as of this moment, your Outstanding amount gets progressively reduced to 0.00 and you do this with the Expected Pmt column in which you would put the Expected Pmt to 0.00 for the new payments that are added or changed to give 0.

In the above example, the loan is refinanced with lower payment amounts since the 439 was too high for the borrower – 6 payments were added and these now become 175.20 to reach 0.00. One could argue that these payments are extra and thus the Expec. Pmt should be 0.00 for each, so we manually change the Expect. Pmt to 0.00.

NOTE: In order to be allowed, to change the Expected Pmt amount, this must be allowed by the Margill Administrator in Settings:

As these new payment become paid over time, the Outstanding amount gets reduced…

If on the other hand, a second amount (new Advance) was lent to the borrower and extra payments were added, then I would not change my Expected Pmts to 0.00 since these new payments become part of the normal payments, in the normal scheme of things. So Outstanding is quite subject to interpretation…

I actually cheated below by entering 3 of the 12 new payments with Expected Pmt of 0.00 to bring my Outstanding back to 0.00. Outstanding must be 0.00 or greater, never less than 0.00 even if one could argue the borrower overpaid.