Matching trades with complex tax rules

[Dan Sladden]

Hi

Here in the UK the rules for matching sells to buys for capital gains tax purposes are complex. The basic rule is to use the average cost, but there is a "30 day rule" which means that a sell should match with a buy in the following 30 days.

Take a look at cgtcalculator.com if you want to explore the gory details.

So how much is it possible to take the pain out of this with ledger?

In particular, is it possible to compute the amount for a split from the current balance of an account (see below).

By way of example, suppose I have executed these trades in commodity AAA.

1. 2019/01/01 Buy  10 @ £10
2. 2019/02/01 Buy   5 @ £12
3. 2019/03/01 Sell -8 @ £15
4. 2019/04/01 Sell -2 @ £18
5. 2019/04/15 Buy   3 @ £20
6. 2019/05/01 Sell -5 @ £22
7. 2020/05/01 Sell -3 @ £25

So for calculating gains

• #3 should use the average cost of #1 and #2
• #4 should use the cost of #5 (within 30 days)
• #6 should use the remaining average cost of all trades to date
• #7 likewise

I need to track the running gain because the tax year ends on 5 April.

The best I can think of with ledger is this:

2019/01/01 Buy

    Assets:Investments    10 AAA ; @ £10.00

    Assets:Cash           -£100.00

    [Lots:AAA:Lot0]       -10 AAA

    [Lots:AAA:Lot0]       £100.00

2019/02/01 Buy

    Assets:Investments    5 AAA ; @ £12.00

    Assets:Cash           -£60.00

    [Lots:AAA:Lot0]       -5 AAA

    [Lots:AAA:Lot0]       £60.00

2019/03/01 Sell

    Assets:Investments    -8 AAA ; @ £15.00

    Assets:Cash           £120.00

    [Lots:AAA:Lot0]       8 AAA

    [Lots:AAA:Lot0]       (-(£100+£60)/(10+5)*8)

    Income:Gains

2019/04/15 Buy

    Assets:Investments    3 AAA ; @ £20.00

    Assets:Cash           -£60.00

    [Lots:AAA:Lot1]       -3 AAA

    [Lots:AAA:Lot1]       £60.00

2019/04/01 Sell

    Assets:Investments    -2 AAA ; @ £18.00

    Assets:Cash           £36.00

    [Lots:AAA:Lot1]       2 AAA

    [Lots:AAA:Lot1]       (-£60/3*2)

    Income:Gains

2019/04/01 Merge Lot1 AAA into lot 0

    [Lots:AAA:Lot1]       =0 AAA

    [Lots:AAA:Lot0]

2019/04/01 Merge Lot1 £ into lot 0

    [Lots:AAA:Lot1]       =£0

    [Lots:AAA:Lot0]

2019/05/01 Sell

    Assets:Investments    -5 AAA ; @ 22.00

    Assets:Cash           £110.00

    [Lots:AAA:Lot0]       5 AAA

    [Lots:AAA:Lot0]       (-(£100+£60-(£100+£60)/(10+5)*8+(£60-£60*2/3))/(10+5 -8+3 -2)*5)

2020/05/01 Sell

    Assets:Investments    -3 AAA ; @ £25.00

    Assets:Cash           £75.00

    [Lots:AAA:Lot0]       =0 AAA

    [Lots:AAA:Lot0]       =£0

    Income:Gains

OK, so the last one isn't so bad - it's easy when the position is being closed out. But the others... And this is but a simple example.

Note BTW that the later matching buy #5 for sell #4 comes deliberately before it in the journal.

Hence my question: is it possible to compute the amount for a split from the current balance in an account? What I'd like to be able to enter for the sell transactions is something like:

2019/03/01 Sell

    Assets:Investments    -8 AAA ; @ £15.00

    Assets:Cash           £120.00

    [Lots:AAA:Lot0]       8 AAA

    [Lots:AAA:Lot0]       (-(balance £)/(balance AAA)*8)

    Income:Gains

2019/04/01 Sell

    Assets:Investments    -2 AAA ; @ £18.00

    Assets:Cash           £36.00

    [Lots:AAA:Lot1]       2 AAA

    [Lots:AAA:Lot1]       (-(balance £)/(balance AAA)*2)

    Income:Gains

2019/05/01 Sell

    Assets:Investments    -5 AAA ; @ 22.00

    Assets:Cash           £110.00

    [Lots:AAA:Lot0]       5 AAA

    [Lots:AAA:Lot0]       (-(balance £)/(balance AAA)*5)

...and the last one (closing out) is fine.

Any suggestions would be very welcome.

Thanks

Dan

[Martin Michlmayr]

* Dan Sladden <dan.s...@gmail.com> [2021-02-07 04:59]:

> 2019/03/01 Sell
> Assets:Investments -8 AAA ; @ £15.00

This is wrong already. Unfortunately, you'll have to specify the
exact lot; run
ledger bal Assets:Investments --lots
on your example to see the problem -- you shouldn't have any lots
in red.

Unfortunately, I'm not aware of any way to automate it in ledger
and you'll have to specify the right lots manually.

--
Martin Michlmayr
https://www.cyrius.com/

[Dan Sladden]

I've ended up using the structure described above, effectively manually tracking all the necessary lots, working out the gains at cgtcalculator.com and implementing the results in ledger by hand. It works and there aren't any lots in red because I never use @ or @@ to specify lot prices. Unfortunately that means I also have to manually build a price database.

[the.so...@gmail.com]

On Monday, 22 February, 2021 at 2:26:12 pm UTC+5:30 dan.s...@gmail.com wrote:

I never use @ or @@ to specify lot prices.

Have you out the trading accounts method? https://github.com/ledger/ledger/wiki/Multiple-currencies-with-currency-trading-accounts

It still requires calculation of realized capital gains by hand, but it can at least help you automatically calculate _unrealized_ capital gains.

 

Unfortunately that means I also have to manually build a price database.

 There is one downside to using the prices database for transaction prices (as opposed to market prices):

You can normally have multiple transactions in a single day with different prices for different transactions.  

But if you're using the prices database, then you can't (since you can't specify time in transactions nor in the prices database).