SubscribeHow to: sharing trip expenses using excel
Yesterday we were calculating our Washington trip expenses and it occurred to me that if there is an excel template where I could enter the trip expenses and who paid what to find out how much we need to pay up / collect, it would be great. I looked around for few seconds, couldn’t find anything. So I went ahead and built expense sharing worksheet on google docs. Feel free to use it for sharing your trip / party / picnic / apartment expenses.
Here is how I have done it.
- First I have created a list of people who need to share. Just for simplicity I have limited the no to 10. You can edit the excel and change it if you want.
- Then I have created an expense table like the one shown below. The requirements for this are simple, (1) should be able to capture descriptions of each spending (2) should be able to specify who footed the bill (3) should be able to mention the amount (4) should be able to exclude people from sharing a particular expense. Again, for simplicity sake I have limited the number of people who can be excluded from sharing an expense to 4. You can always edit this and change the formula accordingly.
- Now next to the list of people I have added 3 columns to show (1) how much that person has already paid (2) how much is his/her share (3) what is the difference.
- The formula for (1) how much already paid is straight forward sumif(), the formula for a particular person’s share is something like this,
If the row has a person
(total expenses / no.of people) – (total expenses excluded for this person / no. of people)
else “0″The actual formula involved sumif() over the four columns since each expense can be excluded for maximum of 4 people.
- Once I have entered data the output looked something like this,
Hence both Yerry Jang and Dointy Paired Hilbert need to collect 237 $ and 90 cents from Barren Wuffet, Gill Bates and Beff Jezos put together. Not bad
How to use it?
Just save the expense sharing excel sheet from google doc [or download the expense sharing excel sheet] to your machine / account. Enter the expenses in columns B,C and D. Enter the people names in column L. Incase you need to exclude any one from a particular line item, just enter their number (it will be next to the person’s name in column K]. As you enter the expense details, the Column O displays the actual dues.
Feel free to comment / extend / share / suggest. Your turn…
|
|
Posts & Navigation
Tags: downloads, Excel Tips, free, how to, sharing, spending |
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Hi , Dropped in here through india uncut. Execllent effort to capture the expense statement. Have personally done this many times, but never had the patience to pull out a format like this.
Just have one small query. As per your example , the total amount spent is 714$ but if I add the shares , it adds upto 686$ only. Shouldn’t the amount spent and shares for each person match ?
@Sabs, welcome to PHD, thanks for appreciating my post.
I am sorry, but when I try to add the shares {139, 137, 142, 145, 151 } it adds up to 714. Are you referring to the image or one of the excel sheets. Let me know if there is some confusion?
Sorry about bombarding you. Your version in the download is different from what you have in google docs. The google doc version and the screen shot in your post doesn’t have the problem I mentioned.
@ Sabs, hey thanks for pointing out the mistake. Let me update that faulty excel. You have a good day
Hi Chandoo:
I personally developed on for myself and made it public. you can find it here http://www.screeperzone.com/expensesharer . It reduces the number of money/check transactions . thought it might be useful. Yours is good too.. thanks!
Hi Chandoo,
Another simple online solution to handle shared expenses:
http://shortreckonings.com
The user interface was carefully thought to make expense entry as quick as possible. For example, the tool does auto-complete descriptions so that you never need to enter twice recurring expense descriptions.
You can enter non-even split, for example fixed amounts, percentages or any kind of sharing weights.
Enjoy!