Report to locate Unrealized Gains

[David Carlson]

David,

It is not all that difficult to build a spreadsheet that both calculates
"net" and "gross" capital gains and separates commissions and fees even for
partial lots.  It gets more interesting when different accounting
procedures such as FIFO, LIFO, Average and purely selective lot matching
are considered.  I think that there are probably commercial programs that
do this too, and all brokerage house back rooms do that and more, since
some of their clients have margin accounts, trade in derivatives etc.  How
difficult would it be to put some of this into GnuCash?  In any case it
won't happen in the next several months.

In your second paragraph you mention the value of shares that you did not
sell when you do not completely close a position.  Wouldn't that depend on
report settings where 'nearest in time' would (i hope) prefer a quoted
price over an actual transaction price on that day?

When I mentioned residual value of comodities I was referring to the
optional Accounts register columns labelled, for example, Present (USD or
your default currency.  That column will not go to zero when closing a
position unless the realized gain is correctly entered.  Of course, it is
more difficult to know if it is correct when there are multiple lots. That
is a handy thing to know.

In another thread about lots I see that the lot feature will not work
correctly if the realized gains are in the same transaction as the sale.
The manual has for years indicated that putting the gain in the same
closing transaction was the preferred procedure, and it is the only way
that I do it.  I guess I will not be using lots.

David C

On Tue, Feb 7, 2017 at 9:00 PM, David T. <sunfish62 at yahoo.com> wrote:

> David,
>
> Thanks for the pointers. I do find it interesting in the context of this
> list to receive a recommendation to lump this information all together,
> given the attention to the minutae of What’s Being Done With My Money. I
> believe I’ve heard it mentioned before though as well.
>
> Rolling the fee in will also affect the share price for the entire book, I
> suspect, making the valuation of any other holdings in the sale commodity
> incorrect, at least for a day. Not a big concern in the grand scheme, but
> something to note.
>
> Regardless, it seems to me that this not-so-minor issue of unrealized
> gains needs to be prominently documented—not just in the Guide (where 7 of
> us might go to read about it), but directly in the footer of the reports
> affected. It is not difficult to imagine a user spending a huge amount of
> time binary-searching their accounts for ghost unrealized gains that are
> simply an artifact of GnuCash calculating gains in an unexpected way. Oh,
> yeah, right—that’s me.
>
> Or perhaps GnuCash shouldn’t offer to provide me with
> incorrectly-calculated Unrealized Gains numbers at all. And, before someone
> jumps in to tell me otherwise, yes, they ARE wrong. I don’t rely on
> "alternative gains" to pay my taxes.
>
> Another idea that involves someone with developer skills (Note: NOT ME):
> go in and modify the reports so that they take into account fees and
> commissions. I am not entirely sure what needs to be done, but it seems to
> me that the splits are in the account (if not the transaction itself), and
> could be included in the calculation somehow. Or, if my feeble
> understanding of this is off, perhaps the options for the file could
> include designations for Commissions and Fees Accounts which would be used
> to adjust the basis of commodities. If these are not set, the
> aforementioned message about gains inaccuracies could be displayed on the
> report.
>
> Finally, you mention monitoring "residual value" in the Chart of Accounts;
> which column are you using for that information? Frankly, that would go a
> long way to helping me root out where GnuCash believes I am out of whack,
> especially on all those old, closed commodity accounts.
>
> Cheers,
> David T.
>
> > On Feb 7, 2017, at 11:36 PM, David Carlson <david.carlson.417 at gmail.com>
> wrote:
> >
> > David T,
> >
> > As you have pointed out, GnuCash generates Realized Gain numbers that do
> > not make any sense when you list commissions and fees explicitly.  For
> what
> > it is worth, if you had simply used net cost on purchase and net proceeds
> > on sale, GnuCash would have worked fine as far as the residual cost basis
> > values in your security accounts are concerned and the lot function works
> > and the Trial Balance report works.
> >
> > The key to the whole thing is keeping the correct residual cost basis in
> > each security account after the realized gain is recorded for each
> closing
> > transaction. Obviously, this is easier when the entire lot is sold, as
> then
> > the residual value should go to zero in the current value column of the
> > chart of accounts. That is my quick check, as a balance sheet report for
> > closed security accounts should have zero value when it has zero shares.
> >
> > Like you, I want to track commissions and fees, so I have resorted using
> a
> > spreadsheet to calculate gross and net costs and gains when I have
> partial
> > lot sales.  I started that procedure in 2008, but I have a lot of older
> > transactions in my data file.
> >
> > I do not use the lots feature yet, so I cannot comment on how much that
> > complicates things.
> >
> > By the way, I am very happy to see the work you are doing with the
> > documentation.
> >
> >
> > David C
> >
> >
> >
> > On Tue, Feb 7, 2017 at 10:07 AM, David T. via gnucash-user <
> > gnucash-user at gnucash.org> wrote:
> >
> >> Screen shot got eaten again.
> >>
> >>
> >>
> >>> On Feb 7, 2017, at 8:38 PM, David T. via gnucash-user <
> >> gnucash-user at gnucash.org> wrote:
> >>>
> >>> Chris,
> >>>
> >>> The binary method might work, but it is exceedingly time consuming when
> >> one is working with a ten-year data file with 185 commodity accounts and
> >> thousands of transactions. I tred that method, in fact, before I sent my
> >> question to the list. It’s simply not feasible except when you’re
> talking a
> >> small data set.
> >>>
> >>> Moreover, my first encounter with a transaction with unrealized gains
> >> left me confused.
> >>>
> >>> Here is the situation that confuses me:
> >>>
> >>> I have a set of transactions from 2007: an Initial Holdings transaction
> >> of 200 shares, with a cost basis of $23.80. I sold 100 shares for $3480,
> >> with $63.99 commission and $.06 fee, with $3415.95 coming along to me.
> My
> >> broker statement for that transaction listed capital gains of $3404.05
> >> (3480-63.99-.06-(23.80/2)), which is what I entered as a separate
> capital
> >> gains transaction on the same date. (See screen shot below)
> >>>
> >>> The Balance Sheet report for this date lists unrealized gains of
> $104.30
> >> (just to be clear, this is the only transaction affecting any
> commodities
> >> in my data file on this date). This figure stumped for a LONG time,
> until I
> >> realized that
> >>>
> >>> 104.30 (Unrealized gainson report) =(63.99 [commission]+.06
> >> [fee]-(23.80/2) [initial cost basis])*2
> >>>
> >>> In other words, the Balance Sheet report was off by the total fees,
> >> reduced by the initial cost basis.
> >>>
> >>> That seems wrong to me, and I certainly don’t know how fix this. I
> mean,
> >> I already *have* the gain transaction entered (and it’s the right one,
> >> according to my broker). So, what am I supposed to do to fix the Balance
> >> Sheet report?
> >>>
> >>> For humor, I decided to Lotify this account (open the View Lots window
> >> and scrub account). When I do this, it inserts a new Gain transaction
> for
> >> $3468.10 (i.e., not deducting fees), and the unrealized gain goes away.
> >> However, I am now staring at TWO gain transactions in the amount of over
> >> $3000, so I would expect the Unrealized gains number to be negative, or
> >> something. But this doesn’t happen; the gains remain at zero. Obviously,
> >> the gains have been realized, but by a set of transactions that gives
> >> completely incorrect information about the overall gain.
> >>>
> >>> I am open to being edified here—both on how I have misconstrued the
> >> problem AND on how to actually fix it going forward.
> >>>
> >>> Oh—and that’s only the first transaction I encountered. It’s from 2007.
> >> It’s a long way to Tiperary.
> >>>
> >>> David
> >>>
> >>
> >> _______________________________________________
> >> gnucash-user mailing list
> >> gnucash-user at gnucash.org
> >> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> >> -----
> >> Please remember to CC this list on all your replies.
> >> You can do this by using Reply-To-List or Reply-All.
> >>
>
>