To split rent between flatmates in rooms of different sizes, give each person a room weight, multiply it by the days they stayed, and divide the rent in proportion to those weighted days. Electricity and wifi are split by days stayed, or by sub-meter units where you have them. The sheet below does it with five formulas and a check cell.
What columns does the sheet need?
In Google Sheets, make row 1 the headers and rows 2 to 5 the four flatmates:
- A Person, B Room weight, C Days stayed, D Weighted days, E Rent share, F Electricity, G Internet, H Total payable.
- Inputs in J1:K3: total rent in K1, electricity bill in K2, internet bill in K3.
Room weight is a number you all agree: 1 for a standard room, 1.5 for a larger one with a balcony, 1.2 for a bit more space. It is not a market value; it just expresses the difference. Everyone should accept the weights before anyone signs up to them.
What are the formulas?
| Column | Formula in row 2 | What it does |
|---|---|---|
| D Weighted days | =B2*C2 | Room weight times days stayed |
| E Rent share | =ROUND($K$1*D2/SUM($D$2:$D$5),2) | Rent divided in proportion to weighted days |
| F Electricity | =ROUND($K$2*C2/SUM($C$2:$C$5),2) | Bill divided by days stayed |
| G Internet | =ROUND($K$3*C2/SUM($C$2:$C$5),2) | Plan cost divided by days stayed |
| H Total payable | =E2+F2+G2 | What this person transfers |
Copy row 2 down to row 5. Add a check cell, say K5: =ROUND(SUM(H2:H5)-SUM(K1:K3),2). It must show 0. If a rupee goes missing, the ROUND settings are the usual reason, so adjust the last person's amount.
What does a worked example with four flatmates look like?
Say the rent is ₹40,000, the electricity bill is ₹2,100 and the wifi plan costs ₹1,050 for a 30-day month. Three flatmates stay all month; the fourth, Divya, joins on the 15th and stays 15 days (say the 15th to the 29th). Aman has the larger room.
| Person | Weight | Days | Weighted days | Rent (₹) | Electricity (₹) | Internet (₹) | Total (₹) |
|---|---|---|---|---|---|---|---|
| Aman | 1.5 | 30 | 45 | 15,000 | 600 | 300 | 15,900 |
| Bhavna | 1 | 30 | 30 | 10,000 | 600 | 300 | 10,900 |
| Chirag | 1 | 30 | 30 | 10,000 | 600 | 300 | 10,900 |
| Divya | 1 | 15 | 15 | 5,000 | 300 | 150 | 5,450 |
| Total | - | 105 | 120 | 40,000 | 2,100 | 1,050 | 43,150 |
The weighted days add up to 120, so Aman's rent is 40,000 times 45 over 120, which is ₹15,000. Electricity: 2,100 over 105 days is ₹20 a day, so 30 days is ₹600 and 15 days is ₹300. The totals reconcile to 40,000 plus 2,100 plus 1,050, which is ₹43,150.
How should we treat a part-month joiner or leaver?
The formula spreads the whole rent across the weighted days that were actually occupied, so the people who stay all month cover the vacant stretch of the room through a slightly higher rate. Is that fair? That depends on your landlord and your agreement. An alternative is to price each weighted day at full occupancy (rent over 135 weighted days here) and let the flat share the vacant days equally. Decide before the month, and write the method in a note cell.
What about electricity by sub-meter?
If rooms have their own sub-meter, put units used in a new column, say I, and replace the electricity formula with =ROUND($K$2*I2/SUM($I$2:$I$5),2). If the sub-meter units do not add up to the main meter reading, the difference is common-area use; share it by days. Do not assume a per-unit rate; use the actual bill.
Month-end checklist
- Update days stayed for each person.
- Enter the actual rent, electricity bill and internet cost.
- Confirm the check cell reads 0.
- Share a view-only copy, and keep the sheet for deposit discussions.
SpendixAI is not a bill-splitting app, so keep this sheet for the split. Your own share, once paid, can be imported as part of your bank statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


