Excel Formula for Mileage Reimbursement

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for mileage reimbursement is =B2*C2 when column B holds miles driven and column C holds the reimbursement rate per mile. For example, with 150 miles in B2 and a rate of $0.67 in C2, the formula returns $100.50. For a full workbook, you can sum all trip amounts with =SUM(D2:D10).

Why You Need a Mileage Reimbursement Formula

Mileage reimbursement is a standard business expense: an employee or contractor drives a personal vehicle for work, and the employer pays a set amount per mile. The IRS sets an optional standard mileage rate that many companies adopt (for 2025, the business rate is $0.70 per mile, but your employer may use a different figure). Manually multiplying miles by rate for dozens of trips is error-prone, so a spreadsheet formula automates the math and creates a clear audit trail.

Setting Up Your Spreadsheet for Mileage

Before writing formulas, lay out your data in a consistent table. A good structure has one row per trip or per day, with columns for:

  • Date (A)
  • Purpose or client (B)
  • Starting odometer (C)
  • Ending odometer (D)
  • Miles driven (E) – computed with =D2-C2
  • Rate per mile (F) – either typed per row or referenced from a single cell
  • Reimbursement amount (G) – computed with =E2*F2

If your rate is the same for all trips, put it in one cell (e.g., H1) and use an absolute reference: =E2*$H$1. This lets you update the rate once and have every row recalculate.

Step-by-Step: Basic Mileage Reimbursement Formula

Assume your sheet has miles in column B and rate in column C, starting at row 2. Here’s how to build the formula:

  1. Click cell D2 (where you want the reimbursement).
  2. Type =B2*C2 and press Enter. Excel multiplies the miles by the rate.
  3. Drag the fill handle (the small square at the bottom-right of the cell) down to copy the formula to other rows.
  4. To total all reimbursements, click an empty cell and type =SUM(D2:D100) (adjust the range to match your data).

If you have a fixed rate in a separate cell, use =B2*$F$1 where F1 holds the rate. The dollar signs lock the reference so it doesn’t shift when you copy the formula.

Using Odometer Readings Instead of Miles

If you log start and end odometer readings, compute miles first. With start in C2 and end in D2, enter =D2-C2 in E2. Then in G2, use =E2*F2 (or =E2*$H$1 for a fixed rate). This two-step approach is clearer and reduces data entry errors because you don’t have to calculate miles by hand.

Handling Different Rates (Personal vs. Business)

Sometimes you have multiple rates—for example, a higher rate for business miles and a lower one for moving or medical miles. You can add a column for the rate and use an IF formula to pick the right one. Suppose column B has miles, column C has a category (“Business”, “Medical”, “Moving”), and you have rates in separate cells: F1=0.70, F2=0.21, F3=0.21 (medical/moving are often lower). In D2, enter:

=B2*IF(C2="Business",$F$1,IF(C2="Medical",$F$2,$F$3))

This checks the category and multiplies by the corresponding rate. For a more maintainable approach, create a lookup table (e.g., in columns I:J) and use VLOOKUP or XLOOKUP. With categories in I2:I4 and rates in J2:J4, the formula becomes =B2*VLOOKUP(C2,$I$2:$J$4,2,FALSE) (XLOOKUP works in Excel 2021 and Microsoft 365: =B2*XLOOKUP(C2,$I$2:$I$4,$J$2:$J$4)).

Common Problems and How to Fix Them

Three issues trip up most users:

  • #VALUE! error – Usually means a cell contains text instead of a number. Check that miles and rate cells are formatted as numbers, not text.
  • Wrong totals – If you copied a formula down but the rate cell reference moved, you might be multiplying by an empty cell. Use absolute references ($H$1) for fixed rates.
  • Rounding – Excel may show many decimal places. To round to cents, wrap the formula in =ROUND(B2*C2,2).

Also, if you use odometer readings, make sure the end reading is always greater than the start; otherwise you’ll get a negative number. Add a simple check with =IF(D2>C2,D2-C2,"Check odometer").

Comparing to a Mileage Log Template

Excel offers built-in mileage log templates (File > New, search “mileage log”). These templates often include the same columns and formulas pre-built, saving setup time. However, they may not match your company’s required format. Building your own gives you full control and ensures the formulas are transparent. A custom sheet also lets you add extra columns for project codes or vehicle numbers.

Frequently Asked Questions

How do I calculate mileage reimbursement in Excel with a fixed rate per mile?
Put miles in one column and the rate in a single cell (e.g., H1). In the reimbursement column, enter =B2*$H$1 and copy down. The absolute reference keeps the rate constant.

Can I use a formula to sum all mileage reimbursements for a tax deduction?
Yes, use =SUM(D2:D100) to total the reimbursement column. For tax purposes, keep your log and receipts; the IRS standard rate is optional, and you may need to consult a tax professional about what you can deduct.

What if my mileage rate changes mid-year?
Add a column for the rate per trip, or split your data by rate period. Then multiply miles by the applicable rate in each row. Avoid mixing rates in one formula unless you use an IF or lookup.

How do I handle cents in the reimbursement amount?
Use =ROUND(B2*C2,2) to round to two decimal places. This prevents long decimals and matches typical payment amounts.

Does this work in Google Sheets?
Yes, the formulas are identical. In Google Sheets, use the same multiplication and SUM functions. The main difference is the menu path for formatting, but the math is the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.