rwa_calculations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2827fefb-178e-453d-bb90-ceaa7272a12a",
   "metadata": {},
   "source": [
    "### Basel II RWA calculations\n",
    "\n",
    "1. The purpose of the notenook is to perform RWA calculations as described in the https://www.bis.org/publ/bcbs128.pdf\n",
    "2. The results are tested against test cases as provided by the BIS\n",
    "3. The maturity flooring is a simplification (see 32.51 in https://www.bis.org/basel_framework/chapter/CRE/32.htm?inforce=20230101&published=20200327)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b435123e-314a-491c-8ca3-852b3985540e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy.stats import norm\n",
    "\n",
    "# Settings\n",
    "pd.set_option('display.max_colwidth', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e61c068f-2641-4f14-9380-66dd22e5f8c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_RWA(exposure_type, PD, LGD, EAD, add_on=1.06, M=None, S=None):\n",
    "    \"\"\"\n",
    "    Calculate Risk-Weighted Assets (RWA) for different types of exposures.\n",
    "    \n",
    "    Parameters:\n",
    "    - exposure_type (str): Type of regulatory exposure class.\n",
    "    - PD (float): Probability of Default. Must be between 0 and 1.\n",
    "    - LGD (float): Loss Given Default. Must be between 0 and 1.\n",
    "    - EAD (float): Exposure at Default.\n",
    "    - M (float, optional): Maturity for corporate exposures. Must be between 1 and 3. Default is None.\n",
    "    - S (float, optional): Sales for SME corporate exposures. Must be between 1 and 50. Default is None.\n",
    "    \n",
    "    Returns:\n",
    "    - float: Risk-Weighted Assets (RWA) based on the given inputs.\n",
    "    \n",
    "    Raises:\n",
    "    - AssertionError: If PD or LGD are not between 0 and 1.\n",
    "    \"\"\"\n",
    "    \n",
    "    # Validate input ranges\n",
    "    assert 0 <= PD <= 1, \"PD must be between 0 and 1.\"\n",
    "    assert 0 <= LGD <= 1, \"LGD must be between 0 and 1.\"\n",
    "    if M is not None:\n",
    "        M = np.clip(M, 1, 5)\n",
    "    if S is not None:\n",
    "        S = np.clip(S, 5, 50)\n",
    "    \n",
    "    # The G function, the inverse of the standard normal cumulative distribution function\n",
    "    def G(z):\n",
    "        return norm.ppf(z)\n",
    "\n",
    "    # Calculate Correlation R based on exposure type\n",
    "    if exposure_type == \"Residential Mortgages\":\n",
    "        R = 0.15\n",
    "    elif exposure_type == \"Qualifying Revolving Retail Exposures\":\n",
    "        R = 0.04\n",
    "    elif exposure_type == \"Other Retail Exposures\":\n",
    "        R = 0.03 * (1 - np.exp(-35 * PD)) / (1 - np.exp(-35)) + 0.16 * (1 - (1 - np.exp(-35 * PD)) / (1 - np.exp(-35)))\n",
    "    elif exposure_type == \"Corporate Exposures\":\n",
    "        R = 0.12 * (1 - np.exp(-50 * PD)) / (1 - np.exp(-50)) + 0.24 * (1 - (1 - np.exp(-50 * PD)) / (1 - np.exp(-50)))\n",
    "        if S is not None:\n",
    "            R -= 0.04 * (1 - (S - 5) / 45)\n",
    "    else:\n",
    "        return np.nan\n",
    "    \n",
    "    # Calculate Capital Requirement K\n",
    "    if exposure_type == \"Corporate Exposures\":\n",
    "        # Maturity adjustment b for corporate exposures\n",
    "        b = (0.11852 - 0.05478 * np.log(PD)) ** 2\n",
    "        K = (LGD * norm.cdf(np.sqrt((1 - R)**-1) * G(PD) + np.sqrt(R / (1 - R)) * G(0.999)) - PD * LGD) * (1 - 1.5 * b) ** -1 * (1 + (M - 2.5) * b)\n",
    "    else:\n",
    "        K = LGD * norm.cdf(((1 - R)**-0.5) * G(PD) + (R / (1 - R)) ** 0.5 * G(0.999)) - PD * LGD\n",
    "    \n",
    "    # Calculate Risk-Weighted Assets RWA\n",
    "    RWA = K * 12.5 * EAD * add_on\n",
    "    \n",
    "    return RWA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "39b9b0c2-3d28-43f2-9234-290bc793f132",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PD must be between 0 and 1.\n",
      "LGD must be between 0 and 1.\n",
      "RWA for Residential Mortgage: 82.85433774748495\n",
      "RWA for Qualifying Revolving Retail: 27.251803169301215\n",
      "RWA for Other Retail: 54.63611515913094\n",
      "RWA for Corporate Exposure: 257.42410788421637\n",
      "RWA for SME Corporate Exposure: 301.5092152166892\n"
     ]
    }
   ],
   "source": [
    "# Test the function with incorrect PD and LGD values to see if assertions work\n",
    "try:\n",
    "    print(\"RWA for Corporate Exposure:\", calculate_RWA(\"Corporate Exposures\", 1.2, 0.4, 100, 3))\n",
    "except AssertionError as e:\n",
    "    print(e)\n",
    "\n",
    "try:\n",
    "    print(\"RWA for Corporate Exposure:\", calculate_RWA(\"Corporate Exposures\", 0.02, -0.4, 100, 3))\n",
    "except AssertionError as e:\n",
    "    print(e)\n",
    "\n",
    "# Example usage\n",
    "PD = 0.02  # Probability of Default\n",
    "LGD = 0.4  # Loss Given Default\n",
    "EAD = 100  # Exposure at Default\n",
    "M = 3     # Maturity\n",
    "S = 10    # Sales for SME\n",
    "add_on = 1.06\n",
    "\n",
    "print(\"RWA for Residential Mortgage:\", calculate_RWA(\"Residential Mortgages\", PD, LGD, EAD, add_on))\n",
    "print(\"RWA for Qualifying Revolving Retail:\", calculate_RWA(\"Qualifying Revolving Retail Exposures\", PD, LGD, EAD, add_on))\n",
    "print(\"RWA for Other Retail:\", calculate_RWA(\"Other Retail Exposures\", PD, LGD, EAD, add_on))\n",
    "print(\"RWA for Corporate Exposure:\", calculate_RWA(\"Corporate Exposures\", PD, LGD, EAD, M, add_on))\n",
    "print(\"RWA for SME Corporate Exposure:\", calculate_RWA(\"Corporate Exposures\", PD, LGD, EAD, M, S, add_on))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e5b13fa2-669a-4972-bf40-5e4fec900dab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PD</th>\n",
       "      <th>Segment</th>\n",
       "      <th>LGD</th>\n",
       "      <th>SME</th>\n",
       "      <th>Value</th>\n",
       "      <th>RWA_calc</th>\n",
       "      <th>diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>11.30</td>\n",
       "      <td>11.299</td>\n",
       "      <td>0.000088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>14.44</td>\n",
       "      <td>14.444</td>\n",
       "      <td>0.000277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>4.45</td>\n",
       "      <td>4.451</td>\n",
       "      <td>0.000225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>8.41</td>\n",
       "      <td>8.408</td>\n",
       "      <td>0.000238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.980</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>1.85</td>\n",
       "      <td>1.851</td>\n",
       "      <td>0.000541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>2.30</td>\n",
       "      <td>2.305</td>\n",
       "      <td>0.002174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.03</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>4.15</td>\n",
       "      <td>4.149</td>\n",
       "      <td>0.000241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>15.39</td>\n",
       "      <td>15.396</td>\n",
       "      <td>0.000390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>19.65</td>\n",
       "      <td>19.651</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>6.63</td>\n",
       "      <td>6.629</td>\n",
       "      <td>0.000151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>12.52</td>\n",
       "      <td>12.522</td>\n",
       "      <td>0.000160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>1.51</td>\n",
       "      <td>1.513</td>\n",
       "      <td>0.001987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>2.86</td>\n",
       "      <td>2.858</td>\n",
       "      <td>0.000699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>3.46</td>\n",
       "      <td>3.461</td>\n",
       "      <td>0.000289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.05</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>6.23</td>\n",
       "      <td>6.230</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>23.30</td>\n",
       "      <td>23.297</td>\n",
       "      <td>0.000129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>29.65</td>\n",
       "      <td>29.654</td>\n",
       "      <td>0.000135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>11.16</td>\n",
       "      <td>11.163</td>\n",
       "      <td>0.000269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>21.08</td>\n",
       "      <td>21.086</td>\n",
       "      <td>0.000285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>2.71</td>\n",
       "      <td>2.709</td>\n",
       "      <td>0.000369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>5.12</td>\n",
       "      <td>5.116</td>\n",
       "      <td>0.000781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>5.94</td>\n",
       "      <td>5.939</td>\n",
       "      <td>0.000168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.10</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>10.69</td>\n",
       "      <td>10.690</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>39.01</td>\n",
       "      <td>39.010</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>49.47</td>\n",
       "      <td>49.472</td>\n",
       "      <td>0.000040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>21.15</td>\n",
       "      <td>21.154</td>\n",
       "      <td>0.000189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>39.96</td>\n",
       "      <td>39.958</td>\n",
       "      <td>0.000050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>5.76</td>\n",
       "      <td>5.759</td>\n",
       "      <td>0.000174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>10.88</td>\n",
       "      <td>10.877</td>\n",
       "      <td>0.000276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>11.83</td>\n",
       "      <td>11.832</td>\n",
       "      <td>0.000169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>0.25</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>21.30</td>\n",
       "      <td>21.297</td>\n",
       "      <td>0.000141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>49.49</td>\n",
       "      <td>49.490</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>62.72</td>\n",
       "      <td>62.718</td>\n",
       "      <td>0.000032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>28.42</td>\n",
       "      <td>28.424</td>\n",
       "      <td>0.000141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>53.69</td>\n",
       "      <td>53.689</td>\n",
       "      <td>0.000019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>8.41</td>\n",
       "      <td>8.409</td>\n",
       "      <td>0.000119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>15.88</td>\n",
       "      <td>15.883</td>\n",
       "      <td>0.000189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>16.64</td>\n",
       "      <td>16.636</td>\n",
       "      <td>0.000240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>0.40</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>29.94</td>\n",
       "      <td>29.945</td>\n",
       "      <td>0.000167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>54.91</td>\n",
       "      <td>54.911</td>\n",
       "      <td>0.000018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>69.61</td>\n",
       "      <td>69.612</td>\n",
       "      <td>0.000029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>32.36</td>\n",
       "      <td>32.361</td>\n",
       "      <td>0.000031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>61.13</td>\n",
       "      <td>61.127</td>\n",
       "      <td>0.000049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>10.04</td>\n",
       "      <td>10.041</td>\n",
       "      <td>0.000100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>18.97</td>\n",
       "      <td>18.966</td>\n",
       "      <td>0.000211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>19.49</td>\n",
       "      <td>19.488</td>\n",
       "      <td>0.000103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>0.50</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>35.08</td>\n",
       "      <td>35.079</td>\n",
       "      <td>0.000029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>65.14</td>\n",
       "      <td>65.144</td>\n",
       "      <td>0.000061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>82.78</td>\n",
       "      <td>82.778</td>\n",
       "      <td>0.000024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>40.10</td>\n",
       "      <td>40.098</td>\n",
       "      <td>0.000050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>75.74</td>\n",
       "      <td>75.741</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>13.80</td>\n",
       "      <td>13.799</td>\n",
       "      <td>0.000072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>26.06</td>\n",
       "      <td>26.064</td>\n",
       "      <td>0.000153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>25.81</td>\n",
       "      <td>25.813</td>\n",
       "      <td>0.000116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>0.75</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>46.46</td>\n",
       "      <td>46.464</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>72.40</td>\n",
       "      <td>72.395</td>\n",
       "      <td>0.000069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>92.32</td>\n",
       "      <td>92.317</td>\n",
       "      <td>0.000032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>45.77</td>\n",
       "      <td>45.773</td>\n",
       "      <td>0.000066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>86.46</td>\n",
       "      <td>86.460</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>17.22</td>\n",
       "      <td>17.224</td>\n",
       "      <td>0.000232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>32.53</td>\n",
       "      <td>32.535</td>\n",
       "      <td>0.000154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0</td>\n",
       "      <td>31.33</td>\n",
       "      <td>31.333</td>\n",
       "      <td>0.000096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>1.00</td>\n",
       "      <td>Residential Mortgages</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>56.40</td>\n",
       "      <td>56.399</td>\n",
       "      <td>0.000018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>5</td>\n",
       "      <td>78.77</td>\n",
       "      <td>78.772</td>\n",
       "      <td>0.000025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Corporate Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>50</td>\n",
       "      <td>100.95</td>\n",
       "      <td>100.947</td>\n",
       "      <td>0.000030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>50.80</td>\n",
       "      <td>50.794</td>\n",
       "      <td>0.000118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Other Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>95.95</td>\n",
       "      <td>95.945</td>\n",
       "      <td>0.000052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0</td>\n",
       "      <td>21.02</td>\n",
       "      <td>21.018</td>\n",
       "      <td>0.000095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>1.30</td>\n",
       "      <td>Qualifying Revolving Retail Exposures</td>\n",
       "      <td>0.85</td>\n",
       "      <td>0</td>\n",
       "      <td>39.70</td>\n",
       "      <td>39.700</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      PD                                Segment   LGD  SME   Value  RWA_calc  \\\n",
       "0   0.03                    Corporate Exposures  0.45    5   11.30    11.299   \n",
       "1   0.03                    Corporate Exposures  0.45   50   14.44    14.444   \n",
       "2   0.03                 Other Retail Exposures  0.45    0    4.45     4.451   \n",
       "3   0.03                 Other Retail Exposures  0.85    0    8.41     8.408   \n",
       "4   0.03  Qualifying Revolving Retail Exposures  0.45    0    0.98     0.980   \n",
       "5   0.03  Qualifying Revolving Retail Exposures  0.85    0    1.85     1.851   \n",
       "6   0.03                  Residential Mortgages  0.25    0    2.30     2.305   \n",
       "7   0.03                  Residential Mortgages  0.45    0    4.15     4.149   \n",
       "8   0.05                    Corporate Exposures  0.45    5   15.39    15.396   \n",
       "9   0.05                    Corporate Exposures  0.45   50   19.65    19.651   \n",
       "10  0.05                 Other Retail Exposures  0.45    0    6.63     6.629   \n",
       "11  0.05                 Other Retail Exposures  0.85    0   12.52    12.522   \n",
       "12  0.05  Qualifying Revolving Retail Exposures  0.45    0    1.51     1.513   \n",
       "13  0.05  Qualifying Revolving Retail Exposures  0.85    0    2.86     2.858   \n",
       "14  0.05                  Residential Mortgages  0.25    0    3.46     3.461   \n",
       "15  0.05                  Residential Mortgages  0.45    0    6.23     6.230   \n",
       "16  0.10                    Corporate Exposures  0.45    5   23.30    23.297   \n",
       "17  0.10                    Corporate Exposures  0.45   50   29.65    29.654   \n",
       "18  0.10                 Other Retail Exposures  0.45    0   11.16    11.163   \n",
       "19  0.10                 Other Retail Exposures  0.85    0   21.08    21.086   \n",
       "20  0.10  Qualifying Revolving Retail Exposures  0.45    0    2.71     2.709   \n",
       "21  0.10  Qualifying Revolving Retail Exposures  0.85    0    5.12     5.116   \n",
       "22  0.10                  Residential Mortgages  0.25    0    5.94     5.939   \n",
       "23  0.10                  Residential Mortgages  0.45    0   10.69    10.690   \n",
       "24  0.25                    Corporate Exposures  0.45    5   39.01    39.010   \n",
       "25  0.25                    Corporate Exposures  0.45   50   49.47    49.472   \n",
       "26  0.25                 Other Retail Exposures  0.45    0   21.15    21.154   \n",
       "27  0.25                 Other Retail Exposures  0.85    0   39.96    39.958   \n",
       "28  0.25  Qualifying Revolving Retail Exposures  0.45    0    5.76     5.759   \n",
       "29  0.25  Qualifying Revolving Retail Exposures  0.85    0   10.88    10.877   \n",
       "30  0.25                  Residential Mortgages  0.25    0   11.83    11.832   \n",
       "31  0.25                  Residential Mortgages  0.45    0   21.30    21.297   \n",
       "32  0.40                    Corporate Exposures  0.45    5   49.49    49.490   \n",
       "33  0.40                    Corporate Exposures  0.45   50   62.72    62.718   \n",
       "34  0.40                 Other Retail Exposures  0.45    0   28.42    28.424   \n",
       "35  0.40                 Other Retail Exposures  0.85    0   53.69    53.689   \n",
       "36  0.40  Qualifying Revolving Retail Exposures  0.45    0    8.41     8.409   \n",
       "37  0.40  Qualifying Revolving Retail Exposures  0.85    0   15.88    15.883   \n",
       "38  0.40                  Residential Mortgages  0.25    0   16.64    16.636   \n",
       "39  0.40                  Residential Mortgages  0.45    0   29.94    29.945   \n",
       "40  0.50                    Corporate Exposures  0.45    5   54.91    54.911   \n",
       "41  0.50                    Corporate Exposures  0.45   50   69.61    69.612   \n",
       "42  0.50                 Other Retail Exposures  0.45    0   32.36    32.361   \n",
       "43  0.50                 Other Retail Exposures  0.85    0   61.13    61.127   \n",
       "44  0.50  Qualifying Revolving Retail Exposures  0.45    0   10.04    10.041   \n",
       "45  0.50  Qualifying Revolving Retail Exposures  0.85    0   18.97    18.966   \n",
       "46  0.50                  Residential Mortgages  0.25    0   19.49    19.488   \n",
       "47  0.50                  Residential Mortgages  0.45    0   35.08    35.079   \n",
       "48  0.75                    Corporate Exposures  0.45    5   65.14    65.144   \n",
       "49  0.75                    Corporate Exposures  0.45   50   82.78    82.778   \n",
       "50  0.75                 Other Retail Exposures  0.45    0   40.10    40.098   \n",
       "51  0.75                 Other Retail Exposures  0.85    0   75.74    75.741   \n",
       "52  0.75  Qualifying Revolving Retail Exposures  0.45    0   13.80    13.799   \n",
       "53  0.75  Qualifying Revolving Retail Exposures  0.85    0   26.06    26.064   \n",
       "54  0.75                  Residential Mortgages  0.25    0   25.81    25.813   \n",
       "55  0.75                  Residential Mortgages  0.45    0   46.46    46.464   \n",
       "56  1.00                    Corporate Exposures  0.45    5   72.40    72.395   \n",
       "57  1.00                    Corporate Exposures  0.45   50   92.32    92.317   \n",
       "58  1.00                 Other Retail Exposures  0.45    0   45.77    45.773   \n",
       "59  1.00                 Other Retail Exposures  0.85    0   86.46    86.460   \n",
       "60  1.00  Qualifying Revolving Retail Exposures  0.45    0   17.22    17.224   \n",
       "61  1.00  Qualifying Revolving Retail Exposures  0.85    0   32.53    32.535   \n",
       "62  1.00                  Residential Mortgages  0.25    0   31.33    31.333   \n",
       "63  1.00                  Residential Mortgages  0.45    0   56.40    56.399   \n",
       "64  1.30                    Corporate Exposures  0.45    5   78.77    78.772   \n",
       "65  1.30                    Corporate Exposures  0.45   50  100.95   100.947   \n",
       "66  1.30                 Other Retail Exposures  0.45    0   50.80    50.794   \n",
       "67  1.30                 Other Retail Exposures  0.85    0   95.95    95.945   \n",
       "68  1.30  Qualifying Revolving Retail Exposures  0.45    0   21.02    21.018   \n",
       "69  1.30  Qualifying Revolving Retail Exposures  0.85    0   39.70    39.700   \n",
       "\n",
       "        diff  \n",
       "0   0.000088  \n",
       "1   0.000277  \n",
       "2   0.000225  \n",
       "3   0.000238  \n",
       "4   0.000000  \n",
       "5   0.000541  \n",
       "6   0.002174  \n",
       "7   0.000241  \n",
       "8   0.000390  \n",
       "9   0.000051  \n",
       "10  0.000151  \n",
       "11  0.000160  \n",
       "12  0.001987  \n",
       "13  0.000699  \n",
       "14  0.000289  \n",
       "15  0.000000  \n",
       "16  0.000129  \n",
       "17  0.000135  \n",
       "18  0.000269  \n",
       "19  0.000285  \n",
       "20  0.000369  \n",
       "21  0.000781  \n",
       "22  0.000168  \n",
       "23  0.000000  \n",
       "24  0.000000  \n",
       "25  0.000040  \n",
       "26  0.000189  \n",
       "27  0.000050  \n",
       "28  0.000174  \n",
       "29  0.000276  \n",
       "30  0.000169  \n",
       "31  0.000141  \n",
       "32  0.000000  \n",
       "33  0.000032  \n",
       "34  0.000141  \n",
       "35  0.000019  \n",
       "36  0.000119  \n",
       "37  0.000189  \n",
       "38  0.000240  \n",
       "39  0.000167  \n",
       "40  0.000018  \n",
       "41  0.000029  \n",
       "42  0.000031  \n",
       "43  0.000049  \n",
       "44  0.000100  \n",
       "45  0.000211  \n",
       "46  0.000103  \n",
       "47  0.000029  \n",
       "48  0.000061  \n",
       "49  0.000024  \n",
       "50  0.000050  \n",
       "51  0.000013  \n",
       "52  0.000072  \n",
       "53  0.000153  \n",
       "54  0.000116  \n",
       "55  0.000086  \n",
       "56  0.000069  \n",
       "57  0.000032  \n",
       "58  0.000066  \n",
       "59  0.000000  \n",
       "60  0.000232  \n",
       "61  0.000154  \n",
       "62  0.000096  \n",
       "63  0.000018  \n",
       "64  0.000025  \n",
       "65  0.000030  \n",
       "66  0.000118  \n",
       "67  0.000052  \n",
       "68  0.000095  \n",
       "69  0.000000  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Data provided in the Annex 5 of https://www.bis.org/publ/bcbs128.pdf\n",
    "data = [\n",
    "    [0.03, 14.44, 11.30, 4.15, 2.30, 4.45, 8.41, 0.98, 1.85],\n",
    "    [0.05, 19.65, 15.39, 6.23, 3.46, 6.63, 12.52, 1.51, 2.86],\n",
    "    [0.10, 29.65, 23.30, 10.69, 5.94, 11.16, 21.08, 2.71, 5.12],\n",
    "    [0.25, 49.47, 39.01, 21.30, 11.83, 21.15, 39.96, 5.76, 10.88],\n",
    "    [0.40, 62.72, 49.49, 29.94, 16.64, 28.42, 53.69, 8.41, 15.88],\n",
    "    [0.50, 69.61, 54.91, 35.08, 19.49, 32.36, 61.13, 10.04, 18.97],\n",
    "    [0.75, 82.78, 65.14, 46.46, 25.81, 40.10, 75.74, 13.80, 26.06],\n",
    "    [1.00, 92.32, 72.40, 56.40, 31.33, 45.77, 86.46, 17.22, 32.53],\n",
    "    [1.30, 100.95, 78.77, 67.00, 37.22, 50.80, 95.95, 21.02, 39.70],\n",
    "    [1.50, 105.59, 82.11, 73.45, 40.80, 53.37, 100.81, 23.40, 44.19],\n",
    "    [2.00, 114.86, 88.55, 87.94, 48.85, 57.99, 109.53, 28.92, 54.63],\n",
    "    [2.50, 122.16, 93.43, 100.64, 55.91, 60.90, 115.03, 33.98, 64.18],\n",
    "    [3.00, 128.44, 97.58, 111.99, 62.22, 62.79, 118.61, 38.66, 73.03],\n",
    "    [4.00, 139.58, 105.04, 131.63, 73.13, 65.01, 122.80, 47.16, 89.08],\n",
    "    [5.00, 149.86, 112.27, 148.22, 82.35, 66.42, 125.45, 54.75, 103.41],\n",
    "    [6.00, 159.61, 119.48, 162.52, 90.29, 67.73, 127.94, 61.61, 116.37],\n",
    "    [10.00, 193.09, 146.51, 204.41, 113.56, 75.54, 142.69, 83.89, 158.47],\n",
    "    [15.00, 221.54, 171.91, 235.72, 130.96, 88.60, 167.36, 103.89, 196.23],\n",
    "    [20.00, 238.23, 188.42, 253.12, 140.62, 100.28, 189.41, 117.99, 222.86]\n",
    "]\n",
    "\n",
    "# Dummy 3-level column names\n",
    "columns = pd.MultiIndex.from_tuples([\n",
    "    ('PD', '', ''),\n",
    "    ('Corporate Exposures', 0.45, 50),\n",
    "    ('Corporate Exposures', 0.45, 5),\n",
    "    ('Residential Mortgages', 0.45, 0),\n",
    "    ('Residential Mortgages', 0.25, 0),\n",
    "    ('Other Retail Exposures', 0.45, 0),\n",
    "    ('Other Retail Exposures', 0.85, 0),\n",
    "    ('Qualifying Revolving Retail Exposures', 0.45, 0),\n",
    "    ('Qualifying Revolving Retail Exposures', 0.85, 0),\n",
    "], names=['Segment', 'LGD', 'SME'])\n",
    "\n",
    "# Create the original DataFrame again\n",
    "df = pd.DataFrame(data, columns=columns)\n",
    "\n",
    "# Set 'PD' as index for stacking\n",
    "df['PD'] = df[('PD', '', '')]\n",
    "df_dropped = df.drop(('PD', '', ''), axis=1)\n",
    "df_dropped = df_dropped.set_index(df['PD'])\n",
    "\n",
    "# Perform the stack operation\n",
    "df_stacked = df_dropped.stack(level=[0, 1, 2]).reset_index(name='Value')\n",
    "df_stacked.columns = ['PD', 'Segment', 'LGD', 'SME', 'Value']\n",
    "\n",
    "# Applying the function row-wise\n",
    "bis_add_on = 1.0\n",
    "df_stacked['RWA_calc'] = df_stacked.apply(lambda row: calculate_RWA(row['Segment'], \n",
    "                                                                    row['PD'] / 100, \n",
    "                                                                    row['LGD'], \n",
    "                                                                    100, \n",
    "                                                                    bis_add_on,\n",
    "                                                                    2.5, \n",
    "                                                                    row['SME'],\n",
    "                                                                    ), axis=1)\n",
    "\n",
    "df_stacked['RWA_calc'] = df_stacked['RWA_calc'].round(3)\n",
    "df_stacked['diff'] = abs(1- df_stacked['RWA_calc'] / df_stacked['Value'])\n",
    "df_stacked.head(70)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4218e6e2-b0b0-48b6-99a4-512bb6ddacb4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The maximum difference between calculated and provided RW is 0.22%\n"
     ]
    }
   ],
   "source": [
    "print(f\"The maximum difference between calculated and provided RW is {df_stacked['diff'].max():.2%}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}