Can someone help me to answer this in Excel

2. As part of an urgent request, your team is asked to re-assess the risk position of a SJHC bank subsidiary located in North Dakota. Your team is provided with the inputs needed for the rate sensitivity report in Table 1 : a) Using Table 1 as an Excel template, estimate the Gap and Cumulative Gap for maturity buckets through one year. b) Discuss to what extent the bank is exposed to refinancing / reinvestment risk, given the available information for each maturity bucket up to one year (max. one paragraph - five - six sentences) c) What is the expected change in net interest income if interest rates on rate-sensitive assets and liabilities increase further by 2%. Is there any recommendation your team could issue to bank management regarding changes to the repricing gap? (max. one paragraph - five - six sentences) 3. Due to specific data access restrictions, your team has received an aggregated overview of the bank balance sheet of SJHC in book and market values (see Table 2). For simplicity, the earning assets include cash, 4-year commercial loan and government securities (7-years). The commercial loan earns 10% per year, and the Treasury bond 7% per year. The bank has 2 -years' time deposits (paying interest of 4% per year to deposit holders) and 4-years certificate of deposits (paying interest of 6% per year). Any principals are paid fully at maturity T. Additionally, the following assumptions also apply: no defaults or prepayments, annual compounding, and equal book and market values at the beginning of the time period. a) Using Table 2 as an Excel template, estimate the duration of each earning asset and interestbearing liability listed in Table 2 . b) Estimate the leveraged adjusted duration gap and comment on the results. (max. one paragraph - five-six sentences) c) Estimate the change in market value of equity, assets and liabilities if there is a relative increase in all interest rates by 2% percent. Table 1 Liabilities and Equity 2. As part of an urgent request, your team is asked to re-assess the risk position of a SJHC bank subsidiary located in North Dakota. Your team is provided with the inputs needed for the rate sensitivity report in Table 1 : a) Using Table 1 as an Excel template, estimate the Gap and Cumulative Gap for maturity buckets through one year. b) Discuss to what extent the bank is exposed to refinancing / reinvestment risk, given the available information for each maturity bucket up to one year (max. one paragraph - five - six sentences) c) What is the expected change in net interest income if interest rates on rate-sensitive assets and liabilities increase further by 2%. Is there any recommendation your team could issue to bank management regarding changes to the repricing gap? (max. one paragraph - five - six sentences) 3. Due to specific data access restrictions, your team has received an aggregated overview of the bank balance sheet of SJHC in book and market values (see Table 2). For simplicity, the earning assets include cash, 4-year commercial loan and government securities (7-years). The commercial loan earns 10% per year, and the Treasury bond 7% per year. The bank has 2 -years' time deposits (paying interest of 4% per year to deposit holders) and 4-years certificate of deposits (paying interest of 6% per year). Any principals are paid fully at maturity T. Additionally, the following assumptions also apply: no defaults or prepayments, annual compounding, and equal book and market values at the beginning of the time period. a) Using Table 2 as an Excel template, estimate the duration of each earning asset and interestbearing liability listed in Table 2 . b) Estimate the leveraged adjusted duration gap and comment on the results. (max. one paragraph - five-six sentences) c) Estimate the change in market value of equity, assets and liabilities if there is a relative increase in all interest rates by 2% percent. Table 1 Liabilities and Equity