Share Expenses with Family and Friends Using Google Sheets
Hi, everyone! This is Lara Hammock from the Marble Jar channel and in today's video, I'll give you access to the spreadsheet that I use to share expenses with friends and family. And show you how I use it.
You don't need a spreadsheet for a dinner out with friends, right? It's just meal total divided by the number of people -- you can easily do that with a simple calculation. But what if you've gone away for a weekend with friends or are traveling with family? Or if you've decided to throw and party and are splitting the expenses with a group of friends. And what if different people have paid for different things and not everyone participated in every activity? Then it starts to get complicated, right?
So, I can't take total credit for this spreadsheet. I found it online and modified it. But let me share with you how I use it. Here's the spreadsheet. You can find a link to it in the comments. And towards the end of this video, I'll show you how to copy it and modify it for your own use.
Basically, each expense gets its own line. On the left hand side, you have columns for each paying party -- could be individual people or families. On the right hand side, you have columns for each party again, this time showing how the expense was shared. These copy directly over, so you only have to input them on the left side. So, let me give you an example: say my family is on a trip with my mom, my dad, and my brother's family. My mom and dad are divorced (but obviously still on good terms since they vacation together), so they represent 2 different paying parties. My brother and I both have 4 people in our families, but we are still just one paying party each. Each paying party gets one column on the left and one column on the right.
So, let's say I pay $100 for a meal and everyone was there. On the left side, the total amount goes under my family. Skip the parts in Grey -- they are calculated. On the right, I'm going to simplify this and just divide the total into 10 parts which represent people and then distribute them into the Shared columns. 4 parts for my family and my brother's. 1 part each for my parents. If my dad and my nephew were missing, it would be 4 parts for my family, 3 parts for my brother's, 1 for my mom and none for my dad. You get the idea. I do this by putting formulas in, but you can do this on the back of an envelope if you prefer and just enter the straight figure. You can obviously make this more complicated by weighting the value for adults more than kids, but generally my family likes to keep it simple.
So let me put in a couple more lines. My mom and my dad split the hotel rooms - total $1,200. So, that is $600 for each of them in the paying columns. Since each party had one hotel room, that is a straight 1 part per party or $300 a piece. My brother paid for tickets to a museum, which is a little more complicated since the kids tickets were less than adults and there was senior pricing. I have a separate sheet here dedicated to doing calculations like this. And there were 2 more $100 meals, paid for by my dad and by me, with all parties eating. Okay -- that gives us 5 different expense lines.
Here's a cool thing about this spreadsheet -- there is an additional check to make sure your calcs add up. Let's say I double counted a person or added in too much by mistake. I'm going to change this $40 amount to $50 which would be an error. Did you see that when I did that, this column went red? That's a flag to check your calculations since they are not adding up.
Okay -- once you have all of your expense lines in, these orange rows will show you the totals of how much each party paid and also how much each party consumed. Just below that, in aqua, is what everyone is owed and needs to pay. If the amount is negative, that means you have to pay that amount. If the amount is positive, that's the amount you can expect to receive from others. You'll have to do a tiny bit of brain work here to figure out the amounts and who pays each payment, but it is pretty straightforward. In this example, I can pay my whole amount $270 to my Dad. But since that doesn't make him whole (he needs to receive $358), he'll receive a second check from my brother for the remainder (or $88). Then my brother owes my Mom his remainder (or $258). You can add these together and make sure that it comes to the same amount as the total of the positive amounts here.
A couple of things: this spreadsheet can expand to accommodate up to 8 parties. You just have to hit this arrow to show hidden rows here on the left and again here on the right. I've made this example a sample sheet that you can refer to if you get confused about where everything goes. Whenever I do one of these sheets, I make a copy of the whole spreadsheet and start a brand new document. That way I can send it out to the group for verification and use it as backup documentation without sharing private historical information from previous sheets. Also, I really try to encourage people to use PayPal or another instant payment method, but some folks are still old-school and prefer checks. That's fine -- it just might take some follow up to get full reimbursement.
I'm making this available on my Marble Jar Google Drive. It is not editable (you can see it says "View Only"), so once you open it using the link, you'll need to make a copy to use it for your purposes. Go to File, Make a Copy, then rename it whatever you want, and hit Okay.
Let me know what you think! Comments are always appreciated and thanks for watching!
Comments
Post a Comment