top of page

LOAN DATA QUERIES

A leading Asset Financing Company specializing in vehicle loans faced challenges in identifying the key factors influencing loan approvals, defaults, and overall financial performance. Despite a large customer base and a variety of loan products, the company struggled to pinpoint which applicant characteristics and loan parameters were most predictive of success. They sought to reduce risk, optimize loan offerings, and improve customer retention but lacked clear insights into the data they had collected.

​

To tackle this, I designed and implemented a series of Key Inquiry Questions that guided a detailed analysis of the company’s vehicle loan dataset. These questions were crafted to reveal actionable insights, focusing on improving the company's efficiency. I examined applicant details such as age, income, credit score, loan amounts, loan tenure, and loan-to-value (LTV) ratios, alongside demographic data like gender, occupation, and geographic location. I also explored key financial indicators, including credit history length and the number of existing loans.

​

By running SQL queries in BigQuery, aligned with the questions, I extracted critical insights into high-risk loan applicants, demographic patterns, and relationships between factors like income and credit scores.

To provide clarity and facilitate data-driven decision-making, I visualized these insights using Looker Studio.

Through charts and dashboards, I highlighted regional loan trends, and the characteristics of applicants with strong repayment potential.

​

bottles-cash-with-coins-saving-money-concept.jpg

Tools & Files

The Google Cloud Storage - Stored the vehicle loan dataset for secure, scalable, and efficient data handling.

BigQuery - Ran a series of SQL queries on the loan dataset to derive insights.

Looker Studio - Created dynamic, interactive visualizations of the analyzed data, enabling deeper insights.

EDA involved exploring the loan dataset to answer key questions, such as:

  1. Which cities have the highest number of loan applicants?

  2. What is the average credit score of all applicants?

  3. What is the relationship between income and credit score?

  4. Assuming the company is giving out loans at a 15% interest, what profit are they receiving per year?

  5. What is the total sum of requested loan amounts by applicants from Maharashtra?

  6. Which loans have a Loan Tenure of over 60 months and an LTV ratio greater than 80%?

  7. Which applicants have an LTV ratio above 80% and more than 3 existing loans?

  8. What is the total outstanding loan amount for applicants with more than 5 existing loans?

  9. What is the total loan amount requested by existing customers vs. new customers?

  10. If an applicant with an income of ₹50,000 requests a loan of ₹500,000, what should be their monthly EMI over a tenure of 60 months (assuming 15% interest)?

  11. What percentage of applicants with a loan tenure above 36 months have an LTV ratio greater than 85%?

  12. Which applicants have requested loan amounts more than 4 times their annual income?

​

Exploratory Data Analysis

loan-application-financial-help-form-concept.jpg

Key Insights & Findings

  • Demographics of Applicants: The data revealed a nearly equal gender distribution among applicants, with 133,145 females, 132,749 males, and 13,962 others. This balanced distribution suggests that the company's products appeal to both male and female clients equally, which is a positive indicator for inclusivity.

  • Age Distribution: The average age of the applicants is 44 years. This suggests that the company predominantly serves middle-aged individuals, likely in their prime earning years. This is a positive sign since individuals in this age group typically have more financial stability, making them good candidates for loans. However, the company could consider developing tailored products for both younger (first-time buyers) and older demographics (retirees) to expand its market reach.

  • Geographical Insights: Karnataka, Telangana, and Maharashtra emerged as the states with the highest loan applicant distribution, while the top three cities were Kolkata (23,900), New Delhi (23,887), and Hyderabad (23,726). This indicates strong regional demand for vehicle loans in these areas. The company may want to increase its marketing efforts in these regions to capitalize on existing demand and further analyze city-specific factors that drive applications.

  • Financial Health of Applicants:
    The average credit score of applicants is 582.95, slightly below ideal levels (700+), implying that many applicants are either financially vulnerable or have average creditworthiness.
    Notably, 96,982 applicants have a credit score below 500, which is concerning as these individuals represent high-risk loan profiles. The company may want to tighten its risk assessment for these applicants to avoid defaults.
    The average credit history length is 307.97 months (about 25 years), indicating that most applicants have a long credit history, which generally correlates with lower loan default risk. Despite lower credit scores, the extended history could help better evaluate their creditworthiness.

  • Income Levels: 27% of applicants earn more than ₹100,000 annually, indicating a relatively affluent customer base. This group could potentially afford larger loans and longer tenures, allowing the company to design premium loan products tailored to this high-income bracket.

  • Loan-to-Value (LTV) Insights: The average LTV ratio is 71.64%, showing that most applicants are not taking overly risky loans (generally, LTV ratios above 80% indicate higher risk). A deeper dive into loans with tenures over 60 months and LTV ratios greater than 80% may uncover pockets of risk that need to be monitored closely to mitigate default risks.

  • Loan and Credit Insights: The distribution of loan amounts shows that most applicants are requesting loans between ₹50,000 and ₹100,000 (90,556 applicants), with a smaller number requesting larger loans (159,647 applicants). This could indicate that the company’s core market lies in small to medium-sized loans. Additionally, the average loan tenure is 133 months (roughly 11 years), suggesting that applicants prefer long-term financing. The company may benefit from offering flexible loan products with shorter or more diverse tenure options to attract a wider audience.

  • Risk Assessment: Applicants with a credit score below 600 and more than two existing loans represent a significant risk group. The company should consider offering them tailored, lower-risk loan products to minimize exposure to defaults.

  • Customer Behavior: Existing customers have requested ₹11.5 billion in loans, while new customers requested ₹18.1 billion. This imbalance suggests that new customer acquisition is strong, but the company could also focus on retention strategies to encourage higher borrowing from existing clients. This could involve loyalty programs or personalized loan offers.

  • Profitability: Based on a 15% interest rate, the company stands to make ₹4.44 billion in profit annually. This demonstrates a healthy profit margin, but to sustain it, the company should focus on managing high-risk applicants and exploring profitable loan products that cater to the upper-income bracket.

THE DASHBOARD

The data visualization of the analyzed data is shown in the Dashboard below!

Vehicle Loan Dashboard.PNG
3d-cash-money.jpg

This comprehensive analysis of the vehicle loan dataset has provided invaluable insights into applicants' profiles, their financial health, and the dynamics of loan performance within the Asset Financing sector.

 

By leveraging key inquiry questions, I was able to explore various dimensions of the data, including demographic trends, creditworthiness, and the distribution of loan amounts.

​

The insights derived from this project offer a strategic foundation for the company to refine its lending practices and risk assessment procedures.

By understanding the relationship between applicant demographics, credit scores, and loan amounts, the company can develop targeted marketing campaigns, improve customer retention strategies, and create customized loan products that address the varying needs of its clientele.

Additionally, recognizing the importance of managing high-risk applicants through stringent assessment processes can mitigate potential defaults and sustain profitability.

 

Overall, the integration of data-driven strategies informed by this analysis can empower the company to navigate the complexities of the vehicle loan market, enhance competitiveness, and foster long-term growth.

Conclusion

THANK YOU!

Thank you for taking the time out to view my project!

In case you would like to discuss this project further, feel free to email me at:

patriciavalentinedanga@gmail.com.​

bottom of page