Net Worth and Portfolio Values don't match

Reed Welch · September 2016

I am using Q2014 Release R9, and just came across this problem.
I have 9 investment accounts, both retirement and non-retirement.
8 of the 9 accounts, the net worth report and the portfolio value match to the penny.
On the 1 account, the Portfolio Value is ~$190K, which matches my brokerage report, but when I perform a net worth report for this account it is reporting ~$174K.
I have reason to believe that this was not happening 6 months ago, since I was applying for financing, and was using the net worth report to state assets, and they were correct then.
I have tried to match the difference with various holdings, but was unable.
I also performed a "Validate and repair, rebuild investing lots", all appeared OK, and the discrepancy remained.
I also went through every holding individually to make sure they were an "Asset", nothing abnormal.
Any idea on what to look for or fix?

NotACPA · September 2016

Please elaborate upon why, in the problem account, you were unable to "match the difference with various holdings". Did you compare each individual holding in the problem account to your broker's report?

Also, when you did the validate, did you check "Validate File" also? You only reference "Rebuild Investing lots".

Reed Welch · September 2016

Match holdings to the difference - I attempted to see if any one or two positions (holdings) matched the ~$18K difference, and was unable to do so, so I have no idea why the $18 difference exists between the Net Worth report and the Portfolio Value report. Brokers report and Quicken portfolio match exactly. The problem appears to be the Net Worth report and how it arrives at a different value.
Validation - yes did both simultaneously. I did not "Delete Price history", since I need it going back over 5 years.
Any thoughts on what to try? Also, I am unable to "drill down" in the net worth report to see details, when I try, it just brings on the account transactions?

NotACPA · September 2016

Repeating myself, "Did you compare each individual holding in the problem account to your broker's report?"
There's probably multiple differences that sum up to $18k ... not just one.
And, when you drill down and get those transactions, try customizing the report to limit it to Year to Date or Month to Date, which should pretty well reduce it to currently held securities.

Also, check the Price History for each security in the problem account. See if there are any screwy prices there.

volvogirl · September 2016

Yeah you would have to match each security one by one in that portfolio and find which one is different. One thing to try is to check the price history for each security and see if you have any prices dated in the future. Same with transactions, any in the future?

Reed Welch · September 2016

Yes - when I view my investments, the details (shares/quote/market value) all line up exactly to the brokerage account, as does the total market value.
I can change to "year to date", "last 12 months" in the Net Worth report - the final answer doesn't change, it reports Net Worth as of today.
I set the date parameter for the last 12 months, then clicked to drill down, and subtotaled by security, from what I can see it shows the gain or loss for each security for that period. I'm still at a loss as to the inconsistency between the Net Worth Report and the Portfolio Value Report.

Note: when drilling down with date set to include all dates, the report was truncated and did not include all securities. It did have ~26,000 transactions. My intention was to export and open in excel, then create a pivot table to extract selected data to trouble shoot.

Reed Welch · September 2016

Price history looks fine. I checked all 21 securities over a 5 year period. I also set the date for Sept 2120, just in case there was an abnormal entry. Result - no change

Reed Welch · September 2016

Update - with respect to the Net Worth Report for the investment account with the discrepancy.
I customized the report by excluding all current security holdings, and it returned a value, equal to the difference between the Portfolio and Net Worth report values. Iwould have expected it to be close to or equal to 0.00.
I "drilled down" in that report, subtotaled by security, and all the security subtotals were in the +/- $.06 range, nothing significant. And bottom Total line was $0.42, which was inconsistent with the original report amount of -$18,759.
I am at a loss as to what to correct within the rergister, and now skeptical of using the Net Worth report, and don't find it reliable.
Thanks to all for your input and getting me to think outside the box.

me · October 2016

I have come to the same conclusion as you. The account balance and the investing report are generally correct, but the Net Worth report are including some cash balances that are not really in the account. It does come down to bad data, but there is no way to identify with the tools we have at our disposal.

Unknown · October 2016

I am having the same issue. I am using Quicken Home and Business 2017 R2. The register (investing) report for one investment account has the correct holdings and values however when I use the net worth report it shows a higher value for that account. to the tune of more than $13,000.00. I have compared year end balances using both reports and the discrepancy occurs in each year as far back as I have checked (2011).

Unknown · October 2016

I also ran the Net Worth Report excluding all current holdings and rather than return a zero value it returned a negative $25,500.90. Year end subtotals show this same amount going back to 2001.

me · November 2016

A quick update to this chain. To better drill down, I suggest you compare

Report:Net Worth (Account Details checked) against Account:Holdings.

This will show you the security that is not matching, or if the difference is just cash.

Using this I have been able to identify and correct nearly every security transaction (a few are down to fractions of a share that I think stem from splits). I do have cash differences (always zero cash in account and some cash in Net Worth) that I have still been unable to correct.

Reed Welch · November 2016

