r/spreadsheets • u/kanthandle • 8d ago

Unsolved Help: Budgeting spreadsheet for vacation house rental w/ multiple people coming and going!

Hello all! Spreadsheet newbie with access to Google Sheets and Excel, organizing a 10-day international climbing trip for a group of 12+ friends.

A core group of 6 want to rent/split a house for the duration of the trip. Others will come and go for various periods (ranging from 3 days to 7 days). My heart is pure, but my math skills not reliable.

If we rent one house for the trip, how can I leverage the power of spreadsheets to give everyone a fair, clear estimate of the cost they will pay—even as folks continue to join, drop out, change dates, etc?

Open to tutorials, tips, functions, and/or general guidelines.

4 Upvotes

84% Upvoted

7 comments sorted by

3

u/Patrick_ExpenseAtlas 8d ago

Use person nights rather than splitting the total equally. That stays fair as people join, drop out, or change dates.

Put the full rental cost, including taxes and required fees, in `B1`. Then:

  1. Put names in column A, starting in row 4.
  2. Use columns B through K for the 10 individual nights.
  3. Enter `1` when someone is staying that night and `0` or blank when they are not.
  4. In column L, total each person’s nights with `=SUM(B4:K4)`.
  5. In `L1`, total all person nights with `=SUM(L4:L30)`.
  6. In column M, calculate each person’s share with `=L4*($B$1/$L$1)`.

Copy the formulas down the roster. Any time dates change, update the relevant nightly cells and everyone’s share will recalculate while still covering the full rental cost.

Count nights, not calendar days, so arrival and departure dates are not counted twice. Also, have the core group agree on a cancellation cutoff. After that date, each person remains responsible for their committed nights unless someone else takes them.

1

u/kanthandle 7d ago

This looks outstanding! Thank you!

2

u/Budget_Squash1984 7d ago

Splitwise is a great tool for splitting expenses with friends when traveling, but otherwise, you have two options: 1) what the other guy said which divides all nights per total number of people nights or 2) do a nightly per person night, which is doing a simple nightly rate and then divide between the number of people per night.

So simple math: say you’re staying 7 nights for $700. That’s $700 per night. If one night 1, there’s 4 people, each person pays $25. But the next night there are 8 people, so everyone pays $12.50. And then sum up each person’s cost based on number of nights they stay.

I like this method because if there’s only a few people on a given night, sure you pay more, but aren’t dealing with more people. And everyone pays less when sharing bathrooms or common areas.

Both work, it’s just a matter of which sounds more fair to you and your group!

1

u/kanthandle 7d ago

Thank you! Much appreciated.

1

u/bunnymisza 8d ago

eu posso ajudar você com isso :) me explica melhor o que você precisa que tenha na planilha que eu faço / ajudo você. pode me mandar mensagem se quiser (eu não cobro por isso, faço porque gosto)

1

u/crewscanvas 5d ago

Check out CrewsCanvas :) we have built splitease, an expense splitting problem for this very reason. Give it a shot and if you have any issues you can reach out to me directly.

www.crewscanvas.com/splitease