Problem
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. What is the total for interest-rate-sensitive liabilities for the bank?
3. What is the ISGAP of the bank?
4. If interest rates decrease by 1%, what will be the estimated change in net interest income for the bank?
5. What is the weighted average duration of total assets for the bank?
6. What is the weighted average duration of total liabilities for the bank?
7. What is the duration gap of capital for the bank?
8. If interest rates decrease by 1%, what will be the expected change in the market value of capital for the bank?
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:
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:
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:
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:
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?