MPESA Import Loan Disbursals and Loan Fees

Introduction

KEEF is planning to have mobile integration with M-PESA. They are using Mifos for their MIS platform. Mifos currently can import M-PESA loan payments and savings deposits into Mifos. To complete this integration, the ability to disburse loans will be added. This requires the following:

1. Ability to extract pre-approved loans (phone number and net disbursement amount) from Mifos. This extract will be uploaded into the M-PESA system. Once uploaded, M-PESA will disburse the loans to the clients' M-PESA accounts via mobile.
   1. Ability to diburse net disbursal loan amount. The Mifos extract should include the net loan amount, which is defined us total loan amount less loan fees due at disbursement.
2. Ability to import M-PESA disbursement transactions into Mifos.
   1. Ability to match the M-PESA disbursement records into Mifos approved loans and corresponding fees. In the given example, there will be 1 M-PESA transaction matching 2 transactions with Mifos: the Loan for 450,000 and the up-front loan fee of 3,000.  MPESA Disbursement transction of 47,000 will be equivalent to Mifos 2 transactions of 1) 50,000 disbursement and 3,000 loan fee.
      

User Stories

Goals

• Mifos should be able to produce an extract in CSV that contains Approved Net Loan amount and customer phone number. This extract will be used to upload loans into MPESA system for disbursal.
• Ability to import file of disbursement transactions from MPESA into Mifos to disburse the earlier approved loans.
• Ability to deduct loan fees from loan amount and generate net loan amount for export report
• Properly account for all transactions read, ignored, errored, and imported during import.

Non-Goals

The following items will not be addressed in this version of the feature:

• Ability to split loans into several transactions given the MPESA disbursal limit. The M-PESA limit has just been changed to KSH 140,000/day and KEEF disburses by checks for loan amount > KSH 50 000.
• Ability to process Client Fees
• Ability to process Loan Fees NOT due at the time of disbursement. This decision has been made with the reasoning that cashless transaction for other fees can be done by getting permission from client to withdraw funds from saving to pay for other fees. This addresses the issue that the client fees (usually around KSH 100) are to small too incure a money transfer fee (KSH 20) for the clients
• Changes to the Mifos product to restrict which fees can be associated with which product

Definitions and Terminology

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

Related Documents

[MPESA Loan Repayments and Savings Deposits Functional Spec v2|MIFOS:MPESA Loan Repayments plus Savings Deposits v2

Sample Disbursement CSV file (in Excel) (for Export)

Sample M-PESA transaction reports CSV (in Excel) (For Import)

Mifos Permissions and Mandatory Fields

Use Cases

Use Case - Account Clerk or Data Encoder tries to edit client's phone number.

Note that this is a necessity to avoid fraud. With M-PESA, money is disbursed to the clients' phone. Any user who has rights to disbuse loans through M-PESA should NOT have rights to modify a client's phone number. Otherwise, the person can disburse ALL loans to his/her personal account.  That user can VIEW the client phone number however.

Actors

• Data Encoder
• Account Clerk

Preconditions

• Only Adminstrator has permission to edit client phone number.
• Data Encoder and Account Clerk do not have permissions to edit client phone number.

Basic Flow

1. Data Encoder or Account Clerk edits a client account.  They edit the phone number field, and try to submit. 
2. Mifos displays an error message at the top that says "You do not have permission to edit the client's phone number."

Post Condition

• Users are unable to edit the Phone Number.

Use Case - Administrator tries to edit client's phone number.

Actors

• Administrator

Preconditions

Only Adminstrator has permission to edit client phone numbers

Basic Flow

1. Administrator opens a client profile.
2. Administrator edits the phone number.
3. The Administrator saves the client profile.

Post Condition

• The Administrator is able to edit and save the client phone number.

Use Case - Loan Officer(?) creates client with no phone number or national ID

Actor

• Data Encoder
• Accountant Clerk

Preconditions

• Client Phone number and Government ID have been set as mandatory fields under Define Mandatory/Hidden fields.
  

Basic flow

1. Loan officer creates client and does not fill in Client Phone number and/or Government ID. 
2. Loan officer tries to save client.
3. Mifos displays an error message that mandatory fields are not filled out.
   

Post condition

• Mifos displays error message.
  

Mifos Permissions Functional Requirements

Export Mifos Loan Disbursals

Export Use Cases

This feature is in the Reports section and will allow the User to export Approved Loans for disbursements.

Use Case - Administrator sets up M-PESA Disbursement Limit

Right. We should put this on Export. The current KEEF limit for disbursing via MPESA is KSH 50,000. We shouldnt export beyond this amount.

This might be out of scope if we put in Pentaho right now - we can hardcode now and then change it later?

Marie, 30Jan2011. It has been agreed that this will be hardcoded in the report right now and will then be a parameter when Pentaho is integrated into Mifos. This integration is scheduled in 2011.

Actors

• Administrator
  

Preconditions

Basic Flow

1. Administrator edits a properties file with new setting for the Maximum MPESA Disbursal Limit.  Administrator sets limit to 50000.
   

Post-conditions

• Approved loans with amount less than or equal the MPESA Disbursal Limit are included the "List of MPESA Loans" extract.
• Approved loans with loan amount > than MPESA Disbursal Limit are NOT included in the "List of MPESA Loans" extract.
  

Validations

• If this property is not set, then all disbursements are thrown with ERROR, Maximum Disbursement Limit NOT defined.

Use Case - Data Encoder exports Approved Normal Loans - no errors

Actors

• Data Encoder

Preconditions

• (P2) Data Encoder has permission to run "List of MPESA Loans" report in Pentaho.
• Normal Loans have been created and approved in Mifos.

Basic Flow

AT THE END OF THE DAY, at 4pm:

1. Data Encoder calls/extract report “MPESA Loan Disbursals” in Pentaho. (See attached report) Take note that this extract should deduct loan fees from disbursement amount. For each loan, net disbursement amount = loan disbursement amount – total loan fees.
2. Data Encoder selects Loan Product = Normal Loan, Group = ALL, Date = TODAY
3. This extract includes
   1. All Loans in Application Approved status.
   2. Loan Amount <= 50,000
   3. Disbursement Date = Extract Date
4. Data Encoder saves the file in CSV format.
5. Data Encoder logs into MPESA
6. While in the MPESA system, Data Encoder imports the file.
7. While in the MPESA system, Account Clerk approves the MPESA disbursement, then client receives loan amount on their phones

Post-conditions

• A report listing qualified approved loans is generated from Mifos and it should be exported and can be saved as a CSV file format
• All loan amounts reflected on the List of MPESA Loans report will be loan amounts minus loan fees.
• The extract will contain all the fields as required by MPESA system
• '“MPESA Loan Disbursals' is imported and processed in the M-PESA system.
• Clients received their loans via M-PESA.

Questions

• How do we prevent approved loans from being exported twice?  Also: there is a use case below that if there is a time-out, the loans are re-exported.  We need to think through both use cases to ensure they don’t contradict each other. * How do we stop any user from tampering with the extract*?

Validations

• Extract Summary report contains all approved loans, not exported, such as:
  • Approved loans with no phone number.
  • Approved loans with amount > 50,000.
• No bad data - ie, no numbers in columns that require text and vice versa. See FR's below.

Use Case - Data Encoder exports Approved Advance Loans - NO errors

Actors

• Data Encoder

Preconditions

• Data Encoder has permission to run "List of MPESA Loans" report.
• Advance Loans have been created and approved in Mifos.

Basic Flow

THROUGHOUT THE DAY, IMMEDIATELY after Advance Loans have been created and approved for a group.

1. Data Encoder calls/extract report “MPESA Loan Disbursals” in Pentaho. (See attached report) Take note that this extract should deduct loan fees from disbursement amount. For each loan, net disbursement amount = loan disbursement amount – total loan fees.
2. Data Encoder enters the Group for which Advance Loans are to be disbursed, and selects Advance Loan Loan product.
3. This extract should include:
   1. All Loans in Application Approved status.
   2. Loan Amount <= 50,000
   3. Disbursal Date = Extract Date
   4. Only clients in the group specified by the Data Encoder
4. Data Encoder saves the file in a CSV format.
5. Data Encoder logs onto MPESA
6. While in the MPESA system, Data Encoder imports the file.
7. While in the MPESA system, Account Clerk approves the MPESA disbursement, then client receives loan amount on their phones

Post-conditions

• A report listing qualified approved loans is generated from Mifos and it should be exported and saved as an CSV file format
• All loan amounts reflected on the List of MPESA Loans report will be less the loan fees.
• The extract will contain all the fields as required by MPESA system
• 'List of MPESA loans' is imported and procesed in the M-PESA system.
• Clients received their loans via M-PESA.

Validations

• List all approved loans, not exported, such as:
  • Approved loans with no phone number.
  • Approved loans with amount > 50,000.
• No bad data - ie, no numbers in columns that require text and vice versa. See FR's below.

Export Mifos Loan Disbursals Functional Requirements

M-PESA Loan Disbursals Export Columns and Description

Import Loan Disbursal and Fee Transactions

Use Case - Data Encoder import transaction file from MPESA

Actors:

• Data Encoder
• Accountant Clerk

Preconditions

• Data Encoder has permission to import transactions
• Data Encoder has file in xls to import

Basic Flow

1. Data encoder logs onto Mifos and navigates to the Admin section. Data Encoder selects import transactions
2. Mifos displays new screen for import transactions
3. Data Encoder selects M-PESA for Import format, and selects file from their computer for import. Accountant clicks on Continue. Take note that this is the same file used in importing Repayments and Savings Deposits.
4. Mifos imports file and checks for any errors. If there are no errors, Mifos displays Review and Submit screen with “There are no errors found. Click Submit to continue with import”
5. Data Encoder clicks on SUBMIT. Mifos displays confirmation screen that import was successful.

Post conditions

• All approved loans in Mifos will be disbursed in Mifos.
• Each approved loan is disbursed and corresponding loan fees applied. The disbursement transaction and loan fee applications are tied to 1 receipt ID.
• All loans with status "Application Pending Status" will not change.  So there needs to be a check on the loan status right? YES. Again, KEEF will use this for validation later.
• Repayments and savings deposits are processed as stated in Repayment and Savings Import functional specifications.

Validations

• If there are no approved loans found in Mifos, each row that is not an approved loan will show an error message (see below)
• See Import File Details and Validations (M-PESA) for specific column validations.

Alternative Flows

Use Case - Data Encoder Cancels import

1. At step 4, Data Encoder clicks on CANCEL instead, Mifos returns user to admin section

Post Conditions

• No data has been imported

Use Case - Import has errors and Data Encoder chooses to continue

1. At step 4, Mifos determines there are errors from Validations mentioned below. Mifos displays Review & Submit screen with error messages depending on types of error.
2. Data Clerk saves the error log.
3. Data Clerk 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.

Use Case - Import has errors and Data Encoder 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.

Use Case - Import has errors and Data Encoder 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.

Use Case - Loan Officer checks data has been updated in clients' loan and savings accounts

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 Active Normal Loan.
2. Mifos displays Transaction History. Loan Officer confirms that the disbursement has been recorded and appropriate fees are applied under 1 Receipt ID. Currently the Receipt ID is not displayed in Transaction History.
3. LO then navigates to client B's approved Advance Loan that the disbursement has been recorded and appropriate fees are applied under 1 Receipt ID.

Post-conditions

• None

Alternative Flows

Use Case - Loan Officer doesn't see disbursals

1. Loan Officer selects a client whose Normal and Advance Loan are still Pending Approval.
2. Loan Officer goes to view that client's transaction history and sees that disbursement has not yet occurred.
3. Loan Officer selects a client whose Normal and Advance Loan are paid but not yet due for disbursal.  I don’t understand how a loan can be “paid”.  Can you clarify? Previous loans are fully-paid, there is an approved loan but the approved loan is not yet due for disbursement.
4. Loan Officer goes to view that client's transaction history and sees that disbursement has not yet occurred.

Import Transactions (M-PESA) Functional Requirements

M-PESA Import Columns and Description

Since we are using the same plugin to import a file for both disbursals and repayments, I would have liked this table to contain validations and details for all. That would have been nice. That's what I did when I did the repayments and you asked me to separate them to avoid confusion. I trust you can put them together now. :)  oops ;)

Other Assumptions

• Name payment mode = MPESA

QA Considerations

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

Impacts to System

Globalization/Localization

Logging

Change Log

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.

Performance

Setup and Installation

New Installations

Backward Compatibility and Upgrades

Hosting Support

Configuration

Open Questions and Notes

Unanswered

1. How do we ensure that files are unaltered when transferring between MPESA and Mifos for both export and import, considering the files have to be opened in Excel?
2. How do we ensure that Approved Loans are not extracted twice from Mifos and imported twice (disbursed) into the MPESA system causing the clients to receive twice (or thrice) the disbursal amount?

Answer as of 30 January 2011

1. The Excel/CSV format is required by Safaricom's MPESA system. At this point, we have no option but to comply.
2. The risk is actually not in running the extracts twice but importing the same disbursements more than once into the MPESA system. Again, this is a limitation of Safaricom's web interface and is out of our control.

     In both cases, any fraud or errors resulting in the above 2 cases will be caught during end-of-day reconciliation. It is therefore very important that the end-of- day reconciliation process is done and monitored properly.

Other Notes:

• In cases, where an approved loan is in error (no phone number or amount exceeds KES 50,000, the record will NOT be included in the extract file but rather written in the extract summary report.

  Out of Scope

The following section outlines use cases and functionality which either do not require Mifos changes or are out of scope.

Mifos Set-up

Normal Loan

• Amount: KES 6 000 to KES 500 000
• Term: 17 to 55 months
• Interest Rate: 0.7% for the full term

Advance Loan

• Amount: Three times the value of the previous month’s savings
• Term: 1 month
• Interest Rate: 10% per month

Advance Loan Fees

• Fee Name: Advance Loan Fee
• Fee Applies To: Loan
• Frequency: One Time
• Select time of charge for one time fees: At Disbursement
• Amount: 10

Insurance Fee

• Fee Name: Insurance Fee
• Fee Applies To: Loan
• Frequency: One Time
• Select time of charge for one time fees: At Disbursement
• Amount: 100

Normal Loan Fee

• Fee Name: Loan Form Fee - <Loan Amount Range> E.g., "Loan Form Fee - 0 to 9,999"
• Fee Applies To: Loan
• Frequency: One Time
• Select time of charge for one time fees: At Disbursement
• Amount: Note that the amount varies by the loan amount, so there will be several Normal Loan Fees created. And each one will be titled by the range of the loan amount so that the user is able to apply the right fee when creating the loan. For "Loan Form Fee - 0 to 9,999", the fee amount is 150.

Create and Approve Normal Loans for Extraction

Use Case - Users create and approve Normal Loan - Normal Flow

Actors

• Data Encoder
• Account Clerk

Preconditions

Normal Loan set-up

• Normal Loan fees, Insurance Fee and Loan Form Fee - XXXX set-up
• Data Encoder has rights to create loans
• Account Clerk has rights to approve loans

Basic Flow

1. During the group meeting:
   1. Client applies for a loan.
   2. Group approves the loan
2. At the site, shortly after the meeting:
   1. Loan Officer conducts site visit:
3. At the Head Office, Day after the group meeting:
   1. Data Encoder receives Loan Application form for Normal loans.
   2. Data Encoder logs into MIFOS, and clicks on ‘OPEN NEW LOAN ACCOUNT”
   3. Type in the Client Name
   4. Loan Instance Name – select “Normal Loan” for the normal loans, then click on CONTINUE button
   5. Data Encoder creates the loan in Mifos. Loan details are booked. Under the “Apply Additional Fees – Fee Type”, Data Encoder must select Insurance Fee and the appropriate Loan Form Fee from the list of fee types. If loan amount = 6000, User should apply the Insurance Fee and the Loan Form Fee 0 to 9,999 to the loan.
   6. Data Encoder clicks on CONTINUE button
   7. Data Encoder with then Preview the repayment schedule, if all details are correct, click on PREVIEW button. If not Click on Cancel, make necessary amendments, then click CONTINUE, then PREVIEW button.
   8. Data Encoder then click on SUBMIT FOR APPROVAL upon reviewing all details.
   9. Mifos will display confirmation screen that import was successful
   10. Data Encoder clicks on “View loan account details now”. Mifos displays Account Summary details and the fees amount will be displayed. Note that the status of the loan is “Application Pending Approval”
   11. Data Encoder repeats the above step for ALL loan applications received from previous day.
   12. Loan application forms are passed to the Account Clerk.
   13. Loan Account Clerk logs into MIFOs, and searches for clients.
   14. Under “Account Information” section, Accountant will click on the loan whose status is “Application Pending Approval”
   15. A new window will open up, click on EDIT ACCOUNT STATUS.
   16. Select Status “Application Approved” add some notes under “Notes”

Post-conditions

• Normal Loans are created and approved in Mifos.
• Appropriate fees are attached to the loans.

Validations

• The Data Encoder should apply Insurance Fee and Loan Form Fee - XXXX. Emily- - If this needs to be enforced in Mifos, then these fees should be attached to the product definition. Otherwise this should be enforced via processes/training. Jakub: No need to consider this requirement in your estimates
• The Advance Loan Fee should not be applied to Normal Loans. * Emily- - again, needs to be handled via process/training. No way to automate this. Jakub: Please ignore this as while
• User should not be allowed to have 2 outstanding Normal Loans. This can be handled by current functionality. Do not estimate. Can be done for product mix probably
• Loan Amount should be within the range defined in the product definition.
• Intereset rate should be within the range defined in the product definition.

Use Case - Users create and approve Advance Loan - Normal Flow

Actors

• Data Encoder
• Account Clerk

Preconditions

• Advance Loan set-up
• Advance Loan Fee set-up
• Data Encoder has rights to create loans
• Account Clerk has rights to approve loans

Basic Flow

1. 2 days before the group meeting.
   1. Clients pays the loan via MPESA.
   2. Loan repayments recorded into Mifos via MPESA-Mifos Repayment Bridge.
2. 1 day before the group meeting.
   1. Data Clerk prints the collection sheet.
   2. Account Clerk writes maximum allowable amount under Issues for Advance Loan.
3. During the group meeting:
   1. Client applies for advance loan.
   2. Group approves the loan.
   3. Loan Officer writes the Requested amount on the Collection Sheet.
   4. Client signs the Collection Sheet.
   5. Loan Officer emails the Collection Sheet, showing the Request Amount and Client Signature to the Data Clerk.
4. At the Head Office, during the group meeting:
   1. The Data Clerk receives the Collection Sheet via email.
   2. Data Encoder logs into MIFOS, and clicks on ‘OPEN NEW LOAN ACCOUNT”
   3. Type in the Client Name
   4. Loan Instance Name – select “Advance Loan” for the normal loans, then click on CONTINUE button
   5. Data Encoder creates the loan in Mifos. Loan details are booked. Under the “Apply Additional Fees – Fee Type”, Data Encoder must select Advance Loan Fee from the list of fee types.
   6. Data Encoder clicks on CONTINUE button
   7. Data Encoder with then Preview the repayment schedule, if all details are correct, click on PREVIEW button. If not Click on Cancel, make necessary amendments, then click CONTINUE, then PREVIEW button.
   8. Data Encoder then click on SUBMIT FOR APPROVAL upon reviewing all details.
   9. Mifos will display confirmation screen that import was successfu
   10. Data Encoder clicks on “View loan account details now”. Mifos displays Account Summary details and the fees amount will be displayed. Note that the status of the loan is “Application Pending Approval”
   11. Data Encoder repeats the above step for ALL loan advance loans in the collection sheet.
   12. The Group Collection Sheet is passed to the Account Clerk.
   13. Loan Account Clerk logs into MIFOs, and searches for clients.
   14. Under “Account Information” section, Accountant will click on the loan whose status is “Application Pending Approval”
   15. A new window will open up, click on EDIT ACCOUNT STATUS.
   16. Select Status “Application Approved” add some notes under “Notes”

Post-conditions

• Advance Loans are created and approved in Mifos.
• Appropriate fees are attached to the loans.

Validations

Emily- - None of the validations below are specific to this feature; please ignore for estimations

• The Data Encoder should apply Advance Loan Fee to the Advance Loan.

• The Normal Loan Fees should not be applied to Advance Loans.
• User should not be allowed to have 2 outstanding Advance Loans.
• Loan Amount should be within the range defined in the product definition.
• Itntereset rate should be within the range defined in the product definition.

Use Case - Fees not Selected by Data Encoder

Emily- - Don't understand this use case. Right now, when you create a loan product, you can specify the fee type asssociated with the product. Either you can use this feature to enforce fees or process/training. We can make fees required for all loan accounts. Jakub: For your estimates, please ignore this use case # During saving of loan creation, user does not select fee type and leave this section blank.

Marie Q: But we canNOT make fees required for a specific product. Is that right?

1. Mifos should throw an error as this is a mandatory field when posting a loan.

Post Condition

• User is unable to proceed until fee type is selected.

Use Case – Data Encoder posts one loan twice

urg. We need to think through this much more carefully- - how we define this restriction, etc. Jakub: Please ignore for now. Kay- - can we use product mix for this # Create a Normal Loan, Approve, and Disburse.

1. Create another Normal Loan and try to save the loan.
2. Mifos should display error message “Client has an existing loan pending approval”

Post Condition

• User is unable to proceed with loan application

Use Case - Data Encoder picks wrong Fee type

Emily: How does the system know whether/not this is the right fee type? You need a use case for how to define the right fee type per product. If we're starting to look at changing loan product definition and fee process- - that's BIG WORK and will be way out of scope. Jakub: For now, please ignore these requirements

1. Data Encoder create a Normal Loan
2. At "apply fees", Data Encoder selects Advance Loan Fee
3. Mifos should throw an error message “ wrong fees type”

Note. The same should be true when applying Insurance and/or Loan Form Fee to Advance Loan.

Post Condition

• User should not proceed until the right Fee type is selected

Use Case - Account Approves loan with no phone number and National ID

Actor

• Data Encoder
• Accountant Clerk
  

Basic flow

1. Loan is posted in mifos, but client has no phone number and National ID
2. Accountant approves the loan
   

Post condition

• User is unable to approve the loan, Mifos will throw an error message "Phone Number and National ID missing"
  

Validation

• Only loans with phone numbers will be approvable.
• User should not be able to approve a loan where client has phone and nationa ID number equals to "NULL"
  

The way this use case is written - it will require work to the Loan Product Definition pipeline, and we’ll need to think through a good way to implement it, so it’s generic. Could you simply make client phone number and national ID required for all clients? Jakub: For now, ignore this requirement - this use case has been changed above.

Export Loan Disbursals

Use Case - Data Encoder Exports a file for loan with status="Application Pending Approval"

Emily- - I’m confused on this use case.  Shouldn’t it be that the export only includes approved loans?  YES. So there’s no need for this use case? YES. Not for development but for KEEF's  testing later.

Actors:

• Data Encoder
  

Preconditions

• * Data Encoder has rights to export file
• * Export file only has approved loans
  
  

Basic flow

1. Data Encoder posts loans
2. Data Encoder exports files
   
   

Postcondition

• Export file should not contine loans with status equals "Application Pending Approval"

Use Case - Extract times out

Don't think this will happen

1. The report times out before completed.
   

Post-conditions

• Report half-created or not created. User is able to re-run the report with complete list of data.
  

Emily - Is there a way to make the export atomic ie, either the entire thing is exported or it fails completely.  We don’t want anything half-created