Overview
A Loan provisioning is an expense that is reserved for default/bad performing loans/credits. It is an amount set aside in the event of that loans defaults. or It is an expense set aside as an allowance for bad loans or credits. Loan loss provision is also known as the "Valuation Allowance" or "Valuation Reserve". It is also known as "Allowance for Loan Loss"
Background and strategic fit
In any group of loans, banks (used to refer to all financial institutions) expect there can be some loans that do not perform as expected. These loans may be delinquent on their repayments or default the entire loan. This can create a loss to the bank on expected income. Therefore, banks can set aside a portion of the expected repayments from all loans in its portfolio to cover all or a portion of the loss. In the event of loss or in critical situations banks can use the amount set aside to cover the loss instead of taking a loss in its cash flow. This loan loss reserve acts as an internal insurance fund.
Let's assume organization ABC has made $ 100,000 of loans to different individuals and companies. Though ABC organization works very hard to ensure that it lends to only those people who can repay the total amount on time, naturally some may default, some may fall behind and for some ABC organization has to renegotiate.
Organization ABC estimates that 1% of loans amount that is $ 1000 will never comeback. So this $ 1000 is ABC organization's loan loss reserve and it records this amount as negative number on the asset portion of its balance sheet. If and when organization ABC decides to write all or a portion of a loan off, it will remove the loan from its asset balance and also remove the amount of the write-off from the loan loss reserve
Requirements/User Stories
| # | Title | User Story | Importance | Notes |
|---|---|---|---|---|
| 1 | Associate loan provisioning to a loan product | As a user I want to enable loan loss provision and should be able to add loan provision categories while creating a loan product | Must Have | |
| 2 | Update provisioning categories for a existing loan product | As a user I should be able to update loan provision categories for a given loan product | Must Have | |
| 3 | Manual provisioning trigger | As a user I should be able to generate provision data manually on a selected date | Must Have | |
| 4 | Different reports for provisioning data | As a user I want to see the loan loss data by branch, provision categories wise for all loan products | Must Have | |
| 5 | Batch job configuration | As a user I should be able to have loan loss provision generation for a given intervals | Must Have | |
| 6 | Provision history | As a user I should be able to see the history of the provision data | Must Have |
Business Rules
Any loan can be categorized into 4 types and the definition of these categories changes country to country. For example
- Standard: Good financial condition. The debtor is paying the principal and interest punctually without any dues.
- Sub-Standard: Some payments of principal and/or interest are overdue by 'x' amount of days or more.
- Doubtful Loan: Some payments of principal and/or interest are overdue as follows
- 'y' days or more in the case of a loan with an original term of less than 1 year
- 'z' days or more in the case of a loan with an original term of one year or more
- Loss: Some payments of principal and/or interest are overdue as follows
- 't1' days or more in the case of a loan with an original term of less than 1 year
- 't2' days or more in the case of a loan with an original term of one year or more
Based on in which category the loan belongs to organization can reserve the amount based on his country regulations. For example
- For the loans falls under Standard category no need to have any provision or management can take the decision to keep 5% of the loan amount aside
- For the loans falls under Sub-Standard category, the management can decide to keep 20% of the loan amount aside
- For the loans falls under Doubtful category, the management can decide to keep 50% of the loan amount aside
- For the loans falls under Loss category, the management can decide to keep 100% of the loan amount aside
Attributes
Please refer API (s) section below
| Attribute | Description | Notes |
|---|---|---|
Security and Permissions
No new permissions need to be defined
Mifos Functionality Enhancements
We need to create a table to keep all loan provision categories.The table name is 'm_provision_category''. The table definition is defined below. The category names will vary based customer to customer.
| Name | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | Primary Key & Auto Increment |
| category_name | VARCHAR | 100 | Not Null |
| Description | VARCHAR | 200 |
By default Mifos system will create and insert the following categories into this table based on customer requirements. So the data will look like below.
| id | category_name | Description |
|---|---|---|
| 1 | STANDARD | Punctual Payment without any dues |
| 2 | SUB-STANDARD | Principal and/or Interest overdue by x days |
| 3 | DOUBTFUL | Principal and/or Interest overdue by y days with 1 year original term (or) Principal and/or Interest overdue by z days with more than 1 year term
|
| 4 | LOSS | Principal and/or Interest overdue by t1 days with 1 year original term (or) Principal and/or Interest overdue by t2 days with more than 1 year term |
Now how to associate loan provision to a product? In create loan product screen, we have the option to enable loan loss provision as shown in mock up screen below
Organization can choose each category one by one and can add it to the loan product. Organization can choose minimum and maximum over dues in days and can provide provision percentage differently for each loan product. Both Liability & Expense combos will have all GL accounts. So organization can choose liability & expense accounts against each provision category differently,
Once the new loan product form is submitted, product loan provision details will be stored in 'm_loanproduct_provision_details'. The table definition is defined below.
| NAME | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY & AUTO INCREMENT |
| loanproduct_id | BIGINT | 20 | FOREIGN KEY |
| category_id | BIGINT | 20 | FOREIGN KEY |
| min_age | BIGINT | 20 | NOT NULL |
| max_age | BIGINT | 20 | NOT NULL |
| provision_percentage | INT | 4 | DEFAULT 0 |
| liability_account | BIGINT | 20 | FOREIGN KEY acc_gl_account.id |
| expense_account | BIGINT | 20 | FOREIGN KEY acc_gl_account.id |
| created_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| created_on | DATE | ||
| modified_by | VARCHAR | 100 | FOREIGN KEY m_appuser.id |
| modified_on | DATE |
For any given tenant, the provision details for a particular loan product will look like below.
| id | loanproduct_id | category_id | min_age | max_age | provision_percentage | liability_account | expense_account | created_by | created_on | modified_by | modified_by |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 10345 | 1 | 0 | 0 | 5 | abcd account | dcba account | 100 | 31-Dec-2014 | 200 | 10-Jan-2015 |
| 2 | 10345 | 2 | 30 | 60 | 20 | abcd account | dcba account | 200 | 31-Dec-2014 | 400 | 10-Jan-2015 |
| 3 | 10345 | 3 | 60 | 180 | 50 | abcd account | dcba account | 400 | 31-Dec-2014 | 500 | 10-Jan-2015 |
| 4 | 10345 | 4 | 180 | 360 | 90 | abcd account | dcba account | 300 | 31-Dec-2014 | 600 | 10-Jan-2015 |
While editing the loan product, persisted provision details will be restored in the edit loan product screen
Provision Calculation For Loan:
By using the following query we can get all active loan ids along with product id, overdue in days, outstanding loan balance, provision category id, provision percentage, due date
SELECT loan.id as loanid, loan.product_id as productid, GREATEST(datediff(CURDATE(),sch.duedate),0) as numberofdaysoverdue, loan.total_outstanding_derived as outstandingbalance, lpc.category_id, lpc.provision_percentage, sch.duedate FROM m_loan_repayment_schedule as sch LEFT JOIN m_loan as loan ON sch.loan_id = loan.id LEFT JOIN m_loanproduct_provision_category lpc ON loan.product_id=lpc.product_id and lpc.min_age <= GREATEST(datediff(CURDATE(),sch.duedate),0) and GREATEST(datediff(CURDATE(),sch.duedate),0) < lpc.max_age WHERE loan.loan_status_id=300 and sch.principal_amount > 0 and sch.completed_derived=false and lpc.category_id > 0 and lpc.provision_percentage > 0 GROUP BY sch.loan_id HAVING sch.duedate=min(sch.duedate)
The result of the above query will look like below (mock data).
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate |
|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 |
The charge calculation for above data is shown below.
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate | Amount to be reserved |
|---|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 | 557.500000 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 | 1017.900000 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 | 13040.000000 |
As current journal entry is not sufficient for provision reports, we need to introduce a new table in the database. The table name is 'm_loan_provision_entry' and the table definition is shown below.
| Name | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY and AUTOINCREMENT |
| currency_code | BIGINT | 20 | FOREIGN KEY m_currency.id |
| office_id | BIGINT | 20 | FOREIGN KEY m_office.id |
| product_id | BIGINT | 20 | FOREIGN KEY m_product_loan.id |
| category_id | BIGINT | 20 | FOREIGN KEY m_provision_category.id |
| overdue_in_days | BIGINT | 20 | DEFAULT 0 |
| amount_to_be_reserved | BIGINT | 20 | DEFAULT 0 |
| created_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| created_on | DATE | NOT NULL | |
| modified_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| modified_on | DATE |
Whenever the provision calculation is done, system is going to make an entry in the 'provision_history' table. The structure of the table is shown below
| Name | Datatype | Length | Description |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY & AUTO INCREMENT |
| created_on | DATE | NOT NULL | |
| journal_entry_created | INT | 1 | DEFAULT 0 |
| created_by | BIGINT | 20 | m_appuser_id or scheduler job dummy id |
| created_on | DATE |
Changes required in existing Classes:
- While creating any loan product template, provision categories should be added to the response object. For this
- org.mifosplatform.portfolio.loanproduct.data.LoanProductData should have one more instance variable to hold provision categories
- New interface org.mifosplatform.organisation.provisioning.service.ProvisionCategoriesReadPlatformService should have a method to retrieve all provision categories.
- The method name is List<EnumOptionData> retrieveProvisionCategories() ;
- Provision Categories should retrieved and set to LoanProductData in org.mifosplatform.LoanProductsApiResource.handleTemplate(final LoanProductData productData)
- Client application should read response param "provisioncategoies" if available it should display provision categories as shown in create product mock up screen
- While creating a loan product, the provision categories along with min age, max age, percentage to provision should be read and saved into DB. For this
- New entity class org.mifosplatform.organisation.provisioning.domain.LoanProductProvision for 'm_loanproduct_provision_category' should be added
- org.mifosplatform.portfolio.loanproduct.domain.LoanProduct should have set of LoanProductProvision
- Client application should send loan product provision categories as part of the request params while creating a loan product. The parameter name is 'loanproductprovisons'
- Provision categories should be read and added in org.mifosplatform.portfolio.loanproduct.domain.LoanProduct.assembleFromJson(...)
- While retrieving a loan product (s), we need to retrieve the provision categories and associate with the Loan Product object (s). For this
- org.mifosplatform.portfolio.loanproduct.service.LoanProductReadPlatformServiceImpl.retrieveLoanProduct(Long loanProductId) should be modified to associate provision categories
- org.mifosplatform.portfolio.loanproduct.service.LoanProductReadPlatformServiceImpl.retrieveAllLoanProducts() should be modified to associate provision categories
- org.mifosplatform.organisation.provisioning.service.ProvisionCategoriesReadPlatformService should expose a method to retrieve the provision categories based on loan product id
- While updating a loan product (s), we need to update the provision categories associated to that loan product (s). For this
- org.mifosplatform.portfolio.loanproduct.service.LoanProductWritePlatformServiceJpaRepositoryImpl.updateLoanProduct(...) should be modified to validate and update loan product provision categories
New Screens
- New screen "Provisioning History Screen" should be added to show list of provisioning history data
- New screen "Provisioning Screen" should be added to show different provisioning data for a selected date
Changes to Existing Screens
- Create loan product screen should be enhanced to capture loan provision categories along with minimum age, maximum age, provision percentage, liable account, expense account
Changes/Enhancements to Batch Jobs
A new batch job should be defined to generate provisioning data. The suggested interval is 90 days once. This batch job should perform following tasks
- Calculate provisioning amount for all products branch wise and provisioning category wise
- Create journal entries to define the loss amount and amount to be provisioned
Changes/Enhancements to Accounting Entries
To differentiate with provision entries, we need to add another portfolio product type
PROVISIONING(2, "productType.provision")
User interaction and design
Associating Loan Loss Provision categories to a loan product while creating:
Batch job execution for Loan Loss Provisioning:
Manually Triggering Loan Loss Provisioning:
Provision details preview screen in manual creation:
Provisioning history screen:
Organization provisioning summary view:
Organization provisioning summary view by provisioning category:
Branch provisioning summary view:
Branch provisioning view by product:
Organization provisioning view by branch, product and provisioning category:
Exception Handling
Reporting
APIs
1) http://serveraddress:port/mifosng-provider/api/vi/loanprovision/list
optional request parameters for GET request
{"offset" : 0,"limit" : 15,} |
Description: This API will return the provision history data on which provision report is created.The request parameters offset and limit are used for the pagination. Organization can select a particular date and can view the provision/generate the report.
2) https://serveraddress:port/mifosng-provider/api/v1/loanprovisioning
Optional parameters for Create request
{ "createjournalentries" : true, "date" : true, "recalculateprovisioning": true } |
Description: This API should be used to generate loan loss provision entries. Date request parameter indicates on which the provision entries should be created. If createjournalentries parameter is true, then loan loss provision details will be added into journal entry. Before creating new journal entries, it will reverse previous journal entries if they present. If createjournalentries is false then it will not create the journal entries. If recalculateprovisioning true, then it will delete previous provision entries if present and it will create new provision entries. If recalculateprovisioning is false, it will return previous provision entries if present.The request parameters offset and limit are used for the pagination. This method will return list of provision entries in JSON data format
Optional parameters for Get Provisioning details
{ "summaryview=true", "categorywise=false", "branch=all", "offset" : 0, "limit" : 15,}Notes
Questions
Below is a list of questions to be addressed as a result of this requirements document:
| Question | Outcome |
|---|---|
Out of Scope
- We can provide a GUI in client to add provisioning categories into this table which is not covered in this specification.