Spreadsheets

Work trip expense report template in Excel with daily allowance rows

A work trip expense report template in Excel: day-wise rows for travel, stay, food and local transport, receipt numbers, daily limits and advance reconciliation.

Editorial illustration for Work trip expense report template in Excel with daily allowance rows

A work trip expense report in Excel has one row for each expense, grouped by day: date, category, description, mode of payment, receipt number and amount. A totals block adds everything, subtracts the advance you took and shows whether the company owes you or you owe the company. A small day-wise table compares each day with your daily allowance.

How should the rows be laid out?

ColumnHeadingNotes
ADateDate of the expense
BCategoryTravel, Stay, Food or Local
CDescriptionTrain, hotel, dinner, cab
DModeCard, UPI or Cash
EReceipt no.Bill number, or the words No bill
FAmount (₹)Rupees spent

Keep each category spelled the same way every time, using a drop-down, and number your receipts in the order you collect them. Writing the number on the paper bill itself and in column E makes attachment easy.

How do I build the totals and advance reconciliation?

  • Total by category: =SUMIFS($F$2:$F$100, $B$2:$B$100, "Travel"), and the same for Stay, Food and Local
  • Total expenses: =SUM($F$2:$F$100)
  • Advance taken in a cell such as I1
  • Balance in I2: =SUM($F$2:$F$100)-I1
  • Verdict in I3: =IF(I2>0, "Company owes you", IF(I2<0, "You return the excess", "Settled"))

Positive means you spent more than the advance and should be reimbursed; negative means you got more than you spent and the difference goes back.

What does a three-day trip look like?

Say you took an advance of ₹15,000. The expenses are invented.

DayTravelStayFoodLocalDay total
Day 1₹2,400 train₹0₹700₹650₹3,750
Day 2₹0₹4,800₹1,100₹900₹6,800
Day 3₹5,600 flight₹0₹350₹900₹6,850

Totals by category: travel 2,400 + 5,600 = ₹8,000, stay ₹4,800, food 700 + 1,100 + 350 = ₹2,150, local 650 + 900 + 900 = ₹2,450. The total is 8,000 + 4,800 + 2,150 + 2,450 = ₹17,400. Day totals check out too: 3,750 + 6,800 + 6,850 = ₹17,400. Against the advance of ₹15,000 the balance is ₹2,400, so the company owes you.

How do I compare each day with a daily allowance?

If your policy sets a limit per day for food, put it in a cell, say K1 at ₹1,000, which is an invented figure; use the one in your policy. Then for each day in a small table in column H, with the date in H5:

  • Food that day: =SUMIFS($F$2:$F$100, $A$2:$A$100, H5, $B$2:$B$100, "Food")

And the excess over the limit: =MAX(0, food that day - $K$1). On day 2 the food spend is ₹1,100, so the excess is ₹100. Knowing this before you submit avoids an unpleasant surprise from the finance team. Policies vary, so some companies pay fixed per-day amounts instead of actuals; if so, replace the food rows with a single allowance row.

What if I paid in cash and there is no bill?

  1. Write the entry the same day, with the amount, route or purpose.

Type No bill in the receipt column and keep a separate subtotal for no-bill items: =SUMIFS($F$2:$F$100, $E$2:$E$100, "No bill"). Check how your company handles unreceipted expenses, because limits and forms differ, and attach a short self-declaration only if your policy provides for it. Never write up a receipt you do not have.

Where can an app help?

Card and UPI spends on the trip appear in your statement. SpendixAI can import it, keep receipts with the transactions and hold each expense in its own currency for trips abroad, with totals in a base currency using the rate saved with each transaction. A CSV report can then support the sheet.

After the trip

  • All receipts numbered and attached
  • No-bill items explained
  • Advance and balance checked
  • Report submitted and the date noted
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports