Group Shared Expense Calculator 2024 – Free Travel Expense Sharing Template
This Group Shared Expense Calculator template can be used to calculate each person’s share of expenses in a group. For example, when you go on a trip with your friends where multiple people pay for shared expenses. At the end of the trip, you would need to calculate how much each person owes and to whom. Another example is if you participate in a group event which needs multiple purchases.
We were on a family trip last week for 3 days with a friend’s family. We shared expenses for car rental, hotel reservation, fuel, etc. and both families paid for expenses at different times. When we returned from the trip, we had to calculate our share of the expenses. I built this template to help with that.
FEATURES
- Expenses can be split equally among all friends in a single step
- Expenses can be split unequal among any number of friends
- Expenses can be split unequal by % or Amount
- Report shows all balances among friends
- Calculates expense amounts for each friend
FREE DOWNLOAD
VIDEO DEMO
HOW TO USE
Step 1: Enter friends’ names. The template allows up to 12 friends.
Step 2: Enter the expense transactions
- Date: Date of expense transaction for your reference (Not used in any calculation)
- Transaction: Description of the transaction for your reference (Not used in any calculation)
- Amount: Expense Amount
- Category: Category of expense (Not used in any calculation)
- Paid By: Person who paid for the expense
- Split Type: There are three ways expense can be shared/split.
Let’s take expense for Coffee to illustrate the three split types.
Split Equal: When the expense is shared equally among all the friends.
This transaction does not need any further data entry. In this case, the $12 will be shared among all 8 friends equally. $.1.5 each.
Split Unequal – %: When expense is shared unequally among friends and we know the share in %, we can use this split type. One example is when only 2 of the 8 friends had Coffee and their shares are 40% & 60%. We enter the shares in appropriate columns named after the friends.
We will also use this split type when we have all the 8 friends sharing in unequal % share.
Note: when entering %, please enter in decimals. For example, enter 50% as 0.5.
Split Unequal – Amt: When expense is shared unequally among friends and we know the share in actual amounts, we can use this split type. Following the same coffee example, when only 2 of the 8 friends had Coffee and their shares are $4 and $8.
Data Validation
There are some validation rules built in the template to ensure accurate data entry.
- Name entered in Paid By column should be present in the Friends list in the Home sheet
- When using ‘Split Unequal – %’, the % entered for all the friends should add up to 100%
- When using ‘Split Unequal – Amt’, the amounts entered for all the friends should add up to expense amount
If there are any errors, you will see it in the last column Validation.
You will also see visual indicators on the expense transactions.
Purple indicates transactions where the shares (% or Amount) are under the expenses and the Red indicates transactions where shares (% or Amount) are over the expenses.
Please ensure that there are no errors in order for the report to provide accurate balances.
Enter each transaction in a new row. If you are new to Excel tables, please read this article on Introduction to data entry in Excel.
Step 3: View Report
Once we complete entering all the expense transactions, we can move to the report sheet.
The sheet shows all the individual balances on who owes whom how much. We can also see the total amount a person owes and a person is owed to.
We can also select one friend to highlight that friend’s details and summary.
The summary at the top shows Amount Owed To, Amount Owed From and Net Balance. The details show the Amounts Owed To, in Green and Amounts Owed From, in Red.
If there are any errors in data entry in the Expenses sheet, you will see a warning message in the Report sheet.
We can also see the amounts paid by each person, total expenses by each person and the net balance.
If the Net Balance is negative, that means the person will have to pay to others more than he will receive from them. Since we are distributing expense amounts equally, sometimes we end up with a rounding difference of a few cents.
Changing Currency
You can change the currency format displayed to suit your currency. Just select the currency cells and press Ctrl +1.
Choose the currency from the list and press OK.
If you find this shared expenses spreadsheet template useful, please share the template with your friends. If you have any suggestions to improve, please post them in the comments.
79 Comments
Awesome template thanks for sharing.
Thank you!!!
how can I adjust the payment if some friend give some amount in advance or more amount then It owed to pay.
Hi, could you tell me the necessary steps to add a 13th friend to the calculation? Thanks!
Nevermind, I found a way.
Awesome template, thank you very much, you saved me a lot of trouble!
Could you let me know how you added the additional person?
Hello
You can add up to 12 names on the home sheet among whom the expenses can be divided.
Best wishes
Hello FSJ,
Could you please guide me on the process on adding additional friends to the excel. I need to extend the list for 22 people. Please advice. Thank you
I Have downloaded the sheet nd uploaded it in google drive.When i have added the friends list it is not pulling the expenses sheet.Please help
Hello
Thank you for using this template.
Some features of this product does not work properly on Google Drive. You can use this on One Drive to share the details with others.
Best wishes.
What to do when balances are settled?!
The balance will display 0 when they are settled. We just keep entering new transactions as usual.
Best wishes.
How do you input a payment made by a person and where will this reflect?
Hello
Any payment made will reflect in the Expenses sheet.
Best wishes and regards
How could I make this into something I can track month to month?
I’d like to track expenses throughout the year. and how can I put the report to the bottom of the expense tab?
This template allows tracking expenses throughout the year. However, the report shows the current status of the balances owed. It does not break down by month.
Personal Finance Manager https://indzara.com/2013/05/personal-finance-manager-excel-template/ allows tracking expenses by month, but that does not support shared expenses balance calculations.
Best wishes.
Neat work! You saved my day after vacation with friends. Thank you so much!!
Thanks for feedback. Glad that it was useful.
Best wishes.
Thanks for the great sheet! Can you please tell me how to unprotect it? It have a password!
You are welcome. Please see this article for how to unprotect: https://indzara.com/faq-items/unprotectingprotectedsheets/
indzara is the password.
Best wishes.
Hello,
Great spreadsheet, i have some suggestions. On the last tab, you can minimize payments between people. You can see who is owed to the most and who owes the most and have that person pay. You can start there and move your way down until everyone who is owed money gets paid. I have this in excel if you want to see.
Thank you. I agree with the idea. Thanks for the suggestion.
Best wishes.
Hi there – on the second tab (Expenses) I keep getting an error for Data Validation saying “Friend Not Recognized.” Also no names are populated on row 5 of that tab either, in white font it just says Friend 1, Friend 2, etc. even though I added names to the “Home’ Tab.
Thanks!
Thanks for using the template. It appears that the friend name entered in Expenses sheet is not available in the Friends table in the first step. Please check that.
If there are further questions, please email the file to indzara at gmail. Thanks. Best wishes.