Banking KPIs in Excel and Power BI

Ten important KPIs for bank and NBFC. Each card has what it is, why it matters, the Excel formula, the DAX measure and a tiny worked example. Every card shows the Excel formula next to the Power BI DAX measure. For Excel, put the card’s example table in cell A1 (labels in column A, numbers in column B).

About this data: All examples are made-up teaching numbers for banks in Mumbai, London, Pune, Frankfurt, Singapore, Chennai, Toronto, Kolkata, Manchester and Dublin, in ₹, £, € and $. Table names (GL, Deposits, Loans, Assets, Capital, Risk, Provisions, Applications, Marketing, Accounts) are sample names.

Net Interest Margin % KPI card: what, why, DAX and example — **Net Interest Margin %**: Interest earned minus interest paid, per rupee of loans and investments.`DAX: NIM % = DIVIDE(SUM(GL[InterestIncome]) - SUM(GL[InterestExpense]), AVERAGE(Assets[EarningAssets]))Excel: =(B2-B3)/B4`

CASA Ratio % KPI card: what, why, DAX and example — **CASA Ratio %**: Share of cheap current and savings deposits.`DAX: CASA % = DIVIDE(CALCULATE(SUM(Deposits[Balance]), Deposits[Type] IN {"Current","Savings"}), SUM(Deposits[Balance]))Excel: =(B2+B3)/B4`

Gross NPA % KPI card: what, why, DAX and example — **Gross NPA %**: Share of loans where the borrower stopped paying.`DAX: Gross NPA % = DIVIDE(CALCULATE(SUM(Loans[Outstanding]), Loans[DaysPastDue] > 90), SUM(Loans[Outstanding]))Excel: =B3/B2`

Loan-to-Deposit Ratio % KPI card: what, why, DAX and example — **Loan-to-Deposit Ratio %**: How much of the deposits is lent out.`DAX: LDR % = DIVIDE(SUM(Loans[Outstanding]), SUM(Deposits[Balance]))Excel: =B2/B3`

Cost-to-Income Ratio % KPI card: what, why, DAX and example — **Cost-to-Income Ratio %**: Running cost for every unit of income.`DAX: Cost to Income % = DIVIDE(SUM(GL[OpEx]), SUM(GL[OperatingIncome]))Excel: =B2/B3`

Return on Assets (ROA) % KPI card: what, why, DAX and example — **Return on Assets (ROA) %**: Profit earned on everything the bank owns.`DAX: ROA % = DIVIDE(SUM(GL[NetProfit]), AVERAGE(BalanceSheet[TotalAssets]))Excel: =B2/AVERAGE(B3:B4)`

Capital Adequacy Ratio % KPI card: what, why, DAX and example — **Capital Adequacy Ratio %**: Capital kept as a cushion against risky loans.`DAX: CAR % = DIVIDE(SUM(Capital[Tier1]) + SUM(Capital[Tier2]), SUM(Risk[RWA]))Excel: =(B2+B3)/B4`

Provision Coverage Ratio % KPI card: what, why, DAX and example — **Provision Coverage Ratio %**: Share of bad loans already covered by provisions.`DAX: PCR % = DIVIDE(SUM(Provisions[Amount]), [Gross NPA Amount])Excel: =B3/B2`

Loan Approval Rate % KPI card: what, why, DAX and example — **Loan Approval Rate %**: Share of loan applications approved.`DAX: Approval % = DIVIDE(CALCULATE(COUNTROWS(Applications), Applications[Status] = "Approved"), COUNTROWS(Applications))Excel: =B3/B2`

Cost per New Account KPI card: what, why, DAX and example — **Cost per New Account**: Marketing money spent for each new account.`DAX: Cost per Account = DIVIDE(SUM(Marketing[Spend]), DISTINCTCOUNT(Accounts[AccountID]))Excel: =B2/B3`

Quick quiz

1. Net Interest Margin %: Interest income ₹8,40,00,000, Interest paid on deposits ₹4,20,00,000, Average earning assets ₹1,20,00,00,000. The answer is…

2. CASA Ratio %: Current account deposits £72,000,000, Savings account deposits £108,000,000, Total deposits £450,000,000. The answer is…

3. Gross NPA %: Total loans (gross advances) ₹50,00,00,000, Non-performing loans (90+ days unpaid) ₹2,10,00,000. The answer is…

4. Which KPI means: “How much of the deposits is lent out.”

← All business KPI sets (KPI Hub)