Project : Bank Performance, Capital Adequacy, and ISGAP & DGAP Management
Problem 1
For commercial banks, find the breakdown for charge-off rates for the following loan types: construction and development, home equity loans, other 1 - 4 family residential, commercial and industrial loans, loans to individuals, credit card loans, and other loans to individuals for years 2008 through 2017. Complete the following table in EXCEL:
Use the following steps to find this information:
- Go to the FDIC website
- Click on "Industry Analysis."
- From there click on "Research & Analysis."
- Click on "FDIC Quarterly Banking Profile."
- Click on "Quarterly Banking Profile."
- From here select a quarter. You will want the December 31st quarter for each year.
- Click on "Access QBP"
- Click on "Commercial Bank Section"
- Finally, click on "TABLE V-A. Loan Performance, FDIC-Insured Commercial Banks." This will bring up the files that contain the relevant data needed to complete the chart.
Prepare a stacked, column bar chart showing total percent of loans charged-off by year with loan breakdown by type within each year.
In MS Word, in one page or less, explain how charge-off rates have changed since 2008. Which type of loan has changed the most? What year had the most charge-offs? How has the rate of change in charge-offs changed over time? Incorporate your chart (and any other charts you so desire to construct) in your analysis. [1 inch margins, single-spaced, 11 point font.]
Problem 2: The Balance Sheet and Off Balance Sheet items for Home National Bank at 12/31/17 are presented in the accompanying Excel file. Using Excel, answer the following questions regarding the Capital Adequacy of Home National Bank. Show all work. Home National Bank uses the "Standardized Approach" for risk-weighted assets.
1. What is the bank's risk-weighted assets ON balance sheet?
2. What is the bank's risk-weighted assets OFF balance sheet?
3. What is the bank's TOTAL risk-weighted assets?
4. What is the bank's CET1 Capital?
5. What is the bank's Additional Tier 1 Capital?
6. What is the bank's Total Tier I Capital?
7. What is the bank's Tier II Capital?
8. What is the bank's Total Capital?
9. What is the bank's "adjusted" risk-weighted assets used in the calculation of the capital ratios?
10. What is the bank's CET1 risk-based capital ratio?
11. What is the bank's Tier I risk-based capital ratio?
12. What is the bank's Total risk-based capital ratio?
13. What is the bank's Tier I leverage ratio?
14. Is the bank under-, adequately-, or well capitalized? Explain.
15. Does the bank have enough capital to meet the Basel requirements including the capital conservation buffer requirement? Explain. What is the maximum payout?
16. What minimum CET1, Additional Tier 1, or Total Capital does the bank need to be "Well" capitalized and to have a "no payout ratio limitation" for its capital conservation buffer? Give a specific example how the bank could achieve this. Use SOLVER or GOAL SEEK. Print a screen shot of your SOLVER or GOAL SEEK inputs.