After our recent Excel worksheet for visitors to Europe we were asked for a similar one to help people stay onside with the UK Statutory Residence Test (SRT).
This is a test to decide if someone is a UK resident for tax purposes. Depending on the situation, a person can be in the UK for between 15 and 183 days in a tax year.
If you’re hopping in and out of the UK, that can get complicated. Some people accidentally overstay and get a whopping tax bill.
The worksheet lets you enter your itinerary, allowed UK days and the tax year. It’ll work out whether you’re inside the legal limit or not with days remaining or overstayed.
Full instructions and notes are on the worksheet ‘Instructions’ tab.
Download
Download UKresidencyCalculation.xlsx
It’s an Excel ‘macro free’ xlsx file with no external data connections. Naturally, we’ve checked it for viruses etc. but you should NEVER take anyone’s word for that <sigh>.
How to use
Open the worksheet in the Excel of your choice.
All the main stuff is on the UK residency calc tab.
The important cells to change all have a yellow background.
Allowed days
Check with your accountant for the number of days you’re allowed to stay in the UK. It can be between 15 and 183 days.
Allowed days: how many days you’re allowed to be in the UK during the financial year.
In the tax year starting: enter 2018 for the 2018/2019 tax year, 2019 for 2019/2020 etc.
Your Itinerary
Enter your movements over time. They can be outside the financial year in question, the worksheet will figure out the number of days relevant.
Location – just a name for the country or area you’ll be in. Type in ‘UK’ and the UK or Not column will change automatically.
UK or not – either Yes/No but will be changed automatically. Yes if Country = ‘UK’, No if Country is anything else.
Date In / Out – enter the date into and out of that location. You can link the Date In to the previous Date Out to ensure you don’t miss any dates.
The result
Look below the table to see if you’re ‘safe’ or not.
The columns ‘UK Days’ , ‘Non UKDays’ and ‘UK days in chosen tax year’ will help you figure out where the problem is or where you can stay longer!
The worksheet calculates days as ‘midnights’ in the same way that HMRC uses i.e. a simple subtraction of Date Out from Date In.
Compatibility
In short, any modern Excel should open the worksheet. We’ve used fairly simple Excel features for maximum compatibility and easy understanding (if you’d like to tinker with the worksheet).
The worksheet was made over time in Excel 2016 for Windows and Excel 2016 for Mac.
It’s also been tested with Excel Mobile for Windows 10, Excel app for Android, Excel app for Apple iPad/ Iphone and Excel Online, the browser based version of Excel.
Under the hood
See ‘Inside the Schenghen Visa Calculation worksheet’ for details on the Excel features we used.
