Risk-weighted assets calculation: a worked RWA example in Excel

The RWA formula

RWA = exposure amount × CCF × risk weight, summed over all exposures

For loans and securities on the balance sheet the CCF is 100%, so RWA is simply the amount times the risk weight. Total credit RWA is the sum of all lines, or in one cell =SUMPRODUCT(B2:B9,C2:C9,D2:D9). Banks then add RWA for market risk (the trading book) and operational risk, each calculated with its own method, to get total RWA. Every capital ratio uses total RWA as the denominator.

Worked example: RWA and CET1 ratio of a simplified bank

Amounts in USD millions. Yellow cells are inputs: the exposures, their CCF and a simplified risk weight per class. Row 10 totals the credit risk, rows 14 to 22 add market and operational risk and test the CET1 ratio.

Worked example: RWA and CET1 ratio of a simplified bank

	A	B	C	D	E
1	Exposure class	Amount (USD m)	CCF	Risk weight	RWA (USD m)
2	Cash and central bank reserves	120	100%	0%	0.0
3	Government bonds (rated AA- or better)	380	100%	0%	0.0
4	Residential mortgages (LTV 60–80%)	1,450	100%	30%	435.0
5	Regulatory retail loans	420	100%	75%	315.0
6	Corporate loans (unrated)	760	100%	100%	760.0
7	Past-due loans	40	100%	150%	60.0
8	Equity holdings	30	100%	250%	75.0
9	Undrawn corporate commitments	400	40%	100%	160.0
10	Credit risk: exposure after CCF and RWA	3,360			1,805.0
11	Same credit RWA in one SUMPRODUCT				1,805.0
12	RWA density (credit RWA ÷ exposure)				53.7%
13
14	Market risk RWA (from the market risk model)				140.0
15	Operational risk RWA				255.0
16	Total RWA				2,200.0
17	CET1 capital				290.0
18	CET1 ratio				13.18%
19	CET1 minimum 4.5% + conservation buffer 2.5%				7.0%
20	CET1 capital required				154.0
21	CET1 surplus				136.0
22	RWA the bank could add before hitting 7%				1,942.9

A simplified Basel III-style teaching example with fictional figures. Real risk weights depend on ratings, LTV and national rules. Not affiliated with the BIS or the Basel Committee. Educational only, not regulatory or investment advice.

Risk weights under a standardised approach

Under the standardised approach of the Basel III framework, the risk weight depends on the exposure class and, within it, on things like the external rating or the loan-to-value (LTV) ratio. Simplified, typical weights are:

• Cash, central bank reserves and highly rated government bonds: 0%.
• Residential mortgages: from 20% for a low LTV up to 70% for loans above 100% LTV. The example uses 30% for an LTV of 60–80%.
• Regulatory retail, small loans to individuals and small firms in a diversified pool: 75%.
• Unrated corporates: 100%.
• Past-due loans: up to 150%. Equity holdings: 250%.

In the example the USD 1,450 m mortgage book becomes USD 435 m of RWA, while USD 760 m of corporate loans count in full.

Off-balance-sheet items and credit conversion factors

A credit line the customer has not used yet is not on the balance sheet, but it can be drawn when the customer gets into trouble. A credit conversion factor estimates how much of it would be drawn: 40% for general commitments under the final Basel III rules, 10% for commitments the bank can cancel at any time, and higher factors for items such as guarantees that substitute for a loan. USD 400 m of undrawn corporate commitments × 40% = USD 160 m of exposure, weighted at 100% = USD 160 m of RWA.

RWA density and what it tells you

RWA density = RWA ÷ exposure is the average risk weight of the book. Here USD 1,805 m ÷ USD 3,360 m = 53.7%. A mortgage-heavy bank has a low density, a corporate lender a high one. Analysts compare densities between banks to see how risky their books are and, for banks with internal models, how aggressive the models are. To limit that, the final Basel III rules add an output floor: RWA from internal models may not fall below 72.5% of the standardised figure.

From RWA to the capital ratios

Adding USD 140 m of market risk RWA and USD 255 m of operational risk RWA gives total RWA of USD 2,200 m. With USD 290 m of common equity tier 1 (CET1) capital the CET1 ratio is 290 ÷ 2,200 = 13.18%. The Basel minimums are 4.5% CET1, 6% tier 1 and 8% total capital, plus a capital conservation buffer of 2.5% of CET1 on top, and national regulators add further buffers. Against the 7% in the example the bank needs USD 154 m, has a surplus of USD 136 m and could add about USD 1,943 m of RWA before it hits the line. Market risk RWA comes from the trading book, where risk is measured with sensitivities and models related to value at risk and expected shortfall.

Standardised approach vs internal models

Large banks may use internal ratings-based (IRB) models, where the risk weight comes from their own estimates of default probability and loss given default instead of a fixed table. The principle is the same, exposure × risk weight, but the weights differ per loan. The standardised table in this guide is a simplified teaching version. Real calculations follow the national implementation of the Basel framework, with many more classes and conditions.

Common mistakes

• Using total assets instead of RWA. Capital ratios divide by risk-weighted assets. Total assets are only used for the leverage ratio.
• Forgetting off-balance-sheet items. Undrawn commitments and guarantees need a CCF and a risk weight, otherwise RWA is too low.
• Applying the CCF and the risk weight twice or not at all. Exposure × CCF gives the exposure amount, and only that is multiplied by the risk weight.
• Counting only credit risk. Total RWA also includes market risk and operational risk, which can be a fifth of the total or more.
• One weight for all mortgages. Mortgage weights depend on the loan-to-value ratio, so a book with high LTVs carries much more RWA.
• Confusing the minimum with the requirement. 4.5% CET1 is only the floor. Buffers come on top, and paying dividends is restricted once a bank dips into them.