Banking Regulation & Asset-Liability Management
Basel III Net Stable Funding Ratio (NSFR) Formula
Calculate Basel III Available Stable Funding (ASF), Required Stable Funding (RSF), Structural 1-Year Net Stable Funding Ratio NSFR = ASF / RSF (>= 100%), and Stable Funding Surplus/Deficit.
Pair 30-day LCR stress buffers with Basel III’s 1-year structural NSFR = ASF / RSF!
While Basel III’s Liquidity Coverage Ratio (LCR) tests 30-day acute cash outflows, the Net Stable Funding Ratio (NSFR, BCBS d295) prevents banks from funding illiquid multi-year loans with overnight wholesale repo over a 1-year horizon! Regulators require NSFR = Available_Stable_Funding (ASF) / Required_Stable_Funding (RSF) >= 1.00 (100%), applying standardized stability weights: on liabilities, Tier 1/2 Capital & >=1-yr Debt carry 100% ASF, Stable Retail Deposits carry 95% ASF, and Operational Corporate Deposits carry 50% ASF (whereas <6-month financial-institution repo gets 0% ASF!); on assets, unencumbered Level-1 Sovereign HQLA requires 5% RSF, low-risk <=35%-RW Residential Mortgages require 65% RSF, and Performing Corporate Loans (>=1 yr) require 85% RSF!
Verify in a shared bank ALCO workbook—Capital_And_LT_Debt = $30B (100% -> $30.0B, B2), Stable_Retail_Deposits = $80B (95% -> $76.0B, B3), Operational_Corp_Deposits = $28B (50% -> $14.0B, B4 -> Total ASF = $120.00B in E2), Level1_HQLA = $30B (5% -> $1.5B, B5), Prime_Mortgages = $60B (65% -> $39.0B, B6), and Performing_Corp_Loans = $70B (85% -> $59.5B, B7 -> Total RSF = $100.00B in E3): NSFR equals 120 / 100 = 1.2000 (120.00% in E4), with a +$20.00B Stable Funding Surplus (E5)!
| Basel III NSFR ALCO Metric ($30B Cap, $80B Retail, $28B Corp; $30B HQLA, $60B Mtg, $70B Loan) | Formula | Verified Output |
|---|---|---|
| Available Stable Funding ASF ($B) | 1.00*Cap + 0.95*Retail + 0.50*Corp | $120.00B ($82.00B @ $40B Retail) |
| Required Stable Funding RSF ($B) | 0.05*HQLA + 0.65*Mtg + 0.85*CorpLoan | $100.00B |
| Net Stable Funding Ratio (NSFR) | ASF / RSF (>= 1.00 Mandatory) | 1.2000 (120.00%; 0.8200 Breach) |
| Stable Funding Surplus / Deficit | ASF - RSF | +$20.00B (-$18.00B @ $40B Retail) |
Basel III NSFR Ratio Formula
Validate non-negative balance-sheet buckets and strictly positive Required Stable Funding RSF > 0!
=LET(cp_l, B2, rt_d, B3, op_d, B4, hq_1, B5, mt_g, B6, cr_l, B7, IF(OR(cp_l < 0, rt_d < 0, op_d < 0, hq_1 < 0, mt_g < 0, cr_l < 0), "Invalid Balance Sheet", LET(as_f, 1 * cp_l + 0.95 * rt_d + 0.5 * op_d, rs_f, 0.05 * hq_1 + 0.65 * mt_g + 0.85 * cr_l, IF(rs_f <= 0, "Invalid Balance Sheet", ROUND(as_f / rs_f, 4)))))Retail Deposit Outflow into Overnight Repo Stress Check
Simulate $40B of retail deposits migrating into 0%-ASF short-term wholesale funding (set B3 = 40): ASF drops to $82.00B, NSFR breaches 100% at 0.8200 (82.0%), and Stable Funding Deficit hits -$18.00B!
=IF(ISTEXT(E4), E4, ROUND(E2 - E3, 2))Try the example
Use the original values or change them to test your understanding.