IRB risk weight formula in Excel

Formulas to paste into a worksheet for the corporate and retail IRB risk weight under CRR3 and CRR, with values to check the result.

The IRB risk weight of Article 153 and 154 CRR needs only two statistical functions: the standard normal distribution, NORM.S.DIST, and its inverse, NORM.S.INV. With PD 1%, LGD 40% and a 2.5-year maturity, the formulas below give a corporate risk weight of 82.06% under CRR3 and 86.98% under CRR before 2025, the same as the calculator.

Inputs

Enter PD and LGD as decimals: 1% is 0.01. Under CRR3, apply the own-estimate LGD floor before entering LGD, for example 25% for unsecured corporate exposures.

CellInputExample
B1PD0.01
B2LGD0.40
B3Effective maturity M in years, 1 to 52.5
B4Scaling factor: 1 under CRR3, 1.06 under CRR1
B5PD floor: 0.0005 under CRR3 (0.001 for QRRE revolvers), 0.0003 under CRR0.0005
B6Exposure at default (EAD)10000000

Corporate exposures

B7, PD after the floor:

=MAX(B1,B5)

B8, correlation R:

=0.12*(1-EXP(-50*B7))/(1-EXP(-50))+0.24*(1-(1-EXP(-50*B7))/(1-EXP(-50)))

B9, maturity adjustment b:

=(0.11852-0.05478*LN(B7))^2

B10, capital requirement K:

=(B2*NORM.S.DIST((NORM.S.INV(B7)+SQRT(B8)*NORM.S.INV(0.999))/SQRT(1-B8),TRUE)-B7*B2)*(1+(B3-2.5)*B9)/(1-1.5*B9)

B11, risk weight; format the cell as a percentage:

=B10*12.5*B4

B12, risk-weighted assets, and B13, the 8% capital requirement:

=B11*B6
=B12*0.08

Check values with the example inputs: R = 0.192784, b = 0.137486, K = 0.065648, risk weight 82.06%, RWA 8,205,938 and capital 656,475. The calculator shows RWA 8,205,940 because it uses a numerical approximation of the normal distribution; the difference is below 0.00003%. With B4 = 1.06 and B5 = 0.0003 the risk weight is 86.98%.

For corporate SME under Article 153(4), put consolidated annual sales in EUR millions in B14 and replace R in B8 with:

=0.12*(1-EXP(-50*B7))/(1-EXP(-50))+0.24*(1-(1-EXP(-50*B7))/(1-EXP(-50)))-0.04*(1-(MIN(MAX(B14,5),50)-5)/45)

Retail exposures

Retail formulas have no maturity adjustment. Keep B1 to B7 and B11 to B13; replace B8 and B10. Under CRR3 the LGD floors are 5% for residential mortgages, 50% for qualifying revolving exposures and 30% for unsecured other retail.

B8, correlation R: 0.15 for retail secured by residential property, 0.04 for qualifying revolving retail, and for other retail:

=0.03*(1-EXP(-35*B7))/(1-EXP(-35))+0.16*(1-(1-EXP(-35*B7))/(1-EXP(-35)))

B10, capital requirement K:

=B2*NORM.S.DIST((NORM.S.INV(B7)+SQRT(B8)*NORM.S.INV(0.999))/SQRT(1-B8),TRUE)-B7*B2

Check values: a residential mortgage with PD 1%, LGD 20% and R = 0.15 has a CRR3 risk weight of 25.07%. Other retail with PD 5% and LGD 60% has a CRR risk weight of 93.87%.

Download the workbook

IRB risk weight workbook (.xlsx) contains these formulas for 50 exposures on a CRR3 sheet and a CRR sheet. Choose the exposure class in column A and enter PD, LGD, maturity, annual sales for corporate SME and EAD; the sheet applies the PD floor and the unsecured own-estimate LGD floor from its parameter table and returns correlation, K, risk weight, RWA and capital. The workbook is free, unprotected and contains no macros. Example rows reproduce the check values above.

Other spreadsheet programs

  • Excel 2007 and earlier: use NORMSDIST(x) instead of NORM.S.DIST(x,TRUE) and NORMSINV(p) instead of NORM.S.INV(p).
  • Google Sheets and LibreOffice Calc provide the same functions; LibreOffice may require a semicolon as the argument separator, depending on its settings.
  • Excel in a language other than English translates function names and may use a semicolon as the argument separator. The Turkish version of this page gives the formulas in Turkish Excel syntax.

Related

Regulatory sources and scope

Regulation (EU) No 575/2013 (CRR), Articles 153–154 (risk weight formulas), 160 and 163 (PD floors), 161 and 164 (LGD); Regulation (EU) 2024/1623 (CRR3); Basel Framework CRE31. The formulas cover a single non-defaulted exposure; credit risk mitigation, the 1.25 financial-sector multiplier and the output floor are not included. The last review date is not a regulatory effective date.

Last reviewed: