Excel workbook previewLiquidity Coverage Ratio (LCR) Calculator
3 free downloads per account. Just your email and a 6-digit code.
About this template
The liquidity coverage ratio asks one question: could the bank survive a 30-day stress on the liquid assets it holds today? Treasurers and liquidity risk managers are asked to show the calculation to the asset and liability committee, the board and examiners, and to explain each run-off rate. This workbook lays out that liquidity coverage ratio calculation in one sheet, so every number can be traced to a balance and a factor.
The LCR Calculator sheet applies the haircuts and caps of chapter LCR30 of the Basel Framework to the stock of high-quality liquid assets, and the run-off and inflow rates of LCR40 to 30-day cash flows, including the 75 percent inflow cap. The 100 percent minimum comes from LCR20.5. A Monthly Log sheet tracks the trend and a Currency sheet applies the significant currency test from SRP50.
Who it is for
The treasurer or liquidity risk manager who was asked by ALCO or the examiner to show how the liquidity coverage ratio is built, line by line.
What is inside
- LCR Calculator sheet with 8 HQLA lines, 19 outflow lines and 7 inflow lines, each with an editable factor
- Haircuts from LCR30 and run-off and inflow rates from LCR40 pre-filled with their paragraph references
- Level 2 and Level 2B caps applied with the two-thirds and 15/85 adjustments from LCR30.35 and LCR30.36
- Inflows capped at 75 percent of outflows (LCR40.1), net cash outflows and the LCR with a color-coded status
- Monthly Log sheet with 12 worked month-ends, 16 blank rows, the inflow cap, a status and an ALCO review dropdown
- Currency sheet that flags significant currencies at 5 percent of liabilities (SRP50.21) and computes an LCR per currency
- Worked example for an invented USD 1.2 billion community bank, with a How to use sheet
How to use it
Enter your HQLA, outflow and inflow balances in the yellow cells of the LCR Calculator sheet, then compare each factor with your national rule.
Files
- XLSXliquidity-coverage-ratio-calculator.xlsx17 KB
Opens in Microsoft Word or Excel, Google Docs or Sheets, and LibreOffice. Built on free, official texts that anyone can read: laws, regulations, standards and guidance published by public bodies. A template to adapt, not legal, tax or accounting advice.
Questions about this template
What is the formula for the liquidity coverage ratio?
The LCR is the stock of high-quality liquid assets divided by total net cash outflows over the next 30 calendar days. LCR20.5 of the Basel Framework requires the ratio to be at least 100 percent outside a period of stress. Net cash outflows are outflows minus inflows, with inflows capped at 75 percent of outflows under LCR40.1.
What haircuts apply to Level 2 assets?
Under LCR30.43 each Level 2A asset takes a 15 percent haircut. Under LCR30.45 residential mortgage-backed securities in Level 2B take 25 percent, and corporate debt securities and common equity shares in Level 2B take 50 percent. Level 2 assets may form at most 40 percent of the stock and Level 2B at most 15 percent.
What run-off rate applies to retail deposits?
Stable retail deposits usually take a 5 percent run-off rate under LCR40.7, and 3 percent only where the deposit insurance criteria in LCR40.11 are met. Less stable retail deposits take at least 10 percent under LCR40.13. National rules can set higher rates, so each rate in the workbook is an input.




