Disbursing Loans

Loan Payments

Introduction

When a loan officer returns from a meeting or otherwise receives a payment, he/she or a data entry clerk enters the collection details in Mifos and the system updates the relevant records. To record a payment, the user clicks the Apply payment link under Transactions on the loan account details page.

The system enters the current date for the Date of Transaction. The user enters the amount of the payment and, optionally, the receipt ID and date of receipt. When completed, the user previews the payment information and then saves it. Once the payment is recorded the payment is broken into principal, interest, fees, and penalty components, which are displayed in a table on the account details page. The account balance, amount paid and total amount remaining, and the next payment details are updated accordingly.

The loan account details page shows the total amount due on the next scheduled payment date. The user can click the View installment details link to see the total amount broken into the following components for both the current installment and overdue amounts: principal, interest, fees, and penalty.

Early total repayment of loan

After a loan is disbursed, a client can at any time repay the loan in a single payment. To record a total repayment, the user clicks the Repay loan link in the Transaction box on the loan details page. The system calculates the total amount due as of the current date, including principal, interest, fees and penalties, as follows:

• Principal. This is the sum of total principal due in unpaid, due and future installments.
• Interest. For flat and declining interest types, the interest for all future installments is not included, but the interest for all past unpaid installments and the current installment is included. For example, if the loan is for 12 months and is paid monthly and the client wants to repay the complete loan on her 4th installment date, the interest calculated is for the 4th month. If the client misses the 4th installment and wants to repay the loan in between the 4th and 5th installments, the interest for the 4th and 5th installments is included.

Note on holidays: In case a holiday is added after the disbursal of the loan and the original calculation of the repayment schedule, one installment can be shifted to the next repayment date. For example, payment on 1st Jan is shifted to 1st Feb. In this case, if an early repayment is done on or before 31st Jan, it will not consider the interest amount, which would have been due on 1st Jan if Jan 1st were not a holiday.

• Fees. Similarly to interest calculations, the fees due for all future installments is not included, but the fees due for all past unpaid installments and the current installment is included in the total due. Using the previous example, the fee calculated includes only the amount to be paid till the 4th installment, and the fees for the remaining 8 months are ignored. If the client repays the loan in between 4th and 5th installment, the total due will include the fees due for the 5th installment. 
• Penalty. This includes miscellaneous penalties and penalties due to late repayments, if any. The total includes miscellaneous penalties, both for the current installment and for those due through the last installment. Penalties due to late repayment are calculated as of the night previous to the total repayment.

The user specifies the mode of payment, and optionally the receipt ID and date for the total amount due. The system updates the account summary table with the amounts applied to principal, interest, fees and penalties and changes the account state to Closed – Obligation met.

 

Payment not equal to payment due

A client may at any time make a payment that is not in the amount due on a specific date. For example, a client may make a partial payment when she’s short of cash or an overpayment when she has a surplus. A user records these payments in the same way he/she records normal installment payments, by clicking the Apply payment link in the Transaction box of the account details page. However, in these cases, the user needs to modify the default amount shown for the payment to reflect the actual payment amount before previewing and submitting the payment.

The implications of payments that do not match the amount due are described in the following paragraphs.

Partial payments

A partial payment is first applied against the penalty due. If the amount exceeds the penalty, it is applied against the fee due, followed by the interest due and then the principal. The system updates the account activity and transaction history entries. If the user clicks the View repayment schedule link, he/she sees that the partial payment is reflected in both the Installments paid and Installments due sections of the table. The Date paid for an installment is displayed only when the entire installment has been received.

Primary Flow- Partial Payment

1. User clicks on “Apply Payment” link in loan details page.
2. User modifies the default amount and enters an amount less than the expected amount.
3. User clicks on “Review Transaction”.
4. System validates that the date of transaction is between the last payment date and current date.
5. System validates that the amount entered is not greater than the total amount outstanding on that account.
6. System displays the Preview page with the information entered.
7. User clicks on “Submit” to save the transaction.
8. System submits the amount received. The amount is first applied against the penalty due. After penalty payment, if there is an excess, the amount is applied to the fee due, followed by interest due and then the principal.
9. System logs Account activity and Transaction History entries.
10. System: “View repayment schedule" is updated to depict the status of each component (penalty, fee, interest and principal). The partially paid installment is displayed in both “Installments paid” and “Installments due” sections with the amounts paid and due respectively. The “Date paid” should only be displayed when the entire installment is paid off.   

Early payments

Before the system accepts an early payment, it validates that the date of transaction is between the last payment date and the current date, and that the amount entered is not greater than the total amount outstanding for the loan. The amount paid is first applied to the current installment.  The remaining amount is then applied to the penalty due, if any, for the next installment. An excess at this point is applied to the fee due, followed by the interest due, and then the principal. The system updates the account activity and transaction history entries. If the user clicks the View repayment schedule link, he/she sees that the early payment is reflected in both the Installments paid and Installments due sections of the table.

Primary Flow- Early Payment

1. User clicks on “Apply Payment” link in loan details page.
2. User modifies the default amount and enters an amount greater than the expected amount.
3. User clicks on “Review Transaction”.
4. System validates that the date of transaction is between the last payment date and current date.
5. System validates that the amount entered is not greater than the total amount outstanding on that account.
6. System displays the Preview page with the information entered.
7. User clicks on “Submit” to save the transaction.
8. System submits the amount received. The amount is first applied to the current installment due. The excess amount is then applied to the penalty due, if any, for the next installment. After penalty payment, if there is still excess, the amount is applied to the fee due, followed by interest due and then the principal.
9. System displays Preview page for the next installment if there was excess.
10. System logs Account activity and Transaction History entries.
11. System: “View repayment schedule’ is updated to depict the status of each component and installment (penalty, fee, interest and principal).

Principal pre-payments

Mifos provides Principal Pre-payment option for Loan accounts with ‘Declining Balance’ or ‘Declining Balance-Equal Principal Installment’ interest rate type.

This option allows you to make more than one transaction on the same day, even if there is a payment applied for interest on the same date.

Principal Pre-payment option gives also a possibility to make principal pre-payment on non-meeting date (i.e. on a date on which there is no entry in the loan schedule table) or if the duration of the loan went beyond what was entered into loan schedule table by Mifos.
 

Primary Flow- Principal pre-payments

1. User clicks on Apply Principal Pre-payment link in Transaction box.
2. User types all mandatory information such as: Date of transaction, Amount and Mode of payment. You can also specify also Receipt ID and Receipt Date.
3. User clicks on Submit button.
4. Repayment schedule on Loan account details page is updated.

Backdating loan payments

Mifos allows backdated payments if the configuration settings allow it. The system calculates and displays the total amount due as of the current date. If a backdated payment is paid, the user manually calculates the amount due as of the transaction date. The system verifies the date and amount. If the amount entered is not equal to the amount due as of the transaction date, the system does not accept the payment.

The date of payment can be between the date of the last meeting and the current date.

• Partial, back-dated payments. For these payments, the user modifies the date of the transaction to a past date, in addition to modifying the default amount to a lesser amount than expected. When the user submits the transaction, the system applies the amount to the penalty due for the oldest unpaid installment. Any excess is paid against the fee due, followed by the interest due, and then the principal. At this point, the process is repeated for any remaining amount, and the payment is applied against the next unpaid installment, and then the next, until the entire payment has been applied.

Alternate Flow- Backdated Payment

1. User clicks on “Apply Payment” link in loan details page.
2. User modifies date of transaction to a past date.
3. User modifies the default amount and enters an amount less than the expected amount.
4. User clicks on “Review Transaction”.
5. System validates that the date of transaction is between the last payment date and current date.
6. System validates that the amount entered is not greater than the total amount outstanding on that account.
7. System displays the Preview page with the information entered.
8. User clicks on “Submit” to save the transaction.
9. System submits the amount received. The system picks the oldest unpaid installment and applies the amount against the penalty due for that installment. After penalty payment, if there is an excess, the amount applied to the fee due, followed by interest due and then the principal.
10. System rrepeats step 9 for all the unpaid installments until the amount become 0.
11. System logs Account activity and Transaction History entries.
12. System: “View repayment schedule’ is updated to depict the status of each component and installment (penalty, fee, interest and principal).

Overpayments

Client may make payment greater then remaining balance of the loan (an overpayment). Sometimes it is only a few dollars over, sometimes an entire extra installment is mistakenly paid. Handling overpayment of remaining loan balance is available in Mifos and the MIS will track this so that action can be taken to return the overpayment portion to clients in future.

Primary Flow- Overpayment

1. Overpayment situation only happens when repaying the last payment of a loan.
2. System detects an Overpayment during bulk payment upload.
3. The system closes the loan account and makes an entry in the new database table with details such as loan account number, overpayment amount, date and time, status of the overpayment and payment id (to be able to track the overpayment is linked to which payment).
4. On the loan page, the system displays the amount of the overpayment. Adjacent to this, there is a button labeled 'Clear' which is available for a user to manually clear the amount in case the last repayment was adjusted for some reason. The click of this button allows user to specify how much amount he would want to clear off.
5. Each loan can have more than one overpayment.

Missed loan installments

If an installment payment is missed, it is displayed in the overdue information, and the Total Amount Due includes the missed installment. For example, if a client has to repay $100 (principal = $80 and Interest = $20) every month, and she defaults for the month of August, then for the month of September, the following amounts are displayed in the Next Payment details:

            Principal Due= $80

            Interest Due = $20

            Penalty Due = $2 (for the missed/defaulted installment)

            Principal Overdue= $80 (for the missed/defaulted installment)

            Interest Overdue = $20 (for the missed/defaulted installment)

            Total Amount Due= $202

            While the payment is entered, the system accepts either $202 or no payment at all.

Adjusting payments

To rectify errors, the full amount of the last amount paid can be nullified by clicking the Apply adjustment link from the Transactions box on the account details page, the repayment schedule page, or the next installment details page. Multiple repayments can be reversed by reversing each payment amounts one at a time.

Users adjusting loan payments for a loan already of the status Closed – met obligation, must have a specific permission to do so.

When the user clicks the Apply adjustment link, he indicates that the last payment should be reverted, and includes a note describing the reason for the adjustment. These notes are stored with the transaction history, rather than with the Recent notes or All notes for the client. After the adjustment has been previewed and submitted, the user can click the Apply adjustment link again, to reverse the previous payment, and so on, until only the first payment remains.

Once an adjustment is made, the system breaks down the last amount paid into its components and updates the respective amounts where required. For example, if the last amount was paid for principal, interest and fees together, the system breaks the total amount into these amounts and subtracts it from the principal paid till date, the interest paid till date, and the fee paid till date, and adds it to the loan balance of principal, interest, and fees to pay.

After each adjustment, the system updates the following:

• The account summary:
  • Total amount due on the current day is increased by the adjusted amounts
  • Amount in arrears = adjusted amounts, if adjustment date > last installment date
  • Amount paid: the amount adjusted is subtracted from the amount paid
  • Loan balance: the amount adjusted is added to the amount due
• The repayment schedule: both the "installments paid" section and "future installments" sections. The repayment schedule gives a picture prior to the repayment adjusted.
• The respective repayment line is removed from the "installments paid" section and inserted in the "future installments" section.
• The installment details: the current installment and overdue amount due on the next meeting (always from the current day)
• Current installment is updated with the next meeting due amount
• Overdue amount increases with the adjusted payment, if adjustment date > last installment date
• Performance history updates:
  • The number of payments
  • The number of missed payments are incremented:
  • The number of days in arrears are recalculated = Today's date-last payment date
• Transaction history includes entries for the related transactions that were reversed due to the adjustment entries

Alternate Flow - Adjusting payments

1. User makes a complete payment of Rs. 100 on 1st Jan.
2. User clicks on “Apply adjustment” link on loan details page on 15th Jan and nullifies the last transaction.
3. System completes the adjustment flow.
4. User clicks on “Apply payment” link in loan details page on 16th Jan.
5. User modifies the date of transaction to 10th Jan.
6. User makes a partial payment and saves the transaction. 
7. System should consider the amount due as Rs. 100 and complete the partial payment flow.

Waiving Fees and Penalty

See Collecting Client Charges

Additional information

• If adjustment is done after a partial payment, the complete amount paid in last transaction should be nullified.
  • For example, User records a partial payment of 20 rs.
  • User then makes an adjustment on last transaction.
  • The payment of the entire 20 rupees is adjusted.
• If fee due = fee 1 + fee 2, fee 1= 20 and fee 2= 30, and the amount paid = 25- system should not have a logic to distribute the partial amount into various fee types.
  • For example- Client owes 150 Rs.- 25 in penalty, 25 in fees across 3 different fee types, and 50 in interest and 50 in principal.
  • Client makes payment of 35 Rs.
  • System accepts 25Rs to cover penalty and 10Rs to cover fees partially.
  • Repayment schedule will display 10 Rs as paid fees and 15 Rs as unpaid fees.
  • System will not know how the partial payment is disbursed across the various fee types. 
  • The partially paid fee and/or penalty can be waived by the user.
• If an installment is partially paid, it should be considered as a “missed payment” for performance metrics.
• If an account is in “Active in bad standing” state, the state should be changed to “Active in good standing” only on complete payment of due amount.
• Even for backdated transactions, “amount due” displayed by the system should be as of current date
• Early payment- if the client pays complete amount for one future installment and then an upfront fee is applied to the account- this fee should be applied to the first unpaid installment.
• Early payment- if an adjustment is done after an early payment, system should nullify the transaction and revert the transaction like a normal adjustment.
• If a client/group has 2 or more loan accounts of the same product, system will not allow early/partial payment from bulk entry.

  • System will validate that the amount entered is not greater than the total amount outstanding.
  • This amount will be more than the amount calculated by the system through “repay loan”.
  • The assumption is that if the user wants to completely repay the loan, “Repay loan” will be used and not “Apply payment” or bulk entry.

Reverse and Redo Loan Disbursals

Reversing loan disbursals

Data entry errors can result in loans that are created and disbursed to a wrong client/group or recorded against the incorrect client. Or a loan account of the incorrect product can be created and disbursed to a client. The error might not be detected until after multiple repayments have been recorded.

To correct this type of error, a user with the appropriate permissions can reverse the loan disbursal and all payments made again the loan, as long as the loan is within the user’s data scope. The loan must be in an Active in good standing or Active in bad standing states only.

The user clicks the Reverse Loan Disbursal link on the Admin tab, enters the reason for the reversal in the Notes box, and then previews the change before saving it. Mifos changes the loan account state to Cancelled with the flag Loan reversed, and reverses any payments that have been made along with the reversal of the loan disbursal. The notes are included in both the account notes and the adjustment notes, and the change is logged in the account activity. All relevant counters, such as the loan cycle, are decremented and the loan is not included in loan counts for reports. The loan is moved to the Canceled state, with a reason flag of “reversed.”

If several payments have already been collected, the user can use the Redo Loan Disbursal link to recreate a loan and historical payments.

Primary Flow - Reverse Loan Disbursal

1. User clicks on “Reverse loan disbursal” link on the Admin page.
2. User enters in Loan Acct ID and clicks “Find”
3. System makes the following validations:
   • Account id is that of a loan account in “Active in good standing” or “Active in bad standing state”.
   • Account is in user’s data scope.
   • User has permission to perform this action.
4. System: displays the account search result page and the account and client/group information is displayed in the following manner:
5. User: enters the notes and previews and submits.
6. System: following actions are required
7. Reverse all the financial transactions done till date on the account.
8. Log an entry in account activity with description = ‘disbursal adjusted’ and the amount = loan amount
9. Log payment adjustment entries for the payments reversed.
10. Notes specified by the user should be used both as account notes and adjustment notes
11. In transaction history, the entries should also have related transaction Ids. (Not transaction ids—those should be unique, but another ID—same payment ID??)
12. All appropriate counters (like loan cycle, etc) will be decremented-- and this loan shouldn't shouldn't be included in any reports as to "# of loans disbursed", etc. For portfolio reporting purposes, it should be as if this loan didn't exist.
13. Loan account is moved to cancelled state, marked with the reason flag "reversed". This reason flag will not appear as an option when user is manually cancelling a loan.

Alternate flows

1. User searches for an account id which does not exist or which is not a loan account id- An error is thrown
2. User searches for an account which is not in active in good/bad standing state

Additional information

• User is allowed to adjust disbursal only if he/she has permission to do so.
• User is allowed to adjust loan accounts in his/her data scope only.
• Only loans in Active in good standing or Active in bad standing states can be adjusted.

Redoing loan disbursals

After an error has been made and the loan reversed as a result, as explained in Reversing loan disbursals, a user with appropriate permissions can redo the loan disbursal by opening an account for the correct client with the correct product selected.  The loan disbursed should be reversed first before being redone.

To do this, the user clicks the Redo Loan Disbursal link on the Admin tab and selects the client for whom the account is to be redone. The system validates that the client is within the user’s data scope and displays the Redo Loan Account page.  Red warning text indicates that the user is redoing the loan account.

The user enters the loan account information. Note that the user is able to enter a disbursal date in the past.

When the user clicks Continue, the Redo Loan Account Installment page is displayed, on which the Actual Payment Date and Actual Amount paid are user-editable. After the user previews and submits the loan account information, the account is flagged as redone and the loan is immediately moved to Active in Good Standing.

Primary Flow - Redo Loan Disbursal

1. User: clicks on "Redo Loan Disbursal" link on the Admin page.
2. System: makes the following validation:

• User has permission to perform this action.

3. User is taken to the Redo Loan Account Search page where he can select a client/group whose account is to be redone.
4. System makes the following validations:

• Results outside the user's data scope should not be shown to the user.

5. User enters the Client/Group name and clicks on "Search".
6. On clicking Search, the user is taken to the Redo Loan Account Search Results which displays the relevant search results.
7. The user clicks on the required Client/Group name and is taken to the Redo Loan Account page.
8. In the Redo Loan Account page the user can select the Loan product name and click on "Continue".
9. User is then taken to the Redo Loan Account 1 page where the loan account information needs to be entered.

• Note: The system should allow the user to enter a disbursal date in the past.

10. After enetering all the required fields, the user clicks on "Continue".
11. User is then taken to the Redo Loan Account Installments page where he can review the installments.

• Two fields Actual Payment Date and Actual Amount paid are user editable.
• It is possible to choose the mode of payment.

12. After making relevant changes, the user clicks on "Preview".
13. User is then taken to the Redo Loan Account Preview page the entire loan account is presented as a snapshot. If the user wants to make any changes he can click on "Edit loan account information" which redirects him to the Redo Loan Account 1 page.
14. If the user is fine with the loan account information he can click on "Submit" which takes him to a confirmation page.

Additional Information

UI screens

Redo Loan Disbursal: UI screens are accessible here

To view the screens, download and extract the zip folder, click on admin.htm, and if your browser gives you a warning select "Accept blocked content". Click on the Redo Loan Disbursal link under Manage Accounts and follow the user flow below.

Collecting Client Charges

Collection Sheet Entry

Overview

When a Loan Officer returns from the field, the officer has to enter the collection sheet details in the system. Alternatively, he can provide the details to a non-loan officer who can enter the data for him. After necessary validations, the system updates the relevant records with the transaction details provided by the LO/user.

• Collection Sheet Entry forms are generated by a center name and by date for a Loan Officer. If center hierarchy does not exist in an MFI, collection sheet entry can be generated separately for each group.
• There is only one format for Collection Sheet Entry
• Amounts displayed in collection sheet entry (loan disbursals, payments and savings deposits) are of the last/today's meeting. These amounts are not updated if any payment has been made to any of the accounts or penalty is charged due to missed payments. If the payments have been made, Mifos will throw an error if user tries to re-enter the payment again.
• Collection Sheet Entry contains space for the user to enter the actual amounts for repayments collected and disbursements made and the attendance for customers can also be specified. Fields in collection sheet entry are detailed below.
• Collection sheet entry for future dates are not supported.  These contain only the details of payments/disbursements for current and past meetings.
• Only exact amounts in fees will be accepted
• Assumption- Each Collection sheet entry will have approximately 40 records (including client, group and center records).
• If there is a payment and disbursal due at the same time for a client for one loan product (in case when interest is deducted at disbursement), both issues and due sections in the collection sheet entry have text boxes with appropriate amounts.

Collection Sheet Entry Steps

Steps to enter a center’s data in collection sheet entry.   User can enter this feature from Enter Collection Sheet Data link in Mifos in the left navigation pane.

• User selects a Branch. 
• From a list of Loan Officers, User selects one LO.
• Centers assigned to the selected LO are populated and User selects one.
• Transaction date- disabled - defaulted to the last meeting day.
• Specify: Payment type, Receipt#, receipt date (these 3 items would then apply across all transactions collected for the center.
• User continues to next Collection Sheet Entry screen.
• The amounts for each client, group and account are pre-populated when the collection sheet entry loads. These amounts will be as of the current date
• Edit the expected transactions - This is detailed below.
• Preview
• Submit

Data Displayed on Collection Sheet Entry Form

The collection sheet entry form will have the following data:

Sl. No.	Column Name	Description
1	Client/Group Name, Client/Group ID	Client Name should be only First and last name.
Due
2	Loan Product 1, 2…	System calculated values should be pre-filled These can be edited by the users.
3	Savings Account 1, 2…	Mandatory deposit amount for savings account 1 due till the current date. For voluntary savings accounts, this amount will only have the latest amount due and will not include the amounts due from past meetings. System calculated values should be pre-filled. The amount can be edited by the user.
Issues/Withdrawals
4	Loan Account 1, 2…	System calculated values for Loan amount due to be disbursed to the client/group. The amount will be editable.
5	Savings Account 1, 2…	User-entered- Withdrawals made in the field clients/groups/centers
Other Collections
6	Client Charges	System calculated- Fee/Misc fee/ Misc penalty charged to client/group/center account and due
7	Attendance	Drop down with the following options: Present (P), Late (L), Absent (A), Approved Absence (AA).

 

• The validation required when the above data is entered and submitted is:
  • Loan repayment should either be zero or equal to the amount due
  • Loans disbursed should either be zero or equal to the amount due to be disbursed
  • Withdrawals cannot be greater than the current account balances. If it is so, system should throw an error.
  • Fees should either be zero or equal to the amount due
• For group and center savings accounts- The system logs transaction entries for each client deposit/withdrawal made to the group account.
• If a client or group has more than one active loan accounts of the same loan product, the repayment and disbursals due should be clubbed together in the same row. The system will accept only complete or no repayment for the clubbed amount.
• If a client, group, or center has more than one active savings accounts of the same savings product, collection sheet entry will display only one active savings account (which was created the earliest).
• User must preview data entered in collection sheet entry before saving.
• When a collection sheet entry form is submitted, Mifos does the following:
  • Transaction IDs will be generated.
  • Respective savings/loan/client accounts will be updated with the transactions.
• If a collection sheet entry is generated twice, attendance from the last submitted entry will be displayed in the collection sheet entry. Changes made to the attendance will overwrite the attendance entered before.

Loan Disbursal Update from Collection Sheet Entry

Collection Sheet Entry displays disbursals due on a meeting. If the transaction date is modified to a date other than the last meeting date, Mifos updates the disbursal date for that loan account and regenerates the repayment schedule as per the new disbursal date.

UI Notes

• All clients/groups from search criteria entered appear in collection sheet entry; even if a client/group has no active loan or savings accounts and has no charges pending in the client account.
• In Collection Entry UI, the collection sheet grid contains the following details-
  • Details of all client loans and savings accounts
  • Details of center savings accounts
  • Details of client and center accounts
  • Details of group accounts
  • Details of group savings accounts where unit for “Recommended amount for deposits” is “per group”
  • Details of group savings accounts where unit for “Recommended amount for deposits” is “per client”
• If any column is not relevant for a client/group, the respective cell should be left blank, that is, a text box should not be displayed in that cell
• In preview page- the text color should be Red in following cases
  • If the attendance is not “Present”,
  • If loan repayment is zero or there is a partial loan repayment,
  • Mandatory savings amount is not equal to the amount due,
  • Account charges collected are not equal to the charges due.

Out of Scope

• Collection Sheet Entry for clients who do not belong to any group.
• Full repayment to close loan accounts through bulk entry. Full loan repayment and closure must happen directly off the client page.

Import Bank Transactions

Introduction

Al Majmoua currently handles all repayments of loans by clients by having clients deposit their repayments directly at a bank, and then Al Majmoua is able to download online a file of these recorded transactions to import their MIS.  They need the ability to do this in Mifos as well.  See related Business requirements for more detail.

User Stories (Epics)

Priority	User Story	Section in FR
1	As a user (accountant) at the HO, I want to be able to import a XLS of bank transactions from Audi Bank into Mifos so that all details for our clients' payments are accurately recorded in Mifos
1	As a loan officer at a BO, I want to be able to see the clients at branch have paid their loans in Mifos
2	As a user (accountant) at the HO, I want to be able to import a XLS of bank transactions from any bank into Mifos so that all details for our clients' payments are accurately recorded in Mifos

Goals

• Ability to import file of loan repayment transaction details into Mifos
• Transaction History in Mifos accurately shows loan payments.

Non-Goals

The following items will not be addressed in this release:

• Importing bank transactions from any bank other than Audi Bank
• No import of attendance, savings, fees, or loan disbursal information - only loan repayments are imported.
• Additional logging of import than what we do already in Mifos for logging

Definitions and Terminology

• Mandatory fields will be preceded by *
• Links are italicized
• Buttons are Button

Related Documents

• Business Requirements

Import Bank Transactions

This feature is in the Admin section and will allow the User to import bank transactions

Use Cases

Administrator assigns permissions to accountant to import bank transactions

Actors

•  Administrator

Preconditions

•  None

Basic Flow

1. Administrator logs onto Mifos and navigates to the Admin section. Administrator selects Manage Roles and Permissions. Administrator selects the role for accountants.
2. Mifos displays all possible permissions for the role and Administrator scrolls down to "Bulk". Administrator confirms that "Can import transactions" is checked.

Post-conditions

• Accountant role has permission to import bank transactions

Alternative Flows

• None

Validations

• None

Accountant imports transactions (Audi Bank) - file with no errors

Actors

• Accountant

Preconditions

•  Accountant has permissions to import transactions
• Accountant has file in xls (Excel 97 format) to import

Basic Flow

1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import transactions.
2. Mifos displays new screen for Import Transactions.
3. Accountant selects Audi Bank Excel 97 or Audi Bank TSV for bank, and selects file from their computer for import. Accountant clicks on Continue.
4. Mifos imports file and checks for any errors (see Validations below). If there are no errors, Mifos displays Review & Submit screen with "There are no errors found. Click Submit to continue with import.".
5. Accountant clicks on Submit. Mifos displays confirmation screen that import was successful.

Post-conditions

• All data available in the file has been imported and all the correct tables and loan account data correctly populated.

Validations

• If a row has a cell that's empty in a required column is empty, that row is rejected and appropriate error message displayed.
• No bad data - ie, no numbers in columns that require text and vice versa. Date of Transaction is in date format YYYY/MM/DD if it's a TSV file, or does not follow the expected format of that column. See FR's below.

Alternative Flows

Accountant cancels Import

1. At step 4, Accountant clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

• No data has been imported.

Import has errors and Accountant chooses to continue

1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
2. Accountant chooses to continue with import of the valid rows and clicks on Submit.   Mifos displays confirmation screen that import was successful.

Post-conditions

• Valid rows are imported and invalid rows are rejected.

Import has errors and Accountant chooses to Cancel

1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
2. Accountant chooses to cancel and clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

• No data has been imported.

Import has errors and Accountant chooses to import a different file

1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
2. Accountant chooses to import a different file and clicks on Edit Import Information instead. Mifos returns User to the previous screen.  Return to Step 2 of Basic Flow.

Post-conditions

• No data has been imported.

Loan Officer checks data has been updated

Actors

• Loan Officer

Preconditions

• Loan Officer has permissions to view clients loan data
• Administrator has already imported data for that date.

Basic Flow

1. Loan Officer logs onto Mifos and navigates to client A's loan account.
2. Mifos displays Transaction History. Loan Officer confirms that a repayment has been updated correctly.

Post-conditions

•  None

Alternative Flows

Loan Officer doesn't see the data updated

1. Loan Officer selects a client who did not pay for the date of transaction the Administrator had imported.
2. Loan Officer goes to view that client's transaction history and sees that no transaction has been made.

Accountant imports bank transactions (generic) - P2

Actors

• Accountant

Preconditions

•  Accountant has permissions to import bank transactions

Basic Flow

1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import bank Transactions.
   1. OR Accountant clicks on link in Quick Start - Import bank Transactions
2. Mifos displays new screen for Import Bank Transactions.
3. Accountant enters date of transaction (defaulted to today). and selects file from their computer for import. File follows generic format set by Mifos.
   1. Accountant can select up to 5 custom fields they want data imported from the file to map to.  Ie - they select custom Field "Serial" and enter in column name "Serial" where the import will map to that column.  No validations are done on custom field columns.
   2. Accountant clicks on Continue.
4. Mifos imports file and checks for any errors (see Validations below). If there are no errors, Mifos displays Review & Submit screen with "There are no errors found. Click Submit to continue with import.".
5. Accountant clicks on Submit. Mifos returns the Accountant to the Admin screen.

Post-conditions

• All data available in the file has been imported and all the correct tables and loan account data correctly populated.

Validations

• If any cell in a required column is empty  entire import is rejected with appropriate error message.
• No bad data - ie, no numbers in columns that require text and vice versa. Date of Transaction is in date format MM/DD/YYYY or does not follow the expected format of that column. See FR's below.  This is only for columns required in generic format - there is no validation done on Custom fields.

Alternative Flows

User Stories

Priority	Size	User Stories	Mingle card #
1	X-Small	As a User, I can give permission to import bank transactions.
1	Small	As a User, I can click on Import Bank Transactions and see options to import my file (Audi Bank).
1	Small	As a User, I can confirm that I want to import the data into Mifos.
1	Medium	Populate all data from file import in Mifos
2	Medium	As a User, I can click on Import Bank Transactions and see options to import my file (generic)

Import Bank Transactions (Audi Bank) Functional Requirements

Add new permission

FR#	Pri	Description	Comments / Mockups
1.1	P1	Add new permission "Can import transactions" under the Bulk subsection in Permissions. The ADMIN role should by default have this permission checked.
1.2	P1	Assumption is the only check when the user imports transactions is if they have this permission checked in their role. There is no checking of their data scope and hierarchy. Ie: If the user is a Loan Officer in BO1, and has this permission checked in their role, then they are allowed to import any bank file, and there is no check that the data they are importing is only for their branch office.
1.3	P2	If one day we implement importing transactions per branch, then permission should also check for data scope.  Ie, can only import data for a particular branch.

