Average cost capital gains with hledger
Sep 23, 2023
I use hledger, a plain text accounting program to keep track of my stock investments and the capital gains I need to declare for UK tax.
Disclaimer: I’m not a tax professional and while I do use similar calculations for my personal tax reporting, I do not claim that any of this is correct or in agreement with HMRC rules.
Consider an example stock, with ticker FOO. I buy 3 units of stock FOO on the 1st of January for $100 each:
2023/01/01 open accounts
Assets:Cash 1000 USD
Equity -1000 USD
2023/01/01 buy foo
Assets:FOO 3 FOO
Assets:Cash -300 USD
Of course, with double-entry bookkeeping, I have to balance the accounts to zero, so I introduce another account that’s called a trading account. This is a double-currency account which will always have the number of FOO units that I currently own and the amount of money I have paid (and not sold back):
2023/01/01 buy foo
Assets:FOO 3 FOO
Income:Trading:FOO -3 FOO
Income:Trading:FOO 300 USD
Assets:Cash -300 USD
now the accounts balance (notice the zero at the end):
$ hledger balance
USD 700 Assets:Cash
3 FOO Assets:FOO
USD -1000 Equity
-3 FOO
USD 300 Income:Trading:FOO
--------------------
0
Now let’s show the actual value of each account, by adding a pricing entry:
P 2023/01/01 FOO USD 100
$ hledger bal --value=then --empty
USD 700 Assets:Cash
USD 300 Assets:FOO
USD -1000 Equity
0 Income:Trading:FOO
--------------------
0
The Income:Trading:FOO’s value is zero because the USD and FOO value cancel each other out.
This should always be the case when considering the average cost of the commodity. The value of the Income:Trading account should always be zero. Unfortunately, hledger reports don’t seem to support average cost. So that’s the last time I’ll use --value=then in this article. In practice, I use the Income:Trading account to keep track of the trades and always look at the actual value of each commodity in that account (i.e. -3 FOO and $300) rather than converting to a single commodity.
Average cost
More importantly, the trading account helps with calculating the average cost, which is a principle of UK capital gains accounting. The rule states that, when buying shares in the same company for different prices, the value that you use to calculate your capital gains or losses is based on the average purchase value of the stock.
Here’s how much FOO and $ there are in the Income:Trading account:
$ hledger bal
USD 700 Assets:Cash
3 FOO Assets:FOO
USD -1000 Equity
-3 FOO
USD 300 Income:Trading:FOO
--------------------
0
I’ve bought 3 FOO for $300, so my average cost of FOO is $100. This account will reliably keep track of the average cost. For example, say I were to buy more FOO a month later, when the price has increased:
P 2023/02/01 FOO USD 120
2023/02/01 buy foo
Assets:FOO 1 FOO
Income:Trading:FOO -1 FOO
Income:Trading:FOO 120 USD
Assets:Cash -120 USD
$ hledger bal
USD 580 Assets:Cash
4 FOO Assets:FOO
USD -1000 Equity
-4 FOO
USD 420 Income:Trading:FOO
--------------------
0
The Income:Trading account correctly shows that I’ve historically bought 4 FOO for $420, which means that my average cost was $105 per FOO.
Selling
Say that in March, after the price has gone up again, I want to sell one unit of FOO:
P 2023/03/01 FOO USD 150
2023/03/01 sell foo
Assets:FOO -1 FOO
Income:Trading:FOO 1 FOO
Income:Trading:FOO -150 USD
Assets:Cash 150 USD
$ hledger bal
USD 730 Assets:Cash
3 FOO Assets:FOO
USD -1000 Equity
-3 FOO
USD 270 Income:Trading:FOO
--------------------
There are two problems with this. The average cost basis, which I calculate by dividing the USD in the Income:Trading account to the number of FOO in that account has changed: $270/3 = $90 (down from $105 above). But it should’ve stayed the same because I have not bought more FOO.
So there’s an inconsistency here. I should always make sure that I take money from that account at the same rate as my average cost:
2023/03/01 sell foo
Assets:FOO -1 FOO
Income:Trading:FOO 1 FOO
Income:Trading:FOO -105 USD
Assets:Cash 150 USD
But now hledger complains because the transactions don’t balance anymore:
$ hledger bal
hledger: Error: ...
32 | 2023-03-01 sell foo
| Assets:FOO -1 FOO
| Income:Trading:FOO 1 FOO
| Income:Trading:FOO USD -105
| Assets:Cash USD 150
This multi-commodity transaction is unbalanced.
The real postings' sum should be 0 but is: USD 45
Consider adjusting this entry's amounts, adding missing postings,
or recording conversion price(s) with @, @@ or equity postings.
That $45 has to come from somewhere. And it corresponds exactly to my capital gains:
P 2023/03/01 FOO USD 150
2023/03/01 sell foo
Assets:FOO -1 FOO
Income:Trading:FOO 1 FOO
Income:Trading:FOO -105 USD
Income:Gains -45 USD
Assets:Cash 150 USD
$ hledger bal
USD 730 Assets:Cash
3 FOO Assets:FOO
USD -1000 Equity
USD -45 Income:Gains
-3 FOO
USD 315 Income:Trading:FOO
--------------------
0
Now the accounts balance again, the Income:Trading account still shows my average cost ($315/3 = $105) and I know how much my capital gains are!
References
For reference, this is the full ledger file:
2023/01/01 open accounts
Assets:Cash 1000 USD
Equity -1000 USD
P 2023/01/01 FOO USD 100
2023/01/01 buy foo
Assets:FOO 3 FOO
Income:Trading:FOO -3 FOO
Income:Trading:FOO 300 USD
Assets:Cash -300 USD
P 2023/02/01 FOO USD 120
2023/02/01 buy foo
Assets:FOO 1 FOO
Income:Trading:FOO -1 FOO
Income:Trading:FOO 120 USD
Assets:Cash -120 USD
P 2023/03/01 FOO USD 150
2023/03/01 sell foo
Assets:FOO -1 FOO
Income:Trading:FOO 1 FOO
Income:Trading:FOO -105 USD
Income:Gains -45 USD
Assets:Cash 150 USD
The method of keeping track of average costs using a Income:Trading accounts is inspired by Peter Selinger’s tutorial on multiple currency accounting.