Can we email an amortization report to each borrower when interest rates change?

/in /by

Margill Loan Manager – Can we email an amortization report to each borrower when interest rates change?

Yes this can be done.

First, I guess you updated the interest rates though the Main window with Ctrl Alt Shift i. Ideally you have a custom field that identifies the loans that are tied to the specific index (Prime, LIBOR, etc.). With this field you can easily select the proper loans to 1) change the interest rates quickly and 2) send the amortization schedule by email.

You could have the scroll menu with “Prime” or to be more precise “Prime +”

To create the statement to send out, go to Reports > Mail/Email Template > New and create a DocX that offers many more options than the older RTF.

You can then structure the template and enter your logo and add the Merge codes to identify the Borrower, etc. You could also create your statement in Word and copy it here afterwards.

Here is what this could look like (the |105| for example, are Merge fields)…

The merge codes to enter the amortization schedule per se are under the General theme. You can try each to see which is best for you. There are 10 templates and we can program others to meet you exact needs (columns included, titles, etc.).

 

Now that your template is created, test to see if all is good (numbers, names, etc.).

Go to Reports > Document Merge.

You will need to select a date range or show the entire schedule (past, present and future payments). I would opt for a date range to see up to the rate change, not the future.

See the circled red settings below. |991| will be the schedule…

Then press on “Save – Print -Send by Email”.

Here are the options:

You can also add an email  Subject and Message when sending the email.

Email sending must be configured in Tools > Settings > Email Connection. Your IT person will usually set this up properly for each Margill user.

The selected Records will all be sent out by email in a batch. Each takes about 10 seconds to create and send out.

Early payoff – How to do this in Margill Loan Manager

/in /by

Q: How to do an early payoff in Margill Loan Manager

A: For example, the loan term was originally for 5 years or 60 months (so end date was in June 2020). The borrower calls you, the creditor, and wishes to payoff his/her loan early, on October 12, 2018.

Original payment schedule:

 

I first recommend to take a snapshot of the full 60 month payment schedule – this was we have an easy to consult original payment schedule. Click on “Attach”. A PDF will be attached to the Record.

Next change the date to October 12, change the payment to 0.00 so the payoff balance is now shown (64,297.75 in this case). Notice I also changed the 2018-11-01 payment date to 2018-10-13 to see my daily interest on the balance (4.92 per day).

Change the Payment to 64,297.75 (for Oct 12).

You can then delete the next lines that are no longer required (right mouse click).

You could also decide to add extra fees for an early payoff if the contract included this (use Column fees or Line status fees). This will increase the balance of course.

Also, you could create a special payment-type Line status to identify all your early payoffs. Could be interesting for your reports.

If the final payment is late, nothing stops you from changing this final date to enter the true payment date. Extra interest will accrue.

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!

Margill Loan Manager: Is there an audit trail that can be printed to show transactions / changes to records on a daily basis?

/in /by

Q: Is there an audit trail that can be printed to show transactions / changes to records on a daily basis?

A: Yes very easily.

Go to the Main Margill window, select all the loans (probably only Active loans) , Tools > Various > Display the history of changes for selected Records.

You can then right click with the mouse to see the changes for the day, yesterday or any period of time.

You can also see the log of changes loan by loan in the Data window of each loan:

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.

Can Margill Loan Manager be used to manage rentals (rent)?

/in /by

Question: Our company does loans and rentals. Can Margill Loan Manager be used to manage rentals (monthly rents)?

Answer: As strange as this may sound for a loan servicing software, yes Margill Loan Manager can manage rentals.

The difference between a loan and a rental is that loans begin with a principal amount borrowed that usually will decrease as payments are made for the interest and principal portions. Rent on the other hand, is due (accrued), and payable every month. So rent accrues usually on the first of the month and then is paid, hopefully, on the same day. Usually, interest is not charged on rent.

First, in Tools > Settings > Custom Column Titles, change the Column “Fees” title to “Rent”.

Create a new Record. Under Data enter the date at which the first rent is due. The same date should be entered in both Origination Date and First Payment Date. The rents are payable monthly. Principal (Original) should be 0.00 and since there is no interest on the rent, Annual Nominal Rate (%) should also be 0.00%. This is a three year rental for 575 per month. Press on “Add Fees” and a window will allow you to enter the monthly rent amount.

Then press on Compute and answer No to this pop-up. We do not want Margill to compute the Principal (remains 0.00).

The rental payment schedule then appears. I moved my column called “Rent – Accrued” to the left to see it readily without needing to scroll to the right. The rent is “charged” with the “Rent – Accrued” and paid with the “Payment” column.

Notice on line 3 that my rent is a few days late. This does not change anything mathematically since interest is not charged, but this allows you to balance with your banking transactions. A partial rent amount could also have been entered and the schedule then changed based on what was agreed for later repayment between the “landlord” (or equipment rental) and the person who rents.

To be fancy, I renamed a Paid Pmt (x) Line status to “Paid Rent”. Go to Tools > Settings > Line status.

Importing batch payments in Margill Loan Manager (CSV, Excel)

/1 Comment/in /by

Question: Our company collects weekly (bi-weekly or monthly – no matter) payments drawn directly from their bank accounts in some cases and in others as payroll deductions. We receive CSV files from the bank and processors. Can these transactions then be imported back to Margill Loan Manager?

Answer: As you guessed, since I blogged this, Margill does this no problem.

Each and every line of a payment schedule includes a unique line identifier. If this line identifier can be sent back to Margill from your processor (or accounting or ERP system), the software will automatically change a “Due payment” to a Paid, Late or Partial payment or even an Unpaid payment if your system can send back the Line ID with a payment of 0.00 (most systems don’t actually, so a human may have to decide when this payment eventually is considered Unpaid).

To send the transactions to the bank or to the payroll deduction processor, use the Transaction report with any data you want. Let’s keep it simple by supplying the Name, Transaction date, Transaction amount and Unique Line ID.

This report can then be sent to the processor in Excel, CSV or Text format.

Please note. The file created by Margill is not in NACHA format. We work with third party processors Intrix (US) and Perceptech (Acceo)(Canada) for electronic funds transfers to debit your borrower accounts.

Notice the “Line Unique ID” above – your provider must be able to accept this data and return it with the paid payments in the format below. We highlighted special payments (need not for real…):

  • In C2  we have a Partial payment
  • C4 – Unpaid payment
  • B7 – Late payment
  • B15 – Partial Late
  • C23- Unpaid payment
  • The rest are all normal Paid payments – on time, right amount…

This Excel file is imported to Margill and all payments will be updated in seconds… Errors will be flagged the case being.

See also: How can I enter multiple payments and advances in a loan without having to enter them one by one? I have over 100 in one loan. I have the data in Excel.