Setting up brokerage accounts

DebSRay

How do I get rid of the "Cash" entry? I want the "Market Value as of Today" to say: $13,532.00 without deducting the cost of the investment (which seems the obvious manner it should be displayed). I am entering my brokerage info manually …

jacobs

You should not have a negative Cash amount in your portfolio. If that negative cash amount is what you used to purchase the security, then the problem is that you didn't account for how the money was transferred into this account in order to make the purchase. That is, if you transferred cash from a checking account into this new investment account, you need to mirror that action in Quicken, with a transfer from your checking account o the brokerage account. That would result in a positive Cash amount in the portfolio. Then you'd have a Buy transaction to purchase the sercurity; the Buy transaction set aCash and reduces the Cash amount to zero. Your negative Cash amount suggests that you don't have a transaction transferring the cash into this account to start with.

Jon

An alternative would be to use Add Shares transactions instead of purchases. That would let you add mutual fund shares without spending any cash, so you wouldn't need any dummy deposits.

jacobs

Is there really not a way that Quicken can deal with these very simple record keeping transactions ?

Quicken Mac can definitely handle this — unless your real-world account is set up for Average Cost Basis, which Quicken Mac inexplicably does not support yet. Vanguard mutual funds held at Vanguard typically default to Average Cost Basis unless you've specified otherwise with Vanguard, but since you own Vanguard funds in an E*Trade account, the question is what cost basis method you have in place for this account at E*Trade.

In the example below, $16,565.70 is the cost basis of the sale for fed tax purposes.

No, in your example $16,565.70 is the gross proceeds from your sale. That's not the cost basis. If your account at E*Trade is using FIFO, then the 579.542 shares you sold on 4/2/07 came out of the original purchase of 5,105.551 shares on 12/23/05. Those shares were purchased for $28.4995681/share, and you sold 579.542, so your sale had a cost basis of 579.542 shares x $28.4995681 = $16,516.70, and your gain on the sale was $16,565.70 - $16,516.70 = $49.00.

Your account's cost basis would then be the basis of the remainder of the original 5,105.551 shares — e.g. 5,105.551 - 579.542 = 4526.009 x the purchase price of $28.4995681/share = $128,989.3031 + the other three purchases of 2,857.70 + 758.49 + 2,249.69 = 134,855.1831. Well, that's ballpark close to Quicken's cost basis of $134,414.10 — but it's not right.

So let's re-do the math using LIFO rather than FIFO. Now, the sale of 579.542 shares would consist of the 21.821 shares from the 3/23/07 purchase, the 89.588 shares from the 12/23/06 purchase, and the remaining 468.133 shares from the original 12/3/05 purchase. Your remaining shares consist of 4,637.418 from the 12/23/05 purchase (e.g. the original 5,105.551 - 468.133 = 4,637.418) plus the 62.663 shares from the 6/2/09 purchase. 4,637.418 + 62.663 = 4,700.081 shares. And the cost basis of those remaining shares is:

      4,637.418 x $28.49956841 = $132,164.4115
   + 62.663 x $35.90140913 = $2,249.69

for a total of (drumroll) $134,414.1015 — which is exactly what Quicken is showing. So, we now know you have your Quicken account set to use LIFO, and the math in Quicken works correctly. The question is: what's the basis for the account at E*Trade? I just Googled E*Trade, and it appears their default if you don't specify otherwise is FIFO. (I don't know if that was the case back in 2007, but it probably hasn't been changed since you created your account there.) You can find this within your E*TRADE account preferences under "Lot Selection". If you find it is FIFO, you can change the account setting in Quicken. (You may need to delete and re-add the sale transaction so the sale transaction uses up the shares from the correct lots.)

If your E*Trade account is using something other than LIFO or FIFO (like HIFO, LOFO or MT), Quicken can't match it exactly. But if you find out what method is being used, you can emulate it by manually selecting the specific lots to sell for each sale transaction. If you have a lot of sales over the years, this could be rather tedious to re-create… but it is possible to do.

Make sense?

jacobs

You should not have a negative Cash amount in your portfolio. If that negative cash amount is what you used to purchase the security, then the problem is that you didn't account for how the money was transferred into this account in order to make the purchase. That is, if you transferred cash from a checking account into this new investment account, you need to mirror that action in Quicken, with a transfer from your checking account o the brokerage account. That would result in a positive Cash amount in the portfolio. Then you'd have a Buy transaction to purchase the sercurity; the Buy transaction set aCash and reduces the Cash amount to zero. Your negative Cash amount suggests that you don't have a transaction transferring the cash into this account to start with.

DebSRay

Ok, that helps somewhat … especially for new purchases. I am trying to build my brokerage account from purchases made over time … starting in 2005. I will try making 'dummy' deposits in my checking account and transferring them? Thank you for taking the time to respond !

Jon

An alternative would be to use Add Shares transactions instead of purchases. That would let you add mutual fund shares without spending any cash, so you wouldn't need any dummy deposits.

DebSRay

Ok, I'll also experiment with Add Shares … thank you so much for that advice !

DebSRay

Oh man, I have been keeping detailed Cost Basis spreadsheets for my investments for decades … always matching to the penny with what the brokerage indicates … I thought it would be more streamlined to move everything into Quicken … WRONG! I do not want to connect Quicken to my accounts, so I enter the info manually … after many hours, I cannot figure out what Quicken does. In the detail below, I have used "Add Shares", on the advice of a community member. Fine, that works, I get it. However, if you add a sale in, the numbers are wonky. In the example below, $16,565.70 is the cost basis of the sale for fed tax purposes. If you add the costs, you get: $134,806.18 for 4700.081 shares …

However, on the portfolio screen, there are two problems 1) The cost basis is:$134,414.10 and 2) The market value includes the proceeds of the sale, so I will have to have some kind of dummy transaction to get rid of that. I experimented with using "Remove Shares" … that takes care of the cash entry, but because of FIFO or LIFO, the cost basis is still not accurate.

Is there really not a way that Quicken can deal with these very simple record keeping transactions ? Thanking you in advance for any advice !

Jon

If you're using Add Shares instead of Buy for historical transactions you'll also want to use Remove Shares instead of Sell, otherwise you'll still have issues with the cash balance in your account. You should select some point in time where you will start keeping track of the cash in your account and not have any Buy/Sell transactions before that date.

In the Settings panel for your account there's a Cost Basis section at the bottom where you can select whether to use LIFO or FIFO for different types of assets.

jacobs

Is there really not a way that Quicken can deal with these very simple record keeping transactions ?

Quicken Mac can definitely handle this — unless your real-world account is set up for Average Cost Basis, which Quicken Mac inexplicably does not support yet. Vanguard mutual funds held at Vanguard typically default to Average Cost Basis unless you've specified otherwise with Vanguard, but since you own Vanguard funds in an E*Trade account, the question is what cost basis method you have in place for this account at E*Trade.

In the example below, $16,565.70 is the cost basis of the sale for fed tax purposes.

No, in your example $16,565.70 is the gross proceeds from your sale. That's not the cost basis. If your account at E*Trade is using FIFO, then the 579.542 shares you sold on 4/2/07 came out of the original purchase of 5,105.551 shares on 12/23/05. Those shares were purchased for $28.4995681/share, and you sold 579.542, so your sale had a cost basis of 579.542 shares x $28.4995681 = $16,516.70, and your gain on the sale was $16,565.70 - $16,516.70 = $49.00.

Your account's cost basis would then be the basis of the remainder of the original 5,105.551 shares — e.g. 5,105.551 - 579.542 = 4526.009 x the purchase price of $28.4995681/share = $128,989.3031 + the other three purchases of 2,857.70 + 758.49 + 2,249.69 = 134,855.1831. Well, that's ballpark close to Quicken's cost basis of $134,414.10 — but it's not right.

So let's re-do the math using LIFO rather than FIFO. Now, the sale of 579.542 shares would consist of the 21.821 shares from the 3/23/07 purchase, the 89.588 shares from the 12/23/06 purchase, and the remaining 468.133 shares from the original 12/3/05 purchase. Your remaining shares consist of 4,637.418 from the 12/23/05 purchase (e.g. the original 5,105.551 - 468.133 = 4,637.418) plus the 62.663 shares from the 6/2/09 purchase. 4,637.418 + 62.663 = 4,700.081 shares. And the cost basis of those remaining shares is:

      4,637.418 x $28.49956841 = $132,164.4115
   + 62.663 x $35.90140913 = $2,249.69

for a total of (drumroll) $134,414.1015 — which is exactly what Quicken is showing. So, we now know you have your Quicken account set to use LIFO, and the math in Quicken works correctly. The question is: what's the basis for the account at E*Trade? I just Googled E*Trade, and it appears their default if you don't specify otherwise is FIFO. (I don't know if that was the case back in 2007, but it probably hasn't been changed since you created your account there.) You can find this within your E*TRADE account preferences under "Lot Selection". If you find it is FIFO, you can change the account setting in Quicken. (You may need to delete and re-add the sale transaction so the sale transaction uses up the shares from the correct lots.)

If your E*Trade account is using something other than LIFO or FIFO (like HIFO, LOFO or MT), Quicken can't match it exactly. But if you find out what method is being used, you can emulate it by manually selecting the specific lots to sell for each sale transaction. If you have a lot of sales over the years, this could be rather tedious to re-create… but it is possible to do.

Make sense?

DebSRay

The generosity of your response has left me breathless … thank you so much for taking the time to do this … Your explanation is perfectly clear … my brokerage accounts are set up for Average Cost Basis - the $16,565.70 came from:

GrossProceeds - (SalePrice-AvgCostBasis)*NumberSold. $20000 - (34.51 - 28.584)*579.542

So, going back through all my spreadsheets would be working harder, not smarter. I am just going to pick a recent date and begin the process from there … although, I am very confused as to why Quicken would not offer an Average Cost option …

Again, thank you so much for your response …

jacobs

I am very confused as to why Quicken would not offer an Average Cost option …

Me, too! It's been inexplicably overlooked. I could understand that it wasn't built at when Quicken Mac was first released a decade ago, but I don't understand how it hasn't been addressed after all this time. Especially since it's a default cost basis for Vanguard funds, and is the required basis method for Canadians. Well, hopefully some day…

Jon

BTW there is a Idea thread asking for ACB, you should add your vote & comments:

https://community.quicken.com/discussion/7670157/cost-basis-adjusted-cost-basis-acb-9-legacy-votes