Blog that looks into various aspects of interest calculation and loan servicing

Replacing LIBOR – Are you Thinking About This $200 Trillion Dollar Problem With Your Contracts?

Darren Gold, VP at UnitedLex Corp., wrote an interesting article about the London Interbank Offer Rate (LIBOR) which is used all over the world as the reference for a great number of loan contracts.  The UK’s Financial Conduct Authority recently declared that it would no longer require banks to submit the data that enabled LIBOR to be calculated after the beginning of 2021.

Although there will be alternatives such as Secured Overnight Financing Rate (SOFR) in the USA and Sterling Overnight Index Average (SONIA) in the UK, this change does come with significant uncertainty and we must wonder what this will mean for current contracts and how they will be dealt with in the future.

Replacing LIBOR – Are you Thinking About This $200 Trillion Dollar Problem With Your Contracts?

Predatory Lending Practices: How “Confession of judgment” documents turned New York’s court system into a debt-collection machine…

How an obscure legal document called a “Confession of judgment” turned New York’s court system into a debt-collection machine that’s chewing up small businesses across America.

Source: Bloomberg, November 20, 2018
Story by Zachary R. Mider and Zeke Faux

Although not allowed in most states, a “confession of judgment” signed by the borrower allows a creditor, without proof, to accuse a borrower of not paying the expected monthly payment for example and then to legally seize their assets.

Read the full article at :

Loan Servicing with Excel? Pitfalls and alternatives

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!

A little banking humor! His name was Boudreaux…

His name was Boudreaux. He was from Cutoff, Louisiana … and he needed a loan, So…….. 

He walked into a bank in New York City and asked for the loan officer. 

He told the loan officer that he was going to Paris for an International Cajun festival for two weeks and needed to borrow $5,000, and that he was not a depositor  of the bank. 

The bank officer told him that the bank would need some form of security for the loan, so Boudreaux handed over the keys to a new Ferrari The car was parked on the street in front of the bank. 

Boudreaux produced the title and everything checked out.. The loan officer agreed to hold the car as collateral for the loan and apologized for having to charge 12% interest. 

Later, the bank’s president and its officers all enjoyed a good laugh at the Cajun from Louisiana for using a $250,000 Ferrari as collateral for a $5,000 loan. An employee of the bank then drove the Ferrari into the bank’s private underground garage and parked  it. 

Two weeks later, Boudreaux returned, repaid the $5,000 and the interest of $23.01. The loan officer said, “Sir, we are very happy to have had your business, and this transaction has worked out very nicely, but we are a little puzzled.” 

 “While you were away, we checked you out on Dunn & Bradstreet and found that you are a distinguished alumni from LSU, a highly sophisticated investor and multi-millionaire with real estate and financial interests all over the world. Your investments include a large number of oil rigs around south Louisiana. What puzzles us is, why would you bother to borrow $5,000?” 

The good ‘ole boy replied, “Where else in New York City can I park my car for two weeks for less than $25.00 and expect it to be there when I return?” 

His name was Boudreaux…. Keep an eye on those Louisiana boys! 

Just!! cuz we talk funny doesn’t mean we’re stupid ! 


Intercompany Loan Management with a Loan Servicing Software

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:


Margill Loan Manager

You can also try our software for 30 days for free:

Or Schedule a demo: