Happy Holidays!
The entire Margill team wishes you, in spite of the current exceptional situation, a very Happy Holiday Season.
May 2022 be filled with happiness and health!
Photo Mira Kireeva – Unsplash
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):
What’s criminal? Lenders beware – amendments to Criminal Code under Bill C-274
An Act to amend the Criminal Code Bill-C-274 (the Bill) entered first reading in the House of Commons on May 11, 2021. The Bill would amend1 the definitions of “criminal rate” and “interest” in subsection 347(2) of the Criminal Code, while also repealing section 347.1, which had allowed certain exceptions for payday loans.
The Bill will be of interest to lenders, especially payday lenders and other non-traditional lenders, as the amendments proposed would lower the criminal rate at which interest charged or received is under the Criminal Code from 60% to 30%.
To continue to read the text by Me Joyce M. Bernasek and Me Ramz Aziz from the law firm Osler, follow this link.
Credit agreement best practice – LIBOR cessation
Osler law firm offers an excellent article regarding the London Interbank Offered Rate (LIBOR) which will eventually end. And even in these difficult times of the COVID-19 pandemic, there is no suggestion that LIBOR’s end will be delayed and should end, as expected, on December 31, 2021.
A text by Andrew G. Herr, Lisa Mantello and Joyce M. Bernasek that you can read here.
Global FinTech survey report – Key findings
Financial institutions, including banks, asset/fund managers and insurers, as well as established FinTech businesses and start-ups, have been presented with major disruptive events with the advent of COVID-19 and national lockdowns, and with the impending risk of global or regional recessions.
In May and June, a group of attorneys from the law firm Norton Rose Fulbright undertook a survey of a range of banks, asset/fund managers, insurers, established FinTech businesses, FinTech start-ups and venture capital and consulting firms across the globe. You can consult the key findings of this survey.
To read the article, follow this link.
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!
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 [email protected] 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.