CECL reporting in SQL Server
Apr
26
Written by:
Charles Flock
4/26/2016 1:06 PM
Current Expected Credit Loss (CECL) is coming soon. In this article we talk about new functionality that we have added to XLeratorDB to makes loan level cash flow modeling simple. Using our new table-valued function CDRCASHFLOW in SQL Server you can create extremely sophisticated models using some very simple SQL which scales up for hundreds of thousands if not millions of individual loans. If your data already resides in SQL Server, you can implement this functionality without making any changes to your existing applications.
The Financial Accounting Standards Board (FASB) and the International Accounting Standards Board (IASB) have been working on a new accounting standard and reporting requirements for establishing allowance for loan and lease losses (ALLL) and other than temporary impairment (OTTI). These standards will phase in over the next few years, and will have big impacts on banks and other financial institutions. Certainly the new standards will require a more robust technology solution than simply cranking out a few more spreadsheets.
A big part of the new reporting requires the modeling of loan cash flows and tracking them from the point of origination. One of our customers contacted us late last year and asked if we could come up with a flexible modeling tool that would let them model the cash flows for every loan using SQL and which would let them store the results in their SQL Server database. We developed the CDRCASHFLOW function which incorporates the techniques used in pricing and analyzing the cash flows for mortgage-backed securities (MBS).
The new function incorporates the flexibility of all our other loan amortization table-valued functions, so it supports bullets, balloons, interest-only, annuity payments, different interest and principal repayment schedules, monthly, quarterly, semi-annual, and annual (to name a few) repayment schedules, delayed start principal repayments, etc. However, what makes the function ideally suited for CECL reporting is that it incorporates Conditional Prepayment Rates (CPR), Constant Default Rates (CDR), and Loss Severity (LS) calculations. Each of these inputs can be entered either as a single value or a flexible schedule which changes over the life of the loan. To get more details on the actual calculation, you can just follow this link.
Since CDRCASHFLOW is a table-valued function you can use the function in any application environment that can open a connection to SQL Server: C#, VB.Net, F#, java, C++, etc. And since it runs in SQL Server, it's very easy to embed the calculation with your existing data without having to make any changes to your existing systems.
In our environment we test with 25,000 loans which produce 2,461,500 cash flows which are inserted into a table and the process take between 82 and 83 seconds.
We also thought it would be interesting to be able to calculate the internal rate of return and the discounted cash flows from the modeled cash flows so we have also included CDRCashFlowIRR and CDRCashFlowDCF as functions. Of course, you can also just generate the cash flows and feed them into the existing XIRR and XNPV functions if you prefer.
You should download the free 15-day trial and see how you can incorporate XLeratorDB into your CECL reporting. If you're not a SQL Server user, you should try out the 15-day free trial of XLeratorDLL which includes these new function as well as the more than 240 financial functions in XLeratorDB/financial. If you have any questions or if there is other functionality that you would like to see added to XLeratorDB just send an e-mail to support@westclintech.com
