Income in foreign currency, need tax report in my home currency, plus fx gains/losses with FIFO

[Anton Andriyevskyy]

Dear community, please help me understand if Beancount is able to help me solve the following problem.

I live in Australia and therefore report to Australian Taxation Office in AUD.

I earn in USD, however, so I experience FX gains and losses, and I can deduct losses in my tax return.

Here is a simple example:

option "operating_currency" "AUD"

2019-07-01 open Assets:Transferwise:USD USD "FIFO"
2019-07-01 open Assets:Transferwise:AUD AUD "FIFO"

2019-07-01 open Income:Upwork           USD

2019-07-01 price USD 1.50 AUD
2019-07-02 price USD 1.60 AUD
2019-07-03 price USD 1.70 AUD
2019-07-04 price USD 1.80 AUD


2019-07-01 * "Earnings at Upwork"
    Assets:Transferwise:USD                     10.00 USD
    Income:Upwork

2019-07-02 * "Earnings at Upwork"
     Assets:Transferwise:USD                     10.00 USD
     Income:Upwork

2019-07-03 * "Converted 10 USD to 15 AUD at 1.7"
     Assets:Transferwise:AUD                    17.00 AUD
     Assets:Transferwise:USD                   -10.00 USD @ 1.7 AUD

From here, I need a report for the Taxation Office, where it will say:

- how much I earned in AUD; each income should be converted to AUD for reporting purposes with using the exchange rate of the transaction date

- what is my foreign currency exchange gains / losses

The government designates to use FIFO for fx gains/losses calculation.

You advice is very appreciated.

[Martin Blais]

On Sun, Nov 29, 2020 at 7:45 PM Anton Andriyevskyy <x.me...@gmail.com> wrote:

Dear community, please help me understand if Beancount is able to help me solve the following problem.

I live in Australia and therefore report to Australian Taxation Office in AUD.

I earn in USD, however, so I experience FX gains and losses, and I can deduct losses in my tax return.

Here is a simple example:

option "operating_currency" "AUD"

2019-07-01 open Assets:Transferwise:USD USD "FIFO"
2019-07-01 open Assets:Transferwise:AUD AUD "FIFO"

2019-07-01 open Income:Upwork           USD

2019-07-01 price USD 1.50 AUD
2019-07-02 price USD 1.60 AUD
2019-07-03 price USD 1.70 AUD
2019-07-04 price USD 1.80 AUD


2019-07-01 * "Earnings at Upwork"
    Assets:Transferwise:USD                     10.00 USD
    Income:Upwork

2019-07-02 * "Earnings at Upwork"
     Assets:Transferwise:USD                     10.00 USD
     Income:Upwork

2019-07-03 * "Converted 10 USD to 15 AUD at 1.7"
     Assets:Transferwise:AUD                    17.00 AUD
     Assets:Transferwise:USD                   -10.00 USD @ 1.7 AUD

From here, I need a report for the Taxation Office, where it will say:

- how much I earned in AUD; each income should be converted to AUD for reporting purposes with using the exchange rate of the transaction date

You'll have to create a table of 

   date, account, amount, rate

You can write a script, or use a query like this:

  bean-query $L "select date, account, position, convert(position, 'AUD'), getprice(currency, 'AUD', date) where account ~ 'Income:Upwork' and currency = 'USD' and year >= 2020"

 

- what is my foreign currency exchange gains / losses

The government designates to use FIFO for fx gains/losses calculation.

I'm not a tax lawyer (warning/disclaimer) but I will venture that if you are just temporarily holding your income for a short time before you transfer it, those are probably small and over many transfers your losses probably cancel out your gains. Those rules probably start to have more of an impact when you trade currencies. (Personally I'd make a quick one-off rough estimate to confirm the total is small and if so not bother reporting, and keep my spreadsheet in case they come.)

Now, if you really want those, there are a few ways to do this.

1. You could write a script and convert your Income postings to AUD, and produce a table of equivalent trades. You could even spit out another Beancount file from it with {...} costs if you want FIFO matching.

2. You could use the currency trading accounts method and write a script to do a few things:

- Convert the Income:Upwork postings to AUD

- Filter and sum up just the Earnings + Conversions transaction you show above.

The resulting gain/loss should be in corresponding Equity accounts.

That won't give you the realized/unrealized breakdown though, you'll just have total gain/loss.

Note: There's a partial implementation of the currency trading accounts method here:

https://github.com/beancount/beancount/blob/master/beancount/plugins/currency_accounts.py

In v3 this will be the normal way everything works, see discussion here:

https://docs.google.com/document/d/1qPdNXaz5zuDQ8M9uoZFyyFis7hA0G55BEfhWhrVBsfc/edit#heading=h.rs49kcml7cjx

You advice is very appreciated.

--
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/52be0393-6372-4031-9b12-9d3b6313fcb3n%40googlegroups.com.