Add new screen for Import Bank Transactions (Audi Bank)

FR#	Pri	Description	Comments / Mockups
2.1	P1	Add Manage Imports section with Import Transactions link in Admin section
2.2	P1	New Import Data Transactions Screen with following fields: * Import format (dropdown) * Select import file (browse for select file)
2.3	P1	Import format Field will have two options: Audi Bank (tab delimited) and Audi Bank Excel 97 formats
2.4	P1	Screen has buttons Continue and Cancel.  Clicking on Continue imports the file selected and Mifos does validations and brings User to Review & Submit screen.  Clicking on Cancel returns User to Admin screen.
2.5	P1	If there is no import plugin for the MFI, then the Import Format options will be blank and if the User tries to import a file and Continue, the following error message will be displayed at the top.  Also if the User does not select a plugin even if there are options then the following error message will be displayed. Please select the import type.
2.6	P1	If there is no Mode of Payment configured in Mifos to be the same as the first cell of that file (in this case, should always be Bank Audi sal), then an error message will be thrown on the Import screen: No payment type found named <bank> where <bank> is the first cell of that file.

Import File Details and Validations (Audi)

FR#	Pri	Description	Comments / Mockups
3.1	P1	Audi Bank import file is currently in Excel.  Al Majmoua will save these as a tab-delimited file first before importing into Mifos.
3.2	P1	Audi Bank import file first few lines contain description of file.  These are to be ignored.  Only data after row of column headings will be imported.
3.3	P1	Import file will have columns in below table
3.4	P1	There will be bare basic error checking - to successfully import the data.
3.5	P1	If a row with C (for Credit in D/C column) contains a cell that's missing a required field, Mifos displays an error message for each row this occurs. Row <#> is missing data. where the row # is the original row # of the import file.
3.6	P1	If any value under Trans.Date column is not in format of YYYY/MM/DD if User is using TSV importer, then Mifos displays an error message for each row this occurs. Transaction date value in Row <#> does not follow expected format (YYYY/MM/DD). where the row # is the original row # of the import file.  No error message will be thrown to validate date format when using Excel importer since the importer will check for correct date format automatically.
3.7	P1	If any value under the Serial column is not numeric (ie text or special character) then Mifos displays an error message for each row this occurs. Serial value in Row <#> does not follow expected format. where the row # is the original row # of the import file.
3.8	P1	If any value under the Description column does not follow the format PMTMAJ<2 letter code><5 digit loan id for external id> or <7 digit account id> or <15 digit loan id for Mifos loan id> then Mifos displays an error message for each row this occurs. Mifos should throw an error message when: 1. PMTMAJ<two-letter-code> does not precede some numbers. If the numbers that follows is not 5 digits. 2. If number of digits without <two-letter-code> is not 7 digits or 15 digits. If the numbers that follows is not 5 digits or 15 digits.  The following error message should be displayed. Loan ID could not be extracted from Row <#> where the row # is the original row # of the import file.
3.9	P1	Check if the same file name has been imported.  If so, then throw an error message and reject the whole import. Same file name has been imported.  Please import a different file.
3.10	P1	After clicking on Continue, Mifos will display the Review & Submit screen with the following: Review the information below.  Click Submit if you want to continue with import or click Edit to make changes. Click Cancel to return to Admin page without submitting information. Import information Import file name: <name of file> Import Status: <#> rows contained no errors and will be imported. where <#> is the number of valid rows being imported.  See mockup.
3.11	P1	If any error messages apply, then Mifos adds the messages line by line. The following rows contained errors and will not be imported: Row <x> is missing data. Serial value in Row <x> does not follow expected format. and then the error messages are displayed one per line.
3.12	P1	User can then either Click on Edit import information to go back to previous screen and upload new file. Continue with import and Submit. Cancel out of the workflow (returning to Admin screen).
3.13	P1	If User clicks on Submit, Mifos imports the file and displays confirmation screen - see mockup ->
3.14	P1	There is no option to revert a file upload once it has been submitted.
3.15	P1	There is no checking of duplicate rows (see 3.9)
3.16	P1	Currency of values imported is directly inherited from the loan product that the loan account is mapped to.
3.17	P2	If there are no rows found with import data, the following error message should be thrown: No rows found with import data.

 

Audi Bank Import Columns and Description

Column Name	Required	Description	Comments	Validations	Range	Example	Maps to Mifos
Trans.Date	Yes	Payment date		Validate date is in range (see above)	YYYY/MM/DD (for tab-delimited files)	2009/09/01	Transaction Date
Serial	Yes	reference number for the transaction		Only validate value is numeric (see above)		1234567	Serial Number (internal field in DB)
Value Date	No		Ignore this column
Reference	No		Ignore this column
D/C	Yes	Debit or Credit	Only import rows with C	No validation - only import rows with C	D or C	C	None
Amount	Yes	transaction amount	can be in USD or LBP, currency is not indicated	Only validate amount is a number	numeric number	2577	Transaction amount
Balance	No	ignore	Ignore this column
Description	Yes	contains the loan id	Only import rows with A, Z, or C in the 2nd letter of <two-letter-code> Need to extract the loan id from this row loan id is 5 digits or 15 digits	Only validate if the loan id can be extracted for rows with A, Z, or C - so if format is incorrect and loan id cannot be extracted, display error message (see above)	PMTMAJ <two-letter-code><5 digit external loan id> or <7 digits account id> or <15 digit mifos loan id><space><two-digit LA code> <client name>	PMTMAJ EC12345 82 Joe Smith PMTMAJ 1234567 82 Joe Smith PMTMAJ 1234567890123456 82 Joe Smith	External ID

Loan Repayment Details

FR#	Pri	Description	Comments / Mockups
4.1	P1	Assumption: All transaction detail is for loan repayments (loan fees, interest, principal). There is no data in file for loan disbursals, savings withdrawals or deposits, client, group or center fees, or client attendance.
4.2	P1	Trans.Date is actual repayment date recorded
4.3	P1	Amount that is being paid - pre-payments and partial payments are allowed.  Amount in file is applied in the following order - Penalties, Fees, Interest, Principal.
4.4	P1	Trans. Date cannot be after today in Mifos
4.5	P1	Mode of Payment must be configured in Mifos to have Bank Audi sal, which will be the mode of payment used for all transactions in that import.
4.6	P1	If there is an overpayment, Mifos should thrown an error.

Other Assumptions

LSIM

• LSIM will be on - but should work on or off

QA Considerations

• Performance History - PAR, other values calculated correctly
• View Loan Account Details - correct dates and values imported
• Transaction History - each transaction should still be logged, with the user who did the import

Standard Considerations

Security (Permissions, Roles, and Data Scope)

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?	Yes - adding new permission Can import bank transactions
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 special considerations.  Default value is checked in ADMIN role and unchecked for all other roles.
What will be the default values for default roles in a new installation?

Impacts to System

Does this feature affect Bulk Loan Creation?  How?	No
Does this feature affect Collection Sheet Entry?  How?	No
Does this feature affect Redo Loans?	No
Does this feature affect Undo Loans?	No

Globalization/Localization

Will this feature support users localizing data that they enter?
Does this feature involve any date/time related data, and if so how should conversions be handled?
Is there currency or other numeric data ? If so does it require any special handling or validation?

Logging

Change Log

Do changes to the data that is collected or stored by the new feature have to be fully logged by the system?	Yes
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.

Does the feature affect any existing reports?	No
Does the feature require adding any new reports?	No

Performance

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

Setup and Installation

New Installations

Will the feature include demo data?	No
Does the feature require any data to be gathered at setup runtime?	No

Backward Compatibility and Upgrades

Is there any data conversion that needs to be done as part of an upgrade?
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.	ADMIN role should be updated to have this permission checked.
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?	No

Hosting Support

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

Configuration

Does this feature require changes to configuration files?	No
If so, is this feature enabled or disabled by default?	N/A

Open Questions and Notes

Answered

• we need a sample of what data can be dumped from the banking system
  • please see samples attached to MAJ:Bank Imports 
• Is it one file for the whole MFI or one file per branch office?
  • one file per currency per bank (there are currently 5 banks) - SB
• Is this one person w/ an Administrator role doing this at the HO
  • It will be more than one user in the HO accounting department, not all of which should be admins
• What is the name of their bank?
  • Audi Bank is the main one
• Is it only Audi Bank or BSL too?  Both are referenced in some of the sentences
  • it is actually 5 banks, please see MAJ:Bank Imports 
• How important is it to share what type of error it is and at what level does it need to be at if it's necessary - ie do we need to tell what row the error occurs on or can we just say generically what the error is?
  • Medium important, for each row that has an error, an accountant is going to have to examine it and manually correct it, possibly after calling a bank or branch. So, it would be better to have Mifos report which rows have errors, otherwise the accountant will have to manually scan the import files and keep trying to import until she finds all the errors.
• Does Excel format mean it won't accept Unicode (Arabic) characters?
  • Not sure, but it doesn't matter. We only need to import (loan id, bank ref #, trans date, amount), none of which will have Arabic characters.
• Typical size of file - will this have impact on performance? - Load of transactions - 1500 transactions/file?
  • please see samples attached to MAJ:Bank Imports , these are representative of a typical day. Keep in mind though, the growth projections for MAJ:Al Majmoua 
• Can we get access to the bank's website?
  • they probably won't give us their online banking password, but just let me know what you need and I will ask their accounting department.
• I deleted the spec I had in progress from mifos.org in lieu of what you said (Sam) about Al M's desire to keep information away from competitors.  We can revisit later what we can and cannot put on mifos.org
  • thank you!!!
• Do we need to have the Excel format converted first to a different format for easier/quicker development of this feature?
  • whatever is best for feature development.
• Current spec is assuming option 2 of multicurrency (products have been created and entered in respective currency)
  • interesting... let's catch up on multi-currency then, I thought option 2 was a no-go when we discussed it Monday

can we get the bank to only put loan id in the description field? No

Unanswered

• credit rows in Audi with no loan id - are these reversals, or missing loan ids?
• look at Audi USD line 202

Audi Bank		Generic Import
Pros	Cons	Pros	Cons
Faster	Restricts us to adding one bank at a time in the future	Can accommodate all 5 of Al Majmoua's banks	Will take longer to implement

Review and Approvals

 

Date	Name	Role	Status
		PM
		Dev
		QA

 

Deposits and Withdrawals to Savings Accounts

Deposits and Withdrawals on Savings Accounts