The scenario: You live with roommates and share some expenses. For example, one person is in charge of writing the rent check, another person pays utilities, another person pays for internet access. You share occasional grocery and household items. Let’s make this hypothetical scenario more complicated: Three of you share milk, but the other person doesn’t. How do you keep track of it all without writing 800 checks to each other at a time?
When I lived with four other roommates in college, I developed a roommate expense tracking spreadsheet. The spreadsheet was complicated enough so that it not only tracked items we all shared, but also allowed for tracking items that some people shared but not others. This allowed us to visibly see what we were spending money on, equitably divide expenses, and consolidate paybacks. Instead of writing a check for each item someone purchased, we settled our accounts monthly.
Now that we live with housemates again, I’ve resurrected the spreadsheet with the power of shared Google documents. In the old days, my roommates had to save, mark, and initial receipts, which I would enter into the spreadsheet and then print out each month. With a shared Google spreadsheet, each of us can go to Google docs and update the spreadsheet ourselves with receipts.
The rest of this post describes how to use the spreadsheet and includes links to view my open Google spreadsheet example and to download an Excel version.
View and Download
Hopefully my server doesn’t crash again with the Excel download. If you’re willing to mirror the download, please let me know!
- Roommate Expense Tracker – published Google spreadsheet
This spreadsheet includes a template and four example spreadsheets that go along with the diagrams above.
- Roommate Expense Tracker Excel spreadsheet download – shared-expenses.xls
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 License.
Step by step through the spreadsheet
Click on screenshots to open a larger version in a new window.
If you’re using a Google spreadsheet, you can share the spreadsheet by going to the Share tab and adding your roommates’ email addresses as collaborators. This allows them to access the spreadsheet and edit it with their expense line items.
If you’re going to be using an Excel spreadsheet on a single workstation, then your roommates will have to be diligent about giving you their receipts. For easier entry, they should write their name at the top of the receipt, circle the shared items, and note if any items are shared between specific individuals (but not everyone).
My sample spreadsheet is set up for four roommates. If you have more or less people, you’ll want to adjust the spreadsheet by inserting/deleting columns and copying the formulas. You’ll also want to change the column names to more accurately reflect the names of your roommates (unless you happen to be named Bob, Joe, John, and Larry)!
You should make a copy of the template worksheet each time you are settling expenses (monthly, biweekly, etc.). The worksheet is meant as for “one time use,” not as an ongoing thing. You can date it at the top.
1. Enter receipts.
For each item:
- Enter the item description (“milk” or “utilities”).
- Enter the amount paid. Don’t forget to include sales tax if applicable.
- Enter in /# how many people are splitting this expense. In most of the line items, the item is being shared by everyone, so the dividing number is 4. In this example, milk is only shared by three people, and printer cartridges are shared by two. (The screenshot shows the notes I’ve added to those specific rows.) Note: If something is divided unevenly, such as “rent,” then you can leave this blank.
- The each column automatically calculates how much each person would owe (amount/#).
- Enter the name of the person who paid for this item in paid by.
- Now, manually type the each amount under the appropriate name. For example, Bob, Joe, and Larry split milk (but not John). The milk amount ($4.99), divided by 3, is approximately $1.66. So, $1.66 is entered under Bob, Joe, and Larry. John remains blank. Note: For uneven amounts such as “rent,” you can just type the amount under each name. In this example, Bob has a bigger room and pays more rent than the others.
Since I haven’t figured out if it’s possible to sort selected rows in a Google spreadsheet, you may find it easier to group items together that were paid by the same person.
Also — don’t worry about the “approximate” amount. The rounding issues will be dealt with in the next step!
2. Make adjustments for rounding.
As noted above, there may be some small rounding problems, resulting in some very slight differences (on the order of pennies) between what was actually paid and what people owe collectively.
The bottom of the spreadsheet accounts for these differences. The blue total shows the actual amount that was paid. The people totals in italics show how much each person owes, and that sum allocated is in italics under the blue total. The difference takes the difference. In this case, the allocated amount is 3 cents less than what was actually paid.
Some roommates won’t care about the leftover pennies. However, my roommates happened to be very detail-oriented, so we had an additional adjustment row. Taking the small difference, divvy it up amongst the roommates in a sort-of-fair way. In this case, since Bob (oops, this should have been applied to Joe!) took the brunt of the expenses by paying the rent, he’s exempt from the additional 1-cent adjustment applied to everyone else (reference the yellow highlighted cells in the diagram).
3. Indicate how much each person contributed.
Now, sum up the amount each person paid in the Paid row (highlighted yellow in the diagram). You can do this by typing “=” to start a formula in the cell, then highlighting the specific amount cells relevant to each person. This is where grouping people’s expenses comes in handy, as you can just highlight an entire block instead of individual cells.
In this example, you can see that since
Bob (oops, that should be Joe) paid rent, he paid the most out of everyone.
After doing this, check your totals. The grey boxes on the right side of the spreadsheet guide you through making sure the numbers all work out. Breaking it down:
- $1230.52 Check totals – This row takes the sum of the actual amount paid — same formula as the blue total.
- $0.03 Does this equal difference? – This is the sum of the Adjust row. It should equal the green “difference” amount.
- $1230.52 Does this equal total? – This is the sum of the Owed row. It should equal the blue total amount.
- $0.00 Is this 0? – This sums up the Pay row. It should theoretically be zero, as the positive (people who owe money) numbers should equal the negative (people who are owed money) numbers.
4. Add helpful notes for payment.
The final step is to add helpful notes for payment. The negative pay amount means that this person paid the most, and other people owe them money. You can add a note in the bottom row for who the check should be made out to. If there is more than one person with a negative amount, then you can add a note for how to split the check between each person who is owed money.
And there you have it — a very fair way to track and consolidate shared household expenses! This spreadsheet certainly isn’t for everyone (such as those of the “who cares” variety), but for some, having a expense tracking worksheet can be a great way to avoid financial roommate conflict.