Excel for Bankers: From Beginners to Pro Guide

Skip to content

Excel for Bankers BD

Updated 24 October 2026 · 12 min read · Beginner to advanced

Excel for Bankers in Bangladesh

From your first SUM to PivotTables and Power Query, learn the Excel skills that branch, credit, trade and audit teams use every day. Start with the live EMI calculator.

Loan EMI calculator (BDT)

Loan amount (Tk) Annual rate (%) Tenure (years) Rate typeReducing balanceFlat rate

Monthly EMI-

Total interest-

Effective rate-

=-PMT(12%/12,60,1000000)

Part 1: Beginner basics

Keep one row per record, one column per data type, and no merged cells inside data. These habits make sorting, filters and PivotTables work.

Account numbers and lakh-crore format

Format account numbers as Text so leading zeros stay. For Bangladeshi digit grouping (1,23,45,678) use this custom number format in Ctrl+1:

[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

Try it: enter a number

First formulas

Task	Formula
Total deposits	=SUM(E2:E100)
Largest balance	=MAX(E2:E100)
Count accounts	=COUNTA(A2:A100)
Interest on tax rate in H1	=C2*$H$1

Press F4 to lock a reference ($H$1). Use Ctrl+T for Tables, Ctrl+Shift+L for filters, and Freeze Panes to keep headings visible.

Part 2: Intermediate logic and dates

IF, IFS and conditional totals

=IF(E2<5000,"Below Minimum","OK")

=IFS(D2<90,"Standard",D2<180,"Sub-Standard",D2<270,"Doubtful",TRUE,"Bad/Loss")

=SUMIFS(E2:E500,C2:C500,"Current",F2:F500,"Dhanmondi")

Try it: days overdue

Illustrative thresholds only. Keep real thresholds and provision rates on a parameters sheet and follow the latest Bangladesh Bank circular.

Dates with Friday-Saturday weekend

=NETWORKDAYS.INTL(A2,B2,7)

=EDATE(A2,12)

Add conditional formatting for overdue loans and data validation drop-downs for product types to prevent entry errors.

Part 3: Lookups and reconciliation

Use exact match every time in banking data.

=XLOOKUP(A2,Master!A:A,Master!B:B,"Not found")

=INDEX(Master!B:B,MATCH(A2,Master!A:A,0))

=IF(COUNTIF(Statement!A:A,A2)=0,"Unmatched","Matched")

The same pattern reconciles nostro accounts, mobile financial service settlements, ATM and card files, and BEFTN or RTGS returns. Filter the unmatched rows on both sides, then check =SUM(Ledger!E:E)-SUM(Statement!E:E).

Part 4: Financial functions

Need	Formula
Monthly installment	=-PMT(12%/12,60,1000000)
Interest part	=IPMT(rate,period,nper,pv)
FDR value, quarterly	=FV(8%/4,4*5,0,-500000)
DPS maturity	=FV(7%/12,60,-5000)
Effective rate	=RATE(nper,-emi,loan)*12
Project return	=IRR(cash_flows)

Flat-rate quotes look cheaper than they are. Switch the calculator at the top to Flat rate to see the true effective rate.

Part 5: Advanced analysis

• PivotTables: branch by product deposit reports, sector-wise loans, top 20 borrowers, with slicers for filters.
• Dynamic arrays: =UNIQUE(), =SORT() and =FILTER(A2:E500,D2:D500>90) in Microsoft 365.
• Power Query: merge 64 branch files into one table and refresh next month in seconds.
• Dashboards: KPI cards for deposits, loans, NPL % and ADR, with three to five clear charts.
• Macros: record repeated formatting or export steps, and check your bank's IT policy first.

=SUMPRODUCT(Outstanding,Rate)/SUM(Outstanding)

Part 6: Banking use cases in Bangladesh

Use case	Core approach
ADR monitoring	Loans / deposits compared with a limit in a parameter cell
CRR and SLR	Required = ATDTL x rate, then surplus or shortfall
NPL and provision	NPL / total loans, with a class-to-rate lookup table
Loan aging	Buckets with LOOKUP, then PivotTable
LC tracking	Expiry minus TODAY(), margin %, exchange-rate lookup by date
Audit sampling	RANDBETWEEN, duplicate checks, sequence gap tests

Rates, limits and classification rules change. Always confirm against the latest Bangladesh Bank circulars and your bank's policy.

Part 7: Controls and security

1. Separate inputs, calculations and outputs.
2. Never hard-code rates inside formulas.
3. Add check cells, for example =IF(ROUND(A1-B1,2)=0,"OK","CHECK").
4. Protect formula cells and use maker-checker review.
5. Keep customer data on approved bank systems only. Use dummy data for practice.
6. Add a ReadMe sheet and clear file names with dates and versions.

Part 8: Historical lookup and visit report

Pick a customer and a month to see the year-to-date visit history, balance trend and overdue status in one report. The sample data below is dummy data for practice.

Customer Year to date through

Month	Visits	Balance (lakh)	Overdue days	Status

Same report in Excel

=XLOOKUP(B1,Visits[ID],Visits[Customer])

=SUMIFS(Visits[Count],Visits[ID],B1,Visits[Month],"<="&B2)

=MAXIFS(Visits[OverdueDays],Visits[ID],B1,Visits[Month],"<="&B2)

Store one row per customer per month in a Table, then add a PivotTable with Customer in rows, Month in columns and Sum of Visits in values for the full-year grid.

Part 9: Portfolio analytics

Move the slider to see how the loan portfolio looked in any month of the year. Ratios update from the same dummy data.

Portfolio as at 2026

By sector

By classification

Top 5 borrowers (concentration)

NPL ratio through the year

Classification uses the illustrative 90, 180 and 270 day thresholds. Provision rates here (1%, 20%, 50%, 100%) are for learning only. Use the current Bangladesh Bank rules for real work.

Same analytics in Excel

=SUMIFS(Out,Class,"<>Standard")/SUM(Out)

=SUMPRODUCT(Out,Rate)/SUM(Out)

=SUMPRODUCT(LARGE(Out,{1,2,3,4,5}))/SUM(Out)

Frequently asked questions

How do I show numbers in lakh and crore in Excel?

Apply the custom format from Part 1, or set the Windows region to English (Bangladesh).

Which function calculates a loan EMI?

Use PMT, for example =-PMT(12%/12,60,1000000).

How do I treat Friday and Saturday as weekend?

Use NETWORKDAYS.INTL with weekend code 7.