finance excel project

Project 2: Bank Performance, Capital Adequacy, and ISGAP & DGAP Management Spring 2018 (Total points = 90 pts.)

Problem 1 (10 pts.)

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: (2 pts.)

Use the following steps to find this information:

· Go to the FDIC website at www.fdic.gov.

· 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. (3 pts.)

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.] (5 pts.)

·

Problem 2: (36 pts.)

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? (5 pts.)

2. What is the bank’s risk-weighted assets OFF balance sheet? (7 pts.)

3. What is the bank’s TOTAL risk-weighted assets? (1 pt.)

4. What is the bank’s CET1 Capital? (1.5 pts.)

5. What is the bank’s Additional Tier 1 Capital? (0.5 pts.)

6. What is the bank’s Total Tier I Capital? (1 pt.)

7. What is the bank’s Tier II Capital? (4 pts.)

8. What is the bank’s Total Capital? (1 pt.)

9. What is the bank’s “adjusted” risk-weighted assets used in the calculation of the capital ratios? (1 pt.)

10. What is the bank’s CET1 risk-based capital ratio? (1 pt.)

11. What is the bank’s Tier I risk-based capital ratio? (1 pt.)

12. What is the bank’s Total risk-based capital ratio? (1 pt.)

13. What is the bank’s Tier I leverage ratio? (1 pt.)

14. Is the bank under-, adequately-, or well capitalized? (1 pt.) Explain. (1 pt.)

15. Does the bank have enough capital to meet the Basel requirements including the capital conservation buffer requirement? Explain. (1 pt.) What is the maximum payout? (1 pt.)

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. (6 pts.)

Problem 3 (44 pts.)

Part A:

The management of Ark City State Bank has asked you to examine the interest rate risk of the bank. Management is concerned that interest rates will decrease by the end of the year and wants to see what would happen to the relative profitability of the bank if the decrease actually occurs.

The Balance Sheet at December 31, 2017 is presented in the accompanying Excel file. Also provided are the Macaulay durations for the assets and liabilities. Other information you may need for your analysis is:

1) 8% of Fixed-rate mortgages mature within the next year.

2) 10% of Checkable deposits and 20% of Savings deposits are rate sensitive.

3) Reserves at the Fed DO earn interest.

4) Current market rates are 5%.

5) Round solutions to three decimal places.

Requirement: Use EXCEL to complete the following assignment. I have provided a template for you to use, but you have to input the formulas. Follow examples in my PowerPoint lecture as to how to set up the project in EXCEL. Carry all computations and answers out to 3 decimal places.

To prepare your presentation for the bank officers, you anticipate and answer the following questions:

1. What is the total for interest-rate-sensitive assets for the bank? (2.5 pts.)

2. What is the total for interest-rate-sensitive liabilities for the bank? (3.5 pts.)

3. What is the ISGAP of the bank? (0.5 pts.)

4. If interest rates decrease by 1%, what will be the estimated change in net interest income for the bank? (0.5 pts.)

5. What is the weighted average duration of total assets for the bank? (6 pts.)

6. What is the weighted average duration of total liabilities for the bank? (6 pts.)

7. What is the duration gap of capital for the bank? (0.5 pts.)

8. If interest rates decrease by 1%, what will be the expected change in the market value of capital for the bank? (0.5 pts.)

Part B:

In the same workbook, copy your completed template from Part A two times. Label the first copy “Scenario 1” and the second copy “Scenario 2”. Use Solver or Goal Seek to address the following independent Scenarios. Print a screen shot of your Solver or Goal Seek inputs. You will need to insert lines into the template (see my notes).

Scenario 1: (5 pts.) Suppose you decide to insulate the bank by attracting and issuing Variable rate CDs with a duration of 0.75 years and investing those funds in 10 year T-notes with a duration of 8.75 years.

a. What is the dollar amount of CD’s/T-notes that you must issue/buy to bring ISGAP = 0?

b. Now, what is your DGAP of capital?

Scenario 2: (5 pts.) Suppose you decide to immunize the bank by issuing short-term debt of $25 million with a duration of 0.95 years and investing those funds in long-term treasury bonds.

a. What is the duration of the treasury bonds that you must buy to bring DGAPK = 0?

b. Now, what is your ISGAP?

In the same workbook, copy your completed Scenario 1. Label the copy “Scenario 3”. Use Solver or Goal Seek to address the following independent Scenarios. You will need to insert lines into the template (see my notes).

Scenario 3: (7 pts.) Suppose you do Scenario 1 and bring ISGAP = 0. a) Explain in detail a possible scenario to also bring DGAP of capital = 0. b) Show your work in Excel like I did in your PowerPoint notes. The maximum size the bank can grow to is $240 million. c) Explain the pros and cons of your solution. What other factors must the bank consider?

In the same workbook, copy your completed Scenario 2. Label the copy “Scenario 4”. Use Solver or Goal Seek to address the following independent Scenarios. You will need to insert lines into the template (see my notes).

Scenario 4: (7pts.) Suppose you do Scenario 2 and bring DGAPK = 0. a) Explain in detail a possible scenario to also bring ISGAP = 0. (DGAPK needs to remain between -0.1 and +0.1) b) Show your work in Excel like I did in your PowerPoint notes. The maximum size the bank can grow to is $240 million. c) Explain the pros and cons of your solution. What other factors must the bank consider?

HINT: The bank has to balance.

5

Year

% of loans charged off: 2008200920102011201220132014201520162017

Construction and development

Home equity loans

Other 1-4 Family residential

Commercial and industrial loans

Loans to individuals

Credit card loans

Other loans to individuals