Liquidity coverage ratio (LCR) explained: Measuring liquidity risk (Excel)

How is liquidity risk measured in banks under Basel III? What are the regulatory requirements? And how one can calculate these metrics without access to internal bank data? Today we are discussing the liquidity coverage ratio - a measure of short-term liquidity under stress - and its calculation on a real-world example from publicly available data, making sense of the methodology and discussing its advantages and limitations.
Don't forget to subscribe to NEDL and give this video a thumbs up for more videos in Finance!
Please consider supporting NEDL on Patreon: / nedleducation

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7
Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@mdini4785]

Just came across your material. Great content.
Silly question-- how does =MIN( lvl1 + lvl2a + lvl2b , lvl1 / 0.6 , lvl1 + lvl2 * 8 / 5 ) get you the result of 228502 (sum of all three HQLA levels)?
I understand the order of operations concern you've mentioned below, but I'm puzzled as to how Excel outputs that figure given the formula as-written.

[@RamKumar-ns9kr]

Brother, This is what I am actually looking for a long time. Insightful video many thanks

[@khurram908]

Thanks for posting the video - it's incredibly insightful.

[@paduraruovidiu201]

My man, thank you for showing me this you're so so helpful. I just got assigned a project at FRM, your content is exactly what I'm required to do.

[@NEDLeducation]

Hi Paduraru, and happy to hear the videos help in real-world practice. Stay tuned for more content in risk management.

[@ubayethossain9757]

Excellent piece as always! Thanks!

[@Cherryy999]

Thank you very much. More insightful!

[@mac4u51]

Thank you for sharing the video.

[@shreyajallan7651]

great great help, answers a lot of my questions

[@AmitGupta-no8xb]

Thanks for this.

[@swamivivekananda-cyclonicm8781]

Rare video, nice help. Thank you

[@niteeshsharma9906]

Thanks for sharing, it is really helpful.

[@NEDLeducation]

Hi Niteesh, and glad you liked the video! Stay tuned for more content on risk management :)

[@adimoron007]

Thank you 🙏. This is extremely helpful. I will surely make a patreon contribution. This was an amazing video for a high level understanding of LCR.

[@NEDLeducation]

Hi Aditya, and thanks so much for the kind words. Appreciate your support as well!

[@ridhi_sinha]

Hi NEDL. Would the cash inflow from recurring deposits be considered contractual inflow in the LCR calculations ?

[@kamipanero3567]

good morning, I wanted to download the templates but I can't find them on your drive, can you tell me where they are? thanks and congratulations

[@albertorasile2338]

Hi how could try to forecast this for the next 30 calendar days?

[@user-kp5du5xi2v]

hi, on cell f6 "30-day proxy" why are we dividing by 3?

[@danielrojaspardo]

Hello!
Something to discuss, do you think this ratio works for liquidity risk in funds?

[@scooby7877]

What does 'other outflow' on 17:10 represent? I thought you only need the run off rates for all outflows and the rate that inflows will be returned?

[@gegaoli]

Some nontransaction deposit accounts in the U.S., such as time deposits are subject to early withdrawal penalties.

[@sayalivichare9690]

How did you get 766 in column E

[@user-kp5du5xi2v]

Cant dowload the excel file. could you save it here?

[@sylviakivuva8365]

I have a question in computation of expected cash inflows why haven't we included the sum of other outflows( liabilities in E35 + deposits in E36 +other liabilities in E37) and instead computed only for E37.
Kindly clarify.
Aside from that, this is a helpful video.

[@millyhome7507]

What happens if the LCR ratio is not met?

[@NEDLeducation]

Hi, and thanks for the excellent question! If a bank systematically violates LCR, just like any other regulatory requirement, it can be (provided it comes from a BCBS-affiliated jurisdiction) fined or even put under administration by its country's central bank. Obviously, a one-off violation would most likely result in a simple warning, however the market reaction to such an increase of liquidity and regulatory risk would be enough of a deterrent.

[@santabanter]

I think there's something wrong with your HQLA formula. Currently, 2A and 2B together add up to 81,038 which means no capping needed b/c it's below the 40% threshold. 2B alone is 31,748 which is under the 15% threshold. So levels 1 + 2A + 2B comes to 230,676.
Isn't that what it's supposed to be?

[@NEDLeducation]

Hi, and thanks for the question! Right now, the formula works so that if there is a 40% allowance for Level 2 assets totally, Level 2B assets do not take more than 37.5% of this allowance (corresponding to 15% of total HQLA). You are correct it would be better to enforce the 15% threshold for Level 2B directly. This would require changing the last equation in the HQLA formula to (B13 + B14)/0.85, as Level 1 and Level 2A assets together must correspond to at least 85% of total HQLA. Hope it makes sense!

[@f.radanov3871]

@@NEDLeducation I have calculated according to formula in the Basel document and find 230,676. Just could not understand your explanation.

[@Gus-mi2fv]

I get 230,676 too. I thought i was losing my mind for a second.

[@sharmainesibanda2117]

Thank goodness, using the formulae in the document, I also get 230,676.

[@kawamura2459]

correct: 2A + 2B is about 35%, 2B is around 14%

[@thuantrinh1688]

Some segments in the video are stamped not adjacent to each other