Keeping detailed records of business mileage is crucial for both accurate tax deductions and expense tracking. The HM Revenue & Customs (HMRC) allows you to claim mileage allowance for business travel, but you must maintain precise logs to support your claims. Using an HMRC-approved mileage log template can streamline this process, and Excel, with its versatile features, is an excellent tool for creating such a template. Let's explore how to create and use an HMRC-approved mileage log template in Excel.
Before diving into the template creation, familiarize yourself with the HMRC's approved mileage rates. For cars and vans, the rates are 45p per mile for the first 10,000 miles and 25p thereafter. For motorcycles, it's 24p per mile, and for bicycles, it's 20p per mile.
Creating the HMRC Mileage Log Template in Excel
Start by creating a new Excel workbook and naming it "HMRC Mileage Log Template".
Here's a simple and efficient layout for your template:
Setting Up the Header
In Row 1, enter the following headers: "Date", "Start Mileage", "End Mileage", "Trip Purpose", "Destination", "Mileage Allowed (p)", "Actual Mileage (p)", and "Total (£)".
Format these headers as you see fit, using bold font, background color, or freezing the top row for easy navigation.
Adding Formulas for Calculations
In cell B5 (End Mileage), enter the formula "=B4+D4" (assuming your start mileage is in B3). This will automatically calculate the end mileage based on the start mileage and the journey distance.
In cell F5 (Mileage Allowed), enter the formula "=D6*0.0045". This calculates the allowable mileage based on the 45p rate for the first 10,000 miles. You can adjust this based on the vehicle type.
Populating the Template with Data
Each time you undertake a business journey, add a new row and fill in the details: date, start mileage, end mileage, trip purpose, and destination. The template will automatically calculate the allowed and actual mileage, as well as the total claim amount.
Regularly update your template to keep your mileage records current and accurate.
Tracking Different Vehicle Types
If you use multiple vehicle types, consider adding a "Vehicle" column. You can then apply different mileage rates based on the vehicle type used for each journey.
For example, if you use a bike, you might multiply the distance by 0.02 in the "Mileage Allowed" column instead of 0.0045 for cars.
Exporting Data for Tax Purposes
At tax time, you can export your data to a CSV or Excel file and use it to support your mileage claims. This ensures you're only claiming what you're entitled to, and you have accurate records to back up your claims.
It's essential to keep your records for at least 6 years after submitting your Self Assessment tax return, as HMRC may request them within this period.
Regularly maintaining your HMRC mileage log template in Excel will save you time and effort during tax season, ensure you're keeping accurate records, and help you claim the full allowance you're entitled to.