Negative securities value

Marty Dopps

QWin2019 - V R16.14 - Build 27.1.16.14  Windows 10 OS

I'm closing out a 401k account.  Over the past several years the account has held just three securities, though in the preceding decade there were MANY securities that flowed through it.   Cash balance upon selling the three securities is $127k.  The Total Market Value is $67k which obviously means a negative Securities Value of $60k.  (All amounts are examples only).  I've confirmed the Holdings view contains exactly the $127k cash balance and nothing else.  So what is this negative securities value and where does it come from?  Thanks in advance.  

mshiggins

The times I have encountered this issue, it has always been a mix up in the lot assignments for sales.



If you run a portfolio value and cost basis report for that account for all dates, you should be able to identify the problem securities and the date where the problem started. That should narrow down the sales you need to review.

Jim_Harman

Have you confirmed that there are no Placeholders in the account? Go to Edit > Preferences > Investing Transactions and make sure Show Hidden Transactions is checked. Placeholders will have a gray background and an Action type of Entry.

Once you have straightened out any Placeholders, go to the Holdings view and work backwards in the As of date to see where the error occurs, then look at transactions near that date to see what went wrong.

Back up your data file before making changes to your historic data, in case something goes wrong.

Marty Dopps

To mshiggins, thanks for the suggestion.  I did just that and found the offender.  What's odd is that the account has only 14 transactions over a 2-year period, for this security, including purchase and sale.  Purchased 14k shares of a mutual fund at $1.30 a share in Q3, then 500 or so shares were added at quarterly intervals as dividends.  Where it got weird was at the end of year one when 14,500 shares somehow generated a FV of in excess of $120k.  And these were the only two transactions for this security in that first year.  Any ideas?  

Jim_Harman

Marty Dopps said:

To mshiggins, thanks for the suggestion.  I did just that and found the offender.  What's odd is that the account has only 14 transactions over a 2-year period, for this security, including purchase and sale.  Purchased 14k shares of a mutual fund at $1.30 a share in Q3, then 500 or so shares were added at quarterly intervals as dividends.  Where it got weird was at the end of year one when 14,500 shares somehow generated a FV of in excess of $120k.  And these were the only two transactions for this security in that first year.  Any ideas?  

Have you checked the price history for this security? Perhaps a wrong value was recorded at some point. 

Also when you recorded the dividends, did you enter them as shares added or as dividends reinvested? Reinvest would be the proper transaction to use.

Marty Dopps

Marty Dopps said:

To mshiggins, thanks for the suggestion.  I did just that and found the offender.  What's odd is that the account has only 14 transactions over a 2-year period, for this security, including purchase and sale.  Purchased 14k shares of a mutual fund at $1.30 a share in Q3, then 500 or so shares were added at quarterly intervals as dividends.  Where it got weird was at the end of year one when 14,500 shares somehow generated a FV of in excess of $120k.  And these were the only two transactions for this security in that first year.  Any ideas?  

With only 14 transactions over two years, I've verified the price history was correct.  I did, however, record the dividends as shares added and not reinvested.  Just out of curiosity, what might that impact?  

Jim_Harman

Marty Dopps said:

To mshiggins, thanks for the suggestion.  I did just that and found the offender.  What's odd is that the account has only 14 transactions over a 2-year period, for this security, including purchase and sale.  Purchased 14k shares of a mutual fund at $1.30 a share in Q3, then 500 or so shares were added at quarterly intervals as dividends.  Where it got weird was at the end of year one when 14,500 shares somehow generated a FV of in excess of $120k.  And these were the only two transactions for this security in that first year.  Any ideas?  

Adds may affect Quicken's performance calculations and the cost basis when you sell. In the Add transactions, you got to specify the number of shares and also the purchase price and date. If the price is zero, the performance and basis will be way off. 

Often the transaction detail is missing in a 401k statement, and all you get is the starting and ending share count and market value, so you have to approximate the reinvestments.

If the starting share count is S1 and market value is V1 and the ending shares and value are S2 and V2, then you can set the reinvest date to the end of the period, shares purchased to (S2-S1) and the amount of the dividend to (S2-S1) * (V2/S2) for a pretty good estimate of what actually happened.

mshiggins

Marty Dopps said:

To mshiggins, thanks for the suggestion.  I did just that and found the offender.  What's odd is that the account has only 14 transactions over a 2-year period, for this security, including purchase and sale.  Purchased 14k shares of a mutual fund at $1.30 a share in Q3, then 500 or so shares were added at quarterly intervals as dividends.  Where it got weird was at the end of year one when 14,500 shares somehow generated a FV of in excess of $120k.  And these were the only two transactions for this security in that first year.  Any ideas?  

Marty, so the problem security had an initial buy, a series of add shares transactions, and a sale? What did you input for the Add Shares cost for the reinvested dividends?

Marty Dopps

Thanks Jim, I went ahead and changed the transaction types from adds to reinvested.  No change in the outcome, even after closing and opening Q a few times.  What's interesting is that I went into the Holdings view for this particular account, and the Securities Value, the Cash Balance and the Total Market Value figures all remain unchanged, even after scrolling back to random dates, even going so far as to choose some dates before the account even existed.  For some reason those three fields are absolutely locked and won't change, in this account.  Hmmmm

Marty Dopps

mshiggins, yes, that is correct.  For the Add Shares, each quarterly transaction was valued at around $170, so that was the total cost entered.  Share prices were about $1.50 each, so you'd see that I added about 110 shares with each quarterly dividend.  I've confirmed that my running cumulative share total was accurate and the total sale included all those particular shares. 

q_lurker

I don't think anything has been said about the Validate and repair option (File Operations).  I would include the rebuild lots option.  I don't have high hopes, but it should be tried.   

Marty Dopps

q.lurker - I did some research on what that entails, saved a copy of my master Q file and loaded it on another computer to do some "testing" with the Validate and Repair steps.  Guess what - IT WORKED!!!!   Thank you very much!!!!  

q_lurker

Marty Dopps said:

q.lurker - I did some research on what that entails, saved a copy of my master Q file and loaded it on another computer to do some "testing" with the Validate and Repair steps.  Guess what - IT WORKED!!!!   Thank you very much!!!!  

I don't have high hopes, ...

That is why I am not a bettor.  Glad that worked for you.  Also kudos to you for doing some background checking to figure out the right way and manage the risks.  Personally, just having a backup copy would have been sufficient control, rather than using another computer, but I'll never criticize taking a safe route with your financial records.  

Well done.

Marty Dopps

Thanks for the kudos.  I retired last year after 38 years in accounting and finance for a $4 billion firm.  The past two decades I was in a leadership role involved in MANY financial systems implementations and upgrades.  When you're responsible for the payroll system for upwards of 9k people, you take the concepts of backing up and "playing in the sandbox" (our term for test versions) VERY seriously.  :-)