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!

Main Window Overview and Customization

In order to have an even better idea of what Margill Loan Manager can do for you, we have added 3 new videos:

The first one offers an overview or the Main Window and how you can customize it for your own needs:

Main Window Overview and Customization

In the second one, we look into how the Payment Schedule looks and feels:

Payment Schedule – Look and Feel

And lastly, always on the Payment Schedule subject, we take a look at the Critical Fundamentals:

Payment Schedule – Critical Fundamentals

Enjoy and do not hesitate to contact us at support@margill.com if you need any information.

The Lost Art of Interest Calculation

In 2008, Marc Gelinas, CEO of Jurismedia Inc., developer of Margill interest and loan servicing solutions, published the White Paper in the Real Estate Law & Industry Report titled “The Lost Art of Interest Calculation”. Since then, more has been learned and lending practices have evolved thus this major update. The White Paper deals with the fundamentals of applied interest calculation, unfortunately often forgotten by industry professionals.

The borrower-lender relationship: Why and how to nurture it during this crisis?

Most companies have seen their business operations seriously affected by the COVID-19 pandemic and the various government measures taken to mitigate its impact on the population.

Companies have to contend with various issues in the short, medium and long term, such as the closure of many companies’, clients’ and suppliers’ places of business, restricted opening hours, and working from home.

Businesses need to maintain the relationship of trust they have built with their lender a business partner with whom it pays to be proactive, show transparency and uphold best practices during these difficult times.

Continue reading this text by François Renaud, Brigitte Gauthier and Frédéric Boivin Couillard of Lavery.

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!

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:

Column Fees (such as monthly fees or taxes) cannot be re-imported with the bulk payment import via Excel. The payments and dates can be imported but you will need to go in each loan or lease to add these Column Fees manually – so a pain if you have a high volume.

The impact of coronavirus on borrowers and lenders

As companies worldwide continue to assess and react to the outbreak of COVID-19, the consequences of the virus are increasingly having an impact on businesses on a global scale. An interesting article to read here.

Margill Installation – Network Drive not Displayed – Windows 10

You are installing a Margill product and do not see your mapped network drive on Windows 10. Here is how to solve this in many cases anyways…

This is not as difficult as it seems…

Turn on SMB Direct from the Windows Features and edit the registry key called EnableLinkedConnections.

  1. Click the Start button, click Control Panel, click Programs, and then click Turn Windows features on or off.
  2. Select the check box next to the SMB Direct feature to turn it on.
User-added image

To configure the EnableLinkedConnections registry value:

  1. Click Start, type regedit in the Start programs and files box, and then press ENTER.
  2. Locate and then right-click the registry subkey HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System.
  3. Point to New, and then click DWORD Value.
  4. Type EnableLinkedConnections, and then press ENTER.
  5. Right-click EnableLinkedConnections, and then click Modify.
  6. In the Value data box, type 1, and then click OK.
  7. Exit Registry Editor, and then restart the computer.

What is #1 Loan Servicing Software in the World?

Very interesting question, but is there an answer?

Being number 1, being the Top is the ultimate goal for many… The ultimate achievement… The Gold medal!

We hear this in political speeches: we are the BEST, we are the GREATEST, never in the history have we been SO GREAT…

Vying to be Number 1 is also true for loan solution providers.

I thought I would write this article since I came across, simply by surfing the web and checking out other loan tech companies’ websites, that at least three companies have the #1 lending technology. Congratulations to all of you!

# 1 is taken three times so I guess this means Margill Loan Manager is the BEST, the GREATEST #2 software solution out there?

There are many good, even great loan software out there and many not-so-good ones that say they can do just about everything… but none can really. In the end, when you are looking for loan servicing software, what’s important, is that the solution fits your own special needs and that customer support is there for you.

PS: No hard feelings #1s…

Marc Gelinas, CEO
Margill Solutions