-
Notifications
You must be signed in to change notification settings - Fork 32
Manual lot tracking
Associated directory: 15-manual-lots
Sometimes you can't treat a specific commodity as a single pool of fungible units. For example, if the disposal of the commodity is subject to the capital gains tax, you need to be able to figure out the original acquisition price for all the units of a commodity that you are selling to compute the difference between the selling price and the buying price.
In plain text accounting parlance, this problem is usually called "lot tracking", and there are plenty of texts out there that describe various techniques for lot tracking, usually in the context of managing stock and shares portfolios. There is, for example, a wonderful hledger companion program called hledger-lots that automates the process of keeping track of the cost basis (aka original purchase price), generating sell transactions that keep track of gains or losses and many more.
I do not want to cover the topics extensively covered elsewhere, and instead, I want to focus on two subjects which are, in my opinion, covered very sparsely:
-
How do organize your reporting so that you can see your lot information at a glance?
-
How do you track lots manually when they are weird and don't fit into a "stock-like" model?
To draw a distinction, let's talk about "stock-like" commodities. For them, you usually want to track prices (cost basis and disposal price), and you usually have a fixed method of choosing the lots to dispose of when you are doing the sale. Usually, you either choose the least recently acquired or the most recently acquired lot -- strategies that are abbreviated as FIFO (first in - first out) or LIFO (last in - first out).
So how would a "not-stock-like" commodity for which you still want to track lots look like?
Let's take a look at the UK annual pension allowance. In the UK, you have an annual limit to contributions to your tax-incentivised private pension. If you stay under the limit and have an unused allowance, you can use it up in any of the three subsequent tax years, and then it expires. If you go over the limit (plus any unused past allowance), you have to pay a tax charge. As you can see, it is necessary to keep track of your pension allowances for tax purposes.
So pension allowance:
-
Comes in lots that you acquire once a year
-
There is no cost basis and no disposal price when you dispose of these lots
-
When consuming the allowance, you first use up the current year's allowance (which sounds like LIFO), but then you move on to the oldest unusual allowance you still have (which now sounds like FIFO).
-
After three years, unused lots evaporate
How can we track this in hledger? Firstly, as pension allowance is not something that you can buy, sell or convert, we are going to put it into a separate account category - I chose to use the virtual top-level account category for this. We will use virtual postings to introduce new pension allowance, and then we will distribute it between consumed and unconsumed parts, putting them into different subaccounts keyed by year:
$ hledger print -f all.journal virtual:pension 'date:2014-04-05'
2014-04-05
(virtual:pension:allowance:2013/2014) £4000 ; how much the allowance was
virtual:pension:allowance:2013/2014 = £0 ; how much of it is left
virtual:pension:inputs:2013/2014 £100.00 ; how much pension contributions were
virtual:pension:allowance:unused:2013/2014 - 2016/2017 ; remainder went to unused allowance
In this transaction, we introduced an allowance of £4000, and then immediately set the balance to zero to show that it all went elsewhere - £100 was consumed by this year's pension contribution, and the rest went into virtual:pension:allowance:unused:2013/2014 - 2016/2017. The name for the unused allowance account allows one to see at a glance when this allowance could be consumed, and when it would expire.
So what can we do now reporting-wise? Firstly, we can produce the balance report of virtual:pension:inputs alongside your actual pension account (in my examples, it is assets:pension:avivia) to see whether they line up:
$ hledger -f all.journal balance "assets:pension:aviva" "virtual:pension:input" -b "2013-04-06" -p "every year" --transpose --depth 3
Balance changes in 2013-04-06..2018-04-05:
|| assets:pension:aviva virtual:pension:inputs |
========================++==============================================+=========
2013-04-06..2014-04-05 || 0 £100.00 | £100.00
2014-04-06..2015-04-05 || £102.34 £100.00 | £202.34
2015-04-06..2016-04-05 || £102.07 £100.00 | £202.07
2016-04-06..2017-04-05 || £103.86 £100.00 | £203.86
2017-04-06..2018-04-05 || £102.76 0 | £102.76
In the toy examples in this repository, there is no easy way to split pension contributions from pension value appreciation, but you can see that despite that we could see that our recorded values are in line.
We can also use the --historical switch for the balance command to see how the allowances changed over time, and how much of it was used, and remains unused:
$ hledger -f all.journal balance virtual:pension -b '2013-04-06' -p 'every year' --no-elide --tree --cumulative --historical
Ending balances (historical) in 2013-04-06..2018-04-05:
|| 2014-04-05 2015-04-05 2016-04-05 2017-04-05 2018-04-05
===============================++============================================================
virtual || £4000.00 £8000.00 £8050.00 £4240.00 £4240.00
pension || £4000.00 £8000.00 £8050.00 £4240.00 £4240.00
allowance || £3900.00 £7800.00 £7750.00 £3840.00 £3840.00
unused || £3900.00 £7800.00 £7750.00 £3840.00 £3840.00
2013/2014 - 2016/2017 || £3900.00 £3900.00 £3850.00 0 0
2014/2015 - 2017/2018 || 0 £3900.00 £3900.00 £3840.00 £3840.00
inputs || £100.00 £200.00 £300.00 £400.00 £400.00
2013/2014 || £100.00 £100.00 £100.00 £100.00 £100.00
2014/2015 || 0 £100.00 £100.00 £100.00 £100.00
2015/2016 || 0 0 £100.00 £100.00 £100.00
2016/2017 || 0 0 0 £100.00 £100.00
-------------------------------++------------------------------------------------------------
|| £4000.00 £8000.00 £8050.00 £4240.00 £4240.00
The balance of virtual:pension:inputs shows how much was contributed over time (as a running total).
The balance of virtual:pension:allowance:unused shows the amount of unused allowance, and one can take a look at subaccounts to see which allowance should be consumed first, and how much of it is left, or has expired. For instance, we can see that £3850 of 2013/2014 - 2016/2017 allowance was unused when it expired in 2017.
If we want to see just the running total of unused allowance, and how it changed over time, we can use the register command with --depth, which would cause subaccounts to be aggregated to the parent:
$ hledger -f all.journal register virtual:pension:allowance:unused -b '2013-04-06' --depth 4
2014-04-05 virtual:pension:allowance:unused £3900.00 £3900.00
2015-04-05 virtual:pension:allowance:unused £3900.00 £7800.00
2016-04-05 virtual:pension:allowance:unused £-50.00 £7750.00
virtual:pension:allowance:unused 0 £7750.00
2017-04-01 Expired (virtual:pension:allowance:unused) £-3850.00 £3900.00
2017-04-05 virtual:pension:allowance:unused £-60.00 £3840.00
virtual:pension:allowance:unused 0 £3840.00
These commands could be put in a script that is run every time export.sh is run, and code in 15-manual-lots does exactly that.
To contrast the weirdness of the pension allowance, I've added a simple example of "stock options" to the journals in this repository, where a certain amount of "stock option units" is granted every year (free of charge), and then they vest two years later. Vested stock options could then be valued, provided that there are price directives in the journal.
Our setup could be much easier:
$ hledger print -f all.journal virtual:stock -b 2014 -e 2017
2014-12-30 Stock options
virtual:stock options:granted
virtual:stock options:vesting:2016 5 UNITS
2015-12-30 Stock Options
virtual:stock options:granted
virtual:stock options:vesting:2017 10 UNITS
2016-12-30 Stock Options
virtual:stock options:granted
virtual:stock options:vesting:2018 20 UNITS
2016-12-30 Stock Options
virtual:stock options:vested
virtual:stock options:vesting:2016 = 0 UNITS
Much like before, we could use balance --cumulative to see the history of vesting:
$ hledger balance 'stock options:vest' -f all.journal -Y -b2014 --cumulative
Ending balances (cumulative) in 2014-01-01..2017-12-31:
|| 2014-12-31 2015-12-31 2016-12-31 2017-12-31
====================================++================================================
virtual:stock options:vested || 0 0 5 UNITS 15 UNITS
virtual:stock options:vesting:2016 || 5 UNITS 5 UNITS 0 0
virtual:stock options:vesting:2017 || 0 10 UNITS 10 UNITS 0
virtual:stock options:vesting:2018 || 0 0 20 UNITS 20 UNITS
virtual:stock options:vesting:2019 || 0 0 0 25 UNITS
------------------------------------++------------------------------------------------
|| 5 UNITS 15 UNITS 35 UNITS 60 UNITS
We can use balance --valuechange to track the valuation of all vested options over time:
$ hledger balance 'stock options:vested' -f all.journal -Y -b2014 --cumulative --transpose --value=end,$ --valuechange --no-total
Cumulative period-end value changes in 2014-01-01..2017-12-31:
|| virtual:stock options:vested
============++==============================
2014-12-31 || 0
2015-12-31 || 0
2016-12-31 || $4255.60
2017-12-31 || $13529.55
You can find the resulting setup in 15-manual-lots or diffs/14-to-15.diff.
- Key principles and practices
- Getting started
- Getting data in
- Getting full history of the account
- Adding more accounts
- Creating CSV import rules
- Maintaining CSV rules
- Investments - easy approach
- Mortgages
- Remortgage
- Foreign currency
- Sorting unknowns
- File-specific CSV rules
- Tax returns
- Speeding things up
- Tracking commodity lost manually
- Fetching prices automatically
- ChangeLog