Unlocked 4-tab Excel model covering HQLA haircuts, LCR30.37 HQLA-for-HQLA secured-transaction unwind, 40% and 15% caps, 30-day cash outflows and 75% inflow cap, full NSFR ASF/RSF reconciliation, and dynamic accounting controls.
Direct Download: LCR_NSFR_Ratio_Calculator.xlsx
Pre-loaded with Northbridge Commercial Bank's reconciled $20,000.00M balance sheet (Basel III LCR 127.75%, NSFR 140.61%). Blue cells are editable inputs; black cells are live Excel formulas with pre-cached calculated values. Zero macros, zero locked sheets.
LCR_HQLA_Caps: Computes Level 1, Level 2A (15% haircut), and Level 2B (25%/50% haircuts) HQLA, executes the LCR30.37 HQLA-for-HQLA unwind of short-term repos, and applies both unadjusted and adjusted 15% Level 2B and 40% Level 2 caps (catching the $658.67M binding unwind deduction).
LCR_CashFlows: Models 24 weighted 30-day outflow categories (retail, SME, operational vs. excess deposits, non-operational wholesale, repos, derivatives HLBA, downgrade triggers, and off-balance-sheet facilities) and 6 inflow lines subject to the 75% statutory inflow cap (LCR40.75).
NSFR_ASF_RSF: Reconciles 100% of liabilities and equity to Available Stable Funding ($14,992.50M) and 100% of assets plus derivative and off-balance-sheet add-ons to Required Stable Funding ($10,662.50M).
Controls: Seven dynamic mathematical and cross-tab balance-sheet identity checks that must sum to 0 errors under any balanced input change.
Want the Complete 15-Chapter Practitioner Handbook?
The LCR and NSFR Handbook walks through every formula, US Reg WW ยง 249.30 maturity mismatch ladder, EU CRR2 / UK PRA divergence, ILAAP stress test, and Liquidity FTP calculation in full detail.