Spreadsheet for Tracking Roommate Expenses

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.

Shared expenses diagram 1

For each item:

  1. Enter the item description (“milk” or “utilities”).
  2. Enter the amount paid. Don’t forget to include sales tax if applicable.
  3. 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.
  4. The each column automatically calculates how much each person would owe (amount/#).
  5. Enter the name of the person who paid for this item in paid by.
  6. 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.

Shared expenses diagram 2

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.

Shared expenses diagram 3

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.

Shared expenses diagram 4

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.

That’s all!

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.

26 thoughts on “Spreadsheet for Tracking Roommate Expenses

  1. Many thanks for the spreadsheet! It’s a lifesaver for my non-math mind. My paper and pen calculations, but this spreadsheet will save me a lot of time.

    An FYI: your instructions say that Bob paid the rent. The photo shows that Joe paid the rent.

    Thanks again for a great tool.

  2. Jen – I’m glad this helps!

    Paul – Thanks for pointing out the mistake! Poor Joe; guess he got the short end of the stick this time, but I’m sure they can even it out next time.😉

  3. Thank you. You have saved this dunce a lot of work. This kind of thinking makes my brain fry. You’ve done all of that thinking for me!

  4. YAY! This is so awesome. I was sitting here late at night scratching my head trying to wrap my mind around how to go about this. Luckily I thought to run a quick Google search, only to find that you have already invented the wheel. Thanks a bunch!

  5. Hello,

    How come when I’m trying to put in £920.00 for 2 people it’s coming up with hash symbols even though the formular looks the same as the other cells. some reason its not liking the numbers 930

    any help as finding this REALLY useful but i’m stuck now as I can’t carry on until this is fixed.

    Thanks again

  6. Hi Corrie,

    Stumbled upon your site while googling how to manage expenses with room mates. We have recently launched a website http://www.beezang.com to help folks keep track of such expenses with room mates. Will be great if you can take a look and let us know your thoughts. That will help us get better


  7. This of course lacks some of the requirements like the value should be automatically divided and assigned to all individuals, then we can delete if its not shared among them all,

    Also there should be a provision to total values based on whether its shared between all persons or only a few.

  8. I really like this, but I’m having trouble in ‘assigning’ how much people owe each person. In our house, each person pays a couple of bills, so everybody ends up having to write checks to one or more people.

    Are you able to adjust this and add in a line which states ‘who owes whom what’?
    Example: A.J owes $232 to Jan & Dave. Dave owes $119 to A.J and Shiv.


  9. Very helpful!! Thank you. I am attempting to manage my expenses and this is perfect.
    Also, since I have three other roommates, this will be super helpful as well.

  10. Hi Corrie Haffl,

    I am realy thankfull to you..!!
    You made my life very simpler..!! since I only maintaining the expense record in my shared room and at end of month it was bit headeache..!! You Know..!!

    The sheet is simply amazing.. I was searching this from long time and finaly I ended up here..

    Many Many Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s