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.
| Cell | Input | Example |
|---|---|---|
| B1 | PD | 0.01 |
| B2 | LGD | 0.40 |
| B3 | Effective maturity M in years, 1 to 5 | 2.5 |
| B4 | Scaling factor: 1 under CRR3, 1.06 under CRR | 1 |
| B5 | PD floor: 0.0005 under CRR3 (0.001 for QRRE revolvers), 0.0003 under CRR | 0.0005 |
| B6 | Exposure 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 ofNORM.S.DIST(x,TRUE)andNORMSINV(p)instead ofNORM.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
- IRB risk weight formula, step by step: what each term means.
- IRB risk weight table: results for common PD and LGD values.
- IRB risk weight calculator: the same calculation with calculation steps and CSV download.
- Risk Calculation MCP: run the same calculation locally in Claude Desktop, Claude Code or Codex.
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: