3

My apologies if this question is in the wrong section.

Couple of my roommates & I (total 5 people) share the groceries expenses. We record the purchases in an Excel sheet, and also have the ratio of consumption of individual items specified for each member.

For eg.

- Roommate A doesn't consume Milk and Jam, but consumes Bread equally and Butter most, so
    1. Milk = 0%
    2. Jam = 0%
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 50% (total expense for Butter as per consumption ratio)
- Roommate B doesn't consume Milk, but consumes Jam and Bread equally and Butter more, so
    1. Milk = 0%
    2. Jam = 25% (total expense for Jam equally divided between 4)
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 30% (total expense for Butter divided as per consumption ratio)
- Roommate C doesn't consume Butter but consumes Milk, Jam and Bread equally, so
    1. Milk = 50% (total expense for Milk equally divided between 2)
    2. Jam = 25% (total expense for Jam equally divided between 4)
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 0%
- Roommate D doesn't consume Milk, but consumes Jam and Bread equally and Butter less, so
    1. Milk = 0%
    2. Jam = 25% (total expense for Jam divided by 4)
    3. Bread = 20% (total expense for Bread divided by 5)
    4. Butter = 10% (total expense for Butter divide as per consumption ratio)
- Roommate E consumes Milk, Jam and Bread equally and Butter less, so
    1. Milk = 50% (total expense for Milk equally divided between 2)
    2. Jam = 25% (total expense for Jam divided by 4)
    3. Bread = 20% (total expense for Bread divided by 5)
    4. Butter = 10% (total expense for Butter divided as per consumption ratio)

Calculating individual member's share up to this stage is relatively easy, but if the duration is considered, especially when some member may not be present for the entire duration, the calculation is a

For eg.

Considering 100 days of calculation period, 

- Roommate A was present only for 20 days
- Roommate B was present only for 80 days
- Roommate C was present only for 90 days
- Roommate D was present for the entire 100 days
- Roommate # was present only for 70 days

So, I am able to calculate only for the first 2 scenarios listed below, but not for the third one, which would be the most accurate.

  1. Share based on consumption ratio only
  2. Share based on attendance only
  3. Share based on consumption ratio AND attendance

I have really tried hard to come up with the logic for the formula, and I'm sure this is a fairly common calculation method but I just can't wrap my head around it (I don't even know the correct mathematical term for this type of calculation).

I would be very happy if someone helped me with this.

Thanks in advance.

  • 8
    In practical terms, is it actually worth the trouble to get down to that level of granularity? In my experience people usually just use some sort of rule of thumb. It may be off by a few dollars a month, but it's not a big deal. In order to correctly track everything, you would need to keep track of the amounts of everything that each person actually consumed, in terms of individual purchases, not just rough percentages. (For instance, even if someone was only there for 50 out of 100 days, they may have eaten more jam in that time than another person who was there for all 100 days.) – BrenBarn Sep 8 '14 at 8:12
  • 2
    @BrenBarn, you are right in pointing out the granularity level, and of course the actual consumption of the person for a particular item indeed cannot be accurately tracked or measured. Hence, the relative ratios or percentages mutually agreed by the members. – Rohit Banerjee Sep 8 '14 at 9:42
  • 2
    @Rohit I admire the engineering mind you have to try and solve this problem, but let's face it: to create, track and maintain all of this information is a boring chore that is using up your free time. If you had a job that required you to do such a thing for 4 other people, you'd expect to be paid. Take the amount you think you are worth per hour, multiply it by the number of hours this has and will take you. Compare it to the amount you expect to save by doing this spreadsheet. If it's more (it is), then give up, split everything equally and go and do something you enjoy with your free time! – theyetiman Sep 8 '14 at 16:46
  • 1
    @RohitBanerjee If you have to come up with a differential equation to split your grocery bill with your roommates you're doing it wrong. I tried to to do exact same thing in college with my 3 roommates and it failed miserably. We had a fight about it, since this sort of detail always comes down to "Sherlock Holmes-level" forensics as to whether roommate #2 did or did not have a glass of milk at 6 am in the morning while the rest were sleeping or something like that. We ended up just each buying and marking our own food, and sharing when we wanted to. IMO this was best – unknownprotocol Sep 8 '14 at 17:55
  • 1
    @RohitBanerjee I thought a mathematical solution would also work, but I learned people aren't computers (logical) and sometimes are unreasonable, "forgetful" or downright petty. I've still got somewhere a handwritten note from one of my roommates, who refused to pay for his share of a gallon of milk because he claimed he only drank a "few ounces of it with his coffee." lol At that point I realized the plan was doomed. You can't predict or quantify when roommate X went on a milk binge one night and drank roommate Y's share of milk! :) – unknownprotocol Sep 15 '14 at 5:46
2

The solution to this problem is somewhat like grading on a curve. Use the consumption ratio multiplied by the attendance (which is also a ratio, out of 100 days) to calculate how much each person owes. This will leave you short. Then add together all of the shares in a category, determine the % increase required to get to the actual cost of that category, and increase all the shares by that %.

Example, for butter:

- Roommate A, 50% butter,  20 days.
- Roommate B, 30% butter,  80 days.
- Roommate C,  0% butter,  90 days.
- Roommate D, 10% butter, 100 days.
- Roommate E, 10% butter,  70 days.

Say you spend $100 on butter.
Step 1:

- Roommate A, 50% of  20% of $100 is $10
- Roommate B, 30% of  80% of $100 is $24
- Roommate C,  0% of  90% of $100 is  $0
- Roommate D, 10% of 100% of $100 is $10
- Roommate E, 10% of  70% of $100 is  $7

Now, that all adds up to $51.
To get to $100, you need to multiply $51 by approx 196%.
So, you multiply each of those shares by 1.96:

- Roommate A, $19.60
- Roommate B, $47.04
- Roommate C, $ 0.00
- Roommate D, $19.60
- Roommate E, $13.72
- Total:      $99.96 (four cents got lost due to rounding)
  • Very interesting workaround, I will try this in the sheet and let you guys know the results. – Rohit Banerjee Sep 9 '14 at 3:56
  • I'm marking this as the answer since it got the job done (albeit a workaround). @Sparr - Thanks for all the help – Rohit Banerjee Sep 15 '14 at 4:11
  • @RohitBanerjee if it helps, you can caluclate the 1.96 ahead of time. In the example, 100/(20*0.5+80*0.3+90*0+100*0.1+70*0.1) is 1.96, before you deal with the money. – Sparr Sep 15 '15 at 6:10
7

Bren's comment is right on the mark. The typical solution is to divide all bills by 5, and for special items, the person buying it just marks his name that it's not community food. Your attempt at a granularity level this detailed is admirable, but produces false results. What happens when I claim to be a zero percent milk drinker but when someone gives me cookies, I have a glass of milk? The effort to get true accuracy will cost far more in time spent than the results are worth.

  • 3
    I remember watching a group of MIT students calculate what each individual owed at a restaurant down to the penny -- then actually pay by throwing approximate amounts of money at the table until it added up correctly... Sometimes, it just isn't worth worrying about, especially when errors will tend to average out over the course of the term. – keshlam Sep 8 '14 at 12:33
  • 1
    Difference is that here the members involved were able to agree on a certain level of abstraction and the OP has already tackled the problem you're pointing out. The real problem is just getting the calculations worked out. – David Mulder Sep 8 '14 at 15:11
  • @JoeTaxpayer - You are right on the true accuracy part, and of course a glass of milk is must for a cookie. Having said that, some level of approximation will be there and is needed too. The percentages reflect the daily and/or regular consumption patterns but an occasional cookie and milk is always welcome and not accounted for, in the books. :) – Rohit Banerjee Sep 8 '14 at 17:30
5
  1. For a personal finance forum, this is too complicated for sustained use and you should find a simpler solution.

  2. For a mathematical exercise, you are missing information required to do the split fairly. You have to know who overlaps and when to know how to do the splits. For an extreme example, take your dates given:

Considering 100 days of calculation period,

  • Roommate A was present only for 20 days
  • Roommate B was present only for 80 days
  • Roommate C was present only for 90 days
  • Roommate D was present for the entire 100 days
  • Roommate E was present only for 70 days

If Roommate D was the only person present for the last 10 days, they should pay 100% of the grocery bill as they are the only one eating. From your initial data set, you can't know who should be splitting the tab for any given day.

To do this mathematically, you'd need:

  1. A schedule of who is present on which days.
  2. A ratio of how many "shares" of each item someone uses. More like this instead of using percentages:
    • Roommate A uses Milk: 1, Jam: 0, Bread: 1
    • Roommate B uses Milk: 0, Jam: 1, Bread: 2
    • Roommate C uses Milk: 2, Jam: 1, Bread: 1.5
  3. For a time period where Roommates A & B are present, you need to sum the "shares of bread" (3 in this case) and assign the cost based on the proportion of bread a roommate uses to the total amount of bread consumed. If Roommates A & B are the only ones present and $15 is spent on bread, Roommate A owes $5 (1/3*$15) and Roommate B owes $10 (2/3*$15).
  4. Repeat the calculation for every unique interval with different roommates.

But don't forget "In Theory, Theory works. In Practice, Practice works."

Good theory would say make a large, complicated spreadsheet as described above.

