Stock trades and realized gains/losses
Derek Atkins
warlord@MIT.EDU
09 Jan 2003 12:23:02 -0500
Matthew, Conrad,
First, I should point out that it was Linas that first suggested Lots,
and indeed, Linas wrote the (current) code for Lots. I'm just the
only one using them -- indeed I use them to track A/R and A/P. An
invoice is put into a lot, and then payments are applied to that lot.
In this manner I can tell when an invoice has been paid. But credit
should go to Linas, not I, for Lots.
Matthew Vanecek <mevanecek@yahoo.com> writes:
> come to as well. That conclusion is that we should be tracking the
> dollar amounts of securities, relegating the quantities to an ancillary
> position (e.g., part of a Lot).
There is a clear tradeoff here. Which do you want to be easier to
see, the current dollar-value of your holdings or your realized
gains/losses? The problem is that when you track the dollar-amount of
stock transactions you have to perform a lot of work to determine how
many shares you currently own. That means calculating Current Net
Worth is a lot of work.
Also, Matthew, I think you misunderstand how LOTs work. Lots just
link a set of transactions (actually a set of Splits) together. There
is no additional data in the Lot (amounts, values, etc). A Lot
consists of a list of splits.. That's all. Also, LOTs enable more
than just FIFO -- because you can (with the right interface)
specifically state which set of shares you are selling.
> My proposal is this: track the *number* of shares in the Lot, and the
> *dollars* of shares in the Stock account. This will allow proper
> accounting of the money trail, while also tracking numbers of shares.
> It has the side benefit of *not* backtracking to the 1.6.x model of a
> Security and Currency per account.
You again misunderstand how Lots currently work -- there is no data in
a Lot -- it's all in the splits. This does not mean that it cannot
change to meet your request, but what you are asking for is a HUGE
architectural change in the way ALL transactions are handled.
Now, let's assume that we change how "stock" accounts work such that
we now track both a commodity and a currency (again). This means we
would necessarily need to keep TWO balances, a balance of "amount"
(i.e. shares) and a balance of "value" (i.e. dollars). At least
internally that's what we would need to do, but "externally" I think
the user always wants to see the "current value" of the account, which
means the share-balance * current-price-from-pricedb.
> Assets
> Cash
> COI
>
> Income
> RG(L)
>
> Account Debit Credit Bal
> ------------------------------------------
> Cash 100 100
> COI 100 100 <--- 10 shares @ $10
> ------------------------------------------
> RG(L) 100 100
> Cash 200 100 <--- 10 shares @ $20
> COI 100 0
>
> In the above, the number of shares and the price/share is ancillary
> information, and properly belongs in the Lot attributes. Therefore, in
> our "Stock" accounts, we should be tracking the currencty amount instead
> of the number of shares. And you can use just one COI account for many
> different stocks.
The problem with this model is that while you can easily track the
gains/losses, you cannot track the "current value" of your holdings.
At no time can you easily determine "I own N shares of XXXX", because
that information is stored throughout the dataset. The other problem
is that LOTS tie splits together in an account, but in this particular
case the "account" for the "associated" splits are different, making
it even more difficult to track your current holdings. As I said
at the beginning -- tradeoffs.
> Now, from an implementation perspective, it certainly makes sense to
> follow the generally accepted accounting principles. To track the
> dollar amount *instead* of number of shares alleviates the current
> multi-currency issues with stock accounts. It makes life somewhat
It alleviates the gain/loss issue. It exacerbates the
current-holdings (net worth) issue. To-MAY-toe, To-MAH-toe.
Similarly, what you are asking for necessarily requires you to define
a common currency for ALL your accounts. The reason is that you are
now requiring all your transfer to be in a common currency (you need
this in order for all your books to balance). Any account that wants
to track a "holding" in another commodity/currency necessarily needs
to be a stock/currency account. You cannot have "bank" accounts in
multiple currencies, nor could you track income or expenses in
multiple currencies, because you'd lose your gain/loss information if
you ever made a transfer across currencies.
Sure, you could apply logic so that you can only apply a transaction
across accounts of similar currency, and then you have special stock
and currency accounts to perform the proper commodity transfers. But
we're now back to the 1.6 dual-commodity (at least for Stock, MMF, and
Currency accounts). Not that I'm against this concept, mind you --
I'm just pointing it out.
It means you have a "can't get there from here" issue, which means the
register would have to be currency-aware and only allow you to make
transfers across a common currency. A Currency account would be
"special", in that the transaction can be denominated in "either"
currency. A stock account would still only be allowed to have
transactions denoted in its currency.
> easier when generating reports--there is no longer a need to run through
> many Lots to get a total of realized gains/losses for a period. You can
> see at a glance the dollar amounts for gains/losses and current invested
> amount. Net worth calculations are also simpler to implement. Also,
Ok, I'll bite. How are net worth calculations simpler to implement?
Right now all you need to do is get a current price on all your
commodities and then perform a multiplication across all your
accounts. How does your method make it any easier to track net worth?
There is no account anyways that has a running balance of the number
of shares. In your model your stock account is always "how much you
paid for your stock", which really isn't what you want to denote.
> Accountants generally are more interested in dollar amounts, not number
> of shares (I've seen many questions from Gnucash users about how to get
> info in a form appropriate for Accountants). And to retrieve the number
Well, sure, the Accountants want this information..... once a year!
The rest of the year I want to know "how well is my portfolio doing,
and what is it worth?" Shouldn't we optimize for the "likely"
operation, not the once-a-year operation?
> of shares and other Lot information, all you have to do is add the
> Transaction GUID to the GUID list in whatever Lot, or possibly more
> appropriately, add the GUID of the Lot as an attribute of the
> Transaction (foreign key, as it were).
> I don't know how far Derek has gotten on the Lot development. I
Well, Linas had lot's "done" for a while. I fixed a couple of bugs in
them, but they are there and have been for a while. They just aren't
being used by anything except A/R and A/P invoice/payment tracking.
> able to do so. We would want to record the number of shares in the Lot,
> of course, but that is a required interface in any case. The security
No, lots tie splits together. There is no "share" information in a
Lot. You determine the number of shares by looking at the "first"
Split->amount in the lot.
> mnemonic could also be stored in the Lot, which would allow us to
> combine security investments in one account, instead of a separate
> account for each security.
This _sounds_ like a good idea, but it makes account valuation HARD.
As conrad says, this might be a good way to hand Inventory, but a
stock portfolio is NOT inventory. With inventory you want to know how
much you paid for your assets, and subtract out depreciation. But
that's NOT what you want to know about your stock holdings.
But let's assume for a moment that we do store the stock commodity in
the Lot instead of the Account -- you still cannot store the number of
shares in the Lot. The reason is that there is not a 1:1 mapping of
purchases and sales. I could have a Lot that contains the following
Splits (note that I'm leaving out the date, and the "account" is
assumed)):
(type) amt val
Buy 10 ($100)
Sell -5 (-$150)
Sell -3 (-$75)
Sell -2 (-$20)
As you can clearly see from this example, there is no place in the lot
where you can store a number of shares purchased or sold. That
information is necessarily tied to the Splits that are part of the
Lot. Indeed, there is zero meaning to a "10-share Lot", because there
is no guarantee that those 10 shares are going to be sold together or
even at the same price.
> In the current register, as far as I can tell, the only immediate change
> that should be made is to record a dollar amount in Balance instead of a
> number of shares. That's the first step torwards aligning with
> accounting practices. Derek's work with Lots will go a long way to
> making this a reality, if he agrees with my assessment (well, he *is*
> the one writing the code!).
What it sounds like you REALLY want (and you don't need Lots to do
this), is a DOUBLE BALANCE. You want to accumulate a balance in
SHARES as well as a balance in VALUE. But that is still not
sufficient to do what you are asking. The problem is still that you
need to balance each transaction.
Continuing with this assumption of a dual-tracking, in order to create
proper gain/loss splits you would necessarily need FOUR splits in any
sale transaction. For example, let's assume a purchase of 10 shares
and then a sale of 5:
Date Desc balance
Account amt value
T0 Buy Shares 10
Stock 10 $100 <- 10 @ $10
Cash -$100 -$100
T1 Sell Shares 5
Stock -5 -$150 <- 5 @ $30
Cash $150 $150
Stock 0 $100
Gains $-100 -$100
The "purchase" transaction looks the same as it does now. The "sell"
transaction, however, is very different. Let me describe the four
splits that you see:
- The first one denotes the sale of 5 shares at $30/each. You
necessarily need a split that has this information in order to
acurately track your sale -- you need to know the sale-price, which
is computed as value/amount.
- The second split is your 'cash' split, and that denotes the money
that your broker sent to you, which gets deposited in your cash
account.
- The third split is the "gain/loss balancing split". It is special
because it has an "amount" of zero but still has a value. This
means that no stock actually changed hands for this split, but there
is still value in it. Since the stock account is balanced by
"amount", it has no effect on the account balance. It is also
marked as "in the lot" (see below why).
- The last split is the gain/loss split which keeps track of your
gains/losses. This is the split you are looking for.
(Note that the last two splits COULD be a completely separate
transaction if we wanted to do it that way -- tied to the other "sell"
transaction through the LOT.)
Now, let me show you how this looks from the LOT view. In paricular
the Lot view has the following splits:
(type) amt val (amt-bal) (val-bal)
buy 10 $100 10 $100
sell -5 -$150 5 -$50
(gain) 0 $100 5 $50
The (new) invariant of a Lot is that it is "closed" when both the
sum-of-amount _AND_ sum-of-value is zero. That way you know it is
closed when you've accounted for all the shares _AND_ all the
gain/loss.
If we do it this way, then:
a) you get your gain/loss split
b) I get my "net value" and holdings balance
c) it does not require a major architectural shift in the way gnucash works.
This would require two minor change to lots. First, you need to
enforce that all transactions within the lot are denoted in the same
currency. Second, it would also require a change to how
gnc_lot_is_closed() works.
Unfortunately it also would mean that any multi-curency transaction
would need a lot as well (for the same reason -- to track gains/losses
in currency transfers). But I'm not convinced this is a bad thing. ;)
> Well, I think that about covers it for now. Proper accounting
> practices, user satisfaction, ease of implementation, all based on
> common and approved (and logical) accounting practices.
Personally I'm very satisfied with what I have now. Perhaps that's
because I'm not trying to use Gnucash to deal with taxes, and I don't
particular care about the "balance sheet". I want to see "how much
money do I have, and where is it sitting". Gnucash does that very
well right now.
> Can we do it this way?
No. ;) (just kidding)
(well, only mostly just kidding...)
Unfortunately your proposal does not solve all the problems we need to
solve; it just shifts the burden from one place to another. At some
level we need to optimize gnucash for the "common" usage scenarios.
IMHO, "current value" reporting is more important, but as I have shown
we can get you your gain/loss split if we're willing to look at things
a bit funny.
I certainly don't have all the answers, but I don't see Gnucash as a
tool for accountants -- I see it as a tool for PEOPLE. Interfacing to
accountants should be a secondardy goal, not a primary goal. I think
I've shown how we can do both.
-derek
--
Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
Member, MIT Student Information Processing Board (SIPB)
URL: http://web.mit.edu/warlord/ PP-ASEL-IA N1NWH
warlord@MIT.EDU PGP key available