By checking the "account details" in the Net Worth report I was able to see the net worth differences.
Thanks for this tip, very helpful!
In my case, 4 securities had a negative net worth, and the sole source of the net worth vs portfolio value discrepancy, to the penny.
The problem transactions with each one of these securities appears to be with past spinoffs, and only for investment accounts that have a "linked" cash account. The account without a "linked" cash account is just fine.
The transactions with the action "RtrnCapX" appear to be the problem. If I go to the specific transaction and 0 out the amount, the net worth for that particular security is correct, but the cash balance is now off by that amount. Screen shot of what I am talking about;

Any thoughts on how to correct?

me · November 2016

First -- congrats! I love seeing Quicken transactions from 2003.
Second -- I've seen return of capital transactions cause this a few times so I think we are totally on track. I'm actually confident we will fix.

Start by making a copy of your data and trying this on the copy. That way when we mess up, you don't hate me.

Delete this transaction
Re-enter as a dividend on the same day.

If it is not that simple (and I can't confirm it wlll be. Unfortunately my trial and error hasn't led me to a cure all), try this:

Delete this transaction.
Run a validate (I assume you know how to do this)
Re-enter as a dividend on the same day.

Finally it that doesn't work, write back and I'll give all of steps I've used (it will be a catch all). Definitely update with your success or failure.

mshiggins · November 2016

I would be looking at what happened to the cash generated by the RtrnCapX in the linked account.

Changing the return of capital to a dividend would change the cost basis of the spinoff transactions. I would be wary of impacting the cost basis.

me · November 2016

To be clear, this is a punt trying to clean up old transactions. But allowing my annoyance to show, if Quicken's Net Worth Report (sum up all investments plus all cash) yields a different answer than Holdings (sum up all investments plus all cash) it leads me to doubt any information on a report the program is generating.

