...
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 MFI's/Bankorganization'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
...
Loan Loss Provision API(s)
1)
...
http://serveraddress:port/mifosng-provider/api/vi/
...
loanprovision/
...
list
Optional optional request parameters for Create GET request
| Code Block |
|---|
{
"createjournalentriesoffset" : true0,
"datelimit" : true,
"recalculateprovisioning": true
15,
} |
Request Parameters: "Date:date on which report to be generated, createjournalentry=true/false, regenerateprovisions:true/false, offset=number, limit=number", Method: GET
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 createjournalentry parameter is true, then 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. Please find the mock up screen below
2) https://serveraddress:port/mifosng-provider/api/v1/loanprovisioning
Optional parameters for Create request
| Code Block |
|---|
{
"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 createjournalentry createjournalentries is false then it will not create the journal entries. If regenerateprovisions recalculateprovisioning true, then it will delete previous provision entries if present and it will create new provision entries. If regenerateprovisions 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
| Code Block |
|---|
2) http://serveraddress:port/mifosng-provider/api/vi/loanprovision/list
Request Parameters; "offset=number, limit=number', Method: GET
Description: This API will return the provision data on which provision entries are created.The request parameter offset and limit are used for the pagination. This method will return list of provision available data. MFI can select a particular date and can see the provision entries by using https://serveraddress:port/mifosng-provider/api/v1/loanprovision API. Please find the mock up screen below
...
{
"summaryview=true",
"categorywise=false",
"branch=all",
"offset" : 0,
"limit" : 15,
} |
The summary view for all branches is shown below
The summary view for all branches with category wise is shown below
The 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 is shown as below (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:
...
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 | 100app user name | 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_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 | DEFAULT 0 |
| loan_outstanding_balance | BIGINT | 20 | DEFAULT 0 |
| amount_to_be_reserved | BIGINT | 20 | DEFAULT 0 |
| created_byFOREIGN 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 | FOREIGNPRIMARY KEY & AUTO INCREMENT | |
| created_on | DATE | NOT NULLmodified | ||
| journal_entry_created | INT | 1 | DEFAULT 0 | |
| created_by | BIGINT | 20 | FOREIGN KEY | modifiedm_appuser_id or scheduler job dummy id |
| created_on | DATE |
Changes required in existing Classes:
...