Release	Elsie F
Current Owners:	Tejus Datta, Surabhi Choubey
Status (Draft, In Review, Approved)	In Review

Key Notes

Some UI/mockups and wording may change, functionality will remain the same.

Setup and Installation

New Installations

New Installations	Yes/No	Comments
Does this feature require both Mifos Business Intelligence Suite and Mifos Product?	NO
Does this feature require special work for hosting? (sys admin)	NO

Backward Compatibility and Upgrades

Backward Compatibility and Upgrades	Yes/No	Comments
Is there any data conversion that needs to be done as part of an upgrade?	NO
Will customers lose data or will the way existing data is stored change significantly?	NO
Will another feature, workflow or portion of the data model be deprecated as a result of this new feature?	NO
Will existing role permissions be changed or impacted by this feature? If so provide details in the security section.	NO
Will existing customers need to learn a new UI process or change the way they use the system as a result of this new feature?	YES	They will need to enter the client cash flow and also enter the loan installment amounts.

Hosting Support

Hosting Support	Yes/No	Comments
If different user groups are using the same database, are there concerns over the sharing of data related to the feature?	NO
Are there expected to be performance related issues with having many customers sharing the same hardware in support of this feature?	NO

Configuration

Configuration	Yes/No	Comments
Does this feature require changes to configuration files?	Yes
If so, is this feature enabled or disabled by default?	Yes	Enabled only for Declining Balance interest type
Are existing configuration properties used to control this feature? If yes, which ones?	No
Are new configuration properties added for this feature?	Yes	Minimum allowed cashflow warning threshold. Maximum allowed cashflow warning threshold. Minimum allowed Repayment Capacity. Maximum allowed Repayment Capacity. Minimum allowed Indebtedness Rate. Maximum allowed Indebtedness Rate.

Key Functional Areas Impacted

The following are the functional areas of Mifos where changes are required to
accommodate Variable Loan Installments

1.Loan Product Definition.
2. New Loan Account Creation.
3. Loan Disbursal. (No UI Change, only backend changes to accommodate variable
schedule)
4. Payment Application. (No UI Change, only backend changes to accommodate
variable schedule)
5. Adjustments. (No UI Change, only backend changes to accommodate variable
schedule)
6. Pre payment. (No UI Change, only backend changes to accommodate variable
schedule)

This spec is intended to articulate the functional needs with regards to the Variable Installment Loan functionality along with some important use cases.

Loan Product Definition

In addition to the current parameters of loan product definition, the following changes are needed on New Loan Product creation:

Ability to specify loan product as variable installment product.
For loan products that are marked as variable installment product, the user can in addition specify the following:
1. Minimum Gap between Installments (In days).
2. Maximum Gap between Installments (In days).
3. Minimum Installment Amount.
Have the option to compare installments with customer Cash Flow.Cash flow comparison feature will be available for both variable installments loan products and otherwise.
1. For products that will allow comparison of installments with Customer Cash Flow,
2. A warning percentage can also be specified which will be used to alert a user if the loan installment is above a certain percentage of the cumulative cash flow.
3. Ratios Repayment Capacity and Indebtedness Rate can be specified which will be used to determine if the loan should be allowed to be created.

Interest rate types applicable for variable installment loans

A loan product can be configured as a variable installment loan only when a ‘declining balance interest rate type’ is applied towards it. This means that when the LSIM feature is ON and the selected interest rate type is other than ‘declining’, ‘can configure variable installment’ checkbox will get disabled.

New Loan Creation

Cash Flow capture

Based on the loan disbursal date, the number of installments and the frequency of installments, the customer’s cash flow needs to be captured based on the below mentioned logic (Provided the product is setup to compare Cash Flow).

Arrive at the list of all the months that the client installments fall on based on available information. This can be achieved by identified unique months for all the installment dates in the schedule.

Expenses, revenues and notes are captured for:

1) All the months which fall between the first and the last installments, including the first and the last installment months

2) One month before the month in which the first installment falls, and, (The original reason for capturing cashflow from one month before the first installment month is to allow the MFI to enter cash in hand thus far leading into the installment month. Then on the disbursal month, the loan amount is added to the the cumulative cash flow for comparison)

3) One month after the month the last installment falls in. (This is done to allow the MFI to see what sort of an impact loan payment will have on the customer. If the MFI notices that there is a sudden drop of cash of the customer into the month right after the loan payment, then it may lead to a situation where the customer may default, which is why the cash flow for the following month is captured)

Example 1

Disbursal date: 26-Aug-2010

No. of Installments: 3

Frequency of Loan Installments: 1 month

Months: September, October and November.

Cash flow months will be captured for months : August, September, October, November, December.

Example 2

Disbursal date: 26-Aug-2010

No. of Installments: 8

Frequency of Loan Installments: 2 Weeks

Months: September, October, November and December.

Cash flow months will be captured for months : August, September, October, November, December, January.

In addition, the system should also capture the Total Capital and Total Liability. These values will allow for comparison with the ratios that are setup during product creation to determine whether to allow loan creation or not.

Loan Schedule Review

While reviewing the schedule for a variable installment loan, the user should be allowed to perform the following operations:

1.Edit the Installment Dates.
2. Edit the Installment Amounts of all installments except for the last installment. It is important that the system restricts the installment amount from being any lesser than either the sum of Fee and Interest for a period or the Minimum Installment Amount specified during product creation, whichever is greater.
3. Validate the entered dates and amounts for various system constraints such as minimum gap, maximum gap, minimum amount for each installment.
4. If the variable installment loan is to be compared against customer’s cash flow, then an additional section listing the details of the cash flow is to be shown on the review screen.
5. Further, a validation should be done to warn the user if installment amount for any period exceeds the cumulative cash flow for the same period by over the warning percentage (set during product definition).

Indebtedness Rate and Repayment Capacity Calculations

The system should not allow to proceed with the Loan Creation if Indebtedness rate is greater than the allowed value and the Repayment Capacity is less than the threshold.

Indebtedness Rate and Repayment Capacity for a customer are calculated as follows:

Example - Indebtedness Rate

Indebtedness Rate= (Total liability + loan amount)*100/Total capital

During loan product definition, the indebtedness rate has been set as 100.

Lets assume a Rs.1000 loan is being applied for.

On the cash flow screen during loan creation, the user enters the total liability as Rs.5000. He also enters the total capital he has as Rs.4000.

Therefore the Indebtedness rate will be 150.

This will prompt the user with an error message suggesting that the client's indebtedness rate is greater than the permissible limit.

Example 2 - Repayment Capacity

Repayment Capacity=[(Total revenues-Total expenses)+Loan Amount]*100 / Sum of Installments amount

During loan product definition, the Repayment Capacity limit has been set as 150.

Lets assume a Rs.1000 loan is being applied for.

On the cash flow screen during loan creation, the user enters the monthly revenue and expenses.

Lets assume that the sum of all expenses enteres is Rs.5000 and the sum of all revenues entered is 10000.

Also lets assume the sum of all installment amounts is 1500.

Therefore the Repayment Capacity will be 400.

On the review schedule page, the user will be allowed to go ahead with loan creation as the calculated repayment capacity is over the required minimum repayment capacity of 150.

Repayment Schedule generation

Consider the following example to better understand the schedule generation for variable installment loans.

Disbursal Date: 1-Jan-2011

1st Installment Due Date: 1-Feb-2011

Loan Amount: 1000

Rate of Interest: 24% APR

The interest for the first installment will be calculated as follows:

Interest for installment i = Principal outstanding X Daily rate of interest X No.of days

= 1000 X 24/365 X 31

Similarly the interest for all further installments are calculated in similar fashion.

The key difference here from Declining Balance products is that in variable installments declining balance on daily rate of interest is used where as in typical declining balance, the interest is calculated based on the frequency of the loan (monthly or weekly).

NOTE:

I believe the formula for declining to be something as follows:

interest due for period = (principal outstanding / 100 * periodic interest rate) * number of periods

periodic interest rate = (APR/number of periods in year)

Monthly example of 'Declining' method

12% interest rate
Monthly installments
12 months in year
=> periodic interest rate = 12%/12 = 1% per month

				1000
1000	10	250	260	750
750	7.5	250	257.5	500
500	5.0	250	255.0	250
250	2.5	250	252.5	0.0

12% interest rate
installments in duration of days
365 days in year
=> day periodic interest rate = 12/365 = 0.032876% per day
=> interest due for one day = 1000/100 * 0.032876 = 0.32876
=> interest due for period (31 days) = 1000/100 * 0.032876 * 31 = 10.19178

				1000
1000	10.2	250	260.2	750
750	7.4	250	257.4	500
500	5.0	250	255.0	250
250	2.5	250	252.5	0.0

Weekly example of 'Declining' method
1000

10% per annum
52 weeks in year
duration of installment = 1 week
=> periodic interest rate = 10%/52 = 0.19230769% per week
=> first weekly installment interest: 1000 /100 * 0.19230769 = 1.92

				1000
1000	1.92	250	251.92	750

etc

365 days in year
duration of installment = 7days
=> periodic interest rate = 10%/365 = 0.02739726% per day
=> first weekly installment interest: 1000 /100 * 0.02739726 * 7 = 1.917

				1000
1000	1.92	250	251.92	750

Does this look correct Tejus/Kay?

Edit payment schedule from loan account preview page

The user will have the ability to edit the payment schedule so that he can correct his mistakes while viewing the schedule on loan account preview page

Loan schedule will be allowed to be edited only during loan account creation, before loan APPROVAL. Editing a loan schedule would not be possible after the loan account has been approved. This means that when a loan has been submitted for approval, its loan schedule cannot be edited.

Apply Fee

Periodic fee will not be allowed to be applied for variable installment loans.

Also, for variable installment loans, since interest and principal are calculated dynamically based on the entered due dates and amounts, the following two types of fees cannot be applied :
1) Fee as a % of interest
2) Fee as a % of (interest + loan amount)

Other fees will continue to work as before.

Bulk Loan Creation

It will not be possible to use this feature with variable installment loans.

Calculation of Arrears

Arrears calculation will continue as before, where the lateness definition is taken into account before a loan is marked as Active in Bad Standing.

Grace Period

Grace period is specified in terms of number of installments. Grace period for a variable installment loan fetches the installment duration from the frequency of installment specified at loan product level.

For e.g.: If for a variable installment loan product, frequency of installments = 1 week.

Grace period= 2 installments.

Then, only after the completion of 2 installments of one week each will the loan move to active in bad standing.

Edit Loan Product

When a loan product configured for variable installments is edited, it can be made as a non-variable installment loan product and vice versa. The changes made will be visible in all the future loan accounts created for that loan product. Existing accounts are not affected.

Loan Disbursal

During Loan Disbursal, it may happen that the disbursal date is changed. This will result in the loan schedule to be moved forward while maintaining the gap between various installments.

Apply Payment

The payments will get applied the way currently mifos applies payments.

Make Adjustments

Adjustments will work as they are currently in mifos. The variable installment schedule will be retained upon making the adjustment.

Repay Loan

The repayment amount is to be calculated, as it exists for all other types of loan.

Holidays

Introduction of holidays while a loan is active will not result in interest recalculation.

Holidays will be considered during loan account creation and during disbursement as per current mifos behavior.

Moratoriums

If due dates of an variable installment loan overlap with moratorium dates, the entire schedule should shift. Installment due date (which overlapped with the moratorium date) should shift to the next available repayment date. The gap between the subsequent installments should be maintained. Interest will continue to be the same as the original loan schedule.

Example 1

Suppose a loan has 3 installments on 25th Sep, 25th Oct and 25th Nov. A moratorium is defined from 14th Oct to 26th Oct.

Because of the moratorium , the schedule should change to : 25th Sep, 25th Nov, 26th Dec. Interest will continue to be the same as the original loan schedule.

