# Splitting Costs With Friends Just Got Easier

How many times have you gone on a group trip with friends, and figure out the costs at the end of the trip? Maybe one person pays for the hotel, one person pays for the rental car, and someone else pays for the dinners. Or maybe one person pays for everything, making splitting costs much more easier at the end of the trip. No matter how organized you are with receipts and how many e-mails you send back and forth with friends, there’s always going to be some confusion with who owes who. After every trip, we would find ourselves creating some ad hoc Excel spreadsheet to keep track of expenses and haphazardly figure out who owes who. We decided to create a tool that can be used for all scenarios no matter the number of people and costs involved. Here’s the link to the tool we developed in Google Sheets to help solve this widespread social problem of splitting costs with friends.

## Check Out The Tool In Google Sheets

We talked about a different version of this tool in a story a few months ago we wrote about Appizy. While Appizy does offer pretty good functionality, we opted for using Google Sheets instead of Appizy or a regular Excel file since it’s just easier to send to friends if you decide to actually use this tool. One of the important features of Google Sheets is that many people collaboratively edit the Sheet and changes are saved on the fly. If we were to e-mail Excel files back and forth, the process would be a nightmare. We went on a trip a few months ago and these are actual costs from the trip, but with names and cost items slightly changed. Open the Google Sheet, and click on File >>> Make a copy. This way you can actually edit the numbers and names to suit your needs. In case you need the visual, here you go:

To get your own copy:

- Open the tool in Google Sheets
- Click on File
- Click on Make a copy

## Get Your Friends To Enter What They Paid

The first step is to get your friends to enter in what they paid for various things on the trip. This is done all in columns A and B, and then marking off their name in column K. Pretty simply right? Again, the great thing about using Google Sheets for this problem is that multiple people can add costs to the Sheet. The only issue is the one straggler who you constantly have to e-mail to get him or her to enter in their costs. Kind of like getting employees to fill out their expense reports right?

## Figure Out Who Was Included In The Cost

This is where things can get a little dicey. On the Sheet, there are eight people who went on the trip. Some people paid for a lot of things, other people didn’t pay for anything. The important part of this step is to get your friends to enter a “1” in the cell that corresponds to someone that was included in the cost. For instance, on Row 7, the people that “participated” in the care rental were Jim, Leuis, and Allen, hence why cells C7, F7, and I7 have a “1” in them. The other cells in that row have “0” indicating that the person in Row 1 did not partake in that cost. The reason why this can be awkward and somewhat interesting if you are the one coordinating the filling out of this Sheet is that you’ll start seeing people putting costs they normally wouldn’t put on the Sheet. On Row 23, you’ll see that Leuis bought a Philly cheese steak for the beach, and apparently some people ate a lot of the sandwich, but others didn’t. It looks like Jim ate half the sandwich, leaving Leuis and Allen only a quarter of the sandwich for each person. While this Sheet is meant to capture all costs, sometimes people might get a little too detailed with entering in what their expenses were. We see a lot of correlations here with managing expense reports for your team. By giving your employees, friends, or users more control over the tool, you will get even more accurate reporting on what the actual numbers were. Ok, enough accounting talk, how does the tool actually work?

## Find Per Person Cost For Expenses

In Column L, you’ll see a simple equation that figure out the per person cost based on the number of people that participated in that expense. On Row 2, the formula is =iferror(B2/sum(C2:J2),””) This formula simply takes the expense paid and divides it by the SUM() of 1s in that given row. Going back to Row 7, the Car Rental cost is only split between three people (Jim, Leuis, and Allen), so we take $42.50 and divide it by three resulting in a per person cost of $14.17. Now we have two pieces of crucial data for this problem:

- The total dollar amount a given friend has paid for an expense
- The per person cost for that given expense
*only*for the friends that participated in the cost

With these two data points, we can figure out *how much each person has paid* and* how much each person owes*.

## Using SUMPRODUCT() To Find How Much Your Friends Owe

In Row 26, it’s pretty easy to find the total amount a friend has paid. In C26, the formula is =sumif($K2:$K25,C$1,$B2:$B25) This SUMIF() formula sums the costs in Column B depending on the name entered in Column K; which is great since you had all your friends fill out their individual costs so now you know how much each person has paid. To figure out how much each person owes, we use the SUMPRODUCT() formula to add up all the per person costs that a given friend is responsible for. In the image below, you can see how the SUMPRODUCT() formula simply multiplies the “1” or “0” in a given column with the per person cost in Column L. The formula then will add the individual products of these two numbers together, treating each product as a value in an array.

