Hostel and PG Management System in Excel: Rooms, Residents, Rent, Late Fee, Occupancy and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: hostel-pg-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Residents, Payments, Rent ledger, Occupancy, Receipt, 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

Raja PG Stay, Pune, rent month October 2026, as of 17 October (made-up data). Rent is due by the 5th; late fee ₹50 a day, capped at ₹500.

Rent for the month is ₹60,000; ₹49,000 is in and ₹12,550 is still due, late fees included. For flatmates splitting a home, see the flatmate expense split; for plain rent from tenants, the rent collection tracker.

2. Build it sheet by sheet

Step 1. Setup: rent month, late fee and rooms

1. Type the as-of date in B3, the rent month in B4 (01-10-2026), the due day in B5 (5), the late fee per day in B6 (₹50) and the cap in B7 (₹500).
2. B8 works out the due date: =B4+B5-1, which gives 5 October.
3. Room types in A11:C13 (Single ₹9,000, Double ₹6,500, Triple ₹5,000 per bed, with deposits) and rooms in E11:G16 with type and beds.

Step 2. Residents: rent comes from the room

1. Headers: Resident ID, Name, Room, Room type, Rent, Deposit due, Deposit paid, Deposit balance, Joined, Status.
2. D5 finds the type: =XLOOKUP(C5,Setup!$E$11:$E$16,Setup!$F$11:$F$16,"Check room") and E5 the rent from the type (XLOOKUP).
3. H5 deposit balance =F5-G5. Shahrukh Mulla still owes ₹5,000 of his deposit.
4. Pick the room and status from drop-downs. When someone moves out, set Status to Left and keep the row.

Step 3. Payments and the Rent ledger

1. Payments: Receipt no, Date, Resident ID, Name, Rent month, Amount, Mode. A5 ="RC-"&TEXT(ROW()-4,"000").
2. Rent ledger D5 paid this month: =SUMIFS(Payments!$F$5:$F$2000,Payments!$C$5:$C$2000,A5,Payments!$E$5:$E$2000,Setup!$B$4) (SUMIFS).
3. E5 first paid on: =IF(COUNTIFS(…)=0,"",MINIFS(Payments!$B$5:$B$2000,Payments!$C$5:$C$2000,A5,Payments!$E$5:$E$2000,Setup!$B$4)) (MINIFS).
4. F5 days late counts to the first payment, or to today if nothing is paid; G5 late fee =MIN(Setup!$B$7,F5*Setup!$B$6) and H5 balance =C5+G5-D5.
5. I5 status: =IF(H5<=0,"Paid",IF(D5>0,"Part paid","Unpaid")), coloured with conditional formatting.

Step 4. Occupancy and Receipt

1. Occupancy D5: =COUNTIFS(Residents!$C$5:$C$200,A5,Residents!$J$5:$J$200,"Active"), E5 vacant =C5-D5, F5 rent lost on vacant beds.
2. Receipt: type a receipt no in B4; B6 onwards look up date, resident, month, amount and mode. RC-012 shows Salman Shaikh's ₹6,500 with ₹350 late fee still due. Set the print area.

Step 5. Dashboard

1. KPI cards: active residents, beds occupied =Occupancy!D11/Occupancy!C11, rent this month, collected, balance with late fee and unpaid residents.
2. Charts: occupied and vacant beds by room, and balance by resident (dashboard charts).

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. Typing the month as text. Rent month must be a real date (the 1st). "Oct" typed as text never matches Setup B4, so SUMIFS returns 0.
2. Deleting residents who leave. Set Status to Left. The history stays and the occupancy count drops on its own.
3. Late fee without a cap. A forgotten resident can run up a huge fee. MIN with the cap keeps it fair and agreed.
4. Counting days late from the last payment. Use the FIRST payment of the month. MINIFS finds it.
5. Not changing the rent month. On the 1st, change Setup B4. Otherwise the ledger keeps showing last month.

5. More real examples

Example 1: Working women's hostel. Add a Meals column with a monthly mess charge looked up from Setup and add it to rent.

Example 2: Service apartment. Rent per room per night instead of per bed per month; days stayed × rate.

Example 3: Staff quarters. Deduct rent from salary: link the ledger to the payroll calculator by employee ID.

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 a late fee with a maximum in Excel?

=MIN(cap, days_late*fee_per_day), as in Rent ledger G5.

How do I find the first payment date for a resident?

MINIFS on the date column with the resident ID and rent month as conditions.

How do I count occupied beds?

COUNTIFS on the room and Status = "Active", then vacant = beds − occupied.

Can a resident pay in two parts?

Yes. Add two payment rows for the same month; the ledger adds them and shows Part paid until the balance is 0.

Is GST charged on PG rent?

It depends on the facts and current notifications. Check with your accountant before adding a tax column.

7. Quick quiz

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