An insider's view of Basel 3 Derivative capital in MS Excel

We start with an example of a highly leveraged bank's b/s and derive RWA and Basel 3 key ratios [Tier 1 capital, Leverage ratio, etc]. We simulate the bank's lending book in Excel to see how a default in loans wipes out the shareholder's equity of the highly leverage bank, thus correlating with exactly what happened in 2008 financial crisis, which led to evolution of Basel 3. We see the bigger picture of Basel 3 capital calculation for Credit Risk (covered in this lecture for product derivatives) and other risk areas like Market Risk and Operational Risk (will be covered in future courses) and the total capital requirements which is minimum 8%. Everything is simulated in Excel, so learn and understand by doing at the same time. Happy learning!

We start with an example of a highly leveraged bank's b/s and derive RWA and Basel 3 key ratios [Tier 1 capital, Leverage ratio, etc]. We simulate the bank's lending book in Excel to see how a default in loans wipes out the shareholder's equity of the highly leverage bank, thus correlating with exactly what happened in 2008 financial crisis, which led to evolution of Basel 3. We see the bigger picture of Basel 3 capital calculation for Credit Risk (covered in this lecture for product derivatives) and other risk areas like Market Risk and Operational Risk (will be covered in future courses) and the total capital requirements which is minimum 8%. Everything is simulated in Excel, so learn and understand by doing at the same time. Happy learning!

We start with an example of a highly leveraged bank's b/s and derive RWA and Basel 3 key ratios [Tier 1 capital, Leverage ratio, etc]. We simulate the bank's lending book in Excel to see how a default in loans wipes out the shareholder's equity of the highly leverage bank, thus correlating with exactly what happened in 2008 financial crisis, which led to evolution of Basel 3. We see the bigger picture of Basel 3 capital calculation for Credit Risk (covered in this lecture for product derivatives) and other risk areas like Market Risk and Operational Risk (will be covered in future courses) and the total capital requirements which is minimum 8%. Everything is simulated in Excel, so learn and understand by doing at the same time. Happy learning!

Before we dig deep into capital calculations for Derivatives,  we look at the concept of netting and collateral briefly under 3 categories which we will elaborate more in future lectures. The 3 categories are:

a) No netting, no collateral (no ISDA agreement, no CSA agreement)

b) Netting, without collateral (with ISDA agreement, no CSA agreement)

c) Netting with Collateral (With both ISDA & CSA agreement)

Before we dig deep into capital calculations for Derivatives, we look at the concept of netting and collateral briefly under 3 categories which we will elaborate more in future lectures. The 3 categories are:

a) No netting, no collateral. We briefly touch on Gross EAD (Exposure at Default) briefly in this lecture.

b) Netting, without collateral

c) Netting with Collateral

Learn, understand & simulate in Excel- Derivative capital calculation (no netting, no collateral) by understanding all components what makes Exposure at Default( EAD): Notional Amount, Mark to market, Gross/Net EAD, Gross PFE (Potential future exposure), Incurred CVA (Credit Value Adjustment), Risk weighted Assets (RWA) & Capital impact under Standardised (STND) Approach- To calculate the risk weight, a look up table is used which is based on counterparty exposure class [ Sovereign, Institutions, Corporates] and their external rating given by rating agencies like Moody's, S&P, Fitch.

Learn, understand & simulate in Excel- Derivative capital calculation (no netting, no collateral) by understanding all components what makes Exposure at Default( EAD): Notional Amount, Mark to market, Gross/Net EAD, Gross PFE (Potential future exposure), Incurred CVA (Credit Value Adjustment), Risk weighted Assets (RWA) & Capital impact under Standardised (STND) Approach- To calculate the risk weight, a look up table is used which is based on counterparty exposure class [ Sovereign, Institutions, Corporates] and their external rating given by rating agencies like Moody's, S&P, Fitch.

Learn, understand & simulate in Excel- Derivative capital calculation by understanding all components what makes Exposure at Default( EAD): Notional Amount, Mark to market, Gross/Net EAD, Gross PFE (Potential future exposure), Incurred CVA (Credit Value Adjustment), Risk weighted Assets (RWA) & Capital impact under Internal Rating Based Approach- Derive risk weights of counterparty through variables like Probability of Default (PD), Loss Given Default (LGD), Weighted average maturity (WAM). Most importantly understand these concepts.

A tabular STND vs IRB methodology variance analysis of all key indicators which are used in Derivative Capital calculation under Basel 3 for the category: No netting, no collateral.

A brief introduction of capital calculation for the category: Netting, but no collateral. In this scenario, banks can net their assets (receivables, positive market value) with liabilities (payables, negative market value) with the same counterparty. A new netting set calculation assessing various indicators-Net EAD, Net PFE, Weighted average maturity will be introduced. Rest of the criteria for calculating variables discussed above through STND and IRB methodology remains same.

Learn, understand & simulate in Excel- Derivative capital calculation (netting, no collateral) by understanding all components what makes Exposure at Default( EAD): Notional Amount, Mark to market, Gross/Net EAD, Gross PFE (Potential future exposure), Incurred CVA (Credit Value Adjustment), Risk weighted Assets (RWA) & Capital impact under Standardised (STND) Approach- To calculate the risk weight, a look up table is used which is based on counterparty exposure class [ Sovereign, Institutions, Corporates] and their external rating given by rating agencies like Moody's, S&P, Fitch.: Netting, but no collateral. In this scenario, banks can net their assets (receivables, positive market value) with liabilities (payables, negative market value) with the same counterparty. A new netting set calculation assessing various indicators-Net EAD, Net PFE, Weighted average maturity will be introduced. Rest of the criteria for calculating variables discussed above through STND and IRB methodology remains same.

Learn, understand & simulate in Excel- Derivative capital calculation (netting, no collateral) by understanding all components what makes Exposure at Default( EAD): Notional Amount, Mark to market, Gross/Net EAD, Gross PFE (Potential future exposure), Incurred CVA (Credit Value Adjustment), Risk weighted Assets (RWA) & Capital impact under Internal Rating Based Approach- Derive risk weights of counterparty through variables like Probability of Default (PD), Loss Given Default (LGD), Weighted average maturity (WAM). Most importantly understand these concepts.

Netting, but no collateral. In this scenario, banks can net their assets (receivables, positive market value) with liabilities (payables, negative market value) with the same counterparty. A new netting set calculation assessing various indicators-Net EAD, Net PFE, Weighted average maturity will be introduced. Rest of the criteria for calculating variables discussed above through STND and IRB methodology remains same.

Netting, with collateral. In this scenario, banks can net their assets (receivables, positive market value) with liabilities (payables, negative market value) with the same counterparty and also receive cash collateral (margin) to further mitigate their credit exposure. This is the best agreement in terms of capital optimisation. Rest of the criteria for calculating variables discussed above through STND and IRB methodology remains same.

In this lecture, we summarise everything on derivatives capital calculations which we covered in previous lectures and enter the world of capital optimisation which is by far the most important and challenging task most banks are facing today. There are usually 2 ways to optimise capital. a) To issue new shareholder's equity (IPO's, new issuance): This is a very expensive method and involves significant costs for banks (underwriting fee, prospectus, etc), b) Reduce RWA: Challenging and needs expertise: We will look at this strategy in detail in this lecture. Happy learning!

We continue in this lecture and discuss various capital optimisation strategies which is by far the biggest challenge facing banks where Sales & Trading revenues have dried up due to low interest rate environment.

Learn how IRB risk weight is modelled using Merton and Vasicek model using a asymptotic single risk factor model. Simulate PD, LGD, weighted average maturity to understand this derivation. Also understand and derive how Basel simulate correlation with PD. Derive Unexpected loss (UL) in a normal distribution by calculating a conditional Expected Loss (EL) at confidence interval of 99.9% (probability) less the EL (average loss which is already priced in products). This UL is supposed to be held in capital for losses which exceeds EL (similar to what happened in financial crisis of 2008).

Learn how IRB risk weight is modelled using Merton and Vasicek model using a asymptotic single risk factor model. Simulate PD, LGD, weighted average maturity to understand this derivation. Also understand and derive how Basel simulate correlation with PD. Derive Unexpected loss (UL) in a normal distribution by calculating a conditional Expected Loss (EL) at confidence interval of 99.9% (probability) less the EL (average loss which is already priced in products). This UL is supposed to be held in capital for losses which exceeds EL (similar to what happened in financial crisis of 2008).