cost basis tracking

[Tom Jones]

Greetings,

I am currently evaluating ledger, and other applications in the ledger family, for a business where the majority of transactions involve one or more currencies other than the (tax) unit of account.  My questions apply equally well to situations involving inventory, share portfolios, and so on, except perhaps for the number of different "lots" in existence.

I've read the ledger 3 manual, and also the "Trading with Beancount" document.

As far as I can tell, allocation of sold units to bought units for the purpose of cost basis is a manual process.  In order for the gain to be calculated, I have to determine what lot(s) are involved, and refer back to those.  Is my understanding correct?

Is there a way to say to the software "gains on commodity X are to be calculated on a FIFO basis", and have it do the work for you?

If every transaction involves the creation of, and disposal of, one or more lots, this is a lot to keep track of, and it seems like work the computer should be able to do.

To try and construct a minimal example, if we have this:

2014-10-15 buy widgets

  assets:inventory     10 widgets

  assets:GBP           -80 GBP

2014-10-15 buy another widget

  assets:inventory      1 widget

  assets:GBP           -9 GBP

2014-10-16 sell a widget

  assets:GBP            11 GBP

  assets:inventory      -1 widgets

then is there any way the software can compute that the sold widget has a cost basis of 8 GBP, if it is asked to use the FIFO basis for widgets?

  thanks, Tom.

[Martin Blais]

On Sun, Oct 19, 2014 at 6:12 AM, Tom Jones <tomjones...@gmail.com> wrote:

Greetings,

I am currently evaluating ledger, and other applications in the ledger family, for a business where the majority of transactions involve one or more currencies other than the (tax) unit of account.  My questions apply equally well to situations involving inventory, share portfolios, and so on, except perhaps for the number of different "lots" in existence.

I've read the ledger 3 manual, and also the "Trading with Beancount" document.

As far as I can tell, allocation of sold units to bought units for the purpose of cost basis is a manual process.  In order for the gain to be calculated, I have to determine what lot(s) are involved, and refer back to those.  Is my understanding correct?

Yes. That is the current situation.

Is there a way to say to the software "gains on commodity X are to be calculated on a FIFO basis", and have it do the work for you?

Not yet, but I've laid out a plan for how to do this and came up with this idea:

furius.ca/beancount/doc/proposal-inventory

If every transaction involves the creation of, and disposal of, one or more lots, this is a lot to keep track of, and it seems like work the computer should be able to do.

To try and construct a minimal example, if we have this:

2014-10-15 buy widgets

  assets:inventory     10 widgets

  assets:GBP           -80 GBP

2014-10-15 buy another widget

  assets:inventory      1 widget

  assets:GBP           -9 GBP

2014-10-16 sell a widget

  assets:GBP            11 GBP

  assets:inventory      -1 widgets

then is there any way the software can compute that the sold widget has a cost basis of 8 GBP, if it is asked to use the FIFO basis for widgets?

The way this would work is that you would specify a default method to for inventory booking and you would leave the lot specification as ambiguous, so that it triggers the default method, like this:

2014-10-15 * "buy widgets"

  Assets:Inventory     10 WIDGET {} ;; Price inferred to 8 GBP/widget

  Assets:Cash          -80 GBP

2014-10-15 * "buy another widget"

  Assets:Inventory      1 WIDGET {} ;; Price inferred to 9 GBP/widget

  Assets:Cash          -9 GBP

2014-10-16 * "sell a widget"

  Assets:Cash           11 GBP 

  Assets:Inventory      -1 WIDGET {} ;; Ambiguous lot

  Income:Gains

In the first two transactions, because there is a single number missing, it should eventually be able to infer what the price is. I believe Ledger can already do this in the simple case; Beancount doesn't yet fill in missing costs or prices (it's laid out in the same proposal). In Beancount you'd have to at least provide "{}" to indicate that these units are to be held at cost and that this isn't a price conversion.

In the last transaction, because you are intending to dispose of a lot (and this is inferred by the sign of the current position in "WIDGET", which is +11, and the sign of the change, which is -1 and the inverse of the current one), and because there are more than a single lot that matches your ambiguous specification ("{}") at that point in time (10 WIDGET {8 GBP} and 1 WIDGET {9 GBP}), the resolution is ambiguous. An ambiguous lot match would trigger the default booking method for that account, whose default value would be set to something that tells Beancount to raise an error (call this "STRICT" booking method). However, if you override the default value for that account to be "FIFO" instead (once, in the Open directive for that account), it would automatically select the lot with the earliest date (1 WIDGET {8 GBP}).

Finally, note that you will need a posting to absorb the 3 GBP gain ("Income:Gains") in order to balance the transaction.

I'm assuming that in your example you specifically chose to acquire the two lots on the same date. The position in the file (the line number) would be chosen to further disambiguate which of the lots came earliest. (I hadn't thought of this, I've added this to the proposal.)

You will find much more detail at the document.

I'd appreciate your feedback on it (as comments in the margins),

 

  thanks, Tom.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Wm...]

Sun, 19 Oct 2014 04:12:48
<5cba7bd6-7324-48a5...@googlegroups.com>
Tom Jones <tomjones...@gmail.com>
wrote...

>To try and construct a minimal example, if we have this:
>
>2014-10-15 buy widgets
> assets:inventory 10 widgets
> assets:GBP -80 GBP
>
>2014-10-15 buy another widget
> assets:inventory 1 widget
> assets:GBP -9 GBP
>
>2014-10-16 sell a widget
> assets:GBP 11 GBP
> assets:inventory -1 widgets
>
>
>then is there any way the software can compute that the sold widget has a
>cost basis of 8 GBP, if it is asked to use the FIFO basis for widgets?

ledger-cli doesn't, as far as I can tell, keep stock of anything
physical or allow for the change in value unless you kick it in which
case you might as well use a spreadsheet

I'd love to be wrong

--
Wm...

[Martin Blais]

On Sun, Oct 19, 2014 at 7:34 PM, Wm... <tcn...@tarrcity.demon.co.uk> wrote:

Sun, 19 Oct 2014 04:12:48 <5cba7bd6-7324-48a5-b61e-f97aea...@googlegroups.com>

You are.

The explicit method works in Ledger:

2014-10-15 buy widgets

  assets:inventory     10 widget

  assets:GBP           -80 GBP

2014-10-15 buy another widget

  assets:inventory      1 widget

  assets:GBP           -9 GBP

2014-10-16 sell a widget

  assets:GBP            11 GBP

  assets:inventory      -1 widget {8 GBP}

  income:gains

ledger -f /home/blais/tmp/automatch.lgr bal --lot-prices  --no-color inven

    9 widget {8 GBP}

    1 widget {9 GBP}  assets:inventory

It correctly canceled out a unit of WIDGET {8 GBP}.

Ledger does book lots but as far as I can tell it's done (1) a posteriori (it accumulates all the lots and only at the end books lots against each other), and (2) it does so based on the particular method you choose for booking via options, as you can specify whether to care about dates or not, for example, adding in the dates:

ledger -f /home/blais/tmp/automatch.lgr bal --lot-dates  --no-color inven

           -1 widget

11 widget [2014/10/15]  assets:inventory

In this case, the dates would have had to match in order to book the widget lots against each other.

And if you wanted to change "the value" (which I'm assuming you're referring to the cost basis) you can create a transaction that removes the lot you want to change and insert a new one with the adjusted cost basis. This should work in Ledger AFAIK, should be no problem.

[Mike Charlton]

I have been using ledger to track transactions in multiple currencies for a couple of months now. The biggest feature I would like to have is to be able to tag a transaction and then refer to the cost of something by tag.  That way, when I inevitably screw up the cost, it is easy to change :-)  The feature infers eliding capital gains in the selling transaction (which we've had a previous discussion about), but I'm now convinced this is how I would prefer to enter those transactions.

[Martin Blais]

Hi Mike,

This is also meant to be supported in the new inventory booking method I'm proposing, where you can attach a "label" to a lot:

https://docs.google.com/document/d/1F8IJ_7fMHZ75XFPocMokLxVZczAhrBRBVN9uMhQFCZ4/edit#heading=h.slkego4axqdp