Spreadsheets

Settle-up formula: who owes whom between flatmates in a sheet

Work out who owes whom between flatmates with simple spreadsheet formulas: total paid, equal share, balance per person and the fewest transfers.

Editorial illustration for Settle-up formula: who owes whom between flatmates in a sheet

To settle up between flatmates, add up what each person paid, divide the grand total by the number of people to get an equal share, and subtract the share from what each paid. A positive balance means they are owed money; a negative one means they owe. Balances always add up to zero, which doubles as your check.

How do I set up the sheet?

Log shared expenses in columns A to D, rows 2 to 200: date, paid by (Asha, Ben or Chirag), item and amount. In F2:F4 list the three names. Then:

  • Total paid, G2: =SUMIFS($D$2:$D$200,$B$2:$B$200,F2), filled down to G4.
  • Grand total, G6: =SUM($D$2:$D$200).
  • Equal share, G7: =G6/COUNTA(F2:F4).
  • Balance, H2: =G2-$G$7, filled down to H4.
  • Check, H6: =ROUND(SUM(H2:H4),2). It must show 0.

Names must be spelled the same way in column B and in F, since SUMIFS matches exactly. A drop-down list in column B prevents typos: Data, Data validation, List from a range, F2:F4.

Can you show a worked example?

All figures are hypothetical. Over a month, Asha paid ₹12,000 for rent and groceries, Ben paid ₹5,400 for electricity and internet, and Chirag paid ₹2,100 for gas and cleaning supplies. Grand total: 12,000 plus 5,400 plus 2,100 is ₹19,500. The equal share is 19,500 divided by 3, which is ₹6,500.

PersonPaid (₹)Share (₹)Balance (₹)
Asha12,0006,500+5,500
Ben5,4006,500-1,100
Chirag2,1006,500-4,400
Total19,50019,5000

Who pays whom, with the fewest transfers?

With three people, at most two payments are needed. Take the person who owes the most and have them pay the person who is owed the most. Here Chirag owes 4,400 and Ben owes 1,100, and Asha is owed 5,500, so Chirag pays Asha ₹4,400 and Ben pays Asha ₹1,100. Asha receives 5,500 in total, which clears everyone. When there are more flatmates, repeat the same step: largest debtor pays largest creditor until every balance is zero.

What if shares are not equal?

Add a column of weights in I2:I4, for example 1 for each, or 2 for someone who has the larger room and pays double towards rent. The share becomes =$G$6*I2/SUM($I$2:$I$4) in each row, and the balance is paid minus that share. The check cell still has to be zero. Agree the weights before the month starts, not after the bills arrive.

A worked weighted example, again hypothetical: weights of 2 for Asha, 1 for Ben and 1 for Chirag add up to 4. On the same ₹19,500, Asha's share is 19,500 times 2 divided by 4, which is ₹9,750, and Ben and Chirag each owe ₹4,875. Balances become +2,250 for Asha (12,000 minus 9,750), +525 for Ben (5,400 minus 4,875) and -2,775 for Chirag (2,100 minus 4,875). They add to zero, so the check holds.

What about items not everyone shares?

Not every purchase is for the whole flat. Add a column E called Shared with Y or N. A personal purchase marked N stays out of the split. Then the grand total becomes =SUMIFS($D$2:$D$200,$E$2:$E$200,"Y"), and each person's paid figure becomes =SUMIFS($D$2:$D$200,$B$2:$B$200,F2,$E$2:$E$200,"Y"). The rest of the sheet stays as it is. Agree the rule in advance: groceries, rent and internet are shared, personal snacks and clothes are not.

Settling up in practice

  • Settle monthly, on a fixed date after rent.
  • Pay by UPI with the month in the payment note, so both sides have a record.
  • Keep receipts of the large bills.
  • Reset the sheet each month by archiving a copy.

SpendixAI is not a bill-splitting app, so keep the splitting in a sheet like this. Once settled, only your own share is your expense, and that is the number worth tracking in an expense tracker.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports