Margill Loan Manager – Ageing report – with refinanced loans

/in /by

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.

Automatic Margill Loan Manager emails – Gmail managed emails (G Suite / formerly Google Apps) are blocked

/in /by

Q: I have set up automatic emails in Margill Loan Manager. We use G Suite for these but when I test the email connection if get a message saying the Google blocked the app since it is a less secure app. What can be done?

A: We see this once in a while when using GSuite.

Margill has no control over this since Margill simply sends a request to the Gmail (or other) SMTP server and this server checks your User name and Password and accepts to send the email or not. Pretty straightforward stuff, no big technology behind this…

However, GSuite or other mail providers may not accept the communication since it is sent by a software that they do not recognize and may give you a message such as:

You will thus have to allow your email account to communicate with Margill. Log into the G Suite Admin Console (https://gsuite.google.com). You must be the G Suite administrator. Go into security settings and click “Allow users to manage their own access to less secure apps”. Then go into your own Gmail settings and turn on the “Allow access to less secure apps (not recommended)”. Google will tell you a number of times that this is unsafe.

This should now allow the communication.

Setting up and Servicing Agricultural (Farm) Loans Efficiently

/in /by

Setting up and Servicing Cash-flow adapted Agricultural (Farm) Loans Efficiently

Most farmers have special needs when it comes to their loans to buy land, equipment and other farm assets because of their seasonal cash-flow and income spikes. Therefore, agricultural loan products shouldn’t be set up like conventional personal or business loans or mortgages with regular fixed payments, but rather adapted to each farmer’s particular revenue and expenditure rhythms.

A crop farmer most often has greatest cash needs in late Winter (next season purchases), Spring and Summer and greatest cash income in Fall at harvest. Livestock farmers on the other hand, can usually generate steadier expense and income streams.

Depending on crop type and location of the farm (colder countries versus subtropical or tropical countries), there may be two or more harvest seasons. Harvests can be considered Good or Poor, adding yet another cash-flow need to be considered when setting up a loan payment plan.

Lines of credit offer much flexibility of course to the farmer, allowing to borrow and refund as needed.  When lines of credit are not available, for capital purchases for example, amortizing loans become the best option. Calculating a comprehensive, cash-flow adapted payment plan for the farmer can become so difficult with conventional calculation tools or spreadsheets, that small agricultural lenders simply cannot easily cater to their clients’ particular needs.

Margill Loan Manager makes lenders’ tasks so much easier with a what-you-see-is-what-you-get approach to creating the payment/amortization schedule based on an predicted cash flow.

We’ve also included an example of a short-term, bridge loans we often see in AG loans.

Example 1: Interest-only during low season with principal and interest during the harvest months

Step 1: Create loan with normal amortization

Let’s say this for only 24 months (could be years, no matter)…

Press on “Compute’ to create this normal P&I schedule which you can now adapt line by line or in bulk.

Step 2: Highlight the interest-only months (lines) and right click:

Step 3: Highlight the principal and interest (P&I) months to fully amortize (0.00 balance).

Get the proposed payment plan in seconds – notice below that the payments for the interest-only months (lines 14 to 20) have been recalculated automatically since lines 9-13 pay off principal thus reducing the accrued interest (this automatic re-computation is called a Line Behavior – a pretty sophisticated feature).

Notice the payment amount for line 1 (479.88) is higher since payment was over 1 month after the origination date (what is called a long period).

Example 2: Higher set payments during the high cash-flow months and normal amortization during the slow months

Once the preliminary schedule is calculated:

Highlight high revenue months, right click – let’s say the farmer can pay 4000 per month during these 5 months of the year. Margill will ask you to enter the payment amount for the selected lines.

Now select the remaining P&I lines and compute the payment to produce a 0.00 balance:

The resulting payment plan:

Example 3: Over time payments were made, missed and late, fees were automatically added and so another 6 payments are added to the loan as well as a new 20,000 loan approved on April 12, 2021

We first inserted a new line  – line 19 below – with the right mouse click or the  button in which we entered the 20,000 loan (called an “Add. Principal (Loan)” type transaction).  Then we added the 6 extra payments at the end of the schedule:

The payments after the 20,000 loan are then re-amortized (could have been special  lump sum payments in there too):

Below is the schedule containing the past payments and the future expected payments to fully amortize the loan that now stretches on to May 2022:

 

Example 4: Bridge loan to help farmers who are expecting to receive a state or federal grant. The grant only comes in (paid by the government) after the project is completed. Interest can be charged normally or a simple Fee charged since interest may be too low for 2-3 month loans.

In this example, we have a 10,000 loan for approximately 3 months (we estimate payment on August 1 – date can change later on):

The preliminary result after Compute:

I then must add my Fee (200) – I can add either a fee or consider this fee to be interest. You have both options in Margill with Line statuses.

Press on  to insert a line (or right click with the mouse):

Fees could be paid up front:

Or paid at the time of full repayment on August 12 for example (for accounting purposes, Fees must be paid separately from the principal so they are properly accounted for):

Some would like to consider the 200 as  interest so we use a Line status called “Interest Charged” and this shows in the Accrued Interest column:

We can split the payment in two or could have one payment of 10,200, no matter (either way, payment will automatically post 200 to interest first and 10,000 to principal):

or

As a agricultural lender you run into other scenarios? Please let us know and we’ll add to this blog! Write to [email protected] or call at 1-877-683-1815 or 001-450-621-8283 and talk to Marc.

Loan Servicing with Excel? Pitfalls and alternatives

/in /by

Loan Servicing with Excel? Pitfalls and Alternatives

I’m a great fan of Excel. I think it is the most important and useful software ever developed. I first started using spreadsheets many years ago with Lotus 123 which was a great tool that lost the spreadsheet war to Microsoft. Excel can be used in so many ways and here at Margill, we use Excel quite extensively.

Many, if not the majority of our users, before switching to Margill Loan Manager, serviced their loan/mortgage/lease/line of credit portfolios with Excel. Even among our very large clients, many used Excel to service hundreds of millions of dollars and it was doing a pretty good job. Excel is great because of its flexibility and its almost limitless power to manually adapt the loan to very peculiar scenarios.

Spreadsheets do however have major drawbacks and as the guy who guides clients in their migration from Excel to the Loan Manager, I have seen hundreds of workbooks and here are some of the drawbacks I have observed over the years:

1. Too many loans eventually: spreadsheet overload

Excel can do a decent job for a limited number of loans but eventually, the amount of data becomes too much and the loan payment schedules become impossible to manage. Finding data and updating becomes mission impossible.

2. Irregular loans and missed/late/partial payments

A major drawback in Excel is managing missed, partial and late payments. Excel can be fine when all normal payments are paid as they were planned, based on the loan contract (60 payments of $500 on the 1st of each month for example). When payments are not made as they should be, it becomes a real challenge to update so many spreadsheets. Updating a few dozen loans can take hours and a few hundred almost a day. Payment management should take minutes, not hours!

Excel is not ideal either for line of credit type loans or when payments are not set ahead of time. Try importing 500 new payments or draws (aka: advances/additional principal) on various dates for multiple loans. In my experience, this can only be done loan by loan in Excel, by hand, not as a batch operation. A good loan program allows easy import of these ad hoc payments or draws with an automatic re-computation of the interest in seconds.

3. Variable interest rates

For loans based on Prime or LIBOR for example, the fact that rates were very stable over the last couple of years made things more or less manageable with Excel. Now with base rates going up more regularly, updating loans in Excel represents a real challenge since updating the rates must be done manually on a loan-by-loan basis. A good loan servicing platform can update rates in batch.

We often see interest-only loans tied to a base rate in which, when the rate changes, the payments must be adjusted to remain interest-only. With any sort of volume, this becomes almost unmanageable in Excel since, in my experience, there is no way to easily tell Excel to adjust the payment to pay only interest. There’s probably a way to program a special macro to do this but again, not easy. Very few loan servicing software have this advanced option.

4. Data cannot be found easily

I often do screen shares with potential clients and simply finding a specific loan in the spreadsheet system is a challenge. Wrong folder, wrong spreadsheet, wrong tab. You know the feeling! Finding the right loan should take seconds, not a minute or more.

5. Calculation errors

This one is self-explanatory. Excel is wonderful software, but human error is a major problem with spreadsheets since they offer great flexibility but with flexibility comes risk of error. Many studies have been done over the years trying to evaluate the amount of money that has been lost (or gained by someone else) because of human error. The same risk of error exists for loan servicing via Excel.

See this interesting ZDNet article Excel errors: How Microsoft’s spreadsheet may be hazardous to your health

6. Charging fees when payments are missed

I’ve seen so many professional lenders include, in their contracts, a clause that states that fees are charged to borrowers for missed and late payments. Considering the challenge of adding fees in Excel, again, on a loan-by loan basis, these fees are simply not charged and end up as lost revenue for the lender and, even more importantly, the consumer not being penalized, does not change his/her bad habits.

High quality loan servicing solutions should have a feature by which fees are applied automatically for late or missed payments and to go one step further, should automatically advise the consumer a few days ahead of time by email or SMS that a payment is upcoming and to make sure the amount is available in the bank account (for electronic debits (ACH)) or to make sure the check is paid on time.

7. Obtaining financial data when you need it for the proper dates

A major irritant in Excel is obtaining the right financial/accounting data for a specific time period. Most companies will report on a monthly or quarterly basis based on the civil calendar month. In simple scenarios, when payments are due and paid on the 1st of each month, you can pretty easily obtain the interest and balances from the 1st to the end of month in Excel. However, not all payments are payable or paid on the first of each month or quarter. For example, a loan has set payments on the 7th of each month, yet I must report from the 1st of the month to the end of month. With such payments, Excel is simply not able to pull the accrued interest and balances for a calendar month (or quarter, or year) unless a line is inserted at the end of the month that splits the interest in two time periods (from the 1st to the 7th and the 8th to 31st for example). Adding such lines in hundred of loans would lead exhaustion or worse!

Quality loan servicing software don’t need these “reporting” lines since the calculation engine will automatically simulate start and end of month, allowing you to pull any data, any time for any time period.

7a. Distinguishing between accrued interest and paid interest

Along the same lines as 7 above, Excel does not easily distinguish between accrued interest and paid interest. In most spreadsheets I’ve seen, there is only one “Interest” column that is computed with a simple interest or compound interest formula. Even if a payment is missed, the interest accrues but is not paid.

In a properly designed spreadsheet, this unpaid interest should go, not only to the loan balance column, but also to a due (outstanding) interest column, that, in most cases, based on a standard refund order, should (must) be paid before any principal is paid. Throw in fees and the spreadsheet turns to disaster because a refund order is non-existent. This greatly increases the work for your accountants after the fact.

8. Loss of one day’s interest and inclusion/exclusion of start/end day

This is my favorite which I have seen countless number of times particularly for end-of-month payments.

You must ask yourself, if a payment is made on December 31, is it paid in the morning, so at 0:00 in the morning, or is it paid at the end of that day at 12:00 midnight (24:00)? Nobody actually asks this question since the payment is simply paid at some time during the day (10 AM, right after lunch, at 4 PM, who knows, who cares…). Nobody enters the time at which a loan or payment is made since interest is not calculated on a hourly basis, but on a daily basis. Software must then assume that a loan is disbursed or a payment made either at the start of day or end of day (to factor in a full 24 hours or interest).

Industry standard dictates that when a loan is disbursed, interest is calculated on the day the money is lent out but not calculated for the end date. So, for a loan starting Feb.1 (with first payment March 1) and with a final payment 12 months later, interest would start on Feb 1 at 0:00, but no interest would be charged on the loan end date of Feb 1 (payment 12). Even with a loan balance, the interest reported on the loan end date would be 0.00. A silly, but easily understood way to look at this: a loan lent out on Feb. 1 and paid back Feb. 2 would have 1 day’s interest, not 2. A loan from Feb. 1 to Feb. 1 of that same year, well, would probably not have any interest. How many days are there from Feb 1 to Feb 1? In my book, zero.

So a payment on December 31 is actually paid at the start of the day, not at midnight. I call this time 0:00+. In Excel, if Dec. 31 is entered as the payment date, you may have wanted it to be paid at midnight but it is in fact paid at 0:00+ and thus should reduce the balance by that amount at the start of the day, not at the end of the day. The balance reported on that payment line in Excel is thus that on the 31st at 0:00+, not 24:00. The same applies for the interest calculated: also on the 31st at 0:00+, not at 24:00. Thus, by relying on that line’s payment, the accrued interest for that last day (the 31st) is simply ignored and the balance is not accurate to the 31st at 24:00. To obtain the right amounts in Excel, a line should be inserted in the sheet on the 1st of the next month to compute the interest accrued on the 31st. Again, adding extra lines becomes practically unfeasible with a few dozen or hundred loans in Excel. And with every extra operation comes an increased risk of error.

Your 2 cents…

You have run into spreadsheet problems with your loans? Let me know, I’d be happy to add them to my blog.

Want to replace Excel as your loan servicing software?

You have had enough of Excel for your loans, there are many good loan servicing solutions out there, and, well, its my job as a Margill employee, to “objectively” ? recommend our product, Margill Loan Manager… It offers a whole lot of flexibility and accuracy!

How to obtain the daily interest amount (per diem) in Margill Loan Manager

/in /by

Q: How to obtain the daily interest amount (per diem) in Margill Loan Manager?

A: This can be added very easily through a simple Mathematical Equation. 

Go to Reports > Equation Management and click on  

  1. Name the Equation
  2. Select the two required fields (balance and interest rate) from the various themes on the left
  3. Add the operators with the  button
  4. Divide by 365 days with the  button
  5. Save

You can now use this simple interest Equation in various reports picking it up in the Equations theme:

Record List Customized:

Document Merge (DOCX., RTF, PDF) for your Invoices and Statements:

Interest-only: Regular monthly interest vs. Exact day interest

/in /by

Question:

My company does interest-only 12 month bridge loans calculated in two ways.

  1. Payments are based on the number of days in a month with a balloon payment at the end (so payments change depending on the month)
  2. Each of the 12 payments is equal with a balloon payment at the end.

Can these two calculation methods be done in Margill?

Answer:

Yes.

In Simple interest, Margill will usually use the exact number of days in a month and in a year to compute the interest. The Day count would be Actual/Actual (or Actual/365 or Actual/360).

If the interest is to be the same every month, the use the 30/360 Day count which simulates months that are of the same length.

For Compound interest (what is called the Effective rate method – the banking method), there is an extra  calculation method option that calculates using the exact number of days and another that splits payment in equal periods. The Day count does not have to be used to “artificially” simulate the equal periods.

A borrower missed a payment last month. This month he doubles up his payment to compensate. Doubling should get him back on track but I get a higher balance at the end of the loan? Help!

/in /by

Question: A borrower missed a payment last month. This month he doubles up his payment to compensate. Doubling should get him back on track but I get a higher balance at the end of the loan? Help!

Answer: Your borrower will be CLOSE to back on track but there was accrued interest for the month he missed so this is why you are not back to the same end balance (0.00).

To get the exact payment that should have been paid to compensate, he will have to pay the outstanding interest. An easy way to get the exact payment is to highlight that payment line,  right click and do this:

Can I apply a payment only to principal even if there is outstanding interest?

/in /by

Question: Can I apply a payment only to principal even if there is outstanding interest?

Answer:

Yes when this advanced feature is activated. In the Trial version, this is not activated by default since a more advanced feature.

To activate, go to Tools > Settings > User Settings > “Options: Interest-only and Fixed Principal” (blue link). The third option will allow you to pay principal first. You can set this as your default or have Margill give you the three options when you are in a loan. I would check “Offer the three option when creating a loan” for maximum flexibility.

Go back to your loan. Go on the payment that is to pay principal only. Right click with the mouse:

This window will appear allowing you to enter the amount of principal to pay back. I wish to pay $1000 in principal in this example:

This window will appear allowing to choose the third option that will pay back principal before paying any outstanding interest. Notice we write “NOT SUGGESTED”. We wrote this because it is not a standard refund order but we will be taking this off since we see in practice that this is in fact used quite often, particularly in inter-company loans.

Now you will see that this payment ($1000) pays principal even if there is outstanding interest.

 

New “Paid Principal” Line status in version 5.0 of Margill Loan Manager!

The “Paid Principal” Line status will, at the time of payment pay principal (and corresponding APR Fees financed, case being) before paying outstanding interest.

You cannot though, create a schedule with Paid Principal if there are Due Pmt lines before. You would have to use the method explained above (right mouse click > Payments > Fixed Principal Payment).

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.

 

Long first payment deferral versus normal one period (month) deferral

/in /by

Question:

When we compare a loan using a normal amortization schedule (amortization book or calculation on an online website) we do not reach the same number of payments in Margill as in the on-line calculator. Why is this?

Here is an example:

Origination Date:  July 14, 2017.
Original Principal: $ 11,374.
10% interest rate
48 months
Deferred interest and payments until Feb. 15 2018.

Normal amortization tables show payment of approx. $288.00 @ 48 months.
Margill is showing us 51 + payments @ $288.00.

Answer:

This is a most common error because of the “Deferred payment.”

Amortization tables (static) and on-line calculators cannot include deferred payments. They are usually exactly one period (often one month) after the Origination date. You could not thus get the proper payment with a 7 month deferral.

If I do a 48-month loan, with first payment exactly one month after July 14, I thus get the $288 you are looking for ($288.86 to be precise). Leave the payment blank so it is calculated.

So this is not what you are looking for since my first payment date is not properly deferred.

Let’s say we really do want 48 payments with the February date.

Because of the deferred first payment (6 months after a normal 1 month deferral) I am now at a payment of 303.81. Much higher since more interest accrued before any principal gets repaid.

From the screenshots you sent me, you want a 288.00 payment (not 288.86):

So I leave the the Amortization and Term blank and the number of payments will be computed to 52 with a last payment of $82.76.

 

Follow up question:

When I wrote “Deferred interest and payments” I meant thee is no interest from July 2017 to February 2018.

Follow-up answer:

You can simply change the Rate column to 0.00%. Balance then becomes minus $1047.44.

Then delete the extra lines (for which the balance is now negative) by highlighting them > right click with the mouse.

 We are now at 47 payments of $288.00 and the last one at $204.61 (since no interest for 7 months).