A tuition fee sheet lists each student with the monthly fee and a grid of months, where you type the amount received for each. Formulas then show pending money per student, total collected versus due, and the oldest month that is still unpaid, so follow-up is a short, polite message rather than a guess.
How do I set up the grid?
Use row 1 for headings. Put the first of each month in D1:O1, April 2026 to March 2027, formatted to show only the month name. Fill students from row 2.
| Column | Heading | Notes |
|---|---|---|
| A | Student | Name and class |
| B | Monthly fee (₹) | Agreed amount |
| C | Joined from | First month the student attends |
| D to O | April to March | Amount actually received for that month |
| P | Pending (₹) | Formula below |
| Q | Oldest unpaid month | Formula below |
Type the amount received in the month it was meant for, not the month the money arrived. If a parent pays in two instalments for one month, add the two amounts in that cell, as =1000+500, so the cell keeps a trail of what was paid.
Which formulas give pending and oldest unpaid month?
- Pending in P2: =COUNTIFS($D$1:$O$1, "<="&TODAY(), $D$1:$O$1, ">="&$C2)*$B2-SUM($D2:$O2)
- Oldest unpaid month in Q2: =IFERROR(INDEX(FILTER($D$1:$O$1, ($D2:$O2<$B2)*($D$1:$O$1<=TODAY())*($D$1:$O$1>=$C2)), 1), "All clear")
- Total collected: =SUM($D$2:$O$30)
- Total due so far: =SUM($D$2:$O$30)+SUM($P$2:$P$30)
- Total pending: =SUM($P$2:$P$30)
COUNTIFS counts the month headings that are on or before today and on or after the month the student joined, so the sheet only expects fees for months that have started. Type the joining month as a date, such as 1-Apr-2026. FILTER needs Excel 365 or 2021 or Google Sheets. In older Excel, add a helper column per month that shows 1 when the cell is below the fee, and find the first 1 with MATCH.
What does an example look like?
Say there are five students with fees of ₹1,500, ₹1,500, ₹2,000, ₹1,200 and ₹2,500, so the monthly total is ₹8,700. It is the end of July, so April to July, four months, have started. Total due so far is 4 x 8,700 = ₹34,800. If you have collected ₹30,100, pending is ₹4,700.
Look at one student with a fee of ₹2,000: April ₹2,000, May ₹1,000, June nothing, July ₹2,000. That is ₹5,000 received against ₹8,000 due, so pending is ₹3,000. The oldest unpaid month is May, the first month where the cell is below the fee. The sheet names the month; you decide whether to mention June as well.
| Student fee | April | May | June | July | Pending |
|---|---|---|---|---|---|
| ₹2,000 | 2,000 | 1,000 | 0 | 2,000 | ₹3,000 |
How do I handle absences, a break or a fee change?
- For a month when you agreed to waive the fee, type the fee itself and add a cell comment saying waived, so the sheet does not show a false pending amount.
For a change of fee from a certain month, add a second row for the same student with the new fee and the new Joined from month, and in the old row type the old fee with a comment saying moved to new row in each later month, as you do for a waiver. That keeps the old totals intact.
How should I follow up without awkwardness?
- Sort the sheet by the pending column, largest first, once a week.
- Message at the start of the month with the amount and the month it relates to.
- Note the date you messaged in a comment on the cell.
- Record the UPI reference or receipt number when a payment arrives.
- Keep a friendly tone and agree on a simple pay-by date at admission.
Where does an app fit?
Fees paid by UPI land in your bank statement, which a sheet cannot read. SpendixAI is built for tracking your spending rather than fee income, so it will not do this register for you, but it can import your statement for your own expenses, and the fee register stays here in Excel.
Month-end checklist
- Every payment entered under the right month
- Pending column reviewed
- Oldest unpaid month noted for follow-up
- File saved with the month in its name
Useful ideas start with a clearer picture of your own spending.
Explore your reports


