A warning about summing up cost (and a partial solution)

[Martin Blais]

= The problem =

I've just discovered a nasty issue (I'm not sure I'd call it a bug) in the tree_table renderer, which is the piece of code that accumulates balances for the Balance Sheet and Income Statement reports. On this line:

https://bitbucket.org/blais/beancount/src/80d30d6896cf5fdcff8c1156cab77107ee8e0f96/beancount/reports/tree_table.py#lines-161

and this line:

https://bitbucket.org/blais/beancount/src/80d30d6896cf5fdcff8c1156cab77107ee8e0f96/beancount/reports/tree_table.py#lines-164

What I do here is iterate through the Positions of the Inventory for the given account, convert each of the Positions to their cost equivalent and THEN sum them up. Now, in the normal case of STRICT, FIFO or LIFO booking, where every reduction matches an actual existing position, this works fine. However, for "NONE" booking, which is what people use instead of AVERAGE, which isn't there yet, that may causes surprising results/.

Let's take an example. A possible Inventory resulting from Positions held at cost, where the reducing positions have their sale cost (which because the booking method is NONE doesn't have to match any of the other lots) might look like this if you simply - and perhaps mistakengly - record the price as the cost basis without care of the inventory contents:

  +10 MSFT {89.61 USD, 2018-02-07}

   -9 MSFT {96.44 USD, 2018-04-18}

Note here how 96.44 doesn't match any existing lot.

If you first convert to cost and compute the value in cost basis terms, you get 896.10 + (-867.96) = 28.14.

That's for the one share remaining; clearly wrong ($28 <<< $90/share).

I've noticed this in my retirement account whereby large positions are being converted from one fund to another due to some instrument name changes.

The result has been cost basis balances with large negative numbers.

Looks very strange on the balance sheet.

What's happening, if you think about it, is that realized profits are essentially "merged" with the cost basis.

And the most dramatic realization of this is when there's some profit and a very large liquidation, resulting in small or no amount of units.

Basically, if you liquidate the whole account, what remains is the profit/loss...

So if you track those accounts without a rational cost basis, storing the sale price as the basis, you'll see this situation too.

This is one of the most important reasons the Ledger model of tracking costs doesn't work.

By providing a cop-out from booking (via "NONE"), this is what I make possible. 

It's a little dangerous.

= The impact, and changes to bean-web to report market-value = 

Anyhow, because the accounts on which this "NONE" method is applied are always non-taxable, the problem has no important impact other than showing potentially incorrect cost basis sums in the web interface. (I'm not sure if Fava is affected by this; probably not, I presume they do their own aggregation.)

To fix the reporting issue, a solution is to instead compute the sum total of units and report the latest market value instead.

I'm going to change bean-web to do this by default very soon.

(from this branch: https://bitbucket.org/blais/beancount/src/web_at_market_value/)

It also makes more sense to look at the market value.

And besides, as mentioned in other emails, bean-web's destiny is to get deleted.

At least in the meantime this issue will be addressed.

= A solution =

I came across this issue again recently when I rebuilt my script to export all positions to my portfolio tracking spreadsheet.

So this is an annoying recurring thing, really.

(I hope to share how I do portfolio tracking soon, I'd like to write about this, because I've solved a number of personal portfolio management problems recently, including intra-day reporting and rerporting of multiple investment strategies and risk management by using a portfolio of put options and it's all sourced from Beancount and it's actually simple.)

The Right Thing to do would be to implement the "AVERAGE" booking method.

But that's more work than I can afford the time for right now (I try to have a life, especially in the summer) and opens up a can of worms.

So I've made a new plugin to let us approximate it instead: check_average_cost().

What this does is check that when you're adding a reducing posting (negative number of units) to an account with "NONE" booking method, that the cost basis of those postings is within a reasonable range of the average cost basis of the inventory it's being applied to, say, within 1%. While this doesn't automate accurate matching against existing postings, is forces you to fan out more of the profit/loss to a P/L account, thereby largely alleviating the offending numbers.

Here's a simple example:

  +5 MSFT {89.61 USD}
  +5 MSFT {92.87 USD}

If you try to add a posting of 

  -2 MSFT {96.44 USD}

to this, you will get an error that says the 96.44 per-unit cost is too far from the average of $91.24.

It's just an error; it doesn't automate anything.

If you want, you can reduce at the average cost basis and move the imported "cost" to its rightful place, the price:

  -2 MSFT {91.24 USD} @ 96.44 USD

In an ideal world, you'd import reductions like this:

  -2 MSFT {} @ 96.44 USD

and let Beancount's "AVERAGE" booking method fill in the number automatically.

In a large ledger that's been tracked erroneously and with the "NONE" method - like I did - it would take a good amount of time and typing to fix all these manually, so perhaps it's not that great a solution. I don't know. On the other hand, it's an easy check to add as an optional plugin. Let me know if you find this useful, if anything, partially.

[dest...@gmail.com]

I am trying to track holdings in several retirement accounts where the average cost method is the appropriate one. I have read the various documents and discussions on the topic I managed to find in the Beancount universe. I decided that "NONE" booking with manually computed average basis values will do fine for me until an AVERAGE method is implemented. (Thanks for the workaround, and for Beancount, of course.) I also discovered and started using the check_average_cost plugin. In the process, I encountered a shortcoming in check_average_cost -- or in my understanding of average-cost basis computation.

My accounts have two types of reductions: 1. The common sale/redemption/transfer out or 2. administrative fee paid in fund shares. It seems to me that the two reductions have very different effects on the cost basis of remaining shares even though the Beancount posting for the account is identical. I set up a simple example where I buy 20 shares of MFUND1 at $10 and sell 4 at $12. I repeat the same operations with MFUND2 at the same prices except that the second operation, the reduction, is a fee payment in shares.

In the first case, the average cost basis for the 16 shares remaining after the sale is $10. Cost basis is unaffected by the units or the price of the sale -- as one would expect from average booking.

In the second case, average the cost basis of the 16 shares is $15.5 (($20*10+$12*4)/16) because the proceeds from the "sale" are a fee paid to the administrator and thus increase the total cost of the remaining shares. Such reductions as a fee to the administrator always increase the average cost of the remaining shares.

At least, this is my understanding of how average-cost basis works. However, the check_average_cost plugin does not agree. To illustrate, I added third operation to both sequences above, a sale of 10 shares at $14. Here is the entire file:

plugin "beancount.plugins.check_average_cost"
option "operating_currency" "USD"

1900-01-01 commodity MFUND1
1900-01-01 commodity MFUND2

2019-01-01 open Assets:Bank:Checking                        USD
1980-05-12 open Equity:Opening-Balances
2019-01-01 open Assets:Investments:MFUND1       MFUND1   "NONE"
2019-01-01 open Assets:Investments:MFUND2       MFUND2   "NONE"
2019-01-01 open Expenses:Fees                   USD
2019-01-01 open Income:Gains                    USD

2019-01-01 * "Opening Balance for checking account"
  Assets:Bank:Checking                        10000.00 USD
  Equity:Opening-Balances                    -10000.00 USD

2019-01-02 * "Buy 20 shares of MFUND1 at 10 USD"
  Assets:Bank:Checking              -200 USD                         
  Assets:Investments:MFUND1           20 MFUND1 {} @ 10 USD

2019-01-03 * "Sell 4 shares of MFUND1 at 12 USD"
  Assets:Bank:Checking                48.00 USD                         
  Assets:Investments:MFUND1           -4 MFUND1 {10 USD}  @ 12.00 USD
  Income:Gains

2019-01-04 * "Sell 10 shares of MFUND1 at 14 USD"
  Assets:Bank:Checking               140.00 USD                         
  Assets:Investments:MFUND1          -10 MFUND1 {10 USD}  @ 14.00 USD
  Income:Gains

2019-01-02 * "Buy 20 shares of MFUND2 at 10 USD"
  Assets:Bank:Checking              -200 USD                         
  Assets:Investments:MFUND2           20 MFUND2 {} @ 10 USD

2019-01-03 * "Pay account fee of 48 USD in MFUND2, 4 at 12 USD"
  Expenses:Fees                       48.00 USD                         
  Assets:Investments:MFUND2           -4 MFUND2 {10 USD}  @ 12.00 USD
  Income:Gains

2019-01-04 * "Sell 10 shares of MFUND2 at 14 USD"
  Assets:Bank:Checking               140.00 USD                         
  Assets:Investments:MFUND2          -10 MFUND2 {15.5 USD}  @ 14.00 USD
  Income:Gains

This fails bean-check with
...:41:Cost basis on reducing posting is too far from the average cost (15.5 vs. 10)...
    2019-01-04 * "Sell 10 shares of MFUND2 at 14 USD"
    ...
Changing the 15.5 to 10 satisfies the plugin.

[dest...@gmail.com]

In my post about the effect of fees in shares on average cost basis, I forgot to mention that they can be positive, too. TIAA-CREF, for instance, labels these "plan servicing credits". They are in effect partial refunds of administrative costs. They increase the number of shares and always reduce average cost basis of the resulting holding.