Good practice would be to split up the costs in a much, much simpler way.

  • We are actually a group of 20 people living in a big rental house with 2 huge kitchens, and I'm the one responsible for the accounts for my group. I knew that managing the groceries expenses according to everyone's whims and fancies would be very difficult (some of us like to have milk, others dont, etc.), but may be possible with some mathematical modelling and some level of approximation. – Rohit Banerjee Sep 8 '14 at 17:53
  • The Excel sheet template calculations is to test whether the approximations and mathematical formula works in a small scenario. So I collected the data for 3 months for 5 people in my group, just to check the trends. – Rohit Banerjee Sep 8 '14 at 17:53
  • But to directly answer to your points, 1. yes we do have specific schedule of people for the calculation period, 2. shares are of relatively equal measures, hence the percentages for approximation (and the central point is to keep the calculation focused on the money, not on the quantity) 3. & 4. If that were the case, I would not have needed a mathematical formula but instead would have added up the shares manually in the sheet. – Rohit Banerjee Sep 8 '14 at 17:54
  • 1
    The shares are meant to be approximations just like the percentages. You can do the exact same thing with "percentages" but you'll have a case where Roommate A uses 25% Bread and Roommate B uses 50% Bread and they are the only two in the house. Then the calculation for $15 bread would be Roommate A $15*25%/75% and B $15*25%/75%. But that can feel awkward as "all" the bread is only a "75%" amount. Therefore I opted to call it "shares" for simplicity. – Alex B Sep 8 '14 at 18:20
  • 1
    Formula for what a person's cost would be is (Cost during Interval)/(Total shares during Interval)*(person's shares during Interval). – Alex B Sep 8 '14 at 18:21
4

When I was in grad school (at an engineering school) my apartment-mates and I came up with this formula:

  1. We each bought 100% of the food we intended to consume.
  2. We each consumed 0% of the food that we did not buy ourselves.

Worked marvelously.

3

So your whole approach, and the attempt to scale this is flawed. You will alienate roomates, provoke arguments, and make everyone's life more difficult. There are too many variables and unforeseen possibilities. For instance:

  • "Why should I have to pay for Joe to go buy the expensive organic milk when I'm fine with the cheap stuff?"

  • "I planned on being here for 20 days, but was gone that long weekend, recalculate everything please."

  • "I already paid for this month, but now you're asking for more because James wanted to recalculate for a long weekend?"

The right way to do this is to set up loose, reasonable agreement among the participants and treat that as a contract, but with some flexibility/mercy on small dollar amount items.

For instance: There are 5 of us, so everyone provides food (and shops/cooks) one night a week. We're solo on Friday and Saturday (people eat out more then anyway), and everyone puts in $10/week (or whatever) for breakfast cereal, snacks, etc. If you can't be here on your night, work out to trade with someone. If you miss out on a meal... oh well.

As long as people feel like they have a say in the discussion generating this and it's not dictated to them, then most of the time this is far superior. If people need this level of detail, then perhaps those people should live alone or move in with Sheldon Cooper from "The Big Bang Theory".

  • Oh, and while you're at it... "You're in my spot!" – Jared Sep 8 '14 at 18:26
1

I asked how often grocery purchases are made in a comment, but I'm going to assume weekly for simplicity.

If a roommate is present during the week following a grocery purchase, then they owe a share according to their preferences as you outlined them above. You will have to track the grocery cost by category for that week and calculate the balance owed by the person for that week. If there is a partial week where most expect to leave for a holiday or otherwise, then fewer groceries should be purchased for that week, and the cost of shares will decrease accordingly.

One need only indicate preferences once, and weekly attendance thereafter.

The only issue remaining is to determine how to record shares. If a normal person consumes 3 shares of milk, and .5 shares of butter, and so on, you simply add up all of the milk shares for the week and divide the milk bill by those shares. Same with the butter.

The downside of this method is that you have to predict consumption in advance, so you may instead calculate by consumption after the fact with a deposit paid by all to create the initial grocery supply which will be refunded when that person leaves the grocery purchase co-op, and shares are calculated by who participated in the week prior to the grocery purchase. This also allows for a mid-week refresh if any commodity incurs higher than expected consumption, with the mid-week bill being added to the end of week refresh trip.

  • 1
    You may also establish a minimum weekly contribution if there are people who are trying to game the system by selecting unrealistic preferences. – Nathan L Sep 8 '14 at 20:26
  • Your approach would have been perfect, but unfortunately our purchases are quite random (if couple of items are over, it's off to the stores) and not weekly. Additionally, many items such as rice bag, wheat flour, etc. are bought in bigger quantities (to get better bargains) and may last more than a month. Hence, the weekly solution may not work out in my scenario. – Rohit Banerjee Sep 9 '14 at 4:06
  • 1
    With larger items, I would wait until they are used and divide the cost between the weeks that the item was present and in use. If you use purchase dates, you can still probably find some level of unit granularity that works to divide them. – Nathan L Sep 9 '14 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.