Group Shared Expense Calculator – Free Travel Expense Sharing Template

Posted on
Report sheet with balances highlighted for one friend

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.

Group Expense Shared Calculator - Report
Group Expense Shared Calculator – Report

 

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

Group Shared Expense Calculator – Free Excel Template

VIDEO DEMO

 

HOW TO USE

Step 1: Enter friends’ names. The template allows up to 12 friends.

Expense Sharing - Enter Friends' Names
Expense Sharing – Enter Friends’ Names

 

Step 2: Enter the expense transactions

Enter shared Expense Transactions
Enter shared 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.
Three types of expense sharing or expense split
Three types of expense sharing or expense 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.

Shared Expenses Split Equally among friends
Shared Expenses Split Equally among 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.

Shared Expenses Split Unequally among friends - %
Shared Expenses Split Unequally among 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.

Shared Expenses Split Unequally among friends - Amount
Shared Expenses Split Unequally among friends – Amount

 

Data Validation

There are some validation rules built in the template to ensure accurate data entry.

  1. Name entered in Paid By column should be present in the Friends list in the Home sheet
  2. When using ‘Split Unequal – %’, the % entered for all the friends should add up to 100%
  3. 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.

Data Validation Column with error messages
Data Validation Column with error messages

 

You will also see visual indicators on the expense transactions.

Transactions with errors are highlighted
Transactions with errors are highlighted

 

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.

Sample of Expense sharing entered
Sample of Expense sharing entered

 

Step 3: View Report 

Once we complete entering all the expense transactions, we can move to the report sheet.

Report sheet shows all balances among friends
Report sheet shows all balances among friends

Click to view full image

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.

Group Shared Expense Calculator - Excel Template - Report Sheet
Group Shared Expense Calculator – Excel Template – Report Sheet

Click to view full image

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.

Error displayed in Report sheet about data validation issues
Error displayed in Report sheet about data validation issues

 

We can also see the amounts paid by each person, total expenses by each person and the net balance.

Paid Amount, Expenses & Balance for each friend
Paid Amount, Expenses & Balance for each friend

 

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.

Changing Currency Format
Changing Currency Format

Choose the currency from the list and press OK.

 

If you find this template useful, please share the template with your friends. If you have any suggestions to improve, please post them in the comments.

 

Related Personal Finance Templates

38 thoughts on “Group Shared Expense Calculator – Free Travel Expense Sharing Template

  1. Hi,
    I want to divide Utility bill among a group of room mates.
    I got an electricity bill for the amount of Rs. 1300 for a month of 30 days.
    A- Stayed for 30 days, B- Stayed for 30 days, C- Stayed for 12 days & D- Stayed for 7 days.
    How to split Rs. 1300 among we 4 peoples.

    1. Hello

      You may all the days and then divide it in proportion.

      In this case, the total number of days is 30+30+12+7= 79
      Total Bill: 1300

      A’s contribution is (30/79)*1300= 493.67
      B’s contribution is (30/79)*1300= 493.67
      C’s contribution is (12/79)*1300= 197.46
      D’s contribution is (7/79)*1300= 115.18

      Total is 1300

      Best wishes

  2. There appears to be a calculation error in the the amounts owed. The coloured boxes at the top of the results page don’t populate with any data either. Have I done something wrong with setting this up?

    1. Thanks for using the template.
      Please ensure that none of the formulas or links are broken. Please download a fresh copy and check. If it still does not work, please email your issues along with your file to contact@indzara.com

      Best wishes

  3. hello, I am using this template to track expenses on shared lodging for a trip in different cities, but I am having difficulties in adding as many columns in the expense page as many friends I add in the Home page. Iy might seem stupid but could you help me?
    Lisa

    1. Thanks for using our template.

      Please follow the video demo and the text in the template page. In case, it still does not work, please email the template along with the list of issues to contact@indzara.com

      Best wishes

  4. I currently use a “Group Shared Expense” App called Settle Up and while it does work most of the time and has reasonably decent support, It will miss an entry if the device (iPhone) that was used to enter the amount is not currently connected to the internet, it will cause a discrepancy in the account balance of other synced devices.
    I don’t think that this will happen using MS Excel through the MS cloud based “OneDrive”.
    Any comments or suggestions for a cloud based use of your template?

    1. Please try posting the file on OneDrive and use. I have personally not used in mobile.
      Please let us know.
      Best wishes.

  5. This does seem to work on Apache Open Office. Any one has tried?

    1. I meant it doesnt work

    2. Hello

      Thanks for using the template in a different environment. We have created and tested our templates on MS Excel. We cannot assure the productivity of templates on other platforms.

      Best wishes

  6. Hello Sir/Mam,

    Aweosome Template. Thanks a lot for sharing the Sheet and saving people like me who are not good with Excel. It really helps in reducing lot of work and makes expense calculation and settlement neater.
    I just had one request Sir, is it possible to increase number of Friends from 12 to 24? If you can guide me on doing the same that will be really a great help to me.
    Please kindly advice. Thank you very much for your help. Appreciate your help Sir!

    1. You are welcome. Thanks for the feedback.

      Insert columns after column R in the Expenses sheet to add more friends.
      In the Report sheet, we have to insert more columns (after column N) and rows after row 19. Use column N as reference for formulas and row 19 as reference.
      Update formulas in column P for total.
      Insert rows after row 37 for summary. Use row 37 formulas as reference.
      Update formulas in row 39.

      Best wishes,

Leave a Reply

Your email address will not be published. Required fields are marked *