1 Introduction:
- 1.1 Example:
2 How to define a Loan Provision:
3 Loan Loss Provision API(s)
- 3.1 1) http://serveraddress:port/mifosng-provider/api/vi/loanprovision/list
- 3.2 2) https://serveraddress:port/mifosng-provider/api/v1/loanprovisioning
4 Loan Loss Provision Implementation in Mifos X:
5 Provision Calculation For Loan:
6 Changes required in existing Classes:

Introduction:

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 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. The amount deducted from the loan loss reserve may be tax deductible for organization ABC.

How to define a Loan Provision:

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

Loan Loss Provision API(s)

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. Please find the mock up screen below

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,
}

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:

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

Name	Datatype	Length	Remarks
id	BIGINT	20	Primary Key & Auto Increment
category_name	VARCHAR	100	Not Null
Description	VARCHAR	200

By default Mifos X 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

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

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

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	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

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

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

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

In Mifos X, there are two ways to trigger provision calculation for all active loans..

1) Under System-> Manage Scheduler Jobs, 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) Organization can generate provisioning data manually under accounting section as shown in below mock up screen

Mifos system, calculates provision date for all active loans and will generate a tabular view as shown below. Once user selects OK on this screen, this data will be saved into 'acc_gl_journal_entry table'

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

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

Loan Loss Provisioning