To get out of Excel speak for a minute, all we are doing is adding up the per person cost for each expense one by one. Alan owes money for the “Other Car Rental” in Row 17 but doesn’t owe money for “Drinks in Florida” in Row 18, thus he should only be paying his per person cost for “Other Car Rental.” Now you know how much each friend paid and how much each friend owes. If you subtract the owed from the paid number, you’ll get the balance for that friend. In the Sheet, we color coded the ones that owe more than they paid in red with parentheses around the number, and those who paid more than they owe in black. It’s kind of like reading a financial statement, where being “in the black” is good since you know profits are good and money should be coming your way.

## Don’t Forget To Do Final Checks

All good financial modelers know you need to create simply formulas to “check” your work. We did this in Cells M26:M28. We just want to make sure that the sum of all the different costs people paid equals the sum of the total costs in general (listed in B26). Same thing applies to the amount people owe. Since all the checks equal 0, we know that there are no errors in our formulas and that everything balances out. If you are adding more entries to the tool and notice that the checks are greater than or less than 0, that means something may be wrong with the formulas either in the “checks” or the actual formulas themselves for calculating how much people paid vs. owes.

## Someone Needs To Act As The Federal Reserve

Now that we’ve figured out how much people are owed or need to pay, how do we actually settle all the balances? With eight people, it’s too difficult to coordinate Jim to pay Alan and then Alan to pay a little to Steve and Leuis. This problem can get just as complicated as splitting the costs, so the best solution is for someone to act as the “central bank.” This person simply collects all the amounts people owe (in this case, Alan, George, Leuis, William, and Steve), and then doles out the amounts that are owed to people (Jim, Ben, and Allen). We actually used Venmo to quickly get this done.

## Conclusion

Splitting costs with friends now doesn’t have to be tedious and tiresome anymore, but having an actual spreadsheet to track expenses does make your trip seem like a work trip since you are literally creating one giant expense report. This make us wonder how people back in the day (e.g. pre-Google Sheets, Excel, Venmo, PayPal) would pay each other back for group trips. Would they just do a better job of keeping receipts? Would someone write it all down on a notepad? We think it was a combination of both, which probably made the whole splitting costs task even more difficult after the trip. Hopefully this little tool can help you out on your next trip so there won’t be any confusion about who owes who!

## Subscribe to Dear Analyst – A podcast about Excel and data analysis

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Incredible. thank you.

I use https://Kittysplit.com., which is much simpler

Thanks for the link Caspar, this is definitely a useful tool as well!

Just used http://www.kittysplit.com/ for our group travel expenses it was amazing

This is a nice spreadsheet. Can you take it further? Suppose I have a vacation rental for 5 nights. The cost is 500.00 total, or 100.00 per night. I have a group of 5 friends that may or may not go. If all 5 friends go for all five nights, 5 people would pay a shared daily rate for 5 nights (25 value cells = $500). If only two people go for all 5 nights the shared cost is 250.00 each (10 value cells out of 25 = $500). If two people go for all five nights, and two people go for only three nights, and one doesn’t go at all, I need to evenly distribute the shared cost per person per night (16 value cells out of 25 = $500). Can you come up with a 5×5 matrix where a total cost is distributed over the 25 cells, and if one cell is zero, that value is distributed equally to the other 24 cells? A few pennies more in one or two blocks is no problem.

Hey Tony, I think you just answered your own question! A 5X5 table where you put “1s” in each cell to indicate whether or not the friend is staying that night would trigger an equation to count that person into the final cost. It’s taking the current spreadsheet and making it a bit more advanced, but definitely doable!

Thanks for the reply. I found exactly what I’m looking for at Splitwise.com, travel calculator. However, I’m trying to find an excel spreadsheet. Ideally I could create tabs for different items like food supplies, beverages, beach rentals, etc. If I had a total planned cost, I could forecast for those who may go what the shared cost per day would be.

Interesting, yeah there are quite a few apps out there that do a good job of splitting. Not too many we’ve seen that help you with a planned cost, or budget, and figuring out who might be coming on the trip vs. flaking.

GREAT spreadsheet, I’ve been trying something like this but did not quite succeed. Many thanks for putting it up!

One question: why use the IFERROR formula to find the per person cost for expenses? Why not just the expense divided by the sum of 1s (or 0.5s…)?

Thanks Akka! The reason for the IFERROR function is actually a cosmetic reason. In the event that the cost per person results in an error, the spreadsheet will show a “N/A” error which doesn’t look very nice on your spreadsheet. By using IFERROR, we can tell Excel to show a blank or “” if the result is an error.

Ah ok, thanks for clearing that up. The N/A notification is actually better for me, that way I won’t miss something going wrong 🙂

You could also put something else instead of a “” in the IFERROR function so that if a “N/A” does come up you can put something a little more user-friendly like “please check your calculation.”

Thank you so much, just found your Google Sheet and it saved me hours of head-ache.

Thanks Cat! Glad we were able to help!

I am wondering if you can help me to answer the question as to whether a value greater than 1 can be entered for a friend. For example, if I wanted to use this tool to track cost-sharing in a parent group and parents have different numbers of children (who all incur the same cost for any activity), can I just label a column with the parent’s name and then enter a value that equals the number of children they have participating in a given event to divide the cost between families? Otherwise, I think I would need one column per child but I think that a value greater than one should be possible. Would just appreciate a confirmation before I start using the tool. It looks really awesome!

Hi Beth! Yes you can definitely enter in values greater than 1 and the tool would still work. For instance, if “Ben” on the current spreadsheet represented a parent with 3 kids, you could enter “3” in the spreadsheet and the per parent costs, amount each parent owes, and amount each parent is owed would calculate correctly.

Very nicely organized sheet. I just reformatted my sheet to look like yours. Thanks for the inspiration!

Glad you found it useful!

Excellent post. But I have question, the post just deals with car rental case, but what if the group just uses one guy’s own car and he is the only driver? Is there some sheet dealing with such scenario?

In that case, you wouldn’t put the car rental as an expense on the sheet. You would probably just put the gas expenses on the sheet. Hope this helps!

We try to be fair to the car owner that basically he pays the car rental, not to mention for a long distance trip, he will incur extra car maintenance cost. So how about to put en equivalent car rental charge under his name? By the way, you have such good post, even after 4 years it still attracts attention!

Gotcha! In that case if the car owner did rent the car, he should put the costs for the rental and maintenance on the sheet. Thank you about the post! We didn’t think it would still be this popular after 4 years!

What if two people paid for one line item? For instance, there are 6 guests but 2 of them split the bill in the end. How can I enter that so it balances out?

Hi Carolyn, the best way to do this would be to create two rows for the same item. Let’s say Bob and Alice both paid for dinner. Bob paid $40 of the group dinner and Alice paid for $60. You would just put each of those expenses on individual rows and put a 1 for the 6 guests that participated in the dinner. Hope this helps!

Thanks for this helpful spreadsheet! Once people start reconciling the amount they owe, how do you deduct the costs paid from the costs owed? Obviously I am able to do it manually, but there is already a formula in both cells. Would love to somehow do it in a more automatic way rather than case by case.

Hi Becky, thanks for the kind words! Row 28 of the sheet shows the difference between the Costs Paid and Costs Owed. This represents the amount the person is owed (if the number is positive) or the amount the person owes (if the number is negative). Hope this makes sense!

Just wondering how you would add in a currency exchange if you are traveling to a different country?

You could add a few rows at the bottom of the sheet that converts the amounts in USD to your local currency. Hope that helps!

Thank you for sharing!! And for the helpful video! Can I remove columns? We only have 3 participating parties.

Yes you can definitely remove columns depending on how many people you have. The Sum formula automatically re-adjusts in column L to account for the number of people in the columns.

Hi Can I add columns? I have more people that I need to account for.

Yes! Just make sure you add the new column before column J (Steve) and the formula should carry over.

Excellent spreadsheet. How do you deal within a long trip when some participants pay due amounts ?

Hi Denis, can you clarify what you mean when you say “pay due amounts?”

Thanks for sharing, very useful spreadsheet! Saving friendships

You’re welcome! Saving friendships one expense at a time :).

Thank you for making and sharing this for free. It’s great.

You’re welcome!

Hi! When I tried adding more rows for additional expenses, the per person cost column stopped automatically calculating. The other columns work, but now I have to split the cost to add to that column manually. Any idea what may of happened? I tried going back and adding a row higher up (before the rows you created end) but it doesn’t seem to work there either.

Hi, you have to drag the formula in the `Per Person Cost` column down to the new rows you add for additional expenses.