The management of Rockford 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, 2021 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) We assume the bank does not earn interest on the reserves at FED. 2) 10% of Fixed-rate mortgages mature within the next year. 3) 15% of Checkable deposits and 22% of Savings deposits are rate sensitive. 4) Current market rates are 5%. Part A (1.5 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.1 pts.) 2. What is the total for interest-rate-sensitive liabilities for the bank? (0.2 pts.) 3. What is the interest sensitive gap (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.2pt) 5. What is the weighted average duration of total assets for the bank? (0.2pts.) 6. What is the weighted average duration of total liabilities for the bank? (0.2 pts.) 7. What is the duration gap of capital (DGAP) for the bank? (0.2 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.2 pt.) Part B (1.5 pts.) Scenario 1: 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. a. What is the dollar amount of CD's/T-bills that you must issue/buy to bring ISGAP =0 ? (0.2 pt.) b. Show the new balance sheet. (0.2 pt.) c. Now, what is your DGAP of capital? (0.2 pt.) d. Explain the pros and cons of this bank's action. (0.1 pt.) Scenario 2: Suppose you decide to immunize the bank by issuing long-term debt of $30 million with a duration of X years and using those funds to make variable-rate residential mortgage loans with a duration of 0.50 years. a. What is the duration of the long-term debt that you must issue to bring DGAPK =0 ? ( 0.2 pt.) b. Show the new balance sheet. ( 0.2 pt.) c. Now, what is your ISGAP? (0.2 pt.) d. Explain the pros and cons of this bank's action. (0.2 pt.) Below is the Balance Sheet on December 31, 2021. It is the same one attached in excell sheet. It has the information to be used to answer the questions above. Rockford Bank Balance Sheet on December 31 , 2021 \begin{tabular}{lll} 1 - 2 years & 33.00 & 1.80 \\ Greater than 2 years & 23.00 & 15.00 \\ Building and Equipment & 12.00 & 0.00 \\ Other Assets & 1.00 & 0.00 \\ Total Assets & $227.00 \\ \hline \end{tabular} Liabilities CheckabledepositsMoneymarketdemandaccountsSavingsdepositsCertificatesofdeposit:Variable-rateLessthan1year1-2yearsGreaterthan2yearsFedfundsborrowed$9.008.0020.0050.0021.0013.004.0015.001.000.801.000.900.301.808.000.01 Borrowings: Less than 1 year 40.000.40 1-2years9.001.20 Greater than 2 years 32.0012.00 Other liabilities Total Liabilities Equity Capital Total Liabilities and Equity $227.00$5.00 The management of Rockford 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, 2021 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) We assume the bank does not earn interest on the reserves at FED. 2) 10% of Fixed-rate mortgages mature within the next year. 3) 15% of Checkable deposits and 22% of Savings deposits are rate sensitive. 4) Current market rates are 5%. Part A (1.5 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.1 pts.) 2. What is the total for interest-rate-sensitive liabilities for the bank? (0.2 pts.) 3. What is the interest sensitive gap (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.2pt) 5. What is the weighted average duration of total assets for the bank? (0.2pts.) 6. What is the weighted average duration of total liabilities for the bank? (0.2 pts.) 7. What is the duration gap of capital (DGAP) for the bank? (0.2 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.2 pt.) Part B (1.5 pts.) Scenario 1: 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. a. What is the dollar amount of CD's/T-bills that you must issue/buy to bring ISGAP =0 ? (0.2 pt.) b. Show the new balance sheet. (0.2 pt.) c. Now, what is your DGAP of capital? (0.2 pt.) d. Explain the pros and cons of this bank's action. (0.1 pt.) Scenario 2: Suppose you decide to immunize the bank by issuing long-term debt of $30 million with a duration of X years and using those funds to make variable-rate residential mortgage loans with a duration of 0.50 years. a. What is the duration of the long-term debt that you must issue to bring DGAPK =0 ? ( 0.2 pt.) b. Show the new balance sheet. ( 0.2 pt.) c. Now, what is your ISGAP? (0.2 pt.) d. Explain the pros and cons of this bank's action. (0.2 pt.) Below is the Balance Sheet on December 31, 2021. It is the same one attached in excell sheet. It has the information to be used to answer the questions above. Rockford Bank Balance Sheet on December 31 , 2021 \begin{tabular}{lll} 1 - 2 years & 33.00 & 1.80 \\ Greater than 2 years & 23.00 & 15.00 \\ Building and Equipment & 12.00 & 0.00 \\ Other Assets & 1.00 & 0.00 \\ Total Assets & $227.00 \\ \hline \end{tabular} Liabilities CheckabledepositsMoneymarketdemandaccountsSavingsdepositsCertificatesofdeposit:Variable-rateLessthan1year1-2yearsGreaterthan2yearsFedfundsborrowed$9.008.0020.0050.0021.0013.004.0015.001.000.801.000.900.301.808.000.01 Borrowings: Less than 1 year 40.000.40 1-2years9.001.20 Greater than 2 years 32.0012.00 Other liabilities Total Liabilities Equity Capital Total Liabilities and Equity $227.00$5.00