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. The Splitting Costs with Friends Google Sheets template is available for purchase here 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:

1. Open the tool in Google Sheets
2. Click on File
3. 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!

Want to learn how to use Microsoft Excel?

Join 30,000+ students who are taking my introduction to Excel class on Skillshare. Learn the basics of Excel in 1 hour!