IRR gap analysis


This module demonstrates the gap analysis technique and its use in measuring and reporting interest rate risk (IRR) for a financial institution (FI). IRR exists when changes in interest rates impact on the FI's net interest income (NII) for the reporting period. The impact of interest rate changes on the capital value of assets and liabilities is covered under the duration method.


An example balance sheet is shown in figure 1a, Excel Web App #1. The financial data worksheet includes a categorised balance sheet in the range B2:D19 (red border), and associated NII for balance sheet items in column F. Interest rate details and repricing periods are also included.


Interest rate sensitivity


Interest rate sensitivity depends on the time to repricing (change of interest rate) for each asset and liability item. For example, Government bonds (line 4) have a fixed coupon, and interest income is fixed until maturity in five years. A change in market rates in the next 12 months will not change the bond coupon income. On the other hand, the fixed rate loans item matures in 5 months, and new loans issued would then be set to the prevailing fixed rate at that time.


To group items into chronological order of repricing, a time interval, say the next 12 months can be split into intervals, and items repricing in each interval can then be grouped. These time based groups are often called "gap buckets".


  • The time interval or span, and number of gap buckets needs to be determined,

Once the repricing reset or maturity point for an item (asset, liability, or off balance sheet) has been identified, it can be slotted, or included in a repricing / time bucket.


  • Categorise the Assets and Liabilities into "time buckets" based on the time to potential repricing
  • In this example, buckets are set for the intervals: { < 1 month, 1 to < 3 months, 3 to < 6 months, 6 to 12 months, and >= 12 months }

The time to repricing for each item in shown in column J of the financial data worksheet


Fig 1a: Excel Web App #1: worksheet: financial data. Balance sheet columns B and C (red border), NII (interest income - interest expense) column F, and time to repricing in column J

Gap analysis


Based on the time to repricing data and dollar values, the categorisation of the bucket groups appear in the gap buckets worksheet, figure 1b, Excel Web app #1. Within each bucket, the gap (assets - liabilities) is shown in column E, and the cumulative gap in column F. Overall there is a positive gap of $60 million (cell E8), but the gap within individual buckets varies considerably. Three to < six has a positive gap of $80 million (cell E5), whilst six to < 12 has a negative gap of $60 million (cell E6).


Fig 1b: Excel Web App #1: worksheet: gap buckets

The change in net interest income ΔNII



Quasi steady state ΔNII


  1. \(\Delta NII = CumGap \times \Delta r = 80 \times 0.01 = 0.8 \)

Actual annual ΔNII


Assumptions:

  1. On average, the rate change occurs half way through the time buckets, at the mid point. See columns N to P (figure 1b) for the Lower, Upper, and Bucket mid point values.
  2. The change of interest rate only impacts on the remaining time in the time bucket (see figure 2, columns R to U)
  3. The actual change in NII can be estimated at any of the time bucket end points
  4. The maximum interval is 12 months because the notion of NII is an annual concept

Worked calculations (sample)


  1. \(\Delta NII_{1mth} = 50 \times 0.01 \times (1.0 - 0.5)/12 = 0.0208 \)

  2. \(\Delta NII_{3mths} = 50 \times 0.01 \times (3.0 - 0.5)/12 + 10 \times 0.01 \times (3.0 - 2.0)/12 = 0.1125 \)

The results for the actual change in NII are given in cells H8:L8 of the gap buckets worksheet (figure 1b), and also shown in summarised form in figure 2.


xlf-iluka-feb15
Fig 2: gap buckets worksheet Gap (column E), bucket mid point to date (columns R to U), and bucket ΔNII (columns I to L). Source Fig 1b with hidden columns