A PG owner has many small payments to track: one rent per bed, a deposit per guest, and a bundle of monthly costs. A Beds tab, a Payments tab and a monthly grid built with SUMIFS show who has paid, who has not, how full the PG is and what is left after electricity, food and repairs. Everything below works in Excel and Google Sheets.
What does the Beds tab hold?
| Column | Heading | Notes |
|---|---|---|
| A | Bed ID | Such as R2-B |
| B | Occupant | First name or a code |
| C | Monthly rent | What is agreed for that bed |
| D | Deposit held | Amount taken at joining |
| E | Joined on | Real date |
| F | Status | Occupied or Vacant |
Rent is per bed, not per person, so a vacant bed keeps its row with Vacant in the status column.
How do you record payments?
A Payments tab with Date received, Bed ID, Month for and Amount is all you need. Month for is the first day of the month the rent covers, such as 1-Oct-2026, not the date it arrived. That way a late payment still lands against the right month. A UPI reference can go in a fifth column.
How do you build the month grid?
On a Grid tab, list bed IDs down column A. Put 1 April in C1 and extend across with =EDATE(C1,1) until March. In C2 write:
=SUMIFS(Payments!$D$2:$D$2000, Payments!$B$2:$B$2000, $A2, Payments!$C$2:$C$2000, C$1)
Fill it right and down. To see who still owes for a month, use rent due minus received. Rent due is =IF(Beds!$F2="Occupied", Beds!$C2, 0) in a matching grid, so vacant beds do not show as unpaid.
What does a month look like in numbers?
Say there are 20 beds, 17 occupied, at a hypothetical ₹7,000 each. Rent due is 17 x 7,000 = ₹1,19,000. Suppose ₹1,05,000 was received, so ₹14,000 is pending, which is two beds.
| Line | Amount |
|---|---|
| Rent received | ₹1,05,000 |
| Electricity | ₹14,000 |
| Food: cook and groceries | ₹38,000 |
| Repairs and supplies | ₹6,000 |
| Total costs | ₹58,000 |
| Cash left in the month | ₹47,000 |
Occupancy is 17 divided by 20 = 85%. In the sheet, that is =COUNTIF(Beds!$F$2:$F$21, "Occupied")/COUNTA(Beds!$A$2:$A$21). If you pay rent for the building yourself, add that line too, and it will show whether the cash left covers it.
How should you treat deposits?
A deposit is money you hold and may have to return, so keep it in its own column and do not add it to income. Total deposits held, =SUM(Beds!D2:D21), tells you how much cash in the account is not yours to spend. When a guest leaves, record any deduction and the amount returned in a note, with the date.
What costs belong in the cash-flow line?
- Electricity and water, including motor or inverter servicing.
- Food: cook, gas, groceries, and any helper.
- Repairs, mattresses, pest control and cleaning supplies.
- Internet and wifi, plus any broker or listing fees.
How do you plan for vacancies?
Add a Notice column with the date each guest said they would leave, and a column for the date the bed was last vacant. The gap between them is your turnaround time. If beds stay empty for weeks every June and July, you can see it in the grid and plan for it: a lower cost month, a small repair job or an early advertisement. A vacancy you saw coming costs less than one that surprised you.
Record the turnaround in days beside each bed, then average it each year to decide when to renew mattresses or repaint.
Enter these in an Expenses tab and sum them by month. If your costs come from several bank and card accounts, SpendixAI can import the statements and group them into categories, which gives you the totals to copy into the monthly view.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

