Trip Budget in Excel: Plan vs Actual, Expense Log and Split Costs Among Friends (Who Owes Whom)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: trip-budget-split-expenses-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

The trip (made-up) has 10 expenses totalling ₹19,960. Eight of them (₹18,960) are shared by all four: ₹4,740 each. The palace tickets (₹400) are shared by 3 (not Zoya): ₹133.33 each. Boating (₹600) is shared by 3 (not Shahrukh): ₹200 each.

The nets add up to 0. Plus means the group owes that person, minus means that person owes the group. Settle up in three payments:

1. Salman pays Shahrukh ₹3,853.33.
2. Zoya pays Amir ₹1,406.67.
3. Zoya pays Shahrukh ₹633.33 (₹2,040 − ₹1,406.67).

Shahrukh then has 3,853.33 + 633.33 = ₹4,486.66 back (the 1 paisa is rounding). Against the budget of ₹20,500 the trip came in ₹540 under, although travel went ₹480 over (₹6,480 vs ₹6,000).

2. Build it step by step

Step 1. Expense log

1. In A4:E4 type Date, Expense, Category, Paid by, Amount (₹); in F4:I4 the four names; in J4 People; in K4:N4 each name with share.
2. Type each expense from row 5. Paid by, Category and F:I (Y/N) get drop-downs from Data > Data Validation > List (drop-down lists).

Step 2. Who shares and the share

1. Type Y under everyone who shares the expense, N for anyone who skipped it.
2. In J5 type =COUNTIF(F5:I5,"Y"): 4.
3. In K5 type =IF(F5="Y",$E5/$J5,0) and copy right to N5, then down to row 14. Fuel ₹3,200 / 4 = ₹800 each.

Step 3. Total

1. In E15 type =SUM(E5:E14): ₹19,960.

Step 4. Paid, Share, Net

1. In A17:E17 type Person, Paid, Share, Net, Result and the names in A18:A21.
2. In B18 type =SUMIF($D$5:$D$14,A18,$E$5:$E$14): Shahrukh paid ₹9,360 (SUMIF).
3. In C18 type =SUM(K5:K14) (Shahrukh's share column; Amir uses L, Salman M, Zoya N).
4. In D18 type =B18-C18: +₹4,486.67.
5. In D22 type =ROUND(SUM(D18:D21),2): it must be 0.

Step 5. Result message

1. In E18 type =IF(ROUND(D18,0)>0,"Gets back ₹"&TEXT(D18,"#,##0"),IF(ROUND(D18,0)<0,"Pays ₹"&TEXT(-D18,"#,##0"),"Settled")) and copy down.
2. Rules on D18:D21: green above 0.5, red below −0.5.

Step 6. Budget vs actual

1. In A24:D24 type Category, Budget, Actual, Difference; categories in A25:A28.
2. In C25 type =SUMIF($C$5:$C$14,A25,$E$5:$E$14) and in D25 =B25-C25. Travel shows −₹480.

Step 7. Settle up

1. Sort the people by Net. The biggest minus pays the biggest plus first, then repeat until every Net is 0.
2. B31 shows the biggest receiver: =INDEX(A18:A21,MATCH(MAX(D18:D21),D18:D21,0)) = Shahrukh.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Splitting everything by 4. If someone skipped an activity, mark N. COUNTIF then divides by 3 for that row only.
2. Spelling a name two ways. "Shahrukh" and "Sharukh" are two people for SUMIF. Use the drop-down.
3. Settling each bill separately. Ten bills could mean twenty payments. Settle the nets once at the end.
4. Nets not adding to 0. A row with no Y gives #DIV/0!, or an amount is missing. The check cell D22 shows this.
5. Counting a settle-up payment as an expense. Paying back a friend is not trip spending. Keep it out of the log.

5. More real examples

Example 1: Flatmates sharing rent and bills. Every month is a "trip": rent, electricity, internet and groceries with Y/N for who shares.

Example 2: Office lunch group. One person pays each day; the log shows who owes after a month.

Example 3: Puja or wedding costs. Brothers and sisters sharing the costs of a function at home; share by Y/N or by a ratio typed in place of the formula.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I split expenses between friends in Excel?

Log each expense with who paid, mark who shares it, divide by the number of sharers, then Net = Paid − Share for each person.

How do I know who owes whom?

Plus Net means the group owes that person; minus Net means that person owes. Biggest debtor pays biggest creditor first.

What if someone did not join one activity?

Put N under their name for that row; the share is split among the rest.

Can I split unequally, like 60:40?

Yes: type the amounts in that row's share cells instead of the formula, making sure they add up to the amount.

Is this better than a bill-splitting app?

An app logs things the moment you pay. A sheet is free, private and easy to change; many groups use one for one trip.

7. Quick quiz

Ten questions on this page. Sign in to save your score.