How to figure out highest balance in my Monzo account for FATCA

Hello, I’m an American citizen so therefore fall under FATCA rules. If you don’t know what FATCA is, basically US citizens are required to declare any foreign bank accounts which have had at least $10,000 USD in them at any one point during the tax year. I’ve never fallen into this category but last year I undertook a property project so therefore have had some large sums coming in and going out of my Monzo account.

The question the IRS (US version of HMRC) wants to know is “What was the highest balance in this account at any time during the tax year?” I can get a bank statement for the year but since it’s split up by month, and even more confusingly by pots, it’s very hard to figure out at what point I had my highest balance.

Has anyone else fallen into this category and found an easy way to answer the question?

Export the statement as a spreadsheet.

Column J should be your local amount. In column U, which should be blank

Column U2 - =IF(D2=“Pot Transfer”,0,J2)

V2 - Type =U2
V3 - Type =$U$2:U3

Then drag that alllllll the way to the bottom, that’s your running balance.

In an empty cell in column W, doesn’t matter where, add

=large(U:U,1)

That will you get you your largest balance

2 Likes

Actually, this is probably easier

Add a new sheet in the spreadsheet.

In A1, write the first date of the financial year. In the cell below =A1+1. Drag that down

Then in B1

=if(A1<today(),sumifs(‘Monzo Transactions’!$J:$J,‘Monzo Transactions’!$B:$B,“<=”&C2,‘Monzo Transactions’!$D:$D,“<>”&“Pot Transfer”),0)

I would ask Monzo customer care to produce a letter with this amount on headed paper etc

1 Like

Letter on headed paper isn’t necessary for this. If I need to find max balance in a year, I do a variation on @Revels’s suggestion. I don’t use the ‘all time’ CSV export because with many years of transaction data I worry about a mistake/misalignment creeping in. So I:

  • export a CSV of transactions for just the period I’m interested in (1 Jan–31 Dec 2022 in @mattdell’s case)

  • add a ‘Balance’ column to the right of the local currency column

  • add a row at the top for initial balance, find out the balance at the beginning of the financial year I’m interested in (1 Jan 2022 in @mattdell’s case) using a PDF statement export, and I put this balance in as a number

  • then a simple formula to add the balance in the row above to the ‘Amount’ column in the current row and expand that down into all cells in the Balance column

Then all you have to do is select the ‘Balance’ column and your spreadsheet should show you the ‘MAX’ amount (in Numbers this is at the bottom of the window, not sure where Excel shows it).

Edit: @mattdell, you should be done then as the question doesn’t seem to ask when you had your max balance, just what it was (obviously in your case you’ll need to convert the :pound: number to :dollar:. The IRS provides a table of average exchange rates you can use for each year).

3 Likes

This looks promising but

  • For V3 it was always 0 so changed it to =SUM($U$2:U3)
  • I quickly get into the negative because at one point I have a transfer of £40,000 from a pot transfer and then I next do a £40,000 faster payments. Since we excluded “Pot Transfer” from U2 then I’m very quickly into a negative balance in column V
  • Then either way I get an even 10000 in column W which I don’t trust. :thinking:
  • I also think I might need a starting balance for this.

Your second suggestion looked promising but I can’t get that to work either. Column B is always 0. I did have to rewrite your answer to remove the and characters and replace them with "; same with and replaced with '. I’m not sure if there’s any more formatting issues causing the problem but it might help to put any more suggestions in a code block.

I’ll continue to try and get them to work. Thanks!

This was easiest for me!

  • Exported transactions
  • Added column next to local currency column
  • Added row at the top to add my initial balance from a PDF statement
  • Used formula =SUM(J3,L2) to get balance of first transaction after the initial balance and then dragged down to the end
  • Used formula =MAX(L:L) to get max value of column L (where my new Balance column is)

Sorted, thank you! :relieved:

2 Likes

The next problem is that if I use the Excel spreadsheet that only reflects the balance of my “account balance (excluding all pots)”.

My biggest account balance (excluding pots) was around £40,000 on the day I withdrew from my building project pot and then made a payment to my builder. I actually had nearly double that in my pot so of course my balance (including pots) is much more.

I’m fairly certain the IRS will want the value of my account including all my pots. Tricky. :thinking:

If you exclude pots, then you’re excluding the transfer to the pot, so that money effectively remains in your account as you can’t get to the pot without going through the account.

In the formula you got working, I’d add another one before it for “IF POT” to ignore those transfers.

I guess it depends if a pot counts as a separate account or not in the eyes of the IRS. Presumably there’s a reasonable argument to say that it is (e.g. the whole ‘money in a pot won’t stop you going into overdraft in your main account’), but I have no idea what defines an ‘account’.

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.