Requirement Document for Bank Loan Power BI Report

1. Introduction

This document outlines the requirements for a Power BI report that provides insights into bank loan data. The report is designed to help stakeholders analyze loan applications, funded amounts, received amounts, interest rates, and debt-to-income (DTI) ratios.

2. Purpose

The Power BI report aims to:

• Track total loan applications and their monthly trends.
• Analyze funded amounts and amounts received.
• Categorize loans as “Good” or “Bad” based on payment status.
• Display key metrics such as average interest rates and average DTI.
• Provide granular details on loan applications, including homeownership status, loan purpose, and credit grade.

3. Key Features & Metrics

3.1 Summary Dashboard

• Total Loan Applications: Displays the count of all loan applications.
  • MTD (Month-to-Date)
  • MoM (Month-over-Month) Change (%)
• Total Funded Amount: Total amount approved and disbursed for loans.
  • MTD Funded Amount
  • MoM Change (%)
• Total Amount Received: Total payments received from borrowers.
  • MTD Received Amount
  • MoM Change (%)
• Average Interest Rate
  • MTD Value
  • MoM Change (%)
• Average DTI (Debt-to-Income Ratio)
  • MTD Value
  • MoM Change (%)

3.2 Good vs Bad Loans Analysis

• Good Loans:
  • Applications Count
  • Funded Amount
  • Received Amount
• Bad Loans:
  • Applications Count
  • Funded Amount
  • Received Amount

3.3 Loan Status Breakdown

• Fully Paid Loans: Count, total funded amount, total received amount, average interest rate, and average DTI.
• Charged Off Loans: Count, total funded amount, total received amount, average interest rate, and average DTI.
• Current Loans: Count, total funded amount, total received amount, average interest rate, and average DTI.

3.4 Loan Distribution Analysis

• Total Amount Received by Month (Bar Chart)
• Total Amount Received by State (Map Visualization)
• Total Amount Received by Loan Term (36 vs. 60 months)
• Total Amount Received by Employee Length (Years of employment)
• Total Amount Received by Loan Purpose (e.g., Debt Consolidation, Credit Card, Home Improvement, etc.)
• Total Amount Received by Home Ownership (Mortgage, Rent, Own, Other, None)

3.5 Detailed Loan Applications Table

• Loan ID
• Loan Purpose
• Home Ownership
• Grade & Sub-grade
• Issued Date
• Funded Amount
• Interest Rate
• Installment Amount
• Total Received Amount

4. Data Sources & Integration

• Bank Loan Data (Structured dataset with loan details)
• Power BI Integration:
  • Data Import & Transformation
  • Use of DAX Measures for KPIs
  • Visuals and Filters for user interactivity

5. Report Accessibility & Permissions

• Role-based access to ensure data security.
• Read-only access for executives and analysts.
• Editing access for administrators and data engineers.

6. Performance Considerations

• Optimize DAX calculations for better performance.
• Ensure data refresh schedules align with business requirements.
• Implement filters to enhance query efficiency.

7. Project Completion Method

The Power BI report will be built using a combination of SQL queries, Power Query transformations, and DAX calculations:

7.1 SQL Queries

• Extract loan data from relational databases.
• Aggregate funded amounts and received payments.
• Categorize loans into Good and Bad based on payment status.
• Example SQL Query:

SELECT LoanID, Purpose, HomeOwnership, Grade, IssuedDate, FundedAmount, InterestRate, Installment, ReceivedAmount FROM LoanData WHERE IssuedDate >= '2020-01-01';

7.2 Power Query

• Clean and transform raw data into a structured format.
• Merge datasets from multiple sources.
• Handle missing values and data type conversions.
• Example Power

Query Transformation: let Source = Sql.Database("ServerName", "DatabaseName"), FilteredData = Table.SelectRows(Source, each [IssuedDate] >= #date(2020, 1, 1)) in FilteredData

7.3 DAX Calculations

• Create custom measures for KPIs like Total Funded Amount, Total Received Amount, and Average Interest Rate.
• Example DAX Measure: TotalFundedAmount = SUM(LoanData[FundedAmount])

8. Future Enhancements

• Predictive analytics for loan default risks.
• Additional segmentation based on demographics.
• Integration with external economic indicators.

Here’s a sample dataset for Power BI report in CSV format.

bank_loan_sample_data.csv