World Bank Loan Analysis

As someone who worked closely with banks in my job as the controller of a Ford dealership, I was really excited to learn how to use SQL to analyze loan data. We were constantly checking the loan rates and changes - paying special attention to outstanding loans and contracts in transit which affect the cash flow. In this project, I’ve been hired as a data analyst to look over the loans from the IDA and answer the following questions:

1. How many total transactions and how many from Nicaragua
2. How many total transactions per country
3. Maximum owed to IDA
4. Most recent payment
5. Who has the most loans
6. Find the average service charge rate

1. Findings

There were 1,109,994 rows of total data - which held 13,704 rows of data for Nicaragua. India and Bangladesh had the most transactions with 58339 and 40980 respectively. The maximum owed to IDA is 793,256,127.64 and the last payment recorded was on 12/31/14 from the borrower, Ministry of Finance. Bangladesh again came in first place with 1,885,305,576,348.22 in loans and the average service charge rate was 0.778.

2. The International Development Association (IDA) Data

The International Development Association (IDA) credits are public and publicly guaranteed debt extended by the World Bank Group. IDA provides development credits, grants and guarantees to its recipient member countries to help meet their development needs. Credits from IDA are at concessional rates.

You can find the data set HERE

To look at this data, I uploaded the CSV file to bit.io, which is a PostgreSQL database that’s free to use for 72 hours.

3. Analysis

By using the COUNT function, I was able to determine that there are 1,109,994 rows of data that need to be analyzed.

SELECT COUNT (*) FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data.csv";

Then, I specifically wanted to know how much of the data was from Nicaragua. I used the following code to see that there were 13,704 rows.

SELECT COUNT (*) FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data.csv" WHERE country = 'Nicaragua';

To find the total transactions per country I counted and grouped the data by country.

SELECT "country", COUNT(*) FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data.csv" GROUP BY "country";

As you can see below, India and Bangladesh had the most transactions.

The max owed to IDA, 793,256,127.64, was easily found with the MAX function.

SELECT MAX ("Due to IDA") FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data.csv;

To find the most recent payment, I needed to investigate the last repayment date and the borrower. The last date was 12/31/14 with the Ministry of Finance.

SELECT "borrower" FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data" 
where "Last Repayment Date" =(SELECT MAX ("Last Repayment Date") 
FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data" ) LIMIT 1;

I looked at the country with the most in loans next. It makes sense that India is again at the top of the list.

SELECT "country", MAX ("Due to IDA") FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data" 
GROUP BY "country";

Finally, I was able to get the average service charge rate of 0.778 for the loans by using the AVG function.

SELECT AVG("Service Charge Rate") FROM "IDA_Statement_Of_Credits_and_Grants_-_Historical_Data";

4. Conclusion

The IDA aims to reduce poverty by providing financing and policy advice for programs that boost economic growth, build resilience, and improve the lives of poor people around the world. There are 75 countries that are eligible to receive funds, with Bangladesh topping the group. With an average service charge rate of 0.778, people are able to get loans to help with building new businesses and improving their quality of life. To continue doing business, I would suggest watching all the outstanding loans and consider moving rates downward if people are still unable to pay them back.

If you liked my analysis, please add me on LinkedIn