UK residency or Statutory Residence Test (SRT) – an Excel worksheet


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

uk residency or statutory residence test srt an excel worksheet microsoft excel 23130 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet

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.

uk residency or statutory residence test srt an excel worksheet microsoft excel 23131 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet

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.

uk residency or statutory residence test srt an excel worksheet microsoft excel 23132 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet

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.

uk residency or statutory residence test srt an excel worksheet microsoft excel 23133 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet

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.

uk residency or statutory residence test srt an excel worksheet microsoft excel 23135 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet

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.

subs profile e1563205311409 - UK residency or Statutory Residence Test (SRT) - an Excel worksheet
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address