Interest Amount Recalculation for Loan Accounts
- Binny Gopinath Sreevas
- Pramod Nuthakki
Overview
As an Operations Manager, I want to define a loan product that supports interest recalculation.
When repayments are on time and for the exact due amount always, then there is usually no need for interest recalculation.
However, when repayments are either not on time or not for the due amount, then the following scenarios need to be handled:
a) Exact Due Amount is paid and
- is paid Late
- is paid Early
b) Only part of the Due Amount is paid and:
- is paid On Time
- is paid Late
- is paid Early
c) Excess of Due Amount is paid and:
- is paid On Time
- is paid Late
- is paid Early
For such scenarios, the exact interest amount calculation depends on following factors:
a) Days Calculation Basis:
- Actual
- 30 – here number of days will be computed as (Y2-Y1)*360 + (m2-m1)*30 + (d2-d1)
d1 and d2 will be treated as 30 if they are > 30
b) Number of days in a year:
- Actual
- 360
- 365
- 364
c) Rest or Frequency at which outstanding principal is recalculated – may be daily, weekly, fortnightly or monthly
At the end of every business day a batch job (Loan Interest Recalculation) will kick off which will check all active accounts for which interest recalculation is set to "true". The job then checks if there the interest calculated vs interest specified in repayment schedule matches. If yes,
Background and strategic fit
Many financial institutions have the need to re-compute interest based on actual date of repayment. This feature is a must when Mifos needs to support operations of a commercial bank.
Requirements/User Stories
| # | Title | User Story | Importance | Notes |
|---|---|---|---|---|
| 1 | Define Loan Product | As an Operations Manager, I want to define a loan product that supports interest recalculation | Must Have | |
| 2 | Create Loan Account | As an Loan Officer, I want to create a loan account that supports interest recalculation | Must Have |
Business Rules
A Daily Rest may be specified for loans of any frequency and the days in year may be:
- 365 – for all repayment frequency
- 364 – for weekly or fortnightly repayment loans
- 360 – for monthly repayment loans
A Weekly Rest may be specified for loans that are repaid weekly or fortnightly and the days in year may be 365 or 364
A Fortnightly Rest may be specified for loans that are repaid fortnightly and the days in year may be 365 or 364
A Monthly Rest may be specified for loans that are repaid Monthly and the days in year may be 365 or 360
These rules will not be implemented in the Mifos X code. But this is expected to be followed defining loan products and loan accounts. Mifos will be tested only in the above scenarios for consistent interest amount calculations.
Interest Amount Calculation will be done as follows:
Interest = Principal * Annual Interest Rate * Days Calculated / Number of Days in a Year
- Principal – Principal as per the Rest Specified
- Days Calculated – based on “Days Calculation Basis”
Other rules that will be followed:
a) When interest recalculation is turned on for a loan product, then any excess amount (over what is currently due or overdue) will be applied to the outstanding principal always. In this respect, it will override the settings for Repayment Strategy selected.
b) Any interest compounding will stop if any amount is overdue more than the number of days for the account to be treated as Non-Performing Asset (overdue_days_for_npa).
c) If there is a disbursement-time fee, which is calculated on principal+interest - then this fee amount will not be recomputed based on interest recalculations.
d) If any interest (or fee) was waived on an account - and the interest (or fee) changes due to the recalculations - then the waived amount will not be automatically changed by Mifos.
Example:
Total Interest dues: Apr-14 - 300, May-14 - 250 and Jun-14 - 200
Interest Waived = 300 (i.e. entire Apr-2014's interest is waived)
Due to interest recalculation (due to a back-dated transaction), the interest amounts now change to: Apr-14 - 250, May-14 - 200 and Jun-14 - 150
The waived amount remains as 300 (i.e. April's interest of 250 is waived and from May's interest - 50 is waived. If other behavior is needed, then user has to reverse the earlier waiver and then apply interest recalculation and then re-do the waiving with new amount).
The only situation when Mifos will automatically change the waived amount is when the total recalculated interest (or fee) due is less than the waived amount. In such a case, the waived amount is reduced to the recalculated amount.
e) If a fee is already paid and due to the recalculations if the fee is reduced (or increased), then the fee paid transaction is not altered. Any excess fees already paid in can be refunded at the end of the tenure of the loan (as then it will show that the account is overpaid). If there is an increase in fee due to the recalculations, then the additional fee can be collected.
Attributes
For Loan Products and Loan Accounts
(Note: For Loan Accounts – all the below 4 attributes are inherited from Loan Product and cannot be modified for each account)
| Attribute | Description | Notes |
|---|---|---|
| Days In Year | Number of days to be considered for a year when calculating interest. Can be: Actual 365 360 364 | Not Null Default is "Actual" |
| Recalculate Interest? | For this loan product, if interest is to be recalculated when early or late payments or exact amount is not paid (True or false) | Checkbox – Nullable Default is Null |
| Frequency at which Outstanding Principal is recalculated | Rest or Frequency at which outstanding principal is recalculated – Same as Repayment Frequency Daily Weekly Fortnightly Monthly | Dropdown - Nullable Default is Null |
| Days Calculation Basis | Basis for calculation of number of days between the start and end dates Actual 30 | Default is Actual |
Security and Permissions
No new security considerations or permissions for this feature
Mifos Functionality Enhancements
- Loan Products to be enhanced to capture above 4 attributes
- Loan Accounts to be enhanced to capture above 4 attributes
- When make payment screen is submitted – then system checks if the payment was on time and was for the exact amount. If not, then the current and future installment and interests are re-calculated for the loan as described in Business Rules.
- New Batch job to be introduced for Daily Interest Amount Calculation for Loans
New Screens
New Screen will capture "Principal Pre-payment" details. This screen will be similar to "Make Repayment" screen. This screen will used to make a pre-payment on principal.
Fields to be captured:
- Transaction Date
- Principal Prepayment Amount
- Principal Outstanding (Read-only field)
- Interest Outstanding (Read-only field)
- Payment Type
- Payment Details
- Notes
This button will be enabled only for products where "Recalculate Interest?" is set to "true". The future interest is recalculated and repayment schedule is regenerated with revised interest and principal repayments when this screen is submitted. A preview needs to be shown with revised repayment schedule before this screen is submitted.
The older version of the repayment schedule needs to be saved so that it can be viewed, when needed.
Changes to Existing Screens
- Loan Products screens to be enhanced to capture above 4 attributes
- Loan Accounts screens to be enhanced to capture above 4 attributes
- Loan Account screen to be enhanced to have a new "Principal Pre-payment" button
- Make Payment Screen – interest amount calculation and application of principal changes (see Business Rules for calculations)
Changes/Enhancements to Batch Jobs
New Batch job to be introduced for “Interest Re-Calculation for Loans” – For all loans with overdue repayments, the current and future installment and interests are re-calculated for the loan as described in Business Rules.
This batch job will select
a) all loans for which "Recalculate Interest?" is set to true and where either a transaction has happened or a repayment is due on the current date - for such loans
b) all loans which are past their last repayment date and still there is principal outstanding. The job will calculate daily interest for outstanding principal and will keep adding daily interest to thee interest portion of the last installment.
- To be verified: If “Loan Summary” job needs to be modified
- To be verified: If “Update Loan Arrears Aging” job needs to be modified
Changes/Enhancements to Accounting Entries
No changes
User interaction and design
TBD
Exception Handling
TBD
Reporting
No new reports or changes to existing reports needed
APIs
APIs to be enhanced for including the above fields for loans products and loan accounts.
Notes
All existing loans to have the following default value during upgrades:
- Days in Year – Actual
- Recalculate Interest? – checkbox - Null
- Rest or Frequency at which outstanding principal is recalculated – Null
- Days Calculation Basis – Actual
Database changes
1. Modifications to Loan Product (“m_product_loan”)
| Column Name | Data Type | Description |
|---|---|---|
| days_in_month_enum | int | No of Days should be considered in loan calculation Actual or 30 days |
| days_in_year_enum | int | No of Days should be considered in loan calculation Actual or 360 or 365 or 364 |
| interest_recalculation_enabled | tinyint | Boolean flag indicates is interest recalculation required if payment not done as per the schedule. |
2. Modifications to Loan Account(“m_loan”)
| Column Name | Data Type | Description |
|---|---|---|
| days_in_month_enum | int | No of Days should be considered in loan calculation Actual or 30 days |
| days_in_year_enum | int | No of Days should be considered in loan calculation Actual or 360 or 365 or 364 |
| interest_recalculation_enabled | tinyint | Boolean flag indicates is interest recalculation required if payment not done as per the schedule. |
3. Interest Recalculation settings (m_product_loan_recalculation_details)
| Column Name | Data Type | Description |
|---|---|---|
| product_id | bigint | ref m_product_loan |
| compound_type_enum | int | indicates compounding strategy
|
| reschedule_strategy_enum | int | indicates which reschedule strategy to be used on prepayment
|
3. Interest Recalculation settings (m_loan_recalculation_details)
| Column Name | Data Type | Description |
|---|---|---|
| loan_id | bigint | ref m_loan |
| compound_type_enum | int | indicates compounding strategy
|
| reschedule_strategy_enum | int | indicates which reschedule strategy to be used on prepayment
|
Use cases:
1. identify recalculation : check the payment against schedule and should process recalculation in following scenarios
- late payment, should be identified by a batch job
- back dated entry
- pre-payment
- on time with advanced-payment / less payment(ex : on 3rd should pay 1000 but the payment is done for 500 or 1500)
2. process recalculation :
- identify outstanding principal
- identify amount for interest compound
- regenerate schedule based on outstanding and rest frequency(from calendar instance)
- add the extra interest(in-case of late payment ) to next installment
- for last installment, will add extra installments with interest component only
- if prepayment is done then use one of the strategy to reschedule.
- adjust EMI amount
- adjust loan duration
- adjust next installments
3. Capture new fields for loan product and account(editable only from product level but will maintain a copy at account level)
4. Add new screen for pre-payment(internally same as repayment but will return recalculated interest by setting closure date as today)
5. change schedule generator to use newly added days in year and days in month values
Questions
Below is a list of questions to be addressed as a result of this requirements document:
| Question | Outcome |
|---|---|