A Peer-to-Peer Lending Platform Data Model

There are many online portals which allow investors to lend money directly to individual borrowers – with no banks acting as intermediaries. What data model might underlie such a site?

Online lending platforms bring borrowers and investors together and allow them to choose to whom they want to lend their money (in the case of investors) and who they want to borrow money from (in the case of borrowers). Some peer-to-peer lending sites also allow borrowers and investors to make their own deals in terms of lending rates (i.e. interest rates) and loan tenure. They can be good alternatives to traditional lenders such as banks and credit unions (CUs), offering mortgage, car, or private student loans.

Let’s take a quick look at how these portals work and then move on to a data model that could support them.

How Do Peer-to-Peer Lending Platforms Work?

• Borrowers supply their desired loan amount and relevant details like age, employment, current income, current loans, credit score, average monthly bank balance, salary schedule for the last six months, any inquiries or defaults on their accounts in the last twelve months, their reason for borrowing, intention to pay, etc.
• Investors register by filling in relevant details, including the total amount they want to invest. Note that they need to comply with KYC (Know Your Customer), KYB (Know Your Business), and tax regulations. KYC is a process, widely used by financial institutions, that obtains brief information about the identity of a borrower/customer.
• The portals screen borrowers’ profiles and assign them risk ratings (A to F; A stands for best rating, and F stands for worst) based on their current and recent past financial statistics and their borrowing requirements.
• Portals may also decide loan tenures and interest rates; these are primarily based on customers’ risk ratings.
• Borrowers’ loan requests (let’s call them “loan tickets” from now on) are listed (shown on the portal) only after the screening process for that customer is complete.
• Registered investors can view listed loan tickets and their associated risk ratings, borrowing requirements, and other relevant details. These help them to make a decision on their investments.
• To fulfil a loan ticket, investors can contribute any amount, from the portal’s minimum (say $50) to the total loan amount.
• Once a loan ticket is fulfilled, the investors that have contributed to the loan ticket have to release funds to the borrower. Usually, all lending site financial transactions use escrow accounts.
• Once the loan amount is disbursed, borrowers repay the amount in the form of EMIs (Equated Monthly Installments). EMIs are collected in escrow accounts and are eventually distributed back to investors based on their shares in the loan ticket.
• EMI payments include contributions towards both the loan principal and the interest. In the initial stages, interest payments constitute the major part of the EMI.
• There are two possible loan scenarios: borrowers pay some or all the outstanding amount upfront or EMI payment are delayed. These delays can be anywhere from a couple of days to a couple of months. If payments are delayed, borrowers are subject to additional interest and a penalty on defaulted EMIs.
• If borrowers pay part of an outstanding loan amount, it is distributed amongst investors based on their shares in the loan ticket.

The Data Model

You can see the complete data model below. It revolves mainly around two entities: the investors who lend money and the borrowers who request it.

Section 1: Investor

Online peer-to-peer (P2P) lending platforms allow people to register as investors by entering their basic details, including payment methods and nominees. It also captures all transactions that they make against their escrow account with the P2P platform.

The investor table stores investors’ basic details. Most of the columns in this table are self-explanatory except for:

• id – A unique identifier given to each individual investor.
• tax_id – Investor’s government tax ID (or, in the USA, their social security number(SSN)). This column helps the platform stay compliant with tax regulations.
• kyc_complete – The KYC process is performed to capture investors’ complete details. This column holds a Y or an N, depending on whether the process is complete for that investor.
• escrow_account_number – Every investor is assigned a unique escrow account. All financial transactions between investors and borrowers take place through this escrow account.
• fund_committed – The amount that the investor has committed for investment (so far).

The nominee table holds information about investors’ nominees. All investors can register nominees in their profile. Nominees are people known to the investor – most likely their family members or friends – who are entitled to receive payments if the investor dies. All the columns in this table are self-explanatory.

The account_statement table stores the details of all transactions performed by investors. A transaction can be either a deposit or a withdrawal. When an investor puts some money into their escrow account, this is a ‘deposit’ transaction. A ‘withdrawal’ transaction occurs when an investor withdraws some or all of the money in their escrow account. In either case, the closing_balance is updated accordingly.

The payment_method table holds information about the payment methods used to add funds to their escrow account. Investors can add multiple bank accounts to deposit or withdraw their money. The columns in this table are self-explanatory.

Section 2: Borrower

This subject area explains how we capture and maintain borrowers’ details; it also enlightens us on the processes involved in borrower verification, or understanding their ability and willingness to repay.

The process starts with registering borrowers on the site. We will capture information about their education, profession, financial status, and borrowing requirements. Portals usually capture educational details because they play a key role in investors’ decision-making process, especially when borrowers don’t have favourable employment details. Financial details include their monthly income, any current outstanding debts, bank statements for the last six months, any recently bounced checks, and whether they have any regular income.

Once this verification process is complete, borrowers are assigned a risk rating. Their borrowing requirements (i.e. loan tickets) are made available on the portal for public view. At any given point in time, investors can view all open loan tickets, i.e. those which are not yet 100% funded.

The borrower table holds borrowers’ profile details, which are captured in the registration process. The columns in this table are self-explanatory, except for the following:

• kyc_complete – Holds a Y or an N, depending on whether the KYC process is complete for this borrower.
• highest_qualification – The highest educational qualification of this borrower; e.g. undergraduate degree, graduate degree, etc.
• passout_year – The year when the borrower completed their highest qualification.
• university_name – The university where the borrower received their highest qualification.

The employment_detail table stores the employment details of borrowers. The columns in this table are self-explanatory.

Once the portal verifies borrowers’ basic details, it creates loan tickets for their requirements and captures their assets and liabilities. Asset and liability details are made available to investors for reference. Investors may need to refer to these details to determine borrowers’ ability to repay.

A loan ticket is created for each loan requirement. This information is stored in the loan_ticket table. The columns are:

• id – A unique number given to each loan ticket.
• borrower_id – A referenced column from the borrower table.
• loan_amount – The desired loan amount.
• loan_tenure_in_months – The number of months during which the loan will be repaid.
• interest_rate – The interest rate for that loan.
• risk_rating – A risk rating is assigned to each borrower. It depends on their assets, liabilities, and other financial details.
• reason_for_loan – Why the borrower needs this loan. The reason for a loan is a key factor for some investors. For example, some investors prefer to invest for education reasons or debt consolidation, but they may stay away from loans that are financing a vacation.
• ability_to_repay – The portal captures bullet points referring to the borrower’s ability to repay a loan. These bullet points are considered by investors during their decision-making process.
• risk_factors – This column stores information captured by the portal with reference to the risks associated with investing in this loan.

Risk ratings are calculated through an algorithm that is based on the details submitted by the borrower. A platform employee reviews each borrower's profile, validates their financial details (including their credit score), and can manipulate risk rating, loan amount (e.g. by lowering the amount if needed) and loan tenure during the processing of the loan application.

The borrower_liability table holds details about borrowers’ outstanding loans. The columns in this table are:

• id – The primary key of the table.
• loan_ticket_id – References the loan_ticket table.
• liability_cost –The outstanding amount of the loan.
• liability_type – The type of liability, e.g. home loan, car loan, personal loan, etc.
• liability_start_date – The date when the loan was taken out.
• liability_end_date – The date when the loan will be fully paid off.

The borrower_asset table stores information about borrowers’ assets and investments. These assets can be fixed deposits, real estate, and investments (equity/debt) that borrowers own fully or partially. It is not actually collateral for the loan, but it can be liquidated if needed. In addition, providing asset details makes a borrower's profile stronger. The columns in this table are:

• id – The primary key of the table.
• loan_ticket_id – References the loan_ticket table.
• asset_type – The type of asset, e.g. real estate, fixed deposit, mutual funds, shares, etc.
• asset_value – The asset’s current market value.
• ownership_percentage – The borrower’s percentage of ownership. Some assets are purchased in partnership with another person.
• possession_since – The date when the borrower acquired this asset.

Section 3: Loan Fulfillment and Repayment

This subject area holds the details of loan proposals, fulfilment, and repayment.

The investor_proposal table stores data associated with investors’ proposals on loan tickets. After loan tickets are posted on the portal, investors can submit their proposals on them. Most of the columns in this table are self-explanatory, except for:

• proposal_amount – The amount that the investor wants to lend. Investors can propose amounts up to 100% of the loan ticket.
• proposal_date – The date when the proposal was submitted.
• cancel_date – Investors can cancel proposals that have not been converted into disbursement requests. This column holds the date (if any) when the proposal was cancelled.
• last_update_date – Investors can also change the amount of a proposal, but only before it is converted to a disbursement request. This column holds the date of the most recent proposal update.

Now, let’s move on to the loan_ticket_fulfilment table. Once a loan ticket is completely funded, fulfilment requests are created to fulfil the loan ticket. These fulfilment requests are also known as disbursement requests, i.e. for the investors to release the funds to the borrower’s account. (Note: This table also contains EMI and pre-closure information, which we will discuss separately.) The columns in this table are:

• id – A unique number assigned to each fulfilment request. If there are 10 investors contributing to a loan ticket, there would be 10 records in this table referring to that loan ticket.
• investor_proposal_id – The ID of each investor who has contributed to the loan ticket; this also references the amount that investor needs to release.
• release_date_from_investor – The date when the investor released funds to the escrow account.
• disburse_date_to_borrower – The date when the amount is credited to the borrower’s account. Usually, both of these transactions happen on the same day or with a gap of one business day.
• last_update_date – This column is updated when a record is updated.

The loan_ticket_fulfillment table also holds information about each investor’s share in pre-EMI and EMI payouts. When borrowers have only accessed part of their loan amount, they are required to pay interest only on the amount disbursed (until the full loan amount is available). This interest is called pre-EMI interest (PEMI) and is payable monthly until the final disbursement is made, after which the EMIs commence.

• pre_emi_due_date – The date when the pre-emi Is due. Usually, it is the last day of the month when that loan was fulfilled.
• pre_emi_amount – The calculated amount of pre-emi.
• emi_amount – The amount the borrower pays as a monthly installment.
• emi_start_date – The date when the EMI starts. Usually, it is the first day of the next month (e.g. a loan is fulfilled on January 13 and EMI starts on February 1).
• emi_end_date – The date when the borrower is scheduled to pay the last EMI. This is a calculated column that is updated at the time the loan is fulfilled. If a loan tenure is 12 months and the EMI start date is February 1, 2019, then the last EMI will be paid on January 1, 2020.
• number_of_total_emi – The number of EMIs to be paid in this loan.

Borrowers can close (pay off) their loans early by paying the outstanding principal amount as a whole. In banking terms, this is known as the ‘pre-closure’ of a loan. A borrower can pre-close the loan for one or more lenders at a time by paying that lender’s share of the outstanding principal amount. I’ve added two columns to the table to handle this case:

• pre_closure_flag – This column signifies whether the loan is pre-closed. By default, this column remains blank.
• pre_closure_date – The date when the loan is pre-closed. For an ongoing loan, this column remains blank.

The loan_repayment_schedule table holds details about loan repayments. As soon as a loan is disbursed, records are inserted into this table for each EMI payment schedule. If, for example, there are 10 investors who invested in one loan ticket, there would be 10 records in the loan_ticket_fulfillment table. If the tenure for that loan is 12 months, the loan_repayment_schedule table will contain 120 records (10 records x 12 months).

Before we continue, have a look at a sample repayment schedule:

Several columns in the loan_repayment_schedule table are amount columns, created to store the amount due and the amounts paid towards various EMI components. Some of the other columns are:

• id – A unique number assigned to each payment.
• loan_ticket_fulfillment_id – This column holds details related to the investor, loan ticket, and borrower.
• is_emi_payment_defaulted – If the EMI is not paid by the due date, this column is updated with ‘Y’. By default, this column remains blank.
• is_emi_payment_advanced – If one or more future EMIs have already been paid, this column is updated to ‘Y’ against all those records.

What Do You Think About the Lending Platform Data Model?

Do you think that allowing borrowers and investors to make their own lending deals is complex? What changes does this data model need if we were to allow them to negotiate on lending rates and tenure?

Please let us know your views in the comments section.