School Fee Management System in Excel: Fee Structure, Instalments, Overdue List, Receipts and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: school-fee-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Learners, Receipts, Fee ledger, Fee receipt, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Bagale English School, Solapur, academic year 2026-27, as of 17 October 2026 (made-up data). Fees are paid in 4 equal instalments due on 15 June, 15 August, 15 October and 15 December, so 3 instalments are due by today. Transport is ₹9,000 a year for those who use the bus.

October receipts alone were ₹38,250. The grade-wise view is like the education project, but here the numbers are money, not marks.

2. Build it sheet by sheet

Step 1. Setup: fee structure and due dates

1. In B3 type the as-of date and in B5 the academic year.
2. In A8:C12 type each grade with its tuition and term fee a year; D8 adds them =B8+C8.
3. In B14 type the transport fee (9,000). In A17:C20 list the 4 instalments with due dates and share (25% each).

Step 2. Learners: net fee for the year

1. Headers in row 4: Learner ID, Name, Grade, Transport?, Concession %, Annual fee, Concession, Net fee.
2. In F5 add transport only when needed: =XLOOKUP(C5,Setup!$A$8:$A$12,Setup!$D$8:$D$12,0)+IF(D5="Yes",Setup!$B$14,0).
3. In G5 the concession =ROUND(F5*E5,0) and in H5 the net fee =F5-G5. The concession % is a management decision; type it, don't guess it.

Step 3. Receipts: one row per payment

1. Headers: Receipt no, Date, Learner ID, Name, Grade, Amount, Mode. D5 and E5 look up the name and grade from the ID with XLOOKUP.
2. Never type a total here. If a guardian pays two instalments at once, it is one row with the full amount.

Step 4. Fee ledger: due, paid and overdue

1. In B3 count instalments already due: =COUNTIF(Setup!$B$17:$B$20,"<="&Setup!$B$3). On 17 October that is 3.
2. From row 6 pull the ID, name, grade and net fee from Learners. E6 due till date: =ROUND(D6*$B$3/4,0).
3. F6 paid =SUMIFS(Receipts!$F$5:$F$800,Receipts!$C$5:$C$800,A6) (SUMIFS), G6 overdue =MAX(0,E6-F6) and H6 balance for the year =D6-F6.
4. I6 status: =IF(G6=0,"Up to date",IF(F6=0,"Nothing paid","Overdue")); J6 last payment date with MAXIFS (MAXIFS). Colour Overdue rows red.

Step 5. Fee receipt: print one payment

1. On Fee receipt type a receipt number in B4 (R-026). Rows 6 to 11 pull date, learner, name, grade, amount and mode with XLOOKUP on Receipts.
2. Rows 12 to 14 add the learner's net fee, paid so far and balance from the Fee ledger, so the guardian sees the full picture. Print A1:D17.

Step 6. Report and dashboard

1. On Report, B5 counts learners per grade with COUNTIF; C5 to E5 add net fee, collected and overdue with SUMIFS; F5 counts learners overdue with COUNTIFS; G5 is collected ÷ due till date.
2. Rows 13 onwards split collection by mode.
3. On Dashboard the six KPI cards show net fee, collected, collected % of due, overdue, learners overdue and October collection =SUMIFS(Receipts!$F$5:$F$800,Receipts!$B$5:$B$800,">="&DATE(2026,10,1),Receipts!$B$5:$B$800,"<="&Setup!$B$3).

Step 7. Charts

1. Chart 1 at A8: overdue by grade. Chart 2 at A25: collected by grade. Both read the Report, so they move as receipts are added.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Comparing paid with the full year's fee. In October nobody owes the December instalment yet. Compare with due till date, or everyone looks behind.
2. Typing the learner name on receipts. Use the ID. Two learners named Rani in different grades will mix up otherwise.
3. Giving concessions by editing the fee. Keep the grade fee as it is and type the concession % per learner, so the report shows the concession given.
4. Showing negative overdue. A learner who paid ahead would show −₹5,000 and reduce the total. MAX(0, …) keeps overdue at zero.
5. Deleting a wrong receipt. Add a reversing row with a minus amount and a note. Receipt numbers must never have gaps.

5. More real examples

Example 1: Pre-primary with monthly fees. Use 12 due dates in Setup instead of 4; the COUNTIF in B3 and the ÷4 become ÷12.

Example 2: Late fee. Add Late fee = overdue learners × a fixed amount decided by the management, as a separate column so the base fee stays clean.

Example 3: Sibling concession. Add a Guardian ID column and give 10% off the second learner with COUNTIF on that code.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate fees due till date in Excel?

Count the instalment dates that have passed with =COUNTIF(due_dates,"<="&as_of) and multiply the net fee by that share: =ROUND(D6*$B$3/4,0).

How do I find learners with overdue fees?

Overdue = MAX(0, due till date − paid). Filter or colour rows where it is above 0; the Report counts them with COUNTIFS.

Can I print a fee receipt from Excel?

Yes. Type the receipt number on the Fee receipt sheet; XLOOKUP fills the details and the print area is set to A1:D17.

How do I add transport only for some learners?

Add a Yes/No column and +IF(D5="Yes",Setup!$B$14,0) in the fee formula.

What about a new academic year?

Copy the file, change the due dates and as-of date in Setup, clear Receipts and update grades on Learners.

7. Quick quiz

Ten questions on this page. Sign in to save your score.