Assignment
You are expected to use the Excel file Assignment 2_Interest and Liquidity RiskAnalysis by performing and reporting the required computations in the spaces provided. Thecells of the different tabs are color-coded to better indicate where the computations must beperformed and the results reported. Yellow cells include input data and questions. Green cellsdelineate spaces where the computations must be performed and answers must be provided.
Section 1 - Static GAP Analysis
Perform the computation and answer the questions listed in the tab (Rate Sensitivity Report)by using the reported information. In order to complete the data in the balance sheet, you can make the following assumptions:
• The balance sheet and rate sensitivity report is based on a forecasted increase in both the prime and federal fund rates over the next two weeks and then constant rates for the foreseeable future.
• Money market investments include $7.2 million of corporate securities with adjustable rates pegged to the prime rate. The remaining balance includes fixed rate commercial paper securities maturing in 4 months ($6.3 million) and in 8 months ($15.2 million)
• Federal funds sold and reverse repos include overnight lending for $8.3 million and the remaining balance of $8 million include lending with evenly distributed maturities from 2 months to 5 months.
• Real estate installment loans with fixed rates include $180 million in exposure to residential real estate mortgages. Half of the mortgages are amortized loans withmonthly constant principal payments, an average APR of 4.7%, and an average maturity of 10 years. The other half of the mortgages include fixed payments amortized loans with an average APR of 5.1% and an average maturity of 15 years.
• The bank borrows $5.2 million in the federal fund market with overnight maturity
Complete the balance sheet using for aforementioned assumptions and then for each "time bucket" listed compute the listed measures:
• Period GAP
• Cumulative GAP
• GAP ratio
• Cumulative GAP-to-Earning Assets ratio.
Attachment:- Assignment_interest_and_liquidity_risk_analysis.rar