HMRC mileage log spreadsheet: approved rates, the 10,000-mile threshold and the formula

Published 19 October 2026 · 7 min read

An HMRC mileage rates spreadsheet needs three parts: a log with one row per business journey (date, reason, start and end postcodes, miles), a Rates tab with the approved mileage rates and the date you read them on GOV.UK, and a formula that applies them per employee and per tax year. For cars and vans in 2026-27 that means 55p for each of the first 10,000 business miles and 25p for each mile after. The GOV.UK sources below were checked on 19 and 20 October 2026; the example is fictitious.

What HMRC expects in a log

The GOV.UK guide "Claim tax relief for your job expenses: vehicles you use for work" asks employees to "keep records of the dates and mileage of your work journeys" and to add up the mileage for each vehicle type. When they claim, the mileage logs sent to HMRC must include the reason for every journey and the postcodes of its start and end points, with a separate log for each employment. Travel between home and the usual workplace is excluded, "unless it's a temporary place of work" (consulted 19 October 2026). For an employer, the log is the evidence that what was paid stayed within the approved amount, so the same detail is the safe standard.

The approved amount is worked out per tax year, which runs from 6 April to 5 April: "the last tax year started on 6 April 2025 and ended on 5 April 2026" (GOV.UK, Self Assessment deadlines). A limited company must keep its records "6 years from the end of the last company financial year they relate to", and can be fined £3,000 for not keeping accounting records (GOV.UK, "Running a limited company: company and accounting records", both consulted 19 October 2026).

The columns, with the letters used in the formulas below: date (A), business miles (B), miles already done in the tax year (C), claimant (D), tax year (E), passengers (F), then vehicle type, start and end postcodes, reason, approved amount, amount paid, difference and approver.

Approved mileage rates, with their date

The rates come from one GOV.UK page, "Travel – mileage and fuel rates and allowances", updated 21 May 2026, which has one table for 2026-27 and one for tax years 2011 to 2026. The employer guide "Expenses and benefits: business travel mileage" puts it as "55p from 6 April 2026 (45p before 6 April 2026)".

Keep these in a Rates tab with the page name and the date read, and point every formula at it. Pay more than the approved amount and you must report on form P11D and add the excess to the employee's pay, taxed as normal. Pay less and there is nothing to report; the employee can claim Mileage Allowance Relief on the difference. And if an employee uses more than one vehicle in the year, "it's all calculated together". National Insurance differs: the guide's "Rules for National Insurance" page applies 55p to every business mile in the earnings period, with no 10,000-mile step; only the excess goes into earnings for Class 1 (consulted 20 October 2026).

The 10,000-mile threshold in Excel

For a whole tax year, with the year's business miles in B2 and the two car rates in Rates!B2 (first 10,000) and Rates!C2 (over 10,000):

=MIN(B2,10000)*Rates!$B$2+MAX(B2-10000,0)*Rates!$C$2

A log needs the split per journey, because the journey that crosses the threshold is paid at two rates. With the journey's miles in B2 and the claimant's business miles already done in the tax year in C2:

=(MIN(C2+B2,10000)-MIN(C2,10000))*Rates!$B$2+(B2-(MIN(C2+B2,10000)-MIN(C2,10000)))*Rates!$C$2

The first bracket is the part still under 10,000 miles. A 300-mile journey made when the claimant already has 9,900 business miles gives 100 miles at 55p and 200 at 25p: £55 plus £50, so £105.

C2 should be calculated, not typed. With the log sorted by date, the sum of the earlier rows for the same claimant and tax year is:

=SUMIFS(B$1:B1,D$1:D1,D2,E$1:E1,E2)

The tax-year column takes the start year from the date in A2, =IF(A2>=DATE(YEAR(A2),4,6),YEAR(A2),YEAR(A2)-1), which returns 2026 for any date from 6 April 2026 to 5 April 2027. If you start the workbook mid-year, add each claimant's business miles since 6 April as an opening balance, or the threshold will be reached too late.

Passengers and other vehicles

An employer can pay 5p per passenger per business mile "for carrying fellow employees in a car or van on journeys which are also work journeys for them". HMRC's Employment Income Manual, EIM31405 (updated 16 October 2026), specifies that "the same mile travelled may therefore be counted more than once if more than one passenger is carried simultaneously". In the log, with passengers in F: =F2*B2*Rates!$B$6, the 5p in its own cell.

Motorcycles (24p) and bicycles (20p) have one rate for every mile, so no threshold. Electrically assisted pedal bikes take the bicycle rate, other electric bikes the motorcycle rate. Company cars are outside this log: HMRC's advisory fuel rates, reviewed quarterly, "only apply to employees using a company car" (GOV.UK, "Advisory fuel rates", updated 21 August 2026).

What to keep as evidence

1. The log itself: date, postcodes, reason and miles for every journey, sorted by date.
2. The Rates tab with the GOV.UK page name and the date read.
3. The approval: who checked the month, and when.
4. The payment record, matched to the approved amount.
5. The vehicle type declared by each claimant.

A limited company keeps it for the six years required of company records. A monthly total of miles is not a log.

Example (fictitious data)

An employee drives 12,000 business miles in her own car between 6 April 2026 and 5 April 2027, 2,000 of them with one colleague on the same business trips.

Staff driving in the United States are reimbursed on a different basis: see the IRS mileage log template. Two other workbooks follow the same principle: a 2027 team leave planner (UK) and an IT contract register.

FAQ

Does the 10,000-mile threshold start again each tax year?

Yes. GOV.UK sets the rates for the "first 10,000 business miles in the tax year", so the running total restarts on 6 April; the 2026-27 tax year runs from 6 April 2026 to 5 April 2027.

Can a company pay 55p on every mile, including those over 10,000?

It can pay what it decides, but anything above the approved amount must be reported on form P11D and added to the employee's pay, with tax deducted as normal. The log's job is to calculate that excess exactly: here, 30p per mile over 10,000. For Class 1 National Insurance, 55p applies to every mile, so no National Insurance is due on that excess.

Does a mileage log really need postcodes?

For an employee claiming Mileage Allowance Relief, yes: GOV.UK says the logs sent to HMRC must include the reason for every journey and its start and end postcodes. An employer's log backs every payment and any P11D entry, so the same standard is safest.