A simple but powerful Google Sheet template to manage shared expenses among roommates, friends, or family. It automatically calculates who owes whom and provides a simple settlement plan with the minimum number of transactions.
-
Dynamic Setup: Add or remove roommates, and the entire sheet updates automatically.
-
Fair Splitting: Expenses are split based on consumption coefficients for each person.
-
Debt Matrix: See a detailed breakdown of who owes whom.
-
Automated Settlement: A one-click script calculates the simplest way to settle all debts.
Getting started is easy! You don't need to build the sheet from scratch.
1. Create Your Own Copy
Click the link below to create your own copy of the Google Sheet template in your Google Drive:
➡️ Get the Google Sheet Template Here
2. Set Up Your Sheet
-
Open the
roommatestab and enter the names of everyone in your group. -
The headers in the
currenttab and the axes in thedebtsandsettlementtabs will update automatically.
3. Start Tracking Expenses
-
Go to the
currenttab. -
For each purchase, fill out the
reason,amount, whopaid by, and the consumption coefficients for each person. (e.g., a1means one share,2means two shares, and a blank cell or0means they didn't use it).
4. Settle the Debts
-
The
debtsandsettlementtabs will automatically show you the final balances. -
To get the simplified payment plan, click on the Expense Manager > Calculate Final Transactions menu item.
-
A sheet named
final-transactionwill be created with the simplest list of payments to clear all debts. You may need to grant script permissions the first time you run it.
This project uses Google Apps Script to calculate the final settlement plan. The code is included in this repository for reference. If you ever need to re-install it:
-
Open the sheet and go to Extensions > Apps Script.
-
Copy the code from the
settlement_script.gsfile in this repository. -
Paste the code into the script editor, save it, and refresh your sheet.