...
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
...
Attribute | Description | Notes |
---|---|---|
Security and Permissions
No new permissions need to be defined
Mifos Functionality Enhancements
...
Name | Datatype | Length | Remarks |
---|---|---|---|
id | BIGINT | 20 | Primary Key & Auto Increment |
category_name | VARCHAR | 100 | Not Null |
Description | VARCHAR | 200 |
By default Mifos X 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 |
Note: We can provide a GUI in client to add these categories into this table which is not covered in this specification.
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
...
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
Code Block |
---|
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).
...
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
...
- 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
- Provisioning Screen
Changes to Existing Screens
- Loan Product ScreenNew 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.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
Code Block |
---|
PROVISIONING(2, "productType.provision") |
User interaction and design
...
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.