Tracking different cost basis methods for different countries?

[James Cook]

Hi list,

I file taxes in both the US and Canada, for which I have to calculate
capital gains differently:

a) In Canada I'm deemed to have bought most of my assets on the date I
moved here. There's no such fiction on my US taxes.

b) When calculating capital gains, Canada taxes use average cost
booking; for the US I can somewhat choose the booking method.

Has anyone dealt with this using beancount? My tentative plan is below,
but suggestions would be welcome.

Plan:

* In beancount, I'll enter all data from the US point of view:
individual lots get bought and sold, and it should be straightforward
to use beancount to compute my capital gains for US taxes.

* In Canada, I will take advantage of the fact that average cost
booking is more-or-less deterministic: there are (almost) no choices
for me to make, so it should be possible to construct the average
cost basis just from the beancount leger after discarding all the lot
information.

So: I'll cobble together some custom code, probably involving
bean-query, to compute the average cost for each of my sales. I'm not
afraid to write a bit of code, but I'm not sure how much effort this
will be.

* (It's tempting to just use average-cost booking for the US so things
agree in both countries, but one problem with that is point (a)
above.)

Alternative ideas:

* Maintain separate Beancount ledgers for both countries.

* Write some weird plugin to facilitate average cost basis tracking
even while I'm using beancount's standard lot tracking to do things
from the USA point of view.

* Do the Canada stuff by hand. It's probably not that bad.

* Pay someone to do my taxes for me. Last year that cost me a few
thousand dollars, and this year I'd rather spend a few days tinkering
than pay that.

Currently I've been ignoring Beancount's lot-tracking features partly
because of this issue, but the time has come for me to import a bunch
of my trades so I think it's time for me to figure out how I'm going to
do this.

--
James

[James Cook]

On Thu, Mar 04, 2021 at 09:01:42PM +0000, James Cook wrote:
> Hi list,
>
> I file taxes in both the US and Canada, for which I have to calculate
> capital gains differently:
>
> a) In Canada I'm deemed to have bought most of my assets on the date I
> moved here. There's no such fiction on my US taxes.
>
> b) When calculating capital gains, Canada taxes use average cost
> booking; for the US I can somewhat choose the booking method.
>
> Has anyone dealt with this using beancount? My tentative plan is below,
> but suggestions would be welcome.

I should mention: I saw on the list there's some ongoing work on
implementing average cost booking, but I'm assuming that would replace
any other booking method rather than allow tracking both at once. If
I'm wrong about that do let me know!

--
James

[Martin Blais]

It's here:

https://github.com/beancount/beancount/tree/average_booking_rollback

I was going to merge it but it had significant impact on performance so I rolled it back and moved it to this branch.

(All the booking will be reimplemented in v3 in C++, and simplified substantially, with much of the logic implemented near the Inventory object, so you could invoke it manually.)

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20210304212758.awiyxbvkvjjmz7wl%40moth.falsifian.org.

[Ben Blount]

Hey James,

Implementer of the average cost booking here. We rolled it back from v3 due to a performance regression. I've been pretty busy and haven't had a chance to address that to get it merged back again. It works and is correct to the best of my knowledge / tests.

My recommendation for taxes:

I would do the reporting script you mention. I plan to do exactly that for my own US taxes. I can release the code for that if folks are interested - I haven't written it yet.

- Parser my ledger with parser.parse_file()

- Iterate through the transactions, looking for stock sales in tax year 2020.

- Retrieve all the important fields from the sale, and keep running short-term/long-term capital gains.

- Investigate if the programmatic tax info file format is relatively easy. If so I'll dump my data into that format so I can import it into my tax program programmatically. Otherwise I'll just key them in by hand.

For your Canadian taxes, same thing, but you can code in your special casing to change the date and compute average basis. Follow the code here. You can emulate average cost booking on top of the US booked lots: just iterate over the final booked output from parse_file(), but run the average booker whenever you see lots (and you can also replace the date there to your Canadian date). You'll build up Inventory() objects as you iterate through the ledger. Whenever you see a reduction (stock sale), you can use your average basis instead of the one the US booking method picked.

Longer term plan:

Martin is still putting some of the foundational pieces for V3 in place before it makes sense to have others contribute on the core.

One of the key additions in V3 is to make booking more of a utility that you can invoke in your own scripts. V3 is much more modular. It will make writing scripts like the above one simpler - you can recompose parts of beancount itself rather than needing to reimplement bits of it.

I floated the idea of making booking itself configurable via plugin and Martin was open to the idea. I want to implement for my own ledger a "tax optimizing lot selection" booking method for things like Crypto where there's no broker tracking the basis or lot selection.

[Martin Blais]

On Thu, Mar 4, 2021 at 4:01 PM James Cook <fals...@falsifian.org> wrote:

Hi list,

I file taxes in both the US and Canada, for which I have to calculate
capital gains differently:

a) In Canada I'm deemed to have bought most of my assets on the date I
   moved here. There's no such fiction on my US taxes.

That's fascinating. So you have two completely independent cost bases?

Crazy.

Say, you bought AMZN at $2000, moved to Canada when it was $2600 (say this was $3100 CAD at the time), an AMZN is now at $3000, you have 

- A $1000 unrealized gain to the US

- Say, the 3100 CAD is now worth 3120 CAD due to currency fluctuation, you have a (1.2 x 3000 - 3100) unrealized gain to Canada

Now you sell 1 share of it.

You have two separate P/L.

Is that right?

  

b) When calculating capital gains, Canada taxes use average cost
   booking; for the US I can somewhat choose the booking method.

On top of that... so in the US you'll book one way, in Canada another way.

Christ...

 

Has anyone dealt with this using beancount? My tentative plan is below,
but suggestions would be welcome.

Plan:

* In beancount, I'll enter all data from the US point of view:
  individual lots get bought and sold, and it should be straightforward
  to use beancount to compute my capital gains for US taxes.

Taking care of commissions by hand, always (we haven't really automated that problem).

 

 

* In Canada, I will take advantage of the fact that average cost
  booking is more-or-less deterministic: there are (almost) no choices
  for me to make, so it should be possible to construct the average
  cost basis just from the beancount leger after discarding all the lot
  information.

  So: I'll cobble together some custom code, probably involving
  bean-query, to compute the average cost for each of my sales. I'm not
  afraid to write a bit of code, but I'm not sure how much effort this
  will be.

I think you're  going to need a script for that.

What you could do is write a script which iterates through your transactions and computes and maintains the running Canadian basis on the side - in a mirrored hierarchy of Inventory objects (see "realization" in the source code).

  

* (It's tempting to just use average-cost booking for the US so things
  agree in both countries, but one problem with that is point (a)
  above.)

Don't do that... with the recent runup in the market, this could make a significant difference in taxes.

 

Alternative ideas:

* Maintain separate Beancount ledgers for both countries.

* Write some weird plugin to facilitate average cost basis tracking
  even while I'm using beancount's standard lot tracking to do things
  from the USA point of view.

* Do the Canada stuff by hand. It's probably not that bad.

* Pay someone to do my taxes for me. Last year that cost me a few
  thousand dollars, and this year I'd rather spend a few days tinkering
  than pay that.

Honestly, I'm not even sure most accountants are able to do this without making a mistake in a timeframe where it's still profitable for them to do your taxes. What I've seen is a lot of approximations.

I think writing a script like I mention above would be a fun challenge...

 

Currently I've been ignoring Beancount's lot-tracking features partly
because of this issue, but the time has come for me to import a bunch
of my trades so I think it's time for me to figure out how I'm going to
do this.

--
James

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20210304210142.xiq427y5uzhasz64%40moth.falsifian.org.

[Daniele Nicolodi]

On 04/03/2021 22:01, James Cook wrote:
> Hi list,
>
> I file taxes in both the US and Canada, for which I have to calculate
> capital gains differently:

Wait a minute, do you have to calculate capital gains differently _on
the same accounts_? Doesn't this imply double taxation in the US and in
Canada? I thought that bilateral agreements are in place so that this is
not a thing. But I am European citizen and Canada may be different.

Cheers,
Dan

[James Cook]

Fortunately it doesn't imply double taxation. My understanding is that I
first calculate my tax for each country (yes, calculating capital gains
differently for each one) and then I can apply a final adjustment to
avoid double taxation. The adjustment doesn't depend on the precise
details of how my taxes owed to each country were computed.

I'm grateful I had professionals figure that out for my 2018 and 2019
taxes so I'll have an example to work with.

--
James

[James Cook]

Thanks, Martin and Ben. It sounds I should go ahead with the first step
of my plan, i.e. entering all the data from the US point of view. After
that I'll consult the resources you linked and see what I can put together.

Some responses inline...

On 2021-03-04 10:31 p.m., Martin Blais wrote:
> On Thu, Mar 4, 2021 at 4:01 PM James Cook <fals...@falsifian.org
> <mailto:fals...@falsifian.org>> wrote:
>
> Hi list,
>
> I file taxes in both the US and Canada, for which I have to calculate
> capital gains differently:
>
> a) In Canada I'm deemed to have bought most of my assets on the date I
>    moved here. There's no such fiction on my US taxes.
>
>
> That's fascinating. So you have two completely independent cost bases?
> Crazy.
> Say, you bought AMZN at $2000, moved to Canada when it was $2600 (say
> this was $3100 CAD at the time), an AMZN is now at $3000, you have 
> - A $1000 unrealized gain to the US
> - Say, the 3100 CAD is now worth 3120 CAD due to currency fluctuation,
> you have a (1.2 x 3000 - 3100) unrealized gain to Canada
> Now you sell 1 share of it.
> You have two separate P/L.
>
> Is that right?

The $1000 unrealized gain in the US sounds right.

In Canada I'm not sure where you're getting 1.2 x 3000 or where the 3120
CAD comes up. I believe in Canada, to compute my unrealized gain I just
need to measure the value of AMZN in CAD at two points in time: when I
moved here and now. So to continue your example, if the value is
currently 4000 CAD, then in Canada my unrealized gain is 4000-3100=900 CAD.

So when I sell one share I'll owe tax to Canada on a 900 CAD capital
gain, and tax to the US on a 1000 USD capital gain. Fortunately this
doesn't result in double taxation, as explained in my other email just now.

> b) When calculating capital gains, Canada taxes use average cost
>    booking; for the US I can somewhat choose the booking method.
>
>
> On top of that... so in the US you'll book one way, in Canada another way.
> Christ...

Yes. And don't get me started on PFICs...

> Has anyone dealt with this using beancount? My tentative plan is below,
> but suggestions would be welcome.
>
> Plan:
>
> * In beancount, I'll enter all data from the US point of view:
>   individual lots get bought and sold, and it should be straightforward
>   to use beancount to compute my capital gains for US taxes.
>
>
> Taking care of commissions by hand, always (we haven't really automated
> that problem).

Thanks, I'll keep that in mind.

> * In Canada, I will take advantage of the fact that average cost
>   booking is more-or-less deterministic: there are (almost) no choices
>   for me to make, so it should be possible to construct the average
>   cost basis just from the beancount leger after discarding all the lot
>   information.
>
>   So: I'll cobble together some custom code, probably involving
>   bean-query, to compute the average cost for each of my sales. I'm not
>   afraid to write a bit of code, but I'm not sure how much effort this
>   will be.
>
>
> I think you're  going to need a script for that.
> What you could do is write a script which iterates through your
> transactions and computes and maintains the running Canadian basis on
> the side - in a mirrored hierarchy of Inventory objects (see
> "realization" in the source code).

Thanks, I'll take a look.

--
James

[James Cook]

On Sun, Mar 07, 2021 at 04:48:05AM +0000, James Cook wrote:
> Thanks, Martin and Ben. It sounds I should go ahead with the first step
> of my plan, i.e. entering all the data from the US point of view. After
> that I'll consult the resources you linked and see what I can put together.

I wrote some code. I haven't yet tried using it to do my taxes, so I
don't know how useful it will be in practice. Canadian taxes are due
soon so I should know soon...

My code ended up taking the form of a plugin which computes average
cost base and outputs the result in the form of a new metadata field,
without touching anything else.

The code is here:

https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py

One issue I haven't yet thought about: I'll be entering all my costs in
USD, which will cause this plugin to compute average cost bases in USD.
But I need to report to Canada in CAD. This might be the kind of thing
where it's acceptible to just do some reasonable approximation. Or
maybe I'll update my code to use the price database to convert
everything to a target currency.

Here's a copy of the documentation, which includes examples:


********

This plugin is intended to help when you need to calculate your capital gains
in two different ways, and one of them is average-cost booking.

WARNING: I'm not an accountant. Don't trust this for your taxes. Use your own
judgement.

I plan to use this to file taxes in the US and Canada. It works like this: I
enter my data in Beancount using from the US point of view. Then this plugin
does its own accounting of lots using average cost booking, *without changing
the ordinary Beancount-tracked lots*. This plugin reports its results by adding
metadata fields to transactions. This plugin does not make any changes to the
stream other than adding metadata.

For example, on this input:

plugin "falsifian.parallel_average_cost"

2000-01-01 open Assets:Brokerage0
2000-01-01 open Assets:Brokerage1
2000-01-01 open Assets:Chequing

; Capital gains according to the manually-entered booking.
2000-01-01 open Income:Capital-gains

2000-01-01 * "Buy"
Assets:Chequing -400.00 USD
Assets:Brokerage0 4 ACME {100.00 USD}

2000-02-01 * "Buy again"
Assets:Chequing -50.00 USD
Assets:Brokerage1 1 ACME {50.00 USD}

2000-03-01 * "Sell"
Assets:Chequing 220.00 USD
Assets:Brokerage0 -2 ACME {} @ 220.00 USD
Income:Capital-gains -20.00 USD

Thi last transaction becomes this:

2000-03-01 * "Sell"
Assets:Chequing 220.00 USD
Assets:Brokerage0 -2 ACME {100.00 USD, 2000-01-01} @ 220.00 USD
parallel_average_cost_base: 180.00 USD
Income:Capital-gains -20.00 USD

A couple of things to notice:

* A new parallel_average_cost_base field was added. It indicates that the average
cost base for those 2 ACME is 180.00 USD (9.00 USD per unit of ACME).
* The average cost booking was done across all accounts. It doesn't matter that
one buy happened with Assets:Brokerage0 and the other with Assets:Brokerage1.

In this example, I'd use the Income:Capital-gains field for my US taxes, and
use the computed parallel_average_cost_base field to help my compute my
Canadian taxes. (One issue here: the amount is in USD, not CAD.)

Once the cost base has been computed for a posting, it probably shouldn't
change. To make sure, you can add the value as an assertion. For example:

2000-02-01 * "Sell"
Assets:Chequing 200.00 USD
Assets:Brokerage -5 ACME {20.00 USD} @ 40.00 USD
parallel_average_cost_base_assert: 100.00 USD
Income:Capital-gains -100.00 USD

An error will be raised if the computed average cost base is not 100.00 USD.

You may wish to manually set the cost base. For example, when I moved to
Canada, I was "deemed" by Canada to have aquired all my equity at that moment
in time. You can use a "parallel_average_cost_base_set" custom directive to do
that, like so:

2000-02-01 custom "parallel_average_cost_set" 4 ACME 400.00 USD "extra"

This directive does two things:
* It asserts that your total balance of ACME across all accounts is 4.
* It sets the total cost of those 4 ACME to 400.00 USD (so, 100.00 USD per
ACME).

If you transfer equity between two brokerage accounts without buying or
selling, you'll want to turn off the plugin for those postings so it doesn't
adjust your cost base, by adding the "parallel_average_cost_ignore" metadata
field with any value. For example:

2000-03-01 * "Transfer to a different account"
Assets:Brokerage0 -5 ACME {}
parallel_average_cost_ignore: 1
Assets:Brokerage1 4 ACME {10.00 USD, 2000-01-01}
; The metadata value doesn't matter.
parallel_average_cost_ignore: ""
Assets:Brokerage1 1 ACME {20.00 USD, 2000-02-01}
; The metadata value doesn't matter.
parallel_average_cost_ignore: ""

See parallel_average_cost_test.py for more examples.

--
James

[James Cook]

On Fri, Apr 23, 2021 at 12:05:40AM +0000, James Cook wrote:
...

> One issue I haven't yet thought about: I'll be entering all my costs in
> USD, which will cause this plugin to compute average cost bases in USD.
> But I need to report to Canada in CAD. This might be the kind of thing
> where it's acceptible to just do some reasonable approximation. Or
> maybe I'll update my code to use the price database to convert
> everything to a target currency.

Update: I added currency conversion. The version at

https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py

is updated including documentation.

It seems to be doing reasonable things with my Beancount ledger, but I
haven't yet filled out my Canadian taxes, so still unconfirmed that
this is actually helpful.

--
James

[James Cook]

Another update: I (almost) completely redesigned it, and it's actually
kind of usable now! I can use fava to show my balance over time under
*either* booking method, without editing my ledger.


Recap:

* I file taxes in both the US and Canada.

* I need to use different booking methods for each country. Canada
needs average cost booking, but I can't just change my whole ledger
to use that, since then I wouldn't have the right information for US
taxes!

* With this plugin, I can do all my booking from the US point of view,
and the plugin will augment it with a "parallel" ledger that does
everything from an average booking point of view.


The latest version acheives this by adding new "parallel" postings in
"parallel" commodities mirroring the original ones. For example, if I
want to see my VOO balance over time from the average-cost point of
view using fava, all I need to do is go to:

http://localhost:5000/beancount/account/Assets:Par:VOO/


My original VOO postings are unharmed, so I can always just go to

http://localhost:5000/beancount/account/Assets:Vanguard:Brokerage:VOO/

if I want to see things from the US point of view (i.e. using whatever
booking method I used with plain Beancount).


The plugin works by turning this:

2000-01-01 * "Buy"
Assets:Chequing -100.00 USD
Assets:Brokerage 5 ACME {20.00 USD}

2000-02-01 * "Sell"
Assets:Chequing 200.00 USD
Assets:Brokerage -5 ACME {20.00 USD} @ 40.00 USD

Income:Capital-gains -100.00 USD

into this:

2000-01-01 * "Buy"
Assets:Chequing -100.00 USD
Assets:Brokerage 5 ACME {20.00 USD}
Assets:Par:ACME 5 PAR.ACME {20.00 PAR.USD}
Equity:Par -100.00 PAR.USD

2000-02-01 * "Sell"
Assets:Chequing 200.00 USD
Assets:Brokerage -5 ACME {20.00 USD} @ 40.00 USD

Income:Capital-gains -100.00 USD
Assets:Par:ACME -5 PAR.ACME {20.00 PAR.USD} @ 40.00 PAR.USD
Income:Par:Capital-gains -100.00 PAR.USD
Equity:Par 200.00 PAR.USD

Code and documentation are at https://hub.darcs.net/falsifian/misc-pub/browse/beancount_plugins/falsifian/parallel_average_cost.py

--
James

[James Cook]

> The plugin works by turning this:
>
> 2000-01-01 * "Buy"
> Assets:Chequing -100.00 USD
> Assets:Brokerage 5 ACME {20.00 USD}
>
> 2000-02-01 * "Sell"
> Assets:Chequing 200.00 USD
> Assets:Brokerage -5 ACME {20.00 USD} @ 40.00 USD
> Income:Capital-gains -100.00 USD
>
> into this:
>
> 2000-01-01 * "Buy"
> Assets:Chequing -100.00 USD
> Assets:Brokerage 5 ACME {20.00 USD}
> Assets:Par:ACME 5 PAR.ACME {20.00 PAR.USD}
> Equity:Par -100.00 PAR.USD
>
> 2000-02-01 * "Sell"
> Assets:Chequing 200.00 USD
> Assets:Brokerage -5 ACME {20.00 USD} @ 40.00 USD
> Income:Capital-gains -100.00 USD
> Assets:Par:ACME -5 PAR.ACME {20.00 PAR.USD} @ 40.00 PAR.USD
> Income:Par:Capital-gains -100.00 PAR.USD
> Equity:Par 200.00 PAR.USD

Er, that's not a very interesting example because the booking doesn't
change. But you can imagine the ACME.PAR postings will use average cost
booking. There are lots of examples in the _test.py.

--
James