Spreadsheets for tax reporting of overseas dividends and stock trades

[ethzz]

Someone asked about this privately, and in order to share, and perhaps learn something myself (eg, improvements), here are the column headings in a couple of spreadsheets I use in my tax reporting. Here's the one for (1) trades, and another for (2) dividends. (There's one more for interest, but it's almost the same as for dividends.)

Tho they have evolved a little, these are what I've used for years, and they are based on some rather important discussions I had with the tax people (was audited).

First, the one for trades. Maybe not really necessary, but in column A, I simply number each item. Column B is ticker symbol (not full name). Column C is what I use to organize the list, and this begins with (eg) January and proceeds down thru the year. Column D is comparatively mixed up, since purchase dates can vary a lot. Columns E and F are the purchase and sale amounts. (Column G, ...I think this one can be eliminated, I don't think I've used it.) Column H is only there for me (with a total at the bottom) so that I can see how that compares to the eventual yen total.

Columns I and J are the TTM rates for the sale and purchase dates. Those, respectively, are used with a formula to produce amounts in yen in columns K and L. Column M is profit/loss in yen.

For the tax return, the important numbers to input, are the totals at the bottom of columns K and L. On the return, this will then generate the number/total at the bottom of column M, a way to confirm if things are unfolding properly on the tax return.

The sheet for dividends is comparatively simple--only one date to deal with. This one is also organized by transaction date (B), and A is simply item numbers, C is ticker symbol. Dividends are listed in dollars (D), converted using the TTM rate for that pay date (F), and a formula in (G) results in a yen amount, again total at the bottom.

I haven't used columns E and H for a year or two. These are there for (in my case) ADRs or foreign stocks where a third country would withhold some percent of a dividend being paid. These amounts (in total, IIRC) are listed/claimed on a tax return here, but there's only a minor bit of detail needed--one or two names/tickers, and then "etc".

So that's it. Suggestions? Questions? Of course, since these sheets are attachments to your tax return, add a header with all identifying bits possible. Name, address, contact phone, tax year that the sheet is for, tax number, MyNumber, and so on.

This isn't too hard, just bookkeeping, and I do like that it makes me aware of what has happened. Eg, for trades, it's useful to see how P/L comes out in dollars and then yen. There are also some other occasional 'insights' ("Boy, why did I buy and then sell that, like that!?!).

 

[shilpa]

@ethzz Very good. I notice there is no calculation for platform fees, maybe there aren't any but for example for my investments in the UK there is a platform charge of 0.45% that I feel I should be adding to the spread sheet as a cost of holding.

 

[ethzz]

Something I should have added is the site I use for TTM rates--三菱UFJ. I do this manually, but once used to that site, it's fairly quick.

That site was suggested by the tax office, but I don't think it's in any way their 'official' site. I think the main thing is to use something publicly accessible/accepted, and to use it consistently. (Not sure if a J-bank would be preferred over a euro/US source for TTM.) I've never listed this site on my spreadsheets as TTM source, I guess that could be a footnote.

One possible improvement to the above sheets would be to set them up so that TTM rates would be fetched automatically. I've seen comments to this effect in the past, and tho that was explained, that would require me to expand my excel competency, and I've never tried to set it up. (I don't think the UFJ site would be susceptible to that kind of scraping.)

 

[dvidxpsyko]

@ethzz

Spreadsheets for tax reporting of overseas dividends and stock trades

.t3_rlev2u ._2FCtq-QzlfuN-SwVMUZMM3 {

--postTitle-VisitedLinkColor: #979798;

--postTitleLink-VisitedLinkColor: #979798;

}

Tax

I've been using the BOJ 9:00AM rates whose time series can be easily downloaded. http://www.stat-search.boj.or.jp/ssi/cgi-bin/famecgi2?cgi=$nme_a000_en&lstSelection=FM08

Is there some law which says TTM rates must apply?

 

[manishk012]

@ethzz Do you always buy and sell 1:1? I don't see any cost basis calculation in your spreadsheet.

 

[ethzz]

@manishk012

Do you always buy and sell 1:1?

Effectively, yes.

 

[zinga327]

@ethzz Thank you for sharing. This is quite helpful. If you can share any details at all, I am quite curious to know why you were audited and how was the whole process and experience with the tax office - a hassle or quite straightforward and smooth.

 

[joeychannel]

@ethzz I am curious, is this for trades made on a US based brokerage (like Fidelity or Robinhood)?

Or are these spreadsheets for trades you are making on Japan-based brokerages of foreign stocks? Hence the need for a TTM.

As a side note, were you audited just because of the stocks/investments? Perhaps this was in a different thread that I have missed. But more details about the general process and what you learned from the tax office would be insightful.

 

[ethzz]

@joeychannel For trades via a US brokerage (as US I don't trade in Japan).

Why the audit? --I think it was that I brought some money in, perhaps didn't give the right answer when asked about it(?), tho a reason was never given. They seemed to know a lot about me and my financials, even a US property sale that fell just outside the five year scope of the audit (that I hadn't reported here). I suspect they had full access to my US tax filings (there's no other way they could have known). I was honest and open, and tho there were some taxes and penalties at the end, they were nice enough people to deal with. Oddly enough, the following year Clapton/Winwood came thru and the lead guy happened to be there, too--row in front of us and a couple seats off. We nodded in recognition, but didn't talk. Fate must have been continuing on its path of bringing us together... (or Fate's attempt at some humor?)

Anyway, I started filing my US investment activity to their standards, and then they checked/reviewed me again three years later. No problems that time, but they even found some foreign tax that I hadn't claimed and revised a return to reflect that. The first time was '08 or 09, the second review '11 or 12. I haven't heard from them since, but wouldn't be surprised if I got a call.

Tho I was just a lowly foreign english professor, one distinct impression was that these folks were pros. Emphasis on details and being thorough and complete. Nothing keystone cops about them at all. Tax (and people like me) is their job, profession, training/education, experience, and so on.

 

[mindy1234]

@ethzz After cashing out a life insurance investment and receiving it in Japan, I received a form from the tax office asking about it, sent it back and never heard from them again. Gains weren't much though.

 

[preppednsaved]

@ethzz what order of magnitude (10s,100s,1000s 万 $10s, $100s 1m ) was the amounts audited?

 

[gdpotter54]

@ethzz Is it necessary to do bookkeeping like this to report? I was under the impression that the information on the 1099 was enough.

 

[faithbuilder]

@gdpotter54 If the 1099 is calculated in USD, then the currency is wrong. Consider the case where you bought a stock at $1, sold it later at $1, and in between JPY fell relative to USD.

 

[gdpotter54]

@faithbuilder I see what you mean. I'm coming from a slightly different angle here. I'm not actively trading stocks, but rather buying and accumulating dividends, so in my case calculating P/L isn't a concern.

I suppose the only thing I need to do is a currency calculation for my dividends which, since I've just started, are miniscule.

I'm glad that I found this out now, rather than later. Thank you for the illustration.

 

[ethzz]

@gdpotter54 The 1099 is enough for you...to produce spreadsheets like these on your own. You'll have to do the lookup for TTM figures on the proper dates, etc. On the other hand, you could probably hire a tax accountant to do it for you.

If you go to the tax office without having done this kind of prep (esp. during the rush when tax season starts), don't be surprised if they turn you away.

 

[gdpotter54]

@ethzz I don't make nearly enough to hire a tax account. XD

Thank you for sharing these spreadsheets. I'll make some of my own and keep an eye on things. I didn't realize that this would be necessary. I appreciate you for sharing.

Also, since it's kind of related, have you tried doing all of this using the e-Tax system?

 

[ethzz]

@gdpotter54 No eTax, maybe someday. As a retiree I now can get an early slot at the tax office (far, far fewer people waiting than in the old days).

You can begin these sheets any time, and I try to visit them 2-3 times thru the year, and again about now. Then polish off the final details in January. I do not wait for year-end 1099 to begin this, and instead use monthly statements, which list the distributions/trading as it happens. At this point, I'm already done up thru mid-November, so just a little more book work to finish.

 

[gdpotter54]

@ethzz I usually do eTax every year, but that's because my tax situation is quite simple so it was easy to figure out which number goes where. XD

Perhaps this year, I'll do as you do and get some help from the professionals.

Thank you again for sharing these. I really appreciate it.

 

[mindy1234]

@ethzz Great post, thanks! Like another poster, the dollar cost averaging is something I need to think about, especially given most of the US stocks are DRIP.

 

[themadhatter]

@ethzz What do you do for mutual funds, where the capital gain distributions don't actually have a purchase date or value?