finance excel project

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.)

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.)