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

The Balance Sheet on December 31, 2020 is attached to this project and presented in the accompanying Excel file. Also provided are the durations for the assets and liabilities. Other information you may need for your analysis is:

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

2) 15% of Checkable deposits and 22% of Savings deposits are rate sensitive.

3) Current market rates are 5%.

Part A (2 pts.)

To prepare your presentation for the bank officers, you anticipate and answer the following questions (Show your work and carry all numbers out 3 decimal places.)

1. What is the total for interest-rate-sensitive assets for the bank? (0.2 pts.)
2. What is the total for interest-rate-sensitive liabilities for the bank? (0.2 pts.)
3. What is the ISGAP of the bank? (0.2 pts.)
4. If interest rates increase by 1.5%, what will be the estimated change in net interest income for the bank? (0.2 pt.)

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

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

7. What is the duration gap of capital for the bank? (0.3 pt.)

8. If interest rates increase by 1.5%, what will be the expected change in the market value of capital for the bank? (0.3 pt.)

Part B (1 pts.)

Suppose you decide to insulate the bank by attracting and issuing fixed-rate 5-year CDs with a duration of 4.60 years and investing those funds in 90-day T-bills with a duration of 0.25 years.

1. What is the dollar amount of CDs/T-bills that you must issue/buy to bring ISGAP = 0? (0.2 pt.)
2. Show the new balance sheet. (0.3 pt.)
3. Now, what is your DGAP of capital? (0.3 pt.)

4. Explain the pros and cons of this banks action. (0.2 pt.)

Please show formulas used to calculate

Green Bay State Bank Balance Sheet on December 31, 2020 Amount Duration ($ millions) (years) Wtd Avg Duration % wgt ISA $6.00 5.00 0.00 0.00 41.00 41.00 3.00 8.00 0.40 1.60 4.16 Assets Cash and Cash items Reserves at Fed Securities: Less than 1 year 1 - 2 years Greater than 2 years Residential mortgages Variable-rate Fixed-rate (30 years) Commercial loans: Less than 1 year 1 - 2 years Greater than 2 years Building and Equipment Other Assets Total Assets 30.00 15.00 0.40 10.30 2.643% 0.000 2.203% 0.000 0.000% 0.000 18.062% 0.072 1.322% 0.021 3.524% 0.147 0.000% 0.000 13.216% 0.053 6.608% 0.681 0.000% 0.000 22.026% 0.198 14.537% 0.262 10.132% 1.520 5.286% 0.000 0.441% 0.000 100.000% 2.953 30.00 1.5 50.00 50.00 33.00 23.00 12.00 1.00 $227.00 0.90 1.80 15.00 0.00 0.00 122.50 Amount Duration ($ millions) (years) Wtd Avg Duration % wgt ISL $9.00 8.00 20.00 1.00 0.80 1.00 $1.35 8.00 4.4 50.00 21.00 Liabilities Checkable deposits Money market demand accounts Savings deposits Certificates of deposit: Variable-rate Less than 1 year 1 - 2 years Greater than 2 years Fed funds borrowed Borrowings: Less than 1 year 1 - 2 years Greater than 2 years Other liabilities Total Liabilities 50.00 21.00 13.00 4.00 15.00 0.90 0.30 1.80 8.00 0.01 4.054% 3.604% 9.009% 0.000% 22.523% 9.459% 5.856% 1.802% 6.757% 0.000% 18.018% 4.054% 14.414% 0.450% 100.000% 0.041 0.029 0.090 0.000 0.203 0.028 0.105 0.144 0.001 0.000 0.072 0.049 1.730 0.000 2.491 15.00 40.00 40.00 9.00 32.00 1.00 $222.00 0.40 1.20 12.00 0.00 S139.75 Equity Capital Total Liabilities and Equity $5.00 $227.00 Green Bay State Bank Balance Sheet on December 31, 2020 Amount Duration ($ millions) (years) Wtd Avg Duration % wgt ISA $6.00 5.00 0.00 0.00 41.00 41.00 3.00 8.00 0.40 1.60 4.16 Assets Cash and Cash items Reserves at Fed Securities: Less than 1 year 1 - 2 years Greater than 2 years Residential mortgages Variable-rate Fixed-rate (30 years) Commercial loans: Less than 1 year 1 - 2 years Greater than 2 years Building and Equipment Other Assets Total Assets 30.00 15.00 0.40 10.30 2.643% 0.000 2.203% 0.000 0.000% 0.000 18.062% 0.072 1.322% 0.021 3.524% 0.147 0.000% 0.000 13.216% 0.053 6.608% 0.681 0.000% 0.000 22.026% 0.198 14.537% 0.262 10.132% 1.520 5.286% 0.000 0.441% 0.000 100.000% 2.953 30.00 1.5 50.00 50.00 33.00 23.00 12.00 1.00 $227.00 0.90 1.80 15.00 0.00 0.00 122.50 Amount Duration ($ millions) (years) Wtd Avg Duration % wgt ISL $9.00 8.00 20.00 1.00 0.80 1.00 $1.35 8.00 4.4 50.00 21.00 Liabilities Checkable deposits Money market demand accounts Savings deposits Certificates of deposit: Variable-rate Less than 1 year 1 - 2 years Greater than 2 years Fed funds borrowed Borrowings: Less than 1 year 1 - 2 years Greater than 2 years Other liabilities Total Liabilities 50.00 21.00 13.00 4.00 15.00 0.90 0.30 1.80 8.00 0.01 4.054% 3.604% 9.009% 0.000% 22.523% 9.459% 5.856% 1.802% 6.757% 0.000% 18.018% 4.054% 14.414% 0.450% 100.000% 0.041 0.029 0.090 0.000 0.203 0.028 0.105 0.144 0.001 0.000 0.072 0.049 1.730 0.000 2.491 15.00 40.00 40.00 9.00 32.00 1.00 $222.00 0.40 1.20 12.00 0.00 S139.75 Equity Capital Total Liabilities and Equity $5.00 $227.00