Unrealized gain problem: Unrealized Gain per Changes in Balance Sheet don't equal Unrealized Gain pe

Tom Young · January 2018

During the year I pretty much run my accounting and reporting ignoring Unrealized Gain. However at year end I run a Net Worth report - all Accounts including hidden, all Categories including hidden, etc.,
and including unrealized gains - then take the difference between that
Net Worth Report and the prior year's Net Worth report, and ensure that
the difference agrees to a Spending report, (Income and expense by
Category), for the year, configured similarly. This has always worked out for me as things typically agree within a few pennies. This year it didn't work. Of course the first thing I did was validate the file and no problems were reported.

After a lot of work I finally narrowed the issue down to the Unrealized Gain aspect of the reports and then down to one day and one Account: 1/3/2017 in my main brokerage Account.

The change in Unrealized Gain between 1/2/2017 and 1/3/2017 per the Net Worth Report is
$30,410.87 while the 1/3/201 - 1/3/2017 Spending report reports a figure of $
31,782.36, a difference of $1,371.49.

Drilling further into the detail the error resolves itself to misstatements of the day's change in Unrealized Gain of $
562.15, $809.31
and $.01 for 3 securities. One of the securities had a small "Buy" on the 3rd, but not in the amount of the error.

I've often wondered why the programmers of Quicken took the route they took to come up with Unrealized Gain in spending reports. Seems like the accountant's typical way to up with the number - calculate unrealized gain at two balance sheet dates and take the difference - would be a lot faster and quicker. Of course I've also wondered why Unrealized Gain typically shows up in two places in a spending report, both income and expense, in a manner that seems to defy logic.

Would anyone recommend trying a SuperValidate here? This is a file that's had 3 decades or so of wear and tear and I don't exactly understand how SuperValidate differs from regular Validate, but I do seem to remember cautions against using SuperValidate routinely or frequently.

Jim_Harman · January 2018

Just a guess: maybe the Net worth report is using the security price from the Price History and the unrealized gain is using the price from the Buy(s)

Have you by chance started trading ETFs, individual stocks, or other securities whose value fluctuates during the day?

Tom Young · January 2018

That was my first thought but only one of the three securities involved traded that day. There are 15 securities in the Account, all relatively long-term holdings: 3 ETF's, 4 Mutual Fund, 8 stocks.

As far as I can see it's unambiguously a Income/Expense report error though it's hard to understand how that can be. Certainly the "drill-down" into the detail of Unrealized Gain per the Income/Expense report suggests that the number is calculated on the fly each time it's needed, and you'd expect the calc of (# of shares x closing price - basis) would be drawn from the same source as the Net Worth report.

I was thinking that maybe some of the Income/Expense calcs got re-directed to an "high" or "low" price for the day but one of the affected securities is a mutual fund where Open/HIgh/Low/Ending prices are all the same.

Frankly it just doesn't make sense, but I don't think there's a thing I can do about it unless one of the developers wants to step over here and explain how the Unrealized Gain is calculated.

q_lurker · January 2018

So your post sent me down a similar path. What I found along the way:

A regular series of discrepancies in the +/- 0.10 range. Regular in the sense that they appeared in monthly data 10 of 12 months last year. Not worth chasing further.
For stock donation purposes, my pattern is a Remove Shares / Add Shares / Sell Shares sequence. The difference in cost basis between the Remove (the original lot) and the Add Shares was not included in the Income/Expense side. Quite understandable once you think about it. Neither Remove nor Add is going to show up in I/E info, so independent of why one might do Removes or Adds, they need to be recognized as special treatment in this type of processing.
Finally, I had a security sale that proved problematic. 100% sale of security left a -22.75 cost basis apparently due to an older RtrnCap transaction. Deleting and re-entering one of the RtrnCap transactions fixed the discrepancy.

None of those fit your discrepancy, but maybe they'll lead you in a helpful direction.

Tom Young · January 2018

Thank you. Your comments did help me pin down the source of the problem and this in turn suggests that something has changed with the 2018 program.

Despite the known issues with RtrnCap entries I've continued to use them at year end, typically entering them sometime in February of each year with an "effective date" of 12/31 of the prior year, reclassifying DivInc, CGLong and CGShort as needed based on the 1099-DIV. (I've justified this to myself because errors created by RtrnCap entries always seemed to affect some element of the basis at the detail level, but not at the overall level.) Both of the big dollar misses, $562.15 and $809.31, come down to 12/31/2016 RtrnCap entries for those two securities.

That explains the 1/3/2017 date of the disconnect - first day of trading in the new year - but also suggests some programming change in the Quicken 2018 program since this is the first year this disconnect has shown up.

Hope against hope I did delete and re-enter one of the RtrnCap entries, but nothing changed.

markus1957 · January 2018

With respect to RtrnCap, I used to enter as you do when tax forms arrived, but I found I encountered fewer issues with cost basis/gain if the transaction date and the effective date were both entered as 12/31. Also for any sells of a full or partial position with RtrnCap (amortized bond premium is my most used application of RtrnCap), I enter the RtrnCap transaction the day before the sell. At least in my case for amortized bond premiums, I've kept the FI statements and Quicken cost basis within pennies.

Tom Young · January 2018

" if the transaction date and the effective date were both entered as 12/31"

What I meant was that sometime in early February I'd physically enter the transaction in Quicken with a 12/31 date in the "Date" column. I don't think there's any other way of doing this, is there?

markus1957 · January 2018

No, I was vaguely recalling the Market Value input in RtrnCap (which I don't understand) and your "effective date" in my response. So other than the remark about placement of the RtrnCap for a Sell (or Buy w/existing position), ignore the comment.

I'm curious, for the day you have isolated (and maybe +/- a day) how do Market Value and Cost Basis compare in a Portfolio Value report? Do either show a value on the day or a value on the day before/after that does not make sense based on transaction activity?

Tom Young · January 2018

The Market Value and Cost of the two securities remained constant on 1/1 and 1/2 as you'd expect as the market wasn't open.

The Market Value of the two securities changed on 1/3 exactly as you'd expect: (# of shares owned) x (change in quote from the previous day). The Cost of one security was unchanged from 1/2 while the cost of the other security went up by the amount of a reinvested divided.

The Cost of the two securities remained unchanged from the previous day on 1/4; no transactions in either security that day. The Market Value of both securities changed as you'd expect based on the quote change.

There's nothing you can see over those 4 days that smokes out the issue. Only running the Income/Expense report for the one day - 1/3 - and then comparing the changes in unrealized gain on a security-by-security basis to the numbers derived from the 1/2 and 1/3 Holdings pops it out.

q_lurker · January 2018

Does the report comparison work out correctly on 12/31/16? That is, due to the RtrnCap on the security, the _UnrlzdGain should have gone up a like amount. Did it? Or is it that for some reason that effect is being deferred until the next business day, or the next day the price changes?

Could Quicken be figuring - price didn't change, shares didn't change, thus unrealized gain didn't change?

I, too, primarily use RtrnCaps on bond premium amortization. That is almost always one lot and seems to process ok. The case I cited above was, like you cited, a dividend recharacterization where based on EOY 1099's, I changed 4 Div transactions to RtrnCap transactions. Only one of those 4 seemed to relate to this issue..

Tom Young · January 2018

"Does the report comparison work out correctly on 12/31/16?"

It did. 12/31/2016 was a Saturday so the comparison between 12/30 and 12/31 comes down to the Cost Basis of each security on those two days.

In both cases the increase in unrealized gain is exactly the same as the RtrnCap entry for each security entered 12/31/2016.

Those cost bases are also what's reflected in the 12/31/2016 Net Worth Report:

Cost Basis for Account on 12/31/2016 per Holdings: $1,978,366.20
Cost Basis for Account per 12/31/2016 Net Worth : $1,978,366.17

Nothing changes from 1/1/2017 to 1/2/2017.

On 1/3/2017 the market value of the two securities changes - first trading day of the year - and the cost basis of Security 1 changes due to a reinvested dividend.

However the Income/Expense report overstates each security's unrealized gain for the day:

Security 1 change in unrealized gain on 1/3 per report: $1,009.37
Security 2 change in unrealized gain on 1/3 per report: $1,603.35

In each case the reported gain per the Income/Expense report is overstated by the amount of the RtrnCap entry made against each security on 12/31/2016.

q_lurker · January 2018

I am about out of ideas. It is acting like you had a RtrnCap on 1/3 and deleted it but the deletion left some hidden, erroneous basis change that the I/E report is picking up on. Really odd.

My only other repair-type workaround I might see would be to Remove and Add Shares dated either just before or just after 1/3. But that could get real messy if you have a long history of ReinvDiv. I have successfully tested a Shares Transferred where the shares are transferred back into the same account. That generates all the Add Shares per lot.

Beyond that, watch out on the sale, or try a sale and see if lots by lot the right (by Quicken's use) cap gains get calculated. That might point at some other lot that needs fixing. (grasping at straws)

markus1957 · January 2018

q.lurker may be on to something. @Tom, you originally asked if file validation is warranted; it probably wouldn't help unless you also checked Rebuild Investing Lots. There appears to be a black box calc going on that relies on a static table (or corrupted entry acting like that). The Lots rebuild might eliminate the static table possibility. Test on a copy of course.

Tom Young · January 2018

@markus1957 @q.lurker

Rebuild Investing Lots was part of my Validate process as it did seem that had a greater chance of fixing things in this case then Validate by itself.

Yes, it's entirely mysterious why this calc is incorrect as logically it shouldn't be, though one answer is that the RtrnCap entry is held in more than one place in the data file, and one of those places has the wrong date. Data corruption in other words.

I'm going to try one more thing: Backup file, delete the 12/31/16 entries, check to see if that affects the 1/3 I/E calc, Copy file, Validate/Repair, re-enter 12/31 entries. If that doesn't do it then I'm done.

Unrealized gain problem: Unrealized Gain per Changes in Balance Sheet don't equal Unrealized Gain pe

Comments

Categories