If due dates of a variable installment loan fall within moratorium period but do not clash with moratorium dates, the schedule will not shift. None of the installment due dates should shift. Interest will continue to be the same as the original loan schedule.

Example 2

Suppose a loan has 3 installments on 25th Sep, 25th Oct and 25th Nov. A moratorium is defined from 14th Oct to 16th Oct.

The schedule will remain the same since moratorium dates do not overlap with installment due dates.

The above conditions hold true regardless of when the moratorium was defined and the state the loan is in.

Redo Loan

While redoing a variable installment loan, cash flow comparison will not be done even if the loan is setup to have cash flow comparison during loan creation.

This is applicable to any type of loan and not just limited to variable installment loans.

The following fee types are not allowed for application while redoing an agricultural loan:

1) Loan fee as a % of Interest

2) Loan Fee as a % of (Interest+Loan Amount)

3) Periodic Fee

Viewing the original loan schedule

There may be a need for a user to view the original schedule of a loan as created on the disbursal date. To view such schedule the user should go to View repayment
schedule page from the loan details page, then click on the view original schedule link on the right corner

This view will be available for all loan types

Collection Sheet

Collection sheet can be used the same way as other loan types. No additional development work is required to be done in this area.

Other Standard Considerations

Security

Security (Permissions, Roles, and Data Scope)	Yes/No	Comments
Does the user need to be in a particular user hierarchy to use this feature?	NO
Does the office hierarchy affect use of this feature?	NO
Are you using any existing permissions to control this feature?	NO
Are you adding any new permissions or changing existing permission to control this feature?	NO
Are you using any existing activities to control this feature?	NO
Are you adding any new activities or changing existing activities to control this feature?	NO
Are there any special considerations for upgrade scenarios? What will be the default value for new permissions?	NO
What will be the default values for default roles in a new installation?	NA

Impacts to System

Impacts to System	Yes/No	Comments
Does this feature affect Bulk Loan Creation? How?	NO
Does this feature affect Collection Sheet Entry? How?	NO
Does this feature affect Redo Loans?	YES	During Redo loan, the gap between installments will decide the interest calculation. Due dates can be edited during Redo Loan for Variable installment loans. Cash flow comparison will not be done during Loan Redo.
Does this feature affect Reverse Loans?	No
Is this feature affected by Holidays?	No

Globalization/Localization

Globalization/Localization	Yes/No	Comments
Will this feature support users localizing data that they enter?	YES
Does this feature involve any date/time related data, and if so how should conversions be handled?	YES	Date pickers are added for convenience. String translations are done so that localizations do not effect the storage of dates.
Is there currency or other numeric data ? If so does it require any special handling or validation? Is there a need for mult-currency? (Loan Accounts)	NO

Logging

Change Log

Change Log	Yes/No	Comments
Do changes to the data that is collected or stored by the new feature have to be fully logged by the system?	NO
Does the administrator configuring the system need the ability to turn on or off logging for this feature?	NO
Is the feature currently logged but the structure of the logged records changing?	NO

Reporting

Provide any relevant information about reporting requirements for the new features and answer the questions below, providing detail to explain any particular area when necessary.

Reporting	Yes/No	Comments
Does the feature affect any existing reports?	Yes	Collection Sheet Report?
Does the feature require adding any new reports?	NO

Performance

Performance	Yes/No	Comments
Will the feature be a high use-case scenario?	Yes
Will the feature have potential for high concurrency?
Does the feature include complex UI or data gathering logic that will be used by a significant portion of the user base?	NO	But it does include complex UI to be handled by professionals adept and trained with this feature.
Does the feature contain risks of database connection timeout?	NO
Will the feature contain any bulk insert/update/delete transactions?	NO
Will the feature contain any caching mechanisms or cache refreshing mechanisms?	NO
Could the feature result in a large amount of data being sent to the client or between the database and web server?	NO
Would users on a low bandwidth connection likely face issues with a part of this feature?	NO
Does the feature affect existing batch jobs or require adding any new batch jobs?	NO

Browser not supported