Proper Spreadsheets

Templates › Business

Mileage & Expenses Log

Made for anyone claiming mileage and expenses back.

A workbook for logging business journeys and out of pocket expenses, working out the mileage claim at HMRC rates and totalling expenses by category. The summary carries a chart that redraws itself as you type.

Of the 17 columns, you fill in 13 and the other 4 work themselves out, and all 23 figures on the summary do too. There is room for 1,100 rows of your own.

£4.99one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

Already have a spreadsheet of your own? Check it for mistakes, free.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic. What the film shows, in order:
  1. A new row is typed into the Expenses tab, one cell at a time, in the white cells a person fills in.
  2. The shaded cells in that row fill themselves in.
  3. The figures on the Claim Summary tab change to include the new row.
  4. The chart, Expenses by Category, redraws with it.
The chart, drawn from the totals in the table beside it. See it full size
Where you type, and the shaded columns that work themselves out. See it full size

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 1,100 rows. Open any tab to see its columns.

Mileage Log

9 columns

One row per business journey. Enter the date, the route, the reason and the miles, and the claim is worked out for you. Ready for 600 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
DateType a date.
FromType any text.
ToType any text.
Reason for JourneyType any text.
MilesType a number.
Rate BandCar or van drops from 45p to 25p once you pass ten thousand business miles in the tax year.Pick one: Car or van up to 10k miles, Car or van over 10k miles, Motorcycle or Bicycle.
Rate Per MileFills itself in.

Worked out from this row's Rate Band.

The formulaIF(ISBLANK([rate_band]), "", IF([rate_band]="Car or van up to 10k miles", 0.45, IF([rate_band]="Car or van over 10k miles", 0.25, IF([rate_band]="Motorcycle", 0.24, 0.2))))
Mileage ClaimFills itself in.

Worked out from this row's Miles and Rate Band.

The formulaIF(OR(ISBLANK([miles]), ISBLANK([rate_band])), "", ROUND([miles] * IF([rate_band]="Car or van up to 10k miles", 0.45, IF([rate_band]="Car or van over 10k miles", 0.25, IF([rate_band]="Motorcycle", 0.24, 0.2))), 2))
MonthUsed by the summary to pick out this month's journeys.Fills itself in.

Worked out from this row's Date.

The formulaIF(ISBLANK([trip_date]), "", TEXT([trip_date], "YYYY-MM"))

Expenses

8 columns

One row per purchase made for the business. Record what it was, the category and the amount you paid. Ready for 500 rows, every one with its formulas in place.

ColumnWhat goes in itWorked out as
DateType a date.
What It WasType any text.
CategoryPick one: Travel and Transport, Accommodation, Meals and Subsistence, Office Supplies, Equipment, Software and Subscriptions, Phone and Internet, Training, Client Entertainment or Other.
AmountType an amount in £.
Paid WithPick one: Personal Card, Personal Cash, Business Card or Bank Transfer.
Receipt HeldAnything marked No is worth chasing before you submit the claim.Pick one: Yes or No.
Claim StatusPick one: Not Claimed, Submitted or Reimbursed.
MonthUsed by the summary to pick out this month's spending.Fills itself in.

Worked out from this row's Date.

The formulaIF(ISBLANK([expense_date]), "", TEXT([expense_date], "YYYY-MM"))

Claim Summary

23 figures

The totals for your claim: miles driven, what the mileage is worth, and expenses split by category.

FigureWorked out as
Total Business Miles

The total of Miles on Mileage Log.

The formulaSUM([Mileage Log!miles])
Total Mileage ClaimMiles multiplied by the rate for the band on each journey.

The total of Mileage Claim on Mileage Log.

The formulaSUM([Mileage Log!claim_amount])
Miles Left Before The 45p Rate Drops

Worked out from Miles on Mileage Log.

The formulaMAX(0, 10000 - SUM([Mileage Log!miles]))
Average Claim Per Mile

Worked out from Mileage Claim and Miles on Mileage Log.

The formulaIFERROR(SUM([Mileage Log!claim_amount]) / SUM([Mileage Log!miles]), 0)
Journeys Logged

The number of rows on Mileage Log with Miles filled in.

The formulaCOUNT([Mileage Log!miles])
Mileage Claim This Month

Worked out from Month and Mileage Claim on Mileage Log.

The formulaSUMIF([Mileage Log!month_key], TEXT(TODAY(), "YYYY-MM"), [Mileage Log!claim_amount])
Total Expenses

The total of Amount on Expenses.

The formulaSUM([Expenses!amount])
Expenses This Month

Worked out from Month and Amount on Expenses.

The formulaSUMIF([Expenses!month_key], TEXT(TODAY(), "YYYY-MM"), [Expenses!amount])
Total To Claim BackMileage claim plus expenses.

Worked out from Mileage Claim on Mileage Log and Amount on Expenses.

The formulaSUM([Mileage Log!claim_amount]) + SUM([Expenses!amount])
Expenses Not Yet Claimed

The total of Amount on Expenses where Claim Status is “Not Claimed”.

The formulaSUMIF([Expenses!claim_status], "Not Claimed", [Expenses!amount])
Expenses Awaiting Reimbursement

The total of Amount on Expenses where Claim Status is “Submitted”.

The formulaSUMIF([Expenses!claim_status], "Submitted", [Expenses!amount])
Expenses Missing A Receipt

The number of rows on Expenses where Receipt Held is “No”.

The formulaCOUNTIF([Expenses!receipt_held], "No")
Largest Single Expense

The largest Amount on Expenses.

The formulaIFERROR(MAX([Expenses!amount]), 0)
Travel and Transport

The total of Amount on Expenses where Category is “Travel and Transport”.

The formulaSUMIF([Expenses!category], "Travel and Transport", [Expenses!amount])
Accommodation

The total of Amount on Expenses where Category is “Accommodation”.

The formulaSUMIF([Expenses!category], "Accommodation", [Expenses!amount])
Meals and Subsistence

The total of Amount on Expenses where Category is “Meals and Subsistence”.

The formulaSUMIF([Expenses!category], "Meals and Subsistence", [Expenses!amount])
Office Supplies

The total of Amount on Expenses where Category is “Office Supplies”.

The formulaSUMIF([Expenses!category], "Office Supplies", [Expenses!amount])
Equipment

The total of Amount on Expenses where Category is “Equipment”.

The formulaSUMIF([Expenses!category], "Equipment", [Expenses!amount])
Software and Subscriptions

The total of Amount on Expenses where Category is “Software and Subscriptions”.

The formulaSUMIF([Expenses!category], "Software and Subscriptions", [Expenses!amount])
Phone and Internet

The total of Amount on Expenses where Category is “Phone and Internet”.

The formulaSUMIF([Expenses!category], "Phone and Internet", [Expenses!amount])
Training

The total of Amount on Expenses where Category is “Training”.

The formulaSUMIF([Expenses!category], "Training", [Expenses!amount])
Client EntertainmentUsually not allowable against tax, so keep it separate when you file.

The total of Amount on Expenses where Category is “Client Entertainment”.

The formulaSUMIF([Expenses!category], "Client Entertainment", [Expenses!amount])
Other

The total of Amount on Expenses where Category is “Other”.

The formulaSUMIF([Expenses!category], "Other", [Expenses!amount])