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.

Setup

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.

About these ads

26 Comments

  1. Jen
    Posted October 2, 2007 at 5:48 pm | Permalink

    I found this through a google search. Your roommate expense spreadsheet is a lifesaver!

  2. Paul
    Posted October 3, 2007 at 8:11 am | Permalink

    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.

  3. Posted October 3, 2007 at 8:17 am | Permalink

    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. ;)

  4. Jen
    Posted October 4, 2007 at 6:49 pm | Permalink

    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!

  5. Posted October 5, 2007 at 7:34 am | Permalink

    Jen (#2) – Glad you like it!

  6. Deivas
    Posted January 23, 2008 at 7:06 pm | Permalink

    Man, you’re promoting Peace here!
    Explendid!

  7. Erika
    Posted February 12, 2008 at 1:07 am | Permalink

    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!

  8. Posted February 13, 2008 at 7:35 am | Permalink

    Erika – Glad you found this helpful!

  9. Posted March 20, 2008 at 9:11 pm | Permalink

    I developed one for myself and made it public.. chk it out if you like

    http://www.screeperzone.com/expensesharer/xsharer.html

    Thanks!

  10. Posted March 21, 2008 at 11:32 am | Permalink

    Mahin — That’s wonderful! Thanks for sharing the link.

  11. helen davis
    Posted August 15, 2008 at 3:49 am | Permalink

    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

  12. Posted August 19, 2008 at 4:15 am | Permalink

    Hi Helen – are the hashmarks from the column being too narrow?

  13. Posted October 20, 2009 at 5:58 am | Permalink

    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

    Cheers

  14. Posted May 7, 2010 at 9:07 am | Permalink

    GREAT and useful stuff, man…
    Its a piece of cake

  15. Posted April 15, 2011 at 3:11 pm | Permalink

    I just wanted to let you know, that you can record your expenses with your phone, and then export the expense list to your Roommate Spreadsheet using ProOnGo Expense. It’s the best for business expenses though, but I’m sure it’s very helpful for personal tracking, too.

  16. Andrew
    Posted June 12, 2011 at 1:06 pm | Permalink

    I use an app on the Android Market, Roommate Expense Tracker… Simple and mobile… Hope it works for you

  17. Posted June 16, 2011 at 5:18 am | Permalink

    I use to use the excels earlier for the shared expenses. :)

  18. Posted July 27, 2011 at 11:39 am | Permalink

    This has just eaten my face off with it’s awesomeness. AMAZEBALLS. A million thanks!

  19. Mihir
    Posted September 4, 2011 at 10:37 pm | Permalink

    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.

  20. Posted October 6, 2011 at 11:19 am | Permalink

    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.

    Thanks!

  21. Em
    Posted October 12, 2011 at 7:41 pm | Permalink

    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.

  22. Ganesh K
    Posted November 29, 2011 at 3:52 am | Permalink

    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
    GK

  23. Caspar
    Posted January 12, 2013 at 9:51 am | Permalink

    I had this problem too. A new and super simple website I found for one off events is kittysplit.com

  24. Bill
    Posted September 9, 2013 at 7:59 am | Permalink

    Check out SimpleBills.com
    A helpful service that splits utilities between roommates each month.

  25. mighty
    Posted January 3, 2014 at 10:22 am | Permalink

    check out this simple app to easily record expenses and finally divide depending on each members owe to others. “divide by n – lite” on google play. here is the link

    https://play.google.com/store/apps/details?id=com.mighty.dividebyndemo


One Trackback/Pingback

  1. [...] expenses with? Then it suddenly hit me. Why should I go through all the trouble of finding a new roommate to share expenses when I can find my own Seeking Arrangment who would be willing enough to pay for everything? Why [...]

Post a Comment

Required fields are marked *
*
*

Follow

Get every new post delivered to your Inbox.

Join 45 other followers

%d bloggers like this: