Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.5 Banking: Loan Book and NPA Tracker

By Last updated:

Example first. Amir reviews a fictional cooperative lender's loan accounts and wants the loan book and bad loans by city on one page.

Banking project — in 30 seconds

What: A Banking tracker: one Table tblLoans, the calculated column Status, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Banking report: Loan Book (₹ lakh), NPA Amount (₹ lakh), NPA % are the numbers a manager reads first.

Tiny example: From 6 rows: Loan Book (₹ lakh) = 124.0, NPA Amount (₹ lakh) = 44.0, NPA % = 35.5%.

First look at the data (before → after):

Banking project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

LoanID Status
LN-701 Standard
LN-702 NPA
LN-703 Standard
LN-704 NPA
LN-705 Standard
LN-706 Standard

Expected result — KPI cells (expected results)

KPI Formula Result
Loan Book (₹ lakh) =SUM(tblLoans[AmountLakh]) 124.0
NPA Amount (₹ lakh) =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA") 44.0
NPA % =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA")/SUM(tblLoans[AmountLakh]) 35.5%
Before table and formula as text

Before — Table tblLoans (fictional sample rows)

LoanID City Product AmountLakh OverdueDays
LN-701 Pune Home 42.5 0
LN-702 Pune Personal 6.0 120
LN-703 Nashik Gold 3.2 15
LN-704 Nagpur Home 38.0 95
LN-705 Kolhapur Personal 4.5 0
LN-706 Nashik Home 29.8 40

Formula / click path

Status: =IF([@OverdueDays]>90,"NPA","Standard")
Loan Book (₹ lakh): =SUM(tblLoans[AmountLakh])
NPA Amount (₹ lakh): =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA")
NPA %: =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA")/SUM(tblLoans[AmountLakh])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblLoans in Table Design › Properties › Table Name.
  2. Add the column Status with =IF([@OverdueDays]>90,"NPA","Standard"); the Table fills it down.
  3. On a KPI sheet type =SUM(tblLoans[AmountLakh]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and AmountLakh in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Product.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a loan-book tracker: mark each loan NPA when it is more than 90 days overdue (a practice rule for this exercise, not a regulatory definition), total book, NPA amount, NPA % by amount, book by city, a chart and a Product slicer.

Calculated column formulas:

Column Formula
Status =IF([@OverdueDays]>90,"NPA","Standard")

KPI cells (check your numbers):

KPI Formula Result
Loan Book (₹ lakh) =SUM(tblLoans[AmountLakh]) 124.0
NPA Amount (₹ lakh) =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA") 44.0
NPA % =SUMIFS(tblLoans[AmountLakh],tblLoans[Status],"NPA")/SUM(tblLoans[AmountLakh]) 35.5%

PivotTable (expected):

City Sum of AmountLakh
Kolhapur 4.5
Nagpur 38.0
Nashik 33.0
Pune 48.5
Grand Total 124.0

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Product and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Banking_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Banking dashboard guide and its workbook Banking_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of AmountLakh?
  2. Filter the Table on City = Pune (Data › Filter): what is Loan Book (₹ lakh) for Pune alone?
  3. Add the Clustered Column PivotChart and the Product slicer to your Dashboard sheet.
Answer

Pune (48.5). For Pune only (2 rows): Loan Book (₹ lakh) = 48.5. The chart shows 4 bars, one per city.

Ravindra Bagale's Tip

Check Loan Book (₹ lakh) by hand for two rows before you trust the dashboard. If your hand answer and the cell differ, the formula range is usually wrong.

Loading practice tasks...

  1. Monthly rate (Easy): Yearly rate B3 divided by 12.
  2. Months (Easy): B4 times 12.
  3. Monthly EMI (Easy): EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid (Medium): Total paid over the loan, rounded to 0 decimals.
  5. Total interest (Medium): Total paid minus B2, rounded to 0 decimals.
  6. Processing fee (Medium): B2 times B6.
  7. Savings value (Medium): Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned (Hard): Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI (Hard): Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford (Hard): Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share (Hard): Total interest ÷ total paid, rounded to 3 decimals.

See all practice tasks in the Task Hub →