Quicken calculating investment basis and capital gains incorrectly

markslang

I have been using Quicken Home and Business for decades. Among other things, I have a brokerage account with several mutual funds where I have entered transactions since 1994. When I recently sold shares in one mutual fund (previously had purchased and reinvested, only), Quicken reported my basis and capital gains in my tax report, but it differed from what the brokerage provided. After much research, I found a tax report for capital gains for the year, and it showed that Quicken divided my sale transaction into 11 different sales of specific lots of shares selected in some unknown fashion. The problem is that my investments, including the one in question, are set to use average cost for capital gains. The box is selected in the security detail (Use Average Cost). Further, when I recorded the transaction, the box to "Specify Lots" was greyed out. Yet, somehow Quicken chose to select lots anyway without giving me any indication (other than the tax report I discovered after much work).

I had two sessions on the Quicken support line on this issue, one in Jan 2020 and one a few days ago. Both agents shared my screen, and saw what was happening. The first agent told me it was a bug in the program, and asked to file a report using the report a problem link, which I did. The second one did even more extensive work to repair files. We even deleted every transaction I had made on this security that either sold or removed shares (fortunately only 4 transactions since 2018). After repairing files, restarting the program, and reentering the transactions, Quicken still did exactly the same error. The capital gains report is exactly the same. This time the agent told me it was nothing in the program, and I should restore from a backup more than two years ago and reenter all my transactions (hundreds of them). This clearly makes no sense.

Quicken clearly has all the data it needs to calculate average cost. I have manually entered every transaction on all my accounts for decades, and reconciled them each month with statements. The fact that the total value and number of shares is correct suggests that data is all correct. I am trying to find out what I can do to get this corrected. This seems to be a major bug in a very expensive program that Quicken support refuses to acknowledge. If there are other suggestions, I would be very open to hearing and trying them. Thanks.

markus1957

Average Cost computes the average cost of remaining shares on an oldest shares are removed first basis.  I've not seen differences of more than a few pennies in my sales history with average cost mutual funds.

Specific examples would be required to determine the difference between Quicken and your broker.  With only 4 transactions, that should not be difficult to provide. 

markslang

It is my understanding that average cost is average cost. That is, the total price paid for all shares purchased at any time prior to the transaction, divided by the total number shares acquired. It will change a bit as more shares are accumulated in later times. Since Quicken has the data on each of my purchase transactions, it can easily make that calculation -- even if it needs to do it after the fact. I would think that Quicken could easily correct any kind of corruption that might have occurred along the way (if that is part of the problem) by simply going through the individual transactions again. This is not complicated for a computer.

In my case, for example, I sold $75,000 worth of this mutual fund near the end of last year. That was an investment with good returns over the decades I have owned it. Quicken reported a taxable capital gain of about $45,700. I had estimated my taxes based on that number. However, the broker reported the actual gain under the average cost method was about $51,300 when I later got my tax statements for the year. That is not a small difference (about 12%). I could get in a lot of trouble with the IRS if I reported that number and thus paid less tax than I owed. Needless to say, I reported the broker's number on my taxes.

Because these kind of investments, with the returns reinvested as often as monthly over many years, have literally hundreds of transactions, one of the main reasons I purchased Quicken was to keep track of all that data and give me the numbers I need from it. That is the reason for my concern.

Jim_Harman

One thing you could try is to back up first, then do a File > File Operations > Validate and Repair with the Rebuild Investing Lots option set, to see if that affects Quicken's calculation.

Also since you have all the transactions in Quicken and there have not been any previous sales, it should not be too hard to figure out what the correct capital gain should be and compare that to your broker's numbers. 

Have there been any share class conversions, transfers between accounts, splits, return of capital or any other odd events in this fund?

Are there any Placeholder transactions? They will interfere with Quicken's capital gains calculations. To make these visible, go to Edit > Preferences > Investing Transactions and make sure Show Hidden Transactions is checked.

You might also click on Holdings for the account and go back in time to see if there any unexplained jumps in the cost basis and if the basis shown there matches the basis reported by your broker.

markus1957

As an old fund, any chance the shares are segregated into covered and non-covered pools at the broker?

I noticed a long held fund of mine being shown that way with seperate cost basis on the broker website.

Quicken would not be doing that. 

markslang

Thank you for your inputs. I did check the show hidden transactions setting, but it did not change anything I could see. I have run the validate and repair functions, including to rebuild the lots. In fact, while on with the agent, she had me hold down control and shift, if I remember, when selecting the Validate and Repair menu item, which provided an option for super file validation, which we did. We even deleted the four transactions, then ran the repair functions, then restarted the program, then reentered the transactions. Nothing changed.

While the data is all there for me to calculate myself, there are well over a hundred transactions, so that would be a many hour process prone to errors. Besides, if it happens on this investment, it is likely to happen on others when I sell them. The transactions on the example I shared are mostly shares bought and dividend reinvestments. There is one stock split, which is when I was moved to another class of shares. There are a few shares added transactions very early. I believe those are purchases I made before I started using Quicken that I entered so I would have all the individual transaction data. The price and number of shares are saved the same as if I had entered those as shares purchased, but it avoided problems with cash balances as Quicken tried to deduct the purchase prices for shares bought from cash at the brokerage which did not exist.

As far as the broker, they assured me that the basis and capital gains reported include all my transaction history. They do not report this figure to the IRS since many of the transactions predate the reporting window, which is what I assume was meant by the covered and uncovered pools. But they do provide for me the number I need to accurately calculate my taxable gains.

I am still open to suggestions if there are any. Thanks again. This has been very frustrating.

Jim_Harman

I would look closely at the split or share class conversion by clicking on Holdings and adjusting the As of date to check the cost basis before and after. The basis should be the same before and after this event.

The share class conversion should show in your transaction list as one Removed transaction followed by a series of Added transactions, one for each tax lot.

Recent versions of Quicken grossly miscalculate the cost basis for share class conversions if the security is set to Use Average Cost. If there are N tax lots, the resulting cost basis is N times what it should be.

See this Idea post
https://community.quicken.com/discussion/7861196/fix-mutual-fund-conversion-so-cost-basis-is-correct-if-holding-is-set-to-use-average-cost

markslang

Thanks. In my case, the cost basis is exactly the same just before and just after the stock split class conversion. Again, my problem does not seem to be a problem with the calculation itself, but the fact that Quicken is not using average cost even though specified but is selecting lots instead. The capital gains report for my $75,000 sale shows 11 different sell transactions, all with the same sold date but with different bought dates and numbers of shares with each. I assume that means Quicken is actually calculating by selecting specific lots.

Jim_Harman

OK, look at the total cost basis in Quicken just before the sale. Does that match what your broker reports? If not, go back in time and compare to past brokerage statements to see where the discrepancy occurs.

[Corrected] With Average Cost, and assuming there have been no prior sales, returns of capital,  or commissions, I think the gain should be 

$75,000 - (75000 / total market value before sale) * total cost basis before sale.

Is that the case, or is Quicken reporting something different?

markslang

I spot checked some statements. It appears that the cost basis in Quicken is with a few cents of that in my broker statements up until I made the first sale of this security in 2018. That is when Quicken first did the select lots version. The discrepancy gets greater after each sale. Again, this suggests that Quicken is using the select lots method and adjusting the cost basis accordingly (despite being set to use average cost). The broker instead is keeping with average cost.

I am not sure about your logic on the $75,000. That was the sale value of my example transaction. The gain is, as I noted, about $51,000 (from the brokerage). The cost basis is the difference.

markus1957

Quicken is not using lots.  You raised my curiosity so I went to a Vanguard account and a stock fund that had only one sale in a 12 year history; July 1, 2020.  The other transactions being buys, dividends and dividend reinvestments.

The cost basis on my Vanguard quarterly statements thru June 30, 2020 matched to the penny with the cost basis reported in Quicken.  So I look today and see that the cost basis in Quicken after the sale on July 1 does not match the cost basis on the Vanguard website; nor does the capital gain reported by Vanguard match what Quicken computes.

Having been used to the exact penny match up to this point, I looked deeper and confirmed that Vanguard does indeed maintain non-covered and covered cost basis pools as I mentioned before.  They also report capital gains by using the cost basis of the non-covered pool until that pool of shares is depleted.  When I looked at the transaction confirmation of the July 1 sale, the per share cost basis of the sold shares matched the non-covered pool per share cost basis amount to the penny (only a small number of shares were sold relative to the total held, even as related to the non-covered pool).

So I can confirm that at least as far as Vanguard is concerned, they are computing cost basis separately for non-covered shares versus covered shares.  Quicken treats them as one pool.  Not sure that the IRS has any clear guidance on the matter.  Vanguard is a big enough outfit that I'd bet they are in good standing with their method; no reason IRS would argue with Quicken's method either.

markslang

Thank you for pointing that out. Vanguard did seem to report average cost using the non-covered average cost. However, the statement provides the overall average cost. If I use that, it is still different from what Quicken provides. There is also the issue of the capital gains report. Please print a capital gains report from Quicken for 2020 and see if you get the transaction broken down into a number of separate sales. Quicken shows me 11 separate sales.

markus1957

Vanguard is not using the cost basis on the statement (to compute the gains). It is using the non-covered basis until those lots are depleted. You've already confirmed that. (The Vanguard statement merely shows the sum of what can be two very different subsets as it pertains to average cost per share).

As for the report, Quicken is showing the depletion of the oldest lots first. The basis is being maintained as one pool. (Adding- you will note in the report that if you compute cost basis/shares of each line, it is the same $ amount; e.g., the average cost per share).

Adding- Until the account reaches zero shares for the fund in question, the cost basis will never agree; they will converge asymptotically but will never be exactly the same until they are both computing cost basis from the same data set rather than 2 subsets as Vanguard is doing.

q_lurker

A variety of comments -- some relevant, some not.

It is my understanding that average cost is average cost. 

Not always.  (a non-relevant comment).  In prior days,IRS allowed two different forms of average cost for mutual funds - 1) as you might expect, and 2) considering short term holding different that long term holdings, that is, two separate average cost computations.  Quicken never tried to address the #2 case and I don't know if any financial institutions that used it.  Further to that, once you committed to average cost, (AFAIK) IRS required you to stick with that approach. 

Since new tax reforms, IRS does now allow you to switch procedures and also apply average cost to stock holdings.  If you switch back and forth between average cost and by-lot cost, I would that too breaks your understanding. 

But as far as Quicken is concerned, the basic, simple, intuitive average cost is all it has ever done. 

There is one stock split, which is when I was moved to another class of shares. 

and later 

... up until I made the first sale of this security in 2018. 

A stock split and a class conversion change would normally be handled in Quicken differently.  The check you made before and after the conversion may be enough, but be aware that a conversion using Remove Shares and Add Shares for different securities (different classes) with a prior sale of the original security may alter the average cost computation.  Again, I don't think this applies to you but I don't have all the details.  

... shows 11 different sell transactions, all with the same sold date but with different bought dates and numbers of shares with each. I assume that means Quicken is actually calculating by selecting specific lots.

Not a valid assumption.  You should be able to see that for each of the 11 listed sales, the cost per share (basis / shares) is identical.  That SHOULD apply to your 2018 sales as well as the 2020 sales.  Similarly, you should be able to compare the purchase cost of each of those 11 lots to the cost Quicken shows in the cap gains report.  Those comparative values SHOULD differ.  Quicken does 'sell' the shares based on a FIFO sequence even for average cost securities.   

I hope you are on track to reach a resolution on this through the comments from @markus1957 .  Mostly I wanted to clarify some concepts not only for you but for other readers in the future.  To wit:  Quicken does try to do this as simply as possible, but their processes can have errors and can differ from the processes of financial institutions.

(Caveat:  Like you, I use Quicken because it can keep track of all the details.  Unlike you, because Quicken can keep track of all the details, I stick with by-lot costs on all holdings.  I do not use average cost on anything.  Personal choice.  So I do not offer my comments as the voice of experience.  I have experimented over the years but not lately.  FWIW.)      

markslang

OK. This is making more sense. It does seem to be a difference in how Vanguard calculates average cost and how Quicken does it. Before my first sale, both Quicken and Vanguard have the same average cost overall (combining non-covered and covered). That is 53.01. After that first sale, they differ. Vanguard does seem to have used the average cost for non-covered shares (you have to search hard to find that data on Vanguard's website, but it is there.) That is 42.83. I notice that Quicken uses the cost of 53.01 for each of my sales, and Vanguard uses the cost of 42.83 for each of my sales (still in non-covered). However, Vanguard provides an overall average cost on the monthly statement, and that changes. Before my first sale, Vanguard has 53.01. After that first sale in 2018, Vanguard shows the overall average as 53.73. After my second and third sale in 2019, Vanguard shows 57.03. Vanguard must be recalculating that overall average, perhaps with the shares sold removed. Of course, Vanguard does not use that average in their capital gains calculations, but it is confusing to see it compared to Quicken.

My Quicken capital gains report does, on further study, show the same price per share for all the 11 transactions listed, 53.01. However, it is not starting strictly with the oldest shares. Nine of those 11 transactions do correspond with my earliest purchases, in 1994 and 1995. However, there is at least one additional purchase in 1994 that is not listed. Further, there are two purchases from 2001 that are included. I don't see the logic here. Quicken must do something more complicated than order of purchase.

Because of the two different methods, this is still confusing to me. However, the difference in methods seems to be the explanation. It seems to be true that Quicken uses the most basic method. Vanguard uses a more complicated method for some reason. Since I have used the Vanguard data on my taxes for the last two years, I will need to stick with it. Perhaps Vanguard does that so that, when I get into selling the covered shares, which they will report to the IRS, they can report the average cost of all the shares on which they are reporting. That may be an IRS requirement. Presumably, if I had used the Quicken data, it would differ from what the brokerage reports, so I might need to explain it to the IRS at some point.

Thanks for all the help. This has been enlightening. I am just disappointed that neither Quicken nor Vanguard customer support made any mention of any of this when I asked. They should know about this so they can help customers understand.

Jim_Harman

Here is a link to Vanguard's explanation of cost basis methods
https://investor.vanguard.com/taxes/cost-basis/methods#:~:text=When%20we%20calculate%20cost%20basis,of%20a%20hands%2Don%20investor.

The definitive guide to cost basis calculations is IRA Pub. 550. 
https://www.irs.gov/pub/irs-pdf/p550.pdf
The info on mutual funds starts on p. 44. 

I don't see anything in these publications about selling the non-covered lots first when using average cost. I wonder if this security or the account default is set to FIFO rather than Average Cost at Vanguard. This could explain what you are seeing, because the non-covered shares would be treated as one big lot and sold first.  

markus1957

Page 44 seems to make a distinction between covered and non-covered shares.

Further Google searches make it look like segregating the cost basis of covered/non-covered average cost holdings is industry practice.

adding- it makes sense since as of January 2012, the investment house is legally responsible for reporting cost basis of holdings acquired after that date.  They have no legal responsibility for holdings acquired before that date.

Jim_Harman

Thanks for the further research. I think your last sentence should say "They have no legal responsibility for holdings acquired before that date."

markslang

Vanguard is a large and very reputable financial firm. I am sure they are doing what the IRS prefers. The IRS instructions mention covered and non-covered, but are not clear on how to treat them. However, Vanguard on its website says that what you put on your IRS capital gains forms must match the data they supply to the IRS for covered shares. I assume that is why Vanguard treats covered and non-covered differently. If one had been using a composite average cost on tax forms for non-covered shares, then continues that for covered shares, the data reported to the IRS by the brokerage for the covered shares will not match.

My Vanguard mutual funds are all set to average cost. That is the default for mutual funds at Vanguard. When you search down through the holdings, and ask to see capital gains, then identify lots, it shows two lots: one for non-covered and one for covered, with average costs calculated independently for each. I had not noticed that before the discussion on this forum. Vanguard explicitly says they will not report the basis data to the IRS for non-covered shares, but they will report it to the account holder for mutual funds where they track all history. They do report basis data for covered shares to the IRS.

It is still a bit surprising to me that something this common is not described prominently in the Vanguard website and the Quicken help files. That also means the composite average price that Vanguard includes in its statements is really only for the interest of the account holder, and should not be used for tax purposes.

Thanks to all for a very enlightening discussion.

markslang

Just FYI. I talked to a Vanguard agent about this issue, who had to consult an investment advisor. They reported that the IRS requires them to track shares in four categories, non-covered, covered, short-term, and long-term. They calculate the average cost separately for covered and non-covered shares also because the IRS requires it. That is, the IRS requires that they track and report the average cost for all covered shares that are sold based only on the total number of covered shares. It is up to the client to track and report the average cost (or other method of cost basis) for non-covered shares. Vanguard calculates and reports the average price of just the non-covered shares to clients for the client's convenience. They said the way they tracked data in the past only allows them to report average price for older, non-covered shares.

Every other financial firm I found in about a hour of searching on the web seems to do the same thing, if they report non-covered shares at all. This suggests that the simple overall average price cost basis reported by Quicken (including covered and non-covered shares as one lot) is only useful if the user wants to know it, but should not be used for IRS reporting purposes despite it appearing in the Quicken tax reports. I think it would be useful if Quicken and others provided that information. There may be nuances to this that allow an expensive tax professional to do something different in certain circumstances, but I think this is the answer for us normal investors.

Thanks again for all the help tracking this down.

Symondsr

How can I do "Mutual Fund Conversion" in older version of windows Q - I do not have the choice of a transaction with that name.  (I can change the name of the mutual fund from one class to another, I suppose, but have not tried that).  Basis is lost if I just do removed and added

Jim_Harman

If the share price is the same or close enough for your accuracy requirements, you can just change the name.

What version of Quicken are you running? The Mutual Fund conversion has been available for 5 years or so. Click on Enter Transactions in the account and look through the dropdown list.

Note it is not available if your account is set to "Single Mutual Fund". 

Symondsr

I have very old version 2005 Premium Release R6.  I have the same problem (losing basis) when I move shares from Trad IRA to Roth (It does not really affect taxes but it is a score card of performance).  And Brokerage house made an error that was reversed but if I track the steps in Quicken I lose basis. "Shares Transferred Between Accounts"

Symondsr

Maybe there is a way to overwrite basis - dangerous, possibly but I usually know what I am doing.

q_lurker

An alternate to MF Conversion is Corporate Acquisition.  Not sure if that was available in 2006 or not.  CA gets applied across ALL accounts so if the converted security shares were only converted in one account, the transactions may need to be deleted in the other accounts. 

Both MF Conversion and CA rely on Remove Shares and Add Shares to effect the change.  You can enter those manually but the wizard-type action does the Add Shares for each individual lot.  That can be very helpful for positions increased by reinvestments. 

A manual Remove Shares / Add Shares pairing is my normal recommendation for correcting a cost basis value.

Any such Remove / Add Shares pairing will mess with Amount Invested and related performance parameters (like ROI).  Properly done, they will not mess with Average Annual Return.

Brokerage house made an error that was reversed but if I track the steps in Quicken I lose basis. "Shares Transferred Between Accounts"

Not sure what that means.  Not uncommon the brokerage house transactions downloaded into Quicken are insufficient in detail.

Symondsr

I mentioned earlier that the Removed/Added done manually did not preserve cost basis (believing it was my older version shortcoming).  I thought that was your opinion.  If there is a way to do that (with those functions, remove and add) I would like to know.  In one case I was swapped different classes of the MF but the new fund had same number of shares and same price but I lost the basis - goes to zero.  (Might be able to make that work as a rename)  But now doing conversions from trad IRA to Roth.  Same stock but different tax "house"/account number.

Jim_Harman

In the Shared Added dialog there is a place to enter the acquisition date and cost basis. That should preserve the basis.

When you click on Enter Transactions, do you have a Shares transferred between accounts transaction available? Alternatively for Roth conversions where there is no capital gains tax, it is generally not important to track tax lots or cost basis, so you can Sell in one account, transfer the proceeds, and Buy at the same price in the other.

Symondsr

I have "Shares transferred Between Accounts" - mentioned that initially, but the basis is lost in the transfer in my version - goes to zero.  Possibly u were suggesting Buy/Sell at the Basis value?

q_lurker

If the Removed/Added comes from the brokerage, it is likely to omit the basis info on the Added side.  

If using any of the three 'wizards' mentioned (MF Conversion, Corporate Acquisition, or Shares Transferred between Accounts), Quicken will preserve the cost basis on the Add Shares portion - at least in normal situations - and that does go back to QW2005 as best I recall (not including MF conversions that far back).

I used Shares Transferred successfully as far back as 1993, and Corporate Acquisition (I believe) in July 2005 (QW2002 or QW2005, I think) for MF conversions.  In both cases (all cases for me), Basis has transferred successfully.    

Note that Cost Basis is not the same as Amount Invested.

Add Shares done manually, the user needs to provide the basis of the added shares as Jim documented.        

Symondsr

Nothing is coming automatically from the broker.  I will need to look more closely to see why "Shares Transferred" not working for me.