Interest Rate Gap Calculator Excel Template
Interest Rate Gap Calculator Excel Template
Interactive Excel Financial Model
Download, customize, and integrate into your own analysis
What's Included
- Interactive financial model with live Excel formulas
- All formulas visible and fully editable
- Professional formatting with color-coded inputs & outputs
- Formula reference sheet with variable definitions
- Step-by-step instructions sheet
- Compatible with Microsoft Excel 2016 and later
Download Template
Price range: $0.00 through $20.00
Professional Excel template for bank interest rate risk analysis. Calculate Income Gap, Duration Gap, and equity sensitivity instantly.
Online Calculator vs Excel Template
| Feature | Online | Excel |
|---|---|---|
| Instant use — no download | ✓ | — |
| Works offline | — | ✓ |
| Customize formulas & layout | — | ✓ |
| Save & share with colleagues | — | ✓ |
| Integrate into your own models | — | ✓ |
| Print-ready formatting | — | ✓ |
About This Template
Analyze bank interest rate risk with this professional Interest Rate Gap Calculator Excel template. Whether you are preparing for an ALCO meeting, studying for the CFA exam, or building a financial risk management model, this template delivers both Income Gap and Duration Gap analysis in a single, editable worksheet.
What You Can Calculate
This template computes the core metrics used in bank interest rate risk management:
- Income Gap (RSA - RSL) - Measures how net interest income changes when rates move. Positive gap = asset-sensitive; negative gap = liability-sensitive.
- Gap Ratio - Quick indicator of balance sheet positioning (ratio near 1.0 = balanced).
- Delta NII - Dollar change in net interest income for your specified rate shock.
- Duration Gap - Measures equity value sensitivity using the D_A - (L/A) x D_L formula.
- Delta Equity - Economic value of equity change for the rate scenario.
- Percent Delta Equity - Percentage equity sensitivity. Regulators often flag greater than 20% as severe risk.
Required Inputs
Enter your bank balance sheet data:
- Total Assets and Total Liabilities (in millions)
- Rate-Sensitive Assets (RSA) and Rate-Sensitive Liabilities (RSL)
- Asset and Liability portfolio durations (Macaulay duration in years)
- Current interest rate level and hypothetical rate shock (in basis points)
Methodology
The template uses standard ALM (Asset-Liability Management) formulas: Income Gap = RSA - RSL with Delta NII = Gap x Delta i, and Duration Gap = D_A - (L/A) x D_L with Delta E = -DG x A x (Delta i / (1+i)).
Assumptions and Limitations
This model assumes a parallel yield curve shift, static balance sheet, linear duration approximation (no convexity), and single repricing bucket. Risk thresholds are illustrative teaching heuristics, not regulatory standards.
Frequently Asked Questions
Simply change the Rate Change input to test different scenarios. Copy the Results section to a separate area before changing inputs if you want to compare scenarios side-by-side. For comprehensive stress testing, duplicate the Calculator sheet and run +50, +100, +150, +200, -50, -100 bps scenarios in parallel.
Yes. The template produces the core metrics used in ALCO interest rate risk reports: Income Gap, Gap Ratio, Duration Gap, and equity sensitivity. Export the results to your ALCO presentation or copy the Calculator sheet into your existing reporting workbook. Note that the model uses simplified assumptions - for regulatory submissions, verify against your institution approved methodology.
For each asset class (loans, securities, etc.), multiply the class Macaulay duration by its dollar value, sum these products, then divide by total assets. Repeat for liabilities. This template expects Macaulay durations as inputs - the formula converts to modified sensitivity internally via the (1+i) divisor. If you need help calculating individual bond durations, see the Bond Duration Calculator Excel template which can feed into this model.
A fully interactive financial model with live Excel formulas, an Instructions sheet with usage guide, and a Formula Reference sheet with variable definitions and model assumptions. All formulas are visible and editable.
Microsoft Excel 2016 or later. The template uses standard Excel formulas only — no macros, VBA, or add-ins required.
Yes. All cells are fully editable. You can modify any formula, add your own calculations, change formatting, or integrate the model into your existing spreadsheets.
The online calculator runs in your browser for quick calculations. This Excel template gives you a portable, offline financial model you can customize, save, share with colleagues, and integrate into your own analysis.
This template is provided for educational and personal use. You may use it in your own professional analysis and presentations. Redistribution or resale of the template itself is not permitted.
You can re-download the latest version from your account or by requesting a new download link. Free downloads are limited to 5 per email address per month.
