...
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"
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.
Example:
Let's assume MFI/Bank organization ABC has made $ 100,000 of loans to different individuals and companies. Though ABC MFI/Bank 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 MFI/Bank organization has to renegotiate.
MFI/Bank Organization ABC estimates that 1% of loans amount that is $ 1000 will never comeback. So this $ 1000 is ABC MFIorganization's /Bank's loan loss reserve and it records this amount as negative number on the asset portion of its balance sheet. If If and when MFI/Bank 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. The amount deducted from the loan loss reserve may be tax deductible for MFI/Bank organization ABC.
How to define a Loan Provision:
...
Based on in which category the loan belongs to MFI/Bank organization can reserve the amount based on his country regulations. For example
...
Description: This API will return the provision history data on which provision report is created.The request parameter parameters offset and limit are used for the pagination. Organization can select a particular date and can view the provision/generate the report. Please find the mock up screen below
...
| Code Block |
|---|
{
"summaryview=true",
"branchcategorywise=allfalse",
"branch=all",
"offset" : 0,
"limit" : 15,
} |
...
The summary view for all branches with category wise is shown below
Branch
wise summary and regular views are shown belowThe summary view for a selected branch is shown below (categorywise parameter is not used in case of branch view)
The non summary view for a selected branch , the provision details will be is shown as below
All branches provision details will be shown by category. The mock up screen (categorywise parameter is not used in case of branch view)
Non summary view of all branches is shown below
Loan Loss Provision Implementation in Mifos X:
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.
...
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
MFI Organization can choose each category one by one and can add it to the loan product. MFI 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 MFI organization can choose liability & expense accounts against each provision category differently,
...
| NAME | Datatype | Length | Remarks | |||
|---|---|---|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY & AUTOINCREMENT | |||
| 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 | 20NOT NULL | FOREIGN KEY acc_gl_account.id | |||
| expense_account | BIGINT | 20NOT NULL | FOREIGN KEY acc_gl_account.id | |||
| created_by | VARCHARBIGINT | 100 | app user name20 | FOREIGN KEY m_appuser.id | ||
| created_on | DATE | |||||
| modified_by | VARCHAR | 100 | app user name | 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 | Nazeer100 | 31-Dec-2014 | Vishwas200 | 10-Jan-2015 |
| 2 | 10345 | 2 | 30 | 60 | 20 | abcd account | dcba account | Nazeer200 | 31-Dec-2014 | Vishwas400 | 10-Jan-2015 |
| 3 | 10345 | 3 | 60 | 180 | 50 | abcd account | dcba account | Nazeer400 | 31-Dec-2014 | Vishwas500 | 10-Jan-2015 |
| 4 | 10345 | 4 | 180 | 360 | 90 | abcd account | dcba account | Nazeer300 | 31-Dec-2014 | Vishwas600 | 10-Jan-2015 |
While editing the loan product, persisted provision details will be restored in the edit loan product screen
...
1) Under System-> Manage Scheduler Jobs user , organization can find the loan loss provision job. This job will run at customer defined intervals.
...
After calculating the provision amounts for all active loans, the data will be saved into 'acc_gl_journal_entry'.
2) User Organization can generate provisioning data manually under accounting section as shown in below mock up screen
...
| 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 | |
| journalentryjournal_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:
...