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…
22 Responses to “How to: sharing trip expenses using excel”
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?
Hi Chadoo, great work indeed! I have filled in the sheet but have a problem though. I get the #NAME error throughout all colum I and consequently in N and O. Any tips?
Thanks!
Â
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!
Hi,
I found yet another free tool for managing shared expenses:
http://www.expensesharing.com
This one differs that it allows to invite other colloborants,
and is easy to use. In addition it is capable to handle currency conversion.
Thanks for the post.. this template is very helpful... http://www.screeperzone.com/expensesharer is also a very helpful application.takes an extra step to reduce the number of money transactions between participants.
Great effort!...I also struggled quite a bit to share expenses in a fair manner when I was staying with roomies. Being somewhat of a techie myself, I came up with this :
http://sandypai1983.wordpress.com/2010/07/21/great-excel-tool-for-sharing-expenses/
Hope you find this useful. Excel is amazingly handy and useful in all sorts of basic analytical stuff.
How can we use this for ruppes?
In your example, if you exclude 1 person, the amount should increase not decrease. I can't see how the exclusion works. Please explain. Thanks.
Thanks
Hi,
There is a small issue in the formula. It is actually more of an XL issue. In the example if you check the 15th item and see the per head calculation, you will realize that the final value is incorrect.
Here in the end, in the denominator of formula it comes as "10-5-3". XL instead of returning 2 returns 10-(5-3)=2. Hence u get a 18/2=2.3 instead of 18/2=9
I think the formula in column I should be as follows:
=D4/(10-COUNTA(WHO_ALL)-COUNT(E4:H4))
Rather than COUNTIF(WHO_ALL, ""), which is counting blank rows in the WHO_ALL range, when it should be counting actual participants.
I agree with JM.
My god, Chandoo...here I am combing your blog archives in chronological order, and this is the first post on Excel on your blog, despite 3.5 years of blogging before this!
And the titles of some of those earlier blogposts...oh my!
I like!
Actually, I tell a lie. Here's the first: http://chandoo.org/wp/2006/09/04/how-to-get-unique-items-using-excel/
How to reduce the participant from 10 to 3, what necessary changes are require if we are 3.
Great excel, thanks. Just note that the fields that use the "WHO_ALL" name in your experiment tab points to the list in the example instead of the list in the experiment tab 😉
Although I have used this Excel for many trips, I have now found another one that even works better. The excel from this site does not like it when you enter expenses that someone paid when the person paying for it should not be included in the calculation. The one on the other website does. Here's the link:
https://github.com/brsanthu/expense-share-calculator
Regards,
Nika.
How that "exclude" formula work ?
" =D4/(10-COUNTIF(WHO_ALL,"")-COUNT(E4:H4)) "
can you please explain in detail ?
Thanks.... !!