AEOI Hub training module 3 – Accounts data

Hello and welcome to module three of the
AEOI Hub reporting templates training course. This module will be looking at
accounts data in detail. The AEOI Hub reporting templates are macro enabled
Microsoft Excel templates which can be used to generate reports for FATCA and
the Common Reporting Standard ready for submission to the tax authorities. Easy
to use and with no additional software to install. Data is imported, validated
and reports produced in both the correct format for submission to the tax
authority and also in PDF format suitable for management review. If you
don’t already have a copy of the AEOI Hub template you can sign up for a free
trial at This module will cover; Entering accounts data – taking you
through the different methods by which you can enter account data for Financial
Institutions. Validation checks – looking at the validation checks built into the
template that are performed and we will also look at Passive NFEs and how
we assign controlling persons to those entities. Okay let’s start having a look
at how we enter accounts data. Ok so let’s take a look at the accounts data
worksheet. For each Financial Institution that you have in your template you have
a separate accounts data worksheet that looks like this. We have the tool bar up
here which has a number of buttons to trigger
particular actions and then you have the headers and then from here onwards where
you enter the details of the accounts. There are three main ways you can get data
into here, you can use it very much like a spreadsheet, you can use the account
details forms or you can use the accounts data import function. I’m not
going to show you the accounts data import function on this video as there is a
separate video that will take you more through how you set up that and run it
but I’m going to show you on here the first two using it very much like a
spreadsheet and using accounts details forms. So as I mentioned,
you can very much use this like a spreadsheet, enter details here of your
accounts and just work your way across from left to right entering details
about the balance, the income, the account holder type etc and you’ll notice that
as you start to make selections here the cells that are not relevant for this
particular account become greyed out. So for example where I’ve selected
Individual, the field for Organisation name is greyed out because it’s not
relevant. So you can work your way across there and we’ll show you in more
detail in a moment the various sections. You can work your way across there and so forth, the next account you put on the next row and so on and
you can you can work your way across in that manner. So that’s using it very much
like a spreadsheet and that’s absolutely fine. The other way that you can enter
data and edit data in here is using the account details form. Now the way that
works is that you click on any row that you want to edit data for and use this
pencil button and that opens up the Account details form and that has multiple tabs in here, one for each particular area of the account. Also
notice our standard approach to the layout of these screens, we have the Help
button which will take you to the relevant page of our online help about
account details, the Update button which will save changes that you’ve made in
here and close the screen returning to the spreadsheet or the Cancel button
that will close this screen without making changes, without saving what
you’ve changed. I’m just going to cancel for now because I want to take you to a
fresh row where details haven’t been entered yet and just talk you through
all of the key details that are in there. So if I click on
this, see here I’ve got to start with five tabs; Basic Account Info, Financial
Information, Account Holder, Address Details and Tax Information. So the first
screen, Basic Account Info is where you enter the account number and we also
have some other optional inputs. One thing to note as you work through
these screens, you’ll notice that anything that’s required, there is an
indication of that. So the account number is always required. If you have an
account number, or if you don’t have an account number you need some form of
unique identifier or identifier that is appropriate and it’s recommended that
you check the detailed guidance for the particular country that you’re
filing for in relation to. As a general rule, where something is required it will be indicated like that. The other inputs on here are optional so the
account number type, of which these are the permitted entries – International Bank
Account Number, International Securities Number etc that’s optional. When you
select IBAN or ISIN it will also check the account number format. In this
case that’s not relevant so I’m just going to get rid of that. The account type is an optional input and in almost all cases it’s not required by the country
in which you’re submitting, however there are a small number where it is and we
recommend that you check the details of the country you are submitting in when you
think that might be the case, but also in here it will indicate if that is the
case because we know based on the country whether it’s required. These are
the permitted options where you do select the account type then that has an
impact on the Financial Information because for certain account types there are
particular categories of income which won’t be relevant, for example for a
Depository account the categories of Dividends and Proceeds won’t be
relevant so they’ll be disabled when we come to look at
Financial Information. There are also two flags here, to denote if the account had
been closed during the year (and that’s relevant for both FATCA and CRS) and if
the account was dormant throughout the year (and that’s relevant just for for
CRS). Let’s move on to the Financial Information tab. Here, again you can see
it’s indicated where a field is required. The account currency you have a
drop-down list of all of the relevant currencies here. See that if I select GBP it’ll
also show you here, so you can double check for what you’ve selected, the long name for that currency.
Account balance is always required and it will check on here that
you put a relevant and genuine permitted value. It’s always required so if it is
zero then you can put zero for that and also if it the amount is negative that’s
fine, you can enter negative because when it comes to the reporting we will
automatically turn that into zero which is the requirement of the submission
file for both FATCA and Common Reporting Standard. You then have fields
here for Interest, Dividends, Proceeds from sale or redemption of property
and Other income. These are for the total amounts that credited or paid to
the account in the year. One thing you’ll note, as I briefly mentioned in
the Basic Account Info screen, is that if you select a particular account type
then some of these categories will not be relevant and will be greyed out and
not editable. That’s just something to look out for there. The next screen, Account holder. The
required input here, to start things off, is the Account Holder Type. You select
either Individual, Active NFE, Passive NFE, or ODFI or NPFFI (only
relevant for FATCA purposes) when you select this it will then activate and
configure the other inputs, so if I’ve got an Individual then the First name
and Last name are required, Title and Middle name are optional. If I had selected
an Active NFE or Passive NFE then these inputs wouldn’t be relevant, it would warn me of that fact and say I need the Organisation
name instead. So all of these warnings that you’ve seen on screen are all live
at the point that you make inputs so if you change things, all these warnings get
updated live. So the next tab, the Address Details is required and as a
general rule you either enter addresses in what we call ‘fixed’ format, so you
enter the building, the street, the city etc or as ‘free format’ which is a single
field where you just enter the address as you have it. The way you
enter addresses using fixed field formats, you won’t necessarily need to
complete all of these inputs, you just complete the ones that are relevant and
some of these are going to be quite rare so suite, floor and POBox you may
never need to use those, you may never actually have a PO Box address or a
floor indicator in the data that your system holds but the idea is you fill it
this in based on what you have. So for example if you have a building and it’s
on a street and it’s in a city then you complete
those and the country is always required so you need to select an ISO code. You
have the drop-down list of everything that’s relevant, it will warn you if it’s
not relevant, it will also show you the long jurisdiction country name as well to
assist you. The next tab on here is Birth Information, this is only relevant for an
Individual and the key here is the date of birth. We have logic within the template that checks if a date of birth is required and
generally date of birth is required for Common Reporting Standard in virtually
all jurisdictions. It is also required for US FATCA purposes where you don’t have a
Tax Identification Number for the account holder. I would recommend you check
with the country that you’re submitting in to be sure of the situations where
you need the date of birth. So I can put this in here and when I enter date of
birth providing that I enter a valid date in a date format it will also show
me in long format there to avoid confusion where people may be
entering the date as day month or month day and to avoid confusion in that case,
so here’s the 4th of October 1970. Place of birth inputs in many cases are not
required so entering the city of birth and the country, however again as
with all these things we recommend that you check the requirements with the
particular country that you’re filing in to see if that’s required. If it is
required then you put in here the city and the country of birth. Either
select the country of birth from the ISO list or in the case of former countries
that aren’t on the ISO list, i.e. countries that existed many years ago you enter it
there as a free form text. Now notice the warning that’s coming up here
because I’ve started using these inputs, it knows I need the city and the
country. I’m just gonna remove that so I don’t need that in this case. The final tab
on here, Tax Information is where you enter the jurisdictions of residence, so
these are the countries/the jurisdictions that you’re reporting this
account holder for, where they’re tax resident and there are up to three here
that you can use. In the templates we cater for dual residents and
and additionally a third jurisdiction of residence if required. So if someone was
US reportable for FATCA you would put that in there, you also enter the tax
identification number. Notice that we have a check on the format so if the
format’s not valid then we provide that check and once I enter a valid format
in that will be removed. The issuing country, in almost all cases you don’t need to enter anything because by default it’s assumed
that that is the same as the jurisdiction of resident, so for example we’ve got a
US account holder here with the US TIN, the country that’s issuing that TIN
is the US and it’s only in very rare circumstances that you need to select
something there, so in most cases leave that blank. As I mentioned you can have a
second and third jurisdiction of residence, so if this individual is also
UK resident, again enter the jurisdiction and the TIN. If they
don’t have a TIN on record, so if you don’t hold a Tax Information Number for that
individual you can leave that blank, although I would check the position with
the country that you’re reporting in to check if that’s permitted but if it is
we will proceed and create the report accordingly. There may also be a
check on the date of birth that we provide in here to ensure it’s required.
I’m going to go ahead and click update that’s going to update the details for
me on this screen here on the spreadsheet version. So you can see what
we saw in the accounts details form is replicated here on the sections in the
spreadsheet view, so Basic Account Information, Financial Information etc. If I
scroll across to the right, Account Holder/reportable person details, Address
details, Birth Information and then Tax
Information, a jurisdiction at a time, one, two, three. Something you will note is the
toolbar is now not visible, to get that visible again click on this button here
and that will Centre the the toolbar. One of the most important buttons on this
toolbar is the Validate button. This Validate button will run validation
checks on all of the data in the file and it returns you with a list of all
of the errors that are found. So I’ll click on that button. It’s actually
found quite a few errors in this case, if you click on OK
it will take you to the errors worksheet. The errors list here can be downloaded so
you can see all of these errors in a PDF or separate Excel format and it also
groups by type. You get an error code for each type of error in here
relating to in some cases as submitter and setup details but all of the data
that’s relevant for accounts or any accounts errors have the ACE prefix so ACE then a number indicates it’s an account error. So for fields required, I can see here First name, Last name, we require the city of address I
missed out. See I’ve got quite a few errors here because I’ve created a few
accounts quickly without really putting much detail in so that’s why I’ve got
the details here, it’ll show me the the row number on the report that’s
relevant and the account number and if there’s something that I have entered
that there’s a problem with it’ll also show me that. If you want to see a full
list of all the error codes go to the help page. Also on this list of errors, for each of
the errors there is a hyperlink back to the cell that’s relevant, so if I were to click on
this First name is required it would take me back here to the the cell that’s
relevant. It’s indicated in yellow to show me that there’s an error. Another
feature on the toolbar that’s useful and worth being aware of is the recycle
function so this recycle button here will clear all accounts data from this
worksheet. If I select that it’ll effectively clean up this worksheet so I
can start again and start afresh. The next area I wanted to show you on this
video is how we enter details of Passive Non-Financial Entities and allocate
controlling persons to those. So what I have here to start with is a Passive NFE
that I’ve entered details of and if I were to look into the accounts details
we can see that I’ve entered details about the Account Info, Financial Info,
the Account Holder is set as a Passive NFE. I’ve got the address details all
done. What I want to now do is associate controlling persons. I can do
that in one of two ways; I can either ensure that I enter details
of the controlling person immediately beneath the Passive NFE (because that’s
how it works), in which case I need to ensure that the row immediately beneath
that account I’ve selected a controlling person. Select either an Individual or
an Organisation and go ahead like this and enter the details.
Alternatively what I can do is click anywhere on the Passive NFE’s row with
that account and click on this button which is Add a controlling person. What
that will then do is open up the Controlling Person/Substantial Owner
details form. So Substantial Owner is the FATCA term, Controlling persons for CRS,
but we use the same inputs for both. Select Individual or
Organisation. Organisation is only relevant for the FATCA substantial owner. Individual
for a CRS controlling person. Then you enter details of the individual. Address
details, enter birth information if it’s required (for example if the if the date
of birth is required) and then the Tax Information. Tax Information is
about identifying the jurisdictions of residence that are relevant for Common
Reporting Standard or US if it’s relevant for FATCA. So if in this
case I had a individual who’s UK resident I would put in their UK
Tax Identification Number and click on Create. What that’s going to do is add
the controlling person row beneath that Passive NFE. If I wanted to add further
individuals I can do and I just go back to the Passive NFE row, click on the
add controlling person and I can continue like that. Note that I don’t need to at this stage
have all of the details to hand because I can click on Create, it will warn me
that there’s missing incomplete or invalid data, but I can choose to Fix
Later. What that means is that I can get the row created, I can
come back and I can deal with the missing or incomplete data later. So what
this has done, it’s indicated I have a Passive NFE with two controlling persons
and if I were to run the validation checks just to tidy up this form and to
see the checks. I do have error checks because there are details on the
controlling person that are still required. If I go back to the accounts data, what I
just wanted to show on here as well is that at the left hand side of the screen
the account has been given an internal numbering of number one and the
controlling persons are listed underneath that. So this is account
number one, controlling person one, account number one, controlling person two etc and if I had a second account that would be account two with potentially account two, controlling person one etc and that’s the way that we we deal with them
in the AEOI Hub reporting template. That concludes this training module. We hope
you found it useful, if you have any questions please contact our support
line on the telephone number on screen or email [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *