The SBA Loan Story: A Data-Driven Exploration of Small Business Financing
Published: 2023-09-04 14:43:11

In an era where small businesses are the backbone of economies, have you ever wondered how small businesses have managed to meet the financial demands required to run their business? I did, too. That's why I decided to analyze the Small Business Applications and Loan Amounts by Race/Ethnicity dataset, having over 500,000 records of approved loans guaranteed by SBA from fiscal year 2010 to 2019, uncovering hidden insights that could reshape our perception of business financing.

This article is the second part of a two-step project. The first part is about cleaning the data we're using here. You need to read that first [article](https://medium.com/@thedataisaac/guide-to-data-cleaning-and-preparation-for-analysis-using-pandas-library-in-python-1e5cde0df242) if you plan to work with this dataset.
Before we move forward, there's an important data cleaning step that wasn't mentioned in the first article. I'll explain it briefly here. To make sure data is loaded and transferred effectively, each column in the dataset should have the appropriate datatype. For example, the "asofdate" column has dates written as "20220930" and is currently set as an `int`. It's important to change its type to `datetime`. This process is called "data type conversion" and it's done using functions like `.astype()`. 
To do this, you can use the following code:

```python
#Convert the "BankZip" column to type int
foiaData["BankZip"]=foiaData["BankZip"].astype(int)

# Convert the first column "AsOfDate" to a date
foiaData["AsOfDate"]=pd.to_datetime(foiaData["AsOfDate"], format="%Y%m%d")

#Convert other columns to datetime format
for column_to_date in ["ApprovalDate", "FirstDisbursementDate", "PaidInFullDate", "ChargeOffDate"]:
    foiaData[column_to_date]=pd.to_datetime(foiaData[column_to_date])

```
In this analysis project, I am using PostgreSQL and Tableau to explore how small businesses get the money they need. You'll find answers to questions like:
- What percentage of loans have different statuses (like paid in full or charged off)?
- Which banks help small businesses the most?
- Which states get the biggest and smallest loans from the SBA?
- Does the interest rate change based on the type of business?
- Is there a connection between the loan amount and the time needed to pay it back?
- How many loans do small businesses get each year?
Most importantly, this analysis project has a special goal: to figure out the best mix of factors that lead to the right loan, with a good chance of getting it. This gives everyone an equal shot at a loan, no matter where they're from.

## SBA Loan Guarantee Program
The Small Business Administration (SBA) loan guarantee program is designed to help small businesses secure loans from traditional lenders(such as banks) by providing a government-backed guarantee. This guarantee reduces the risk for lenders, making them more willing to lend to businesses that might not qualify for conventional loans due to their size or risk profile. The SBA does not lend money directly, but rather guarantees a portion of the loan (e.g., 75% to 85% of the loan amount)  in case the borrower defaults. This reduces the risk for lenders and encourages them to lend more money to small businesses. However, this also means that the SBA has to bear some of the losses when borrowers default on their loans. 

## Insights
![Small Business Loan Data Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gd6epc99c3k8ir6tadpo.png)
The analysis of the loan data reveals some interesting insights about the impact of the Small Business Administration (SBA) programs on the economy. The data covers a total of 545,714 loans that were approved by the SBA, with a total gross approval amount of 205,958,880,943 dollars. Out of this amount, the SBA guaranteed 152,627,302,681 dollars, which means that the SBA took on the risk of default for about **74%** of the total loan value. The loans supported a total of 5,850,184 jobs, which indicates that each loan created or retained an average of about **10.7 jobs**. The median loan amount was 85,000 dollars, which means that half of the loans were above this amount and half were below. The median interest rate was 6%


### What is the distribution of loan statuses in terms of count and percentage?
```sql
-- Calculate the distribution of loan statuses in terms of count and percentage
SELECT loanstatus, 
    COUNT(loanstatus) AS loan_status_count,
    ROUND(COUNT(loanstatus) * 100.0 / SUM(COUNT(loanstatus)) OVER (), 2) AS percentage
FROM foiaData
GROUP BY loanstatus;
```

![Loan Status Distribution](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o8a4y5wnl391zpwtl4hh.png)
This chart shows that the most common loan status is PIF, which stands for Paid In Full. There are 310,598 loan with this status, which is 56.92% of all loans. The next most common loan status is EXEMPT, which means that the loan was not subject to repayment. The least common loan status is CANCELD which means that the loan was cancelled before It was fully repaid. This information can be used to better understand the risks associated with small business loans 


### Which banks have the highest number of approved loans?
```sql
-- List banks with the highest number of approved loans
SELECT bankname, COUNT(bankname) AS approved_loans_count
FROM foiaData 
GROUP BY bankname
ORDER BY approved_loans_count DESC;
```

![Top Banks with Approved Loans](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w48tz6z0xg4j54oz22rm.png)
This chart can be used to identify the banks that are most active in lending to small businesses. It shows the banks that are most supportive of small businesses. The top 6 banks with the highest number of approved loans are:
1. Wells Fargo Bank, National Association: 44,763 approved loans
2. The Huntington National Bank: 36,673 approved loans
3. JPMorgan Chase Bank, National Association: 25,267 approved loans
4. U.S. Bank, National Association: 15,582 approved loans
5. PNC Bank National Association: 15,419 approved loans


### What is the typical loan approval amount for each project state, and how does it vary?
```sql
-- Calculate the median loan approval amount for each project state
SELECT projectstate, 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY grossapproval) AS median_loan_approval_amount
FROM foiaData
GROUP BY projectstate
ORDER BY median_loan_approval_amount DESC;
```

![Median Loan Amount by State](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/larxixf830m0da6bs442.png)
The map shows that the median loan approval amount varies by project state. The highest median loan approval amount is in Georgia, with $340,000. This information can be used to compare the lending practices of different states. It can also be used to identify states that are more or less likely to approve small business loans.


### What is the average interest rate for each business type?
```sql
-- Calculate the average interest rate for each business type
SELECT businesstype,
    ROUND(CAST (AVG(initialinterestrate) AS numeric),2) AS avg_interest_rate
FROM foiaData
GROUP BY businesstype;
```

![Average Interest Rate by Business Type](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xmh2t9syn9effhwo47mx.png)
The chart shows that the average interest rate varies by business type. The highest average interest rate is for individuals, with 6.83%. The lowest average interest rate is for partnerships, with 6.33%.
A possible reason why the average interest rate for individuals is higher than for other business types is because individuals are considered to be riskier borrowers than businesses. Also, individuals may be borrowing smaller amounts of money than businesses.


### What is the typical loan approval amount for different term durations in months?
```sql
-- Calculate the median loan approval amount for different term durations in months
SELECT TermInMonths, 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY grossapproval) AS median_approval_amount
FROM foiaData
GROUP BY TermInMonths;
```

![Median Loan Approval Amount vs. Term in Months](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x7kaloixvdpz1b6k9khe.png)
The chart shows that the median loan approval amount increases as the term in months increases. This information can be used to understand the factors that affect the amount of a small business loan. It can also be used to help borrowers decide how long they should repay their loans. The median loan approval amount increased as the terms in months increased. This is an indication of a positive correlation between the two variables.


### How do loan approval trends change over the years?
```sql
-- Calculate the count of approved loans for each fiscal year
SELECT approvalfiscalyear, 
   COUNT(approvalfiscalyear) AS approved_loan_count
FROM foiaData
GROUP BY approvalfiscalyear
ORDER BY approvalfiscalyear;
```

![Loan Approval Trend over Years](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t70fncylm0c2w09wxh16.png)
The chart shows that the number of approved loans has increased over time. In 2010, there were 46,999 approved loans. This number increased to 64,065 in 2016. However, the number of approved loans has decreased since then. In 2019, there were only 51,906 approved loans.

**You can find the source code for the SQL data analysis on my GitHub [here](https://github.com/TheDataIsaac/The-Data-Diaries-with-Isaac/tree/main/Dataset%20Analysis/Small%20Business%20Loan%20Data%20Analysis). This includes the queries for creating the dataset table, loading the table, and additional analysis queries that were not covered in this article. For the visuals, you can view them on my Tableau Public profile [here](link_to_your_Tableau_profile).**


## CONCLUSION
We looked into loan statuses, finding out how loans are paid back. We also figured out which banks are big supporters of small businesses. By checking loan approval amounts in different states, we saw that some places get more money than others. We learned that different kinds of businesses get different interest rates. We also saw how loan amounts change based on how long you have to pay them back. And by examining loan approvals over the years, we learned how things have changed.
So, by looking at data, we've uncovered a lot about how small businesses get money. This journey has shown us how data can guide decisions, promote fairness, and shape the future of small business financing.