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:
