Hi, I’m Ifeyinwa Ezeanokwasa
a Financial Manager Data Analyst/BI Risk Manager

Loan Data Analysis Dashboard Documentation

(Developed for The Data Immersed (TDI) using Microsoft Excel)

Introduction:

This documentation outlines the Loan Data Analysis Dashboard created for The Data Immersed (TDI), a financial services company specializing in providing loans to individuals and small businesses. The dashboard is designed to provide the company’s management team with insights into their loan portfolio, risk factors, and customer demographics, enabling data-driven decisions on loan approvals, interest rates, and risk management.

This dashboard is an analytical tool that addresses TDI’s key concerns, such as high default rates, loan performance across different regions, and loan type risk profiles. It provides actionable insights through interactive graphs and key metrics.

Data Cleaning and Transformation

The dataset provided was quite messy, requiring extensive cleaning across all columns. I utilized various Excel functions, including TEXT, CLEAN, and PROPER, to standardize the data. Additionally, I deleted three completely empty rows and replaced empty cells in many columns with appropriate values. Below is a breakdown of the cleaning process applied to each column:

1. Loan Application ID (formerly Id):
   Cleaning Process: Applied the TRIM and CLEAN functions to remove extra spaces and non-printable characters. An empty cell was replaced with ‘0’.
2. State Address (formerly address_state):
   Cleaning Process: Used the VLOOKUP function to replace state abbreviations with full state names.
3. Application Type (formerly application_type):
   Cleaning Process: Applied the TRIM and CLEAN functions to remove special characters and excess spacing.
4. Years of Employment (formerly emp_length):
   Cleaning Process: Applied the TRIM and CLEAN functions to remove strange characters and extracted only the numerical values. Values like “< 1 year” were interpreted as ‘0’, while “10 years+” was simplified to “10.”
5. Emp_Title:
   Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions. Empty cells were filled with “Na.”
6. Grade:
   Cleaning Process: Grouped grades as follows: A-B = Performing, C-D = Sub-standard, E = Doubtful, F-G = Loss.
7. Home Ownership:
   Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions to clean up the data. Abbreviated terms were replaced with full names.
8. Loan Disbursement Date (formerly issue_date):
   Cleaning Process: The date format was corrected using the TEXT TO COLUMNS feature, reorganizing the scattered date values.
9. Last Credit Pull Date (unchanged):
   Cleaning Process: The messy date format was fixed with the TEXT TO COLUMNS feature.
10. Last Payment Date (unchanged):
    Cleaning Process: Similar to other date columns, the scattered date format was cleaned using the TEXT TO COLUMNS feature.
11. Loan Status (unchanged):
    Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions for better formatting.
12. Grouped Loan Status (new column):
    Cleaning Process: Created a new column that grouped loan statuses into two categories: Performing (Fully Paid & Current) and Non-Performing (Charged Off).
13. Next Payment Date (unchanged):
    Cleaning Process: Applied the TEXT TO COLUMNS feature to correct date arrangements.
14. Customer ID (formerly member_id):
    Cleaning Process: Applied the CLEAN function to standardize this column.
15. Loan Purpose (formerly purpose):
    Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions to clean the data.
16. Loan Sub-grade (formerly sub_grade):
    Cleaning Process: Applied the TRIM and CLEAN functions.
17. Loan Tenure (Months) (formerly term):
    Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions.
18. Loan Tenure (Days) (new column):
    Cleaning Process: Created a column that calculates loan tenure in days based on loan tenure in months.
19. Last Repayment Date (new column):
    Cleaning Process: Derived from the Loan Disbursement Date and Loan Tenure (Days).
20. Verification Status (unchanged):
    Cleaning Process: Applied the TRIM, PROPER, and CLEAN functions. Renamed “Source Verified” to “Verified.”
21. Annual Income (unchanged):
    Cleaning Process: Applied the TRIM and CLEAN functions, and changed the format to currency. Empty cells were replaced with ‘0.’
22. Grouped Annual Income (new column):
    Cleaning Process: Created income groups as follows:

• Low Income <= 30,000
• Medium Income <= 60,000
• High Income <= 100,000
• Very High Income > 100,000

23. Debt-to-Income Ratio (formerly dti):
    Cleaning Process: Applied the TRIM and CLEAN functions, approximated values to the nearest whole number, and converted the format to percentage.
24. Monthly Installment (formerly installment):
    Cleaning Process: Applied the TRIM and CLEAN functions, and changed the format to currency.
25. Interest Rate (formerly int_rate):
    Cleaning Process: Applied the TRIM and CLEAN functions, approximated values to whole numbers, and converted the format to percentage.
26. Grouped Interest Rate (new column):
    Cleaning Process: Created interest rate groups as follows:

• Low <= 5%
• Medium <= 10%
• High <= 15%
• Very High > 15%

27. Monthly Interest (new column):
    Cleaning Process: Created using the formula:
    (LoanAmount∗AnnualInterestRate∗LoanTenureinMonths)/(12∗100).
28. Total Interest (new column):
    Cleaning Process: Created a column to calculate the total interest over the loan’s duration.
29. Expected Repayment (new column):
    Cleaning Process: Created a column for the total repayment amount, combining loan amount and total interest.
30. Outstanding Loan Amount (new column):
    Cleaning Process: Calculated the difference between Expected Repayment Amount and Total Loan Amount Paid by the customer.
31. Loan Amount Disbursed (formerly loan_amount):
    Cleaning Process: Applied the TRIM and CLEAN functions, formatted the values as currency, and replaced missing cells with ‘0.’
32. Total Credit Lines of the Applicant (formerly total_acc):
    Cleaning Process: Applied the TRIM and CLEAN functions.
33. Total Loan Amount Paid by the Customer (formerly total_payment):
    Cleaning Process: Applied the TRIM and CLEAN functions, and replaced missing cells with ‘0.’
34. Loan-to-Value Ratio (new column):
    Cleaning Process: Created a column to calculate the loan-to-value ratio using the formula: (Loan Amount/Annual Income)*100
35. Default Amount (new column):
    Cleaning Process: Calculated based on the formula:
    (Charged−off Installment Payment∗LoanTenure) −Total Loan, to understand the actual default amounts.

Attached below the messy dataset before it was cleaned:

Attached is the dataset below after cleaning up the dataset:

Dashboard Overview:

Key Performance Indicators (KPIs) for TDI’s Loan Analysis Report

The following Key Performance Indicators (KPIs) have been identified as critical metrics to track and analyze in TDI’s Loan Analysis Report. These KPIs are essential for monitoring loan performance, identifying trends, managing risk, and enabling data-driven decision-making. By continuously tracking these indicators, TDI will be able to refine its loan approval processes, optimize profitability, and mitigate risks associated with loan defaults:

1. Total Loan Application: Displays the total number of loan applications received by TDI across different quarters.
2. Total Loan Credited: Indicates the total amount disbursed by TDI in loans.
3. Total Loan Paid: Reflects the total amount repaid by borrowers, a crucial measure of TDI’s cash flow and loan performance.
4. Total Default Amount: metric calculates the total monetary value of loans that have defaulted.
5. Average Interest Rate: Shows the average interest rate charged on the loans.
6. Average Loan-to-Value (LTV) Ratio: Reflects the ratio of the loan amount to the value of the asset, important for understanding the collateral adequacy. The collateral structured used is the annual income of the customers.
7. Average Debt-to-Income (DTI) Ratio: Highlights the average of how much of a borrower’s income is being used to repay debt, a key indicator of potential default risk.
8. Non-Performing Loans (NPL): Indicates the percentage of loans that are in default or at risk of default, a critical measure of portfolio risk.
9. Average Non-Performing Loan Credited: The Average Non-Performing Loan Credited metric calculates the average loan amount disbursed for loans that have become non-performing, giving insight into the typical loan size contributing to defaults.
10. Average Performing Loan Credited: The Average Performing Loan Credited metric calculates the average loan amount disbursed for loans that are currently being repaid on time, providing insight into the typical loan size of performing loans.
11. Performing Loan Credited: The Performing Loan Credited metric calculates the total value of loans disbursed that are being repaid on time, helping assess the overall health of the performing portion of the loan portfolio.
12. Non-Performing Loan Credited: The Non-Performing Loan Credited metric calculates the total value of loans disbursed that have entered default or delinquency, highlighting the financial risk associated with non-performing loans in the portfolio.
13. Average Non-Performing DTI: The Average Non-Performing DTI metric calculates the average debt-to-income (DTI) ratio of borrowers with non-performing loans, indicating how much of their income is tied to debt repayment, which may contribute to defaults.
14. Average Performing DTI: The Average Performing DTI metric calculates the average debt-to-income (DTI) ratio of borrowers with performing loans, showing how much of their income is allocated to debt repayment while staying current on payments.
15. Default Rate: The Default Rate metric calculates the percentage of total loans that have entered default out of the total loan portfolio, helping measure the risk and financial health of the loan portfolio.

Summary Dashboard for TDI’s Loan Analysis Report

1. Performing vs. Non-Performing Loans (Pie Chart):

This report aims to evaluate the performance of TDI’s lending activities and assess the quality of our loan portfolio through an in-depth analysis provided by the Loan Data Analysis Dashboard. The evaluation differentiates between “Performing Loans” and “Non-Performing Loans” using specific loan status criteria, such as payment performance, interest rates, customer verification, and loan purpose. The dashboard offers insights into key metrics, allowing us to identify risk areas, monitor loan performance, and enhance the overall health of our loan portfolio by addressing high-risk segments and optimizing approval processes.

• Metric: The proportion of performing versus non-performing loans in the overall portfolio.
• Graph: The pie chart shows that 86.17% of loans are performing, while 13.83% are non-performing.
• Insight: A significant portion of loans are performing, but the 13.83% NPL ratio exceeds industry benchmark of 5%. This suggests a need to focus on risk management for certain customer segments or loan types.
• Mitigation: TDI can mitigate this by refining its credit risk assessment process, particularly focusing on high-risk borrowers and regions where defaults are more prevalent.

2. Loan Purpose by Loan Credited (Bar Chart):

• Metric: The amount of loans disbursed for different purposes (e.g., debt consolidation, credit card, home improvement).
• Graph: Debt Consolidation represents the largest loan purpose, with 53.35% of the total loan credited, followed by Credit Card loans at 13.51%.
• Insight: Debt consolidation loans dominate the portfolio, but they may also represent a high-risk category if defaults are prevalent.

Mitigation: TDI can perform a deeper risk analysis on debt consolidation loans and consider imposing stricter approval criteria or monitoring these loans more closely to reduce default risk.

3. Comprehensive Loan Performance Analysis by Quarter: Key Metrics, Insights, and Risk Mitigation Strategies for TDI Stakeholders:

This chart below, provides a comprehensive overview of loan performance at TDI across different quarters (Q1 to Q4). The visualizations capture the loan application trends, loan approvals (credited), loan repayments, and crucial financial ratios like Average Interest Rate, Loan-to-Value Ratio (LTV), and Debt-to-Income Ratio (DTI).

I. Total Loan Applications:

• Overview: This metric highlights the total number of loan applications TDI received across the four quarters.
• Trend: The number of loan applications increased consistently, with Q4 seeing the highest number of applications (12.1K).
• Insight: The rising loan applications suggest increased demand for TDI’s loan products, either due to favorable market conditions, enhanced marketing efforts, or customer satisfaction with loan terms.
• Mitigation: With increasing applications, TDI should ensure that the loan approval process remains robust. They may also need to scale their operational capacity to manage the larger volumes of applications.

II. Total Loan Credited:

• Overview: Represents the total amount of loans that were successfully disbursed to applicants.
• Trend: The amount credited to borrowers increased quarter over quarter, with Q4 showing the largest credited value ($146.6M).
• Insight: TDI appears to be keeping pace with loan demand, but the rising loan values indicate that the company is taking on more risk by approving higher loan amounts.
• Mitigation: TDI should ensure their credit risk management systems are strong to safeguard against potential defaults, especially as the total loan amounts increase.

III. Total Loan Paid:

• Overview: Reflects the total amount repaid by borrowers.
• Trend: The loan repayments follow a similar upward trend, with Q4 showing the highest repayment value ($157.6M).
• Insight: The steady rise in repayments is a good indicator that borrowers are successfully repaying their loans. This suggests that the loans being approved are manageable for the borrowers.
• Mitigation: TDI should maintain or enhance borrower support services (e.g., reminders, grace periods) to sustain this high repayment performance. Monitoring any discrepancies between loans credited and loans paid will also be key in managing liquidity.

IV. Average Interest Rate:

• Overview: This KPI shows the average interest rates charged on loans each quarter.
• Trend: Interest rates remained stable, hovering between 11.7% and 12.2% across the year.
• Insight: This stability indicates that TDI is offering competitive loan terms, which may contribute to the increasing demand. However, any external factors like inflation or changing monetary policies could impact future interest rates.
• Mitigation: While keeping interest rates stable, TDI should ensure profitability by adjusting rates in response to market conditions. Regular reviews of rate structures will help balance customer affordability and business profitability.

V. Average Loan-to-Value (LTV) Ratio:

• Overview: Measures the risk TDI takes by comparing the loan amount to the value of the collateral.
• Trend: The LTV ratio has gradually increased, peaking at 19.8% in Q4.
• Insight: A higher LTV means TDI is issuing larger loans relative to the value of the collateral. While this may be beneficial for borrowers, it increases the company’s exposure to risk if the borrower defaults and the collateral value isn’t sufficient to cover the loan.
• Mitigation: TDI should consider tightening its lending criteria, particularly for high LTV loans, and closely monitor loans that exceed a safe LTV threshold. Regular reappraisals of collateral values could help manage this risk.

VI. Average Debt-to-Income (DTI) Ratio:

• Overview: Reflects the percentage of the borrower’s income used to repay debts.
• Trend: The DTI ratio has shown a slight upward trend, with Q4 reaching 13.5%.
• Insight: A rising DTI ratio suggests that borrowers are taking on more debt relative to their income, which could increase the likelihood of default if their financial circumstances worsen. While the increase is modest, it still signals that borrowers may be under more financial strain.
• Mitigation: TDI could consider adjusting its loan approval criteria, focusing on borrowers with lower DTI ratios. Additionally, offering financial literacy programs or debt consolidation options could help borrowers manage their overall debt load more effectively.

4. Loan Application, Crediting, and Payment Analysis:

This chart below presents a breakdown of three key metrics related to loan applications, crediting, and payments, segmented into different loan statuses: Fully Paid, Current, and Charged Off:

a. Total Loan Application (Segmented by Loan Status)

• Fully Paid: Represents 32.1K loan applications that have been fully processed, credited, and repaid. This is a positive indicator showing the company’s ability to attract and process loan applications successfully.
• Current: 1.1K applications are still active, meaning the borrowers are making payments as per schedule. This reflects a segment of loans that are currently performing.
• Charged Off: 5.3K applications have resulted in loans that defaulted and were charged off, meaning the company was unable to recover the full amount. This segment is a potential risk and needs mitigation.

Insights:

• A large majority of loans (32.1K) have been fully paid, which is a good sign for overall loan performance.
• However, the 5.3K charged-off loans raise concerns about borrower risk and the need for stronger pre-approval processes or post-disbursement follow-ups.

Mitigation:

• Improved Risk Profiling: Conduct more rigorous background checks and credit scoring before approving loans to lower default rates.
• Early Intervention for Active Loans: Implement automated alerts to identify at-risk loans from the current segment before they move to charged-off status.

b. Total Loan Credited (Segmented by Loan Status)

• Fully Paid: $351.3M in loans were credited and have been completely repaid. This indicates a strong collection strategy for the majority of the loans.
• Current: $18.9M are loans that have been credited and are currently active, meaning they are in the process of being paid back.
• Charged Off: $65.5M represents the total value of loans that were credited but eventually charged off. This suggests a significant portion of the loan book is non-performing.
• Insights: The majority of credited loans have been successfully repaid, but the charged-off amount ($65.5M) is a considerable proportion of total loans and reflects a need for improved credit risk management.

Mitigation:

• Tighter Lending Policies: Apply stricter lending criteria and regularly reassess borrower creditworthiness, especially for high-value loans.
• Debt Recovery Solutions: Increase the recovery efforts, including debt restructuring or legal action, for charged-off loans.

c. Total Loan Paid (Segmented by Loan Status)

• Fully Paid: $411.6M in loans have been fully repaid, which reflects a strong cash flow from loan repayments.
• Current: $24.2M represents ongoing loan repayments, indicating a healthy loan portfolio that is actively being serviced.
• Charged Off: $37.3M of loans were partially repaid before being charged off. This reflects partial success in recovering some value from defaulted loans but also highlights lost potential revenue.

Insights:

• While the fully paid amount ($411.6M) is a positive indicator of loan repayment strength, the $37.3M in charged-off loans shows the importance of loan repayment tracking.

Mitigation:

• Enhanced Repayment Plans: Provide flexible repayment options for borrowers who are struggling financially to avoid moving them into the charged-off category.
• Monitoring High-Risk Loans: Focus more on loans in the current status ($24.2M) and offer support programs to prevent them from becoming non-performing loans.

Overview Dashboard for TDI’s Loan Analysis Report

1. Total Loan Credited by Interest Rate:

The chart titled “Total Loan Credited by Int_Rate” visually represents the distribution of loan amounts across different interest rate categories: High, Medium, and Very High.

• High-Interest Rate loans account for the largest total loan amount, at $206.2M.
• Medium Interest Rate loans see a drop in total credited amounts to $106.4M.
• Very High-Interest Rate loans show a slight increase from Medium, at $123.1M.

In simple terms, this chart shows that loans with higher interest rates (within the “High” range) represent the majority of total loan amounts, while medium-interest loans make up the lowest total. Despite the high-interest loans attracting larger amounts, the increase for “Very High” interest loans is relatively modest.

In relation to the dashboard, this insight highlights a potential risk area—larger loan amounts are concentrated in high-interest categories, which might have a higher probability of default if interest rates strain borrowers. The management can use this information to re-evaluate loan approval criteria and interest rate policies to mitigate risks associated with high-interest lending.

2. Total Loan Applications:

• Overview: This metric highlights the total number of loan applications TDI received across the four quarters.
• Trend: The number of loan applications increased consistently, with Q4 seeing the highest number of applications (12.1K).
• Insight: The rising loan applications suggest increased demand for TDI’s loan products, either due to favorable market conditions, enhanced marketing efforts, or customer satisfaction with loan terms.
• Mitigation: With increasing applications, TDI should ensure that the loan approval process remains robust. They may also need to scale their operational capacity to manage the larger volumes of applications.

3. Loan Application by Tenure and Loan Status:

• Purpose: This graph tracks loan applications by tenure (36 months vs. 60 months) and loan status (fully paid, charged off, current).
• Insight: 60-month loans show higher rates of defaults compared to 36-month loans, suggesting that longer loan tenures carry higher risks.
• Mitigation: TDI can incentivize shorter-term loans or tighten approval criteria for 60-month loans to reduce the default rates.
• Stakeholder Concern Addressed: This insight helps TDI stakeholders assess the impact of loan tenure on performance, allowing them to make better decisions about loan offerings.

4. Loan Application by Home Ownership:

• What it Shows: This pie chart divides loan applications by homeownership status (Own, Mortgage, Rent).
• Identified Problem: Borrowers who rent have a higher likelihood of default, while those who own their homes or have a mortgage are more likely to repay.
• Mitigation: TDI may consider adjusting its loan approval criteria based on homeownership status, offering more favorable terms to homeowners, who are typically lower risk.

5. Loan Application by Verification Status Chart:

This chart visualizes the total number of loan applications segmented by their verification status. Typically, verification status indicates whether a borrower has been successfully verified for the loan process, which directly impacts the likelihood of loan approval and default rates. Here’s a detailed breakdown:

• Verified Applications:

·         This segment represents the number of loan applications where the borrowers have been successfully verified.

·         Insight: Verified applications are a critical indicator of the company’s risk mitigation efforts. A higher percentage of verified loans generally implies better credit screening and reduces the risk of default.

• Not Verified Applications:

·         This shows the number of applications where verification was incomplete or unsuccessful.

·         Insight: These applications carry a higher risk of default, as borrowers who have not been verified could have insufficient or false information. Granting loans to unverified applicants increases the probability of encountering issues such as fraud, late payments, or default

• Mitigation: TDI should consider making customer verification mandatory for all loans or apply higher interest rates to unverified loans to compensate for the additional risk.

6. Heat Map – Loan Approval by Region:

• What it Shows: This heat map visualizes loan approval rates by geographic region, with high default rates identified in California and Florida.
• Identified Problem: Certain regions exhibit higher default rates, which may be due to regional economic conditions, local industries, or borrower profiles.
• Mitigation: TDI can adopt region-specific lending strategies, such as tightening approval criteria in high-risk regions or offering adjusted interest rates to compensate for regional economic risks.

7. Loan Application by Year of Service:

The Loan Application by Year of Service graph shows the number of loan applications based on how long borrowers have been with their employer.

• Employees with 0-3 years of service show a steady number of loan applications, peaking at 4.6K in the first year and decreasing slightly afterward. This indicates that newer employees are more likely to apply for loans, likely due to a need for financial stability early in their careers.
• As the years progress (from 4 to 9 years), the number of applications steadily declines, possibly reflecting greater financial security or fewer borrowing needs as employees become more established in their jobs.
• Interestingly, there’s a sharp increase at 10 years of service, with 8.9K loan applications, suggesting that long-term employees may seek loans for significant life events or investments, such as home purchases, due to greater job stability.

Insights:

1. New Employees (0-3 years) have a relatively high loan application rate, which could indicate a higher risk of default if their financial situations are less stable.
2. The sharp spike at 10 years suggests that employees with a decade of service are more likely to request larger loans, posing potential portfolio risk if defaults occur.

Mitigation:

• Implement stricter approval criteria for new employees (0-3 years) to reduce risk.
• Monitor long-term employees’ loan requests carefully, offering tailored loan products or setting caps on loan amounts at 10 years to minimize risk.

This chart helps TDI balance risk by identifying trends in loan application behavior, enabling data-driven decisions to safeguard the loan portfolio.

8. Loan Application by Group-Grade:

The Loan Application by Group-Grade graph categorizes loan applications based on their performance status: Performing, Substandard, Doubtful, and Loss. The length of each bar reflects the number of loans in each group.

1. Performing Loans (21.4K): This category has the highest number of loans, indicating that a significant portion of TDI’s portfolio consists of loans that are being repaid on time. It shows the bulk of the company’s lending activity is healthy, which is a positive sign for overall loan performance.
2. Substandard Loans (13.1K): A notable number of loans fall under this group, indicating that a considerable portion of loans are at risk of becoming non-performing. These loans may have delayed payments or other risk indicators, but they haven’t defaulted yet.
3. Doubtful Loans (2.8K): These loans are closer to default, and their status suggests a high likelihood of failure to repay. The number of doubtful loans is concerning, as these pose a risk of financial loss to TDI.
4. Loss Loans (1.3K): These loans have defaulted and are unlikely to be recovered. While the number is relatively small, it represents financial losses for TDI, highlighting the need for better risk mitigation.

Insights:

• A significant portion of the loans (13.1K) is substandard, indicating potential risks that could worsen.
• Although a smaller number of loans fall under Doubtful and Loss, these categories should be addressed to prevent further deterioration of the loan portfolio.

Risk Mitigation:

• Strengthen monitoring of substandard loans to prevent their transition into doubtful or loss categories.
• Enhance loan approval criteria to focus on creditworthiness and financial stability.
• Offer loan restructuring or payment plans for borrowers in the substandard category to help them move back into performing status.

This graph highlights the distribution of loan performance grades, helping TDI focus on high-risk categories and take preemptive measures to safeguard its portfolio.

Loan Performance Analysis Dashboard for TDI’s Loan Analysis Report

1. Loan Performance by Interest Rate (Bar Chart):

• Metric: The distribution of performing and non-performing loans across interest rate categories (High, Medium, Very High).
• Graph: Loans with higher interest rates show a higher likelihood of default, as seen by a larger proportion of non-performing loans in the Very High-interest rate category.
• Insight: Higher interest rates tend to correlate with higher default rates. This indicates that loans priced at very high-interest rates may pose greater risk to TDI.
• Mitigation: Adjusting the pricing strategy to balance profitability with risk by either lowering interest rates for high-risk borrowers or reducing loan amounts in these cases.

ii. Non-performing Loan by Annual Income Grouping:

The Non-performing Loan by Annual Income Grouping pie chart shows how borrowers in different income brackets are defaulting on loans:

• Medium Income (40%): Most defaults happen here. Borrowers may be overextending their finances.
• High Income (37%): High-income earners also struggle with loans, likely due to large debts or lifestyle costs.
• Very High Income (16%): A smaller portion, but some wealthy borrowers are still defaulting.
• Low Income (7%): Few defaults here, possibly because low-income borrowers take smaller loans or are more cautious.

Insights:

• Medium and high-income groups are defaulting more, suggesting financial strain despite higher incomes.

Mitigation:

• Adjust loan terms for higher-income borrowers.
• Monitor for overborrowing, and offer support like loan restructuring.

iii. Top 3 Performing and Non-Performing Loans by Purpose:

The Top 3 Performing and Non-Performing Loans by Purpose chart compares loan performance based on usage:

• Non-Performing Loans:
  • Debt Consolidation ($36.3M): Most defaults occur in debt consolidation.
  • Credit Card ($6.7M): Some defaults but not as severe.
  • Home Improvement ($3.9M): Lowest non-performing rate.
• Performing Loans:
  • Debt Consolidation ($196.2M): Highest performing loan category.
  • Credit Card ($52.2M): Second highest.
  • Home Improvement ($29.4M): Performing well but lower volume.

Insights:

• Debt consolidation has high performance but also the most defaults.

Mitigation:

• Strengthen risk evaluation for debt consolidation loans.

iv. Non-Performing Loan by Quarter:

The Non-Performing Loan by Quarter graph illustrates the distribution of defaulted loans across four quarters:

• Q1 ($7.7m): Non-performing loans start at a relatively low level.
• Q2 ($10.7m): There is an increase in loan defaults, indicating early signs of deteriorating loan performance.
• Q3 ($12.8m): Defaults continue to rise, suggesting growing financial strain or ineffective risk mitigation strategies.
• Q4 ($17.6m): The highest proportion of non-performing loans occurs, pointing to significant issues in loan repayment or external economic factors affecting borrowers’ ability to meet obligations.

Insights:

• The steady increase in non-performing loans suggests a worsening trend in loan performance, potentially caused by factors like poor economic conditions, ineffective loan monitoring, or lenient credit approval criteria.

Mitigation:

• Strengthen credit risk assessments and tighten lending criteria.
• Implement stricter follow-up processes for at-risk borrowers.
• Introduce targeted loan restructuring or assistance programs to reduce defaults before they escalate further.

v. Non-Performing Loan by Interest Rate:

In the Non-Performing Loan by Interest Rate graph from the Loan Performance Analysis Dashboard:

• Very High-Interest Rate Loans has the highest proportion of non-performing loans from the month of May up until December. This could be because borrowers with higher rates may struggle with repayment due to the heavier financial burden.
• High-Interest Rate Loans show a moderate level of non-performing loans, from May up until December where borrowers face manageable but occasionally challenging repayment conditions, but had an increase rate of default from the beginning of the year up until April as compared to Very Hight Interest rate, which depicts strains on their Income.
• Medium -Interest Rate Loans tend to have the fewest non-performing loans, as borrowers have smaller payments, making repayment easier and less likely to default.

Insights:

• Borrowers with high-interest loans are more prone to default, suggesting a link between high interest rates and increased risk.
• Medium and low-interest loans perform better, showing fewer defaults, possibly because these loans are easier to manage financially.

Mitigation:

• Offer loan restructuring options to high-interest rate borrowers to reduce their payment burden.
• Reevaluate the criteria for issuing high-interest loans to minimize exposure to default risk.
• Implement more frequent monitoring and support for borrowers with higher interest loans to help prevent defaults.

Recommendation and Strategies to Stakeholder

The Loan Data Analysis Dashboard offers critical insights into the performance and risk associated with TDI’s loan portfolio. It provides a comprehensive view of loan applications, credited amounts, interest rates, and repayment trends, alongside a clear distinction between performing and non-performing loans. By breaking down loan performance by purpose, income grouping, interest rate, and verification status, TDI can gain a deeper understanding of key factors influencing loan default rates and repayment behaviors.

Key insights derived from the dashboard include:

• Loan Performance by Interest Rate: High-interest loans are more likely to default, highlighting the need to reassess high-interest loan offerings.
• Non-Performing Loans by Income Grouping: Loans to medium and low-income groups experience higher rates of default, suggesting that income-based risk assessment and better-targeted loan products are crucial for minimizing defaults.
• Purpose-Based Loan Performance: Loans for credit card purposes outperform those for debt consolidation and home improvement, revealing potential areas for portfolio reallocation or more stringent criteria for higher-risk categories.
• Verification Status and Loan Default: Verified loans tend to perform better, emphasizing the importance of rigorous verification processes during loan approval.

Actionable Recommendations:

1. Revise Loan Approval Criteria: High-risk borrowers, particularly those in high-interest and low-income segments, should face more stringent approval criteria or be offered tailored products with structured repayment plans.
2. Region-Specific Adjustments: The regional loan performance analysis indicates that localized strategies, such as region-specific interest rates and loan conditions, could improve repayment performance.
3. Risk-Based Interest Rate Strategy: Implementing a dynamic interest rate model that adjusts based on the borrower’s risk profile could help mitigate defaults, particularly among high-risk borrowers.
4. Loan Restructuring: Offer restructuring options for borrowers struggling with high interest rates or large loan amounts to reduce non-performing loan percentages and improve overall repayment rates.
5. Enhanced Monitoring and Support: Increase post-loan issuance support and monitoring for high-risk categories, providing early intervention for borrowers at risk of defaulting.

Conclusion:

The dashboard equips TDI’s management with a robust, data-driven framework to continuously assess and optimize the loan portfolio. Through detailed performance metrics and breakdowns by loan purpose, interest rate, income, and verification status, it offers a multi-dimensional analysis that supports effective decision-making.

The insights gained from the dashboard help TDI balance profitability with risk by identifying problematic trends, high-risk borrowers, and underperforming loan categories. For example, by recognizing that high-interest loans are more prone to default, TDI can implement strategic changes in loan offerings, such as interest rate adjustments and loan restructuring options. Similarly, the analysis reveals opportunities to optimize loan approval processes based on borrower profiles, thus minimizing the likelihood of future defaults.

The dashboard is a valuable tool for continuously monitoring loan health, making proactive adjustments, and ensuring TDI’s long-term financial success. With these actionable insights, TDI can enhance portfolio profitability, reduce non-performing loans, and implement risk mitigation strategies that are more closely aligned with customer needs and market conditions. This holistic approach will help strengthen customer relationships, improve loan performance, and support TDI’s growth in a competitive market environment.