My experience is that these return of capital transactions (all on the order of 10-15 years old) are the root cause of these discrepancies. Perhaps I incorrectly assumed that Reed was more concerned in getting his data correct than getting 2003 tax lots right, but I for one am pretty frustrated at trying to clean up my data (and I'm as loyal a user as they come.)

Reed Welch · November 2016

First off, I always use file copies when experimenting, I've been burned in the past. Good to mention in the post for others.

Since I had only 4 securities causing the discrepancy, that I no longer have, I took a simple but maybe not the correct technical approach. I "zeroed" out the amount for the Return on Capital, and entered a Misc Cash transaction for the same date to offset the amount.
As of now the Portfolio Value and Net Worth Amount match up to $0.01, probably a rounding error that I am not going to worry about.

Although I haven't checked it out, by not changing the Market Value field, the adjusted cost basis may stay in tact.

mshiggins · November 2016

Telling another user to change investment transactions that impact cost basis and thus capital gains calculations and not mentioning this is irresponsible. If having that pointed out irritates you, you need to develop thicker skin.

me · November 2016

I promise I was irritated by quicken and not you! And it does seem like Reed has made some progress.

Reed Welch · November 2016

Your point is well taken and important for the user community to understand.

FWIW - I fully understand cost basis and capital gains consequences with respect to investment transactions and spin offs, etc.
For my particular instance, and at this point in time, it doesn't matter, since the "fly in the ointment" securities have been liquidated and and reported in past tax returns.

As "me" mentioned, the real frustration is with Quicken (or should we call it Wicken), since the reports it generates are fundamentally flawed. I have examples of where it doesn't do fundamental math, nor include Put and Call securities value in reports. I wouldn't have a problem if they published limitations on reporting, but have not seen that, and don't think one should have to search forums to find that.

For me and maybe others, it is inexcusable for Q to provide erroneous net worth / portfolio reports, since these are used to apply for financial services.

I have been Q user since the mid 90's and have significant history with Q and have a sense of loyalty. but that is quickly diminishing.

IMO - Quicken is a database, and should not be that difficult to manage.
And yes, I have made past erroneous entries. Which I have been able to find and correct (using Q reports and tools), and everything lined up correctly at the end of the day.

It seems every time it comes under new ownership/control it changes. New features are implemented, some good and some bad. Checking past continuity has not been good, which is a problem for long term users.

After spending over 12 hours in trying to correct issues I am no longer able to do deal with Quicken reporting deficiencies, which is my problem.
The current value of using Quicken is having transactional history over a long period of time for multiple accounts and viewing the investment window, which is convenient.
Right now I'm looking to move on to another financial s/w package - any suggestions?

me · November 2016

Assuming you use the software as I do, I believe Quicken is the only game in town. I'm curious what the community says but personally for 5x the price I'd buy better software but I'm not aware of anything that exists.

In the meantime, I'm committed to beta testing and helping here to make a Quicken the best it can be.

Unknown · November 2016

Use Quicken like Reed: Multiple brokerage accounts, thousands of transactions over many years. Resorted to same steps to "fix" Net Worth Report. Understand my past records now not correct, but problems were many years in the past and no longer consequential.

Reed, I also lament the lack of put and call handling in Quicken

Reed Welch · November 2016

I agree, for now Quicken is the only game in town, for maintaining long term investment transactions and banking history.
I'm very disappointed that they do not address problems by publishing known issues, thus keeping others (and myself) from spending valuable time trying to correct something that cannot be......

I am an old school person - I don't have a problem admitting I made a mistake and owning up to it.
My words of wisdom to my family and friends are to be Honest, Behave Competently and Respect Others.
The new school people apparently think there is something wrong in doing this.
This is apparently a cultural thing that I hope changes.

Reed Welch · November 2016

Well, in summary I am tired and very disappointed in Quicken.

Why spend another hour trying to debug, knowing that it will have minimal tax consequences, other than I am almost OCD.
For now I am going to ignore Q reports for Portfolio and Net Worth, and use that from my investment sites.
Schwab, Fidelity, and others do a very good job at keeping track of investments and the cost basis, providing ytd updates and long versus short term gains. The quality sites even have ytd dividends, taxable versus non-taxable.
Consolidating checking and savings accounts within Q is pretty simple, and easy to export to a spreadsheet.
A few more operations than I would have liked to have done, but reality is what it is. Life goes on...

markus1957 · November 2016

For old, liquidated securities holdings, non-zero net worth values usually can be handled with a single correcting entry for each non-zero value security.

Once you identify the security and cash value amount imbalance associated with the security that is causing a non-zero value for the security following liquidation, enter a Return Cap transaction (dated one day following the liquidation) for the amount needed to zero the holding and select the same account in the pull-down under "Transfer account" so that cash balance is not impacted but cost basis is adjusted to zero the current value reported by Quicken.

I have found the Portfolio Value and the Portfolio Value & Cost Basis reports useful for identifying these issues. Also the Portfolio view in the Investing tab set to "Show closed lots" can be helpful.

me · November 2016

What happens if the remaining balance is negative? I have two negative Balances on my Portfolio Value report, one has a negative cost basis (different amount than Balance) and one is $0. If I look at account:holdings neither shows any share balance.

markus1957 · November 2016

Sometimes the Return Cap entry needs to be a negative value to drive the cost basis in the right direction to zero out the current value of the security. Enter the appropriate positive or negative value to achieve a zero balance (they disappear from the report). And remember to use the transfer back into the same account so the account cash balance is not impacted.

For your $0 holding, make sure cents is being reported in the report as many times the offending value is only nickels or dimes.

Derek · November 2016

Maybe you have errant transactions in a special hidden account called Tax Impact of 401(k) Accounts.
This account is used in conjunction with 401(k) or 403(b) type
investment accounts in Quicken. I have seen the Net Worth report be
out of balance when this hidden account contains invalid entries.

I would work on a copy of your Quicken file. Enter a dummy transaction in your checking account using the Category [Tax Impact of 401(k) Accounts]
and some payment amount. Right click on the dummy transaction and
select Go To matching transfer. This will open up the hidden account.
Examine the register. The entries should be your contributions to
(transfers into) your 401(k) or 403(b) accounts. If the cash balance of
this hidden account is around $18K, you definitely found the problem.
Normally, it should always be a zero cash balance. Experiment with deleting all the transactions.

Furthermore,
the Tax Schedule button on the Account Details dialog for editing an account can be used to delete or add transactions in bulk to this hidden
account. When you change the Tax Schedule Information for a 401(k) or
403(b) account, Quicken prompts you to delete or recreate transactions
in the "Tax Impact" account, depending on your selection. (Toggle back
and forth from the blank selection in the drop-down menus to see this --
you have to click on OK between toggles.)

When the Tax Schedule
Information is blank, the Tax Impact account is automatically used for
contributions/disbursements. Sometimes, this feature gets out of whack,
so when you think you have deleted everything from the Tax Impact
account, you really haven't.

The last thing is be certain all
Securities, even your hidden ones, plus the "No Security (including
Cash)", are selected in the Securities tab of the Net Worth report
customization dialog. The report does very unexpected things
otherwise. Use a date range of "Custom dates" and start from 1/1/1970
and end with some date you want (maybe year or half year boundaries).
You might narrow the time frame of the corruption.

Reed Welch · November 2016

markus1957 - Thanks for the tip.
Given that, and being masochistic, I revisited my Q file/account with the 4 problem securities and was able to get my net worth and portfolio value to line up with each other, plus they now match my brokerage report.
What I had to do was edit all the original RtrnCapX transactions from prior spinoffs, and "zero" out the "Amount" box to allow the Net Worth for that particular security to be "zero". This action reduced the Cash by that amount, which was corrected by making a separate RtrnCapX in that amount for that security back to the same cash account.

Not sure if I mentioned earlier, this situation only existed within a taxable account with a linked cash balance and not in non-taxable accounts which had some of the same securities and spin offs occurring. Possibly investment accounts with linked cash balances are treated differently.

JohnR0836 · December 2016

I had the same problem and this solution worked. Thank you Reed.

Reed Welch · December 2016

Glad to see this worked for you, and hope the methodology assists others!

Net Worth and Portfolio Values don't match

Comments

Categories