Excel workbook previewCapital Adequacy Ratio Calculator (CET1, Tier 1, Total Capital)
3 free downloads per account. Just your email and a 6-digit code.
About this template
A capital adequacy ratio calculation shows whether a bank holds enough loss-absorbing capital for the risks on its balance sheet. Boards, asset and liability committees, examiners and auditors expect to see the three risk-based ratios worked through from the balance sheet: Common Equity Tier 1, Tier 1 and total capital, each against its minimum and the buffers above it. This workbook is for the person who has to produce that calculation and explain every number in it.
The Calculator sheet builds CET1 from common shares, surplus, retained earnings and reserves, deducts the regulatory adjustments named in chapter CAP30 of the Basel Framework, adds Additional Tier 1 and Tier 2, and divides by risk-weighted assets with an optional output floor from RBC20.4. Minimums follow RBC20.1 and the buffers RBC30, all as editable inputs. A Sensitivity sheet and a Conservation Bands sheet use the same live formulas.
Who it is for
The finance or risk manager at a bank who was asked by the board, ALCO or the examiner for a clean, traceable capital ratio calculation.
What is inside
- Calculator sheet with 34 input lines: capital components, 11 CET1 regulatory adjustments, RWA by risk type and rates
- CET1, Tier 1 and total capital ratios with requirement, surplus in USD and a color-coded status for each
- Output floor switch: RWA is the higher of nominated approaches and the floor percentage of standardized RWA
- Capital conservation check: CET1 for buffer purposes, buffer quartiles and the share of earnings that must be retained
- Sensitivity sheet with 6 worked scenarios (loan growth, credit loss, rate shock) plus 10 blank rows
- Conservation Bands sheet that rebuilds the RBC30.4 quartile table for your combined buffer
- Every minimum and buffer rate is an input cell with its Basel Framework paragraph, so national rules can be applied
- Worked example for an invented USD 1.2 billion community bank, with a How to use sheet
How to use it
Replace the yellow input cells on the Calculator sheet with your own capital and RWA figures, then check the minimum and buffer rates against your national rule.
Files
- XLSXcapital-adequacy-ratio-calculator.xlsx16 KB
Opens in Microsoft Word or Excel, Google Docs or Sheets, and LibreOffice. Built on free, official texts that anyone can read: laws, regulations, standards and guidance published by public bodies. A template to adapt, not legal, tax or accounting advice.
Questions about this template
How is the CET1 ratio calculated?
The CET1 ratio is Common Equity Tier 1 capital after regulatory adjustments divided by total risk-weighted assets. Under RBC20.1 of the Basel Framework it must be at least 4.5 percent, with the capital conservation buffer of 2.5 percent from RBC30.2 on top. National rules can set different rates, so the workbook keeps every rate in an input cell.
What is the output floor in the capital ratio?
RBC20.4 sets risk-weighted assets as the higher of the amount from the bank's own nominated approaches and 72.5 percent of the amount calculated with the standardized approaches only. A bank that already uses only standardized approaches sees no effect. RBC90 phases the floor in, so the percentage is an input you can change.
What happens when a bank is inside the conservation buffer?
The bank can still operate but must retain part of its earnings. RBC30.4 divides the buffer into four quarters and sets the share of earnings to keep at 100, 80, 60 or 40 percent depending on the quarter the CET1 ratio falls in. The workbook shows that share and the maximum distributable share.




