How to get account information for FBAR reports

TJTex

Has anyone figured out a quick way to get the account information from Quicken Classic Deluxe Canada in order to complete the FBAR reports? I am facing hours of just hunting through all my accounts to find the maximum value of each account for 2024 to report on FBAR and the 8298 forms.

Any suggestions would be welcome.

I am using the Quicken Canada Deluxe version.

Version: 27.1.60.18 (the most recent version)

NotACPA

Maybe this is a difference between QCan and QUS … but the FBAR report in QUS already includes the account name(s), the date of the maximum value in the account and the date of that value. REPORTS, Reports and Graph Center, "Maximum Account Value (FBAR)" in the Tax section of that dropdown.

Do you not already have that report … or is it showing something different?

Chris_QPW

The FBAR report is only available in Premier and above in Quicken US. I'm not sure if it is even in the Canadian version, but if it is I wouldn't expect it to be in the Deluxe edition, it would be in the Home & Business edition (The Canadian version doesn't have a Premier edition).

Arctic Hare

https://community.quicken.com/discussion/comment/20469934#Comment_20469934

I've never heard of FBAR or 8298 forms, so I presume these items are unique to USA. Also, here is a screen shot of the referenced section of reports centre in Quicken Classic Business & Personal Canadian.

TJTex

https://community.quicken.com/discussion/comment/20469934#Comment_20469934

@NotACPA Thanks but the Canadian version of Quicken does not have the FBAR report. So failing that, how can I find the maximum value in an account for the year? If you are familiar with FBAR and the 8938 forms, you know that they need the maximum value per account in the year and that includes investments as well as cash. Finding the maximum is always an issue without just poking around looking at an account's holdings and then value. It is frustrating for us US citizens living abroad.

NotACPA

@TJTex Well, for banking accounts (including cards) I suppose you'd need to just look at either/both the account register and bank statements.

The register MIGHT not be accurate though because when you write a check it'll usually take some time to clear .. so Q would have a lower value that the bank showed. No idea how significant that would be.

For investment accounts, my brokerage (Fidelity Investments) shows balances on any day that they change. But barring that, the only thing that I can think of is an Account Balances graph for last year done one account at a time to find the date with the highest balance and then writing that down.

For others than the OP, the FBAR report in Q US and IRS Form 8298 are to report to the IRS one's holdings in a Foreign bank/currency. Since @TJTex lives in Canada, there are presumably Cdn bank accounts involved. Most US taxpayers never need to worry about this.

But, DISCLAIMER, I'm not giving tax or legal advice. Look it up yourself to be sure.

TJTex

https://community.quicken.com/discussion/comment/20470524#Comment_20470524

@NotACPA Thanks for the help, but here is what I worked out using the Net Worth report:

The Canadian versions of Quicken do not provide the FBAR report. Instead you need to find the maximum value for each account during the year and use that to complete your FBAR and 8938 forms for IRS and the US Treasury.

Unfortunately, Quicken does not provide ‘Maximum Values’ for accounts. It can only provide an end-of-period balance. This can approximate the maximum value as long as you don’t have two transactions in one day that offset each other, like a deposit and a withdrawal. To get this approximation of maximum values for each account for FBAR and 8938 you need to get the end of day balance for each account in Quicken for the entire year and then use EXCEL to find the maximum value from that data. You can use the Net Worth report in Quicken. Quicken has a limitation of 250 columns for its reports. To get around that limitation you need to split the report into 6 month intervals.

In Quicken:

Select: Reports > Net Worth & Balances > Net Worth

This will bring up the Net Worth report and provide you with a view of your Net Worth as of today.

Select: Customize > Accounts

Check both the ‘Hidden Accounts’ as well as the ‘separate Accounts’ check boxes

You can also select the accounts you are interested in or just leave all the accounts.

Click OK

Select the ‘Interval’ Dropdown and select ‘Day’

Select Date Range dropdown and ‘Custom Dates’ at the bottom of the list.

Select January 1, YYYY from the calendar popout in the ‘From’ date

Select June 30, YYYY from the calendar popout in the ‘To’ date

Click OK

You will now have the first half of the year for daily ending values in each of your accounts.

Select ‘Export’ and click ‘Export to EXCEL Workbook .xlsx’ and provide a location and name for the file. I used a simple name like 24H01

Go back and change the Date Range to be July 1 to December 31 of that year.

Export that to an EXCEL Workbook. I named it 24H02

Note that you can save this report and then reuse it for the following year.

In EXCEL:

Combine both worksheets into one workbook.

On each sheet for each account row use the EXCEL function =MAX(firstcell:lastcell) to get the maximum value for that account for that half of the year.

Build a third sheet with all the names of the accounts and place the maximum value for the accounts in columns representing H01 and H02

Do the max function for each row for those two columns.

Those numbers will be the annual max for each account.

NotACPA

@TJTex Thanks for the detailed explanation. I'm going to suggest to the Mods that this post be stored as an FAQ, in case there are other Q-using ExPats that might need it.

BTW, I'm a former (LONG time ago) ExPat myself. From 1958-1972 my family lived in Milan, Italy.

Fortunately, a) I was a student and had no income then, b) while my Parents did, I had no foreign bank accounts & c) there was no FBAR or IRS 9838 back then.

Quicken Anja

Hello @TJTex,

Per NotACPA's request, I went ahead and moved your post to our Windows FAQ category which should make this easier to find for others.

Note that moving your post here also results in your post being closed as all FAQ posts are closed to further comments to preserve the content.

Thank you!