Four years ago, I was sharing an apartment with 3 of my friends. At the end of every month, we would sit together, jog our memory to remember expense details, and use a calculator to get each person’s share.
And we used to get it wrong every time.
Today I am here with a Shared Expense Calculator Template. It can be used when you are incurring expenses that are shared by many people (for example, sharing apartment expenses or vacation/trip expenses). All you need to do is enter the details, and it will automatically tell you who owes whom.
I wish I had this template back then.
Download the Expense Calculator Template
Now before you jump in and download the template, here is a little bit about how it works:
Shared Expense Calculator
- Create a list of people among whom the expenses are to be shared. This could be the number of people on the trip or the number of people in the flat. To do this, enter the names in the range A2:A11 in the worksheet ‘Backend Calculation’ (I have a dummy list here. You can change it with your own list).
- Enter expenses in the Expense Details tab. The Who Paid column has a drop down that shows all the names you entered in Step 1. So you can simply select the name from the list.
- As soon as you enter the details, it automatically shows you the expense sharing summary. For example, in the example below, Michael (in red) owes Joe (in Green). The amount is mentioned in between the two names.
What goes into making this Shared Expense Calculator
- Excel Formulas – A couple of big formulas, but nothing out of this world.
- Excel Tables.
- Conditional Formatting.
- Custom Number Formatting.
Download the Expense Calculator
Now you know what you need when you sit down to calculate your share of the expenses 🙂
Do you like this shared expense calculator? Are there any other areas where you think an Excel template could be helpful? Share your thoughts in the comments section.
You may also like the Following Excel Templates:
- Excel Leave Tracker Template.
- Task Matrix Productivity Template.
- Employee Timesheet Calculator Template.
- Vacation Itinerary and Packing List Template.
If you are looking for some online tool to do this, try this.
28 thoughts on “Shared Expense Calculator – Download Free Excel Template”
Would help if you can show how the various tables and formulas are used to get the desired output in “Expense Details” worksheet
This is great! I need for two commercial renters with same shared bills but also have some that we each pay on our own then settle up later, like supplies. This Excel makes is super simple to just settle up monthly instead of PayPaling each other five times a month. This Excel file is super simple. Highly recommended.
Can I use this template for 16 people?
This is nice. Is there anything similar to track expenses between ex-spouses and college children? My ex and I are each responsible for half of medical and extracurricular expenses, but thirds with our college child. We also have a child still in high school, who is taking college coursework.
THANKS. That was very helpful.
could you add a paid field where if one person has paid another it will remove them from the list?
Hello sir
How can i ad 1/4 day leave insted of maternity leavs
Plz guide
Hi. The template works really well, thank you for taking the time! Just had one question; if I wish to divide the cost according to a percentage or fraction between the participators, how would you incorporate it into the Backend Calculation? I have already mad a support sheet with fraction for each type of cost, and “Description” in Expense Details is now a drop down with reference to the support sheet. When using the drop down menu it will position the correct fraction of the cost, which each person is responsible for, next to each line in the Backend Calculations. All I need now is to figure out how to include these fractions into the calculation.
wow
This is exactly what I am looking for, but how do I add more rows beyond 25 entries? If this is to be ongoing, I certainly need more than that. And where do I put, for example, that person A has paid person B to settle it?
Hello.. You can simply drag a edge from the right most column of cell E25 and make it as many rows as you want. To mark that someone has paid, you can put that in column P
nice tutorial
http://howto-office.blogspot.com/
Hi, just saw this and it is absolutely incredible. One problem for me is that there are 11 in our group. How can I add another person as the formulas do not naturally recognise an 11th person
Hi Dave.. Glad you liked it.. You can easily do this by going to the tab “Backend Calculation”, select cell A11, hit the tab key and add the name of the 11th person. Everything else will adjust automatically.
I did what you said but it didn’t update the front page. It showed the correct division for 11 people but the 11th person did not appear on the summary page owing anything
This is extremely helpful, although I would like to figure out how to add an additional column to categorize each expense…i.e. Marketing, Advertising etc. Then I’d want to filter each category to a separate sheet to total on there own. I tried modifying this template but then it threw off all of the formulas…anyone tried to do something similar?
Thanks for sharing this useful article.
I have personally used the template of [www.mybudgettracker.co] which is linked with a html analytics page, which gives you insights of your spreadsheet.
Give a try http://www.mybudgettracker.co
HI,
The excel sheet is good if expenses is shared by all of the friends, but I have few expenses that needs to be calculated only with few. Can you help me with that
I too am interested in this update. Is there a detailed explanation of how the current template works, so that I can figure out where to add this feature?
Actually , I found what I needed with this link. http://www.shortreckonings.com/
Hi Sumit, in the shared expense template. if all the items are paid by a single person, it doesn’t show up in the summary. For eg: there are 3 persons and if only person has taken the responsibility to pay for all the trip expense. the summary should show up how much the other 2 owes to the one who paid
Hello Ahamed.. Thanks for commenting.. This is a valid point, and I missed it in the first place. I have updated the template so that now it shows the split even if everything is paid by one or two people.. Have a look!
Thank you. works perfect
Really very useful
Thanks for commenting Alok.. Glad you liked it 🙂
Good stuff!
Thanks for commenting Ray.. Glad you liked it 🙂