Reporting when transferring securities to a new investment account

MochaMan

The updating of my estate plan required the creation of a new investment account for my securities. After running the transfer procedure, several irregularities were detected.
Attached is a PDF document that includes three snapshots of three securities: the old account before running the transfer procedure, the new account only, and the old and new accounts combined. These three snapshots display the problems.
>> The transfer procedure does not differentiate between invested and reinvested amounts. Consequently, the new invested amount is the sum of both the invested and reinvested amount.
>> When both the old and new accounts are activated, the historical income from the investments returns, but the original invested amount is included again. I find it interesting that the number of shares, cost basis and income are correct.
I can only guess that there are problems in the transfer procedure or aggregation of data and calculations after the transfer of securities.
Comments from users and technical support would be appreciated.

MochaMan

I will provide link to referenced attachment

NotACPA

Did you move ALL of the old account to the new account?  If so, the easiest method is to simply disconnect the old account from download, delete the account and provider name and re-enable the account for download from the new provider.

MochaMan

Here's the link to the PDF document...
https://1drv.ms/b/s!ArGjUATNO4CVwoxj8sFrGcSfw2QZWQ?e=k0ndQW

MochaMan

> @NotACPA said:
> Did you move ALL of the old account to the new account?  If so, the easiest method is to simply disconnect the old account from download, delete the account and provider name and re-enable the account for download from the new provider.

The old account is a linked brokerage account. It remains active. A linked credit card, check payments, and auto deposits and payments flow through this account.

q_lurker

You are in a territory of whimsy for Quicken.  Amount Invested is not a good measure especially when more 'unusual' events occur.  That means that related measure (like ROI) can also get rent asunder.  Cost Basis transfers fine.

Also, your observation about reinvestment is also accurate.  Before the transfer, there is a distinction between Amount Invested and Amount Reinvested.  After the transfers (Remove Shares in old account; Add Shares in new account), Amount Reinvested goes to zero as it gets lumped in with the Amount Invested.  

In my view, both Amount Invested and Amount Reinvested are bogus measures with little real-world meaning.  My preferred performance measure is Average Annual Return and I prefer that through the Investment Performance Report.  

MochaMan

I contacted technical support and suggested they run some test cases to diagnose my problems. I assume it would be relatively easy to replicate the problems.
The performance reports under the Investing tab need a major overhaul. Having a fixed number of reports seems like very old technology. The user should have the option to add reports using templates and delete reports. To avoid a lot of extra work, a feature should be included to duplicate an existing report. I frequently find the need to use the same report format with a different subset of securities.

Jim_Harman

Much of what you describe is already in Quicken, although I agree it could be modernized and better organized.

The reports found in the Reports > Banking, Comparison, Investing, Net Worth & Balances, Spending, and Tax menus are all templates. These cannot be deleted but you can customize and save them. You can duplicate a template or saved report by saving it with a new name. You can delete saved reports (but not templates) in the Reports and Graphs Center by clicking on Manage Reports.

The Investing >  Portfolio views are a different way of looking at your investments. You can customize these and save your changes but unfortunately you can't duplicate them or create new ones. The number of these views is fixed at 18.  This would be a good topic for an Idea post.

q_lurker

Regarding the 'template' consideration:  My setting is for all reports to open to the Customization screen (Edit-Preferences-Customize report/graph before creating).  That applies to 'standard' reports, not saved reports.  I do not expect Quicken to anticipate my needs.  By customizing first, I think I avoid a step of seeing a broad brush report that I am going to want painted differently.  YMMV  

MochaMan

> @Jim_Harman said:
> Much of what you describe is already in Quicken, although I agree it could be modernized and better organized.
>
> The reports found in the Reports > Banking, Comparison, Investing, Net Worth & Balances, Spending, and Tax menus are all templates. These cannot be deleted but you can customize and save them. You can duplicate a template or saved report by saving it with a new name. You can delete saved reports (but not templates) in the Reports and Graphs Center by clicking on Manage Reports.
>
> The Investing >  Portfolio views are a different way of looking at your investments. You can customize these and save your changes but unfortunately you can't duplicate them or create new ones. The number of these views is fixed at 18.  This would be a good topic for an Idea post.

---
To review my investments, I normally use the Portfolio Views under the Investing tab. If I need additional detail, I click on the security name to access the Security Detail View. I don’t find the investing reports in the report’s module particularly helpful for evaluating performance.
I have attached a PDF that shows the flows for a very typical investment – no reinvestments involved. The transfer transactions on 9/12/2018 baffle me. I would think the remove and add transactions would both appear in the investment column and would be valued at the original purchase price. Then the bottom-line totals would make sense.

Jim_Harman

This is an excellent example. The Investment Performance report performs an Internal Rate of Return (IRR) calculation. It basically calculates what the annual interest rate on a savings account with daily compounding would have to be to equal the performance of the selected securities and accounts. The details in the report show the numbers and dates that were used for the bottom line percentage calculation.

You should find that the "Avg Annual Return (%)" columns in the Investing > Portfolio views are the same as the Investment Performance Report for the same time perods.

In your example, you bought P&G on 1/4/2016 for $79, 430.26 and transferred it to another account on 9/12/18. It paid dividends in cash totaling $11,205.80 and on 1/22/20 (about 4.05 years later, (lucky you!) it is worth $126,310. Adding the dividends, if you sold it on 1/22, you would have received a total of $137,515.80

Because the Removed and Added transactions for the transfer were on the same day, they cancel each other out and do not affect the calculation. They are the equivalent of selling and re-purchasing the security at the same price on the same day and thus do not affect the performance. 

Ignoring compounding, your return is (137515.80 - 79430.26) / 79430.26 or 73.1% over 4.05 yr or 18.1% / yr. The compounding in the IRR calculation accounts for the 15.3% average annual return shown in the report. 

If you enter the same numbers into Excel's XIRR function, I am confident you will see the same result.

[I will edit the title of your post to better reflect the discussion]

MochaMan

@Jim_Harman -- I agree the IRR is correct. Since the transfer is valued at the closing price on the day of the transfer, I assume Quicken treats the transfer like a sale and a repurchase. The transfer does not reflect what I intended. It would certainly avoid confusion if you were looking at the same numbers in the investing report, portfolio view and security detail view.

Jim_Harman

Which numbers do you expect to be the same? The average annual return should be the same in the portfolio views and the IPR if you set the same date ranges and accounts/securities.

MochaMan

NotACPA -- After reading your original post again, I decided to test results without transferring securities from my OLD account to the NEW trust account. When I activate both accounts, the two data sets seem to combine seamlessly. Although a limited sample, reports, Portfolio Views and Security Detail Views look correct. Do you see this approach creating any other problems?
When you transfer securities from one account to another account, Quicken seems to treat the REMOVE action like a sale and the ADD action like a purchase. Consequently, the invested amounts get doubled and the reinvested amounts get added to the invested amounts.

Jim_Harman

When you say "invested amounts" I assume you mean the number in the "Amount Invested" column in the Portfolio views.

Quicken's Amount Invested is not useful (to me at least) as any sort of performance measure. It should not be confused with the cost basis, which includes the value of reinvested dividends, is reduced by sales, and has a cloumn of its own. It appears that the main value of Amount Invested is as a component of the Return and ROI (%) calculations. Search the in-product Help for "performance calculations" for more info.

Jim_Harman

In your example where the shares are transferred between accounts, what does Quicken show for the Amount Invested, Return, and ROI (%)? Is the ROI (%) different if the shares are not transferred but stay in one account for the whole period? 

q_lurker

MochaMan said:

NotACPA -- After reading your original post again, I decided to test results without transferring securities from my OLD account to the NEW trust account. When I activate both accounts, the two data sets seem to combine seamlessly. Although a limited sample, reports, Portfolio Views and Security Detail Views look correct. Do you see this approach creating any other problems?
When you transfer securities from one account to another account, Quicken seems to treat the REMOVE action like a sale and the ADD action like a purchase. Consequently, the invested amounts get doubled and the reinvested amounts get added to the invested amounts.

@NotACPA's suggestion was to deactivate the Quicken account from the older FI account and then activate the Quicken account connecting it to the new FI account (whether that is just a new account at the original FI, or a new account at a new FI.  That process has no negative Quicken-world ramifications that I know of.  The only thing lost or misrepresented is that the history of the account looks like it was always in the new account at the 'new' FI.  A reminder transaction might be in order.  (This is also a commonly recommended path for lost, stolen, or replaced credit cards.)

As long as all securities are effectively 'transferred' to the same new account at the same time, that procedure should be fine. 

Where I am a bit unclear is when you said "When I activate both accounts ... ".  Only one account is in Quicken and that is only ever connected to one FI account at a time.  The Quicken account is connected to two separate real-world FI accounts, but only one at a time.  "Both" real-world accounts are never activated at the same time and there is only one Quicken account involved.  Care to clarify?

MochaMan

@q_lurker @NotACPA -- Before making changes to my estate plan, I had one Quicken file with two investment accounts with the same financial services company – IRA and Regular. After updating my estate plan, I ended up with one Quicken file with three investment accounts – IRA, Regular and Trust. Apparently, you cannot just rename an existing investment account as a trust. You are required to create a new account number. The account number for the Regular account could have been changed; however, it would have required me to change all my auto pays and deposits. My financial consultant recommended that I keep my Regular account for all my auto pays and deposits and create a separate Trust account for my securities and the majority of my cash. I never realized the transfer of securities would create such a mess. If I decide to delete all the transfers, is there an easy way to do it? It appears you have to do it one transaction at a time. I don’t think I have the patience to do that.

MochaMan

@Jim_Harman -- I think the attached PDF will provide the comparisons you are looking for. I have included views with and without transfers for the Portfolio View, Investment Performance Report and Investment Transaction Report.  

Tom Young

@MochaMan

What you're seeing here clearly is a bug and it may be a knock-on effect of showing the transfer as an event in the Performance Report since the Performance Report identifies one leg of the event as an "Investment."  The fact that a transfer isn't a "cash" event and shouldn't be showing up in the Performance Report has been discussed in here many times.

If your new trust account really is a distinct and separate account at your financial institution then you're pretty much stuck with using Quicken's transfer mechanism and have to live with the (unintended) consequences. 

Jim_Harman

Some observations:

I think the percentage shown in the Investment performance report is correct both with and without the transfers. To verify this, you could enter the same data into Excel's XIRR function and I am confidant that you would see the same result. Because the transfer causes an investment and a return on the same day, it does not and should not affect the IRR calculation. It is the same as if you sold the security and bought it back at the same price on the same day.

I think Quicken's ROI (%) calculation is not useful (wrong?) when there has been a transfer, as your example shows. The no-transfer value of 72.31% over 4.088 years or 17.7% per year is consistent with the IRR in the investment performance report of 15.02%. The difference can be attributed to the compounding that the IRR takes into consideration.

[edit] This issue with the ROI calculation also comes up in other situations. See this discussion
https://community.quicken.com/discussion/7864889/account-level-amount-invested-and-roi-are-calculated-incorrectly-when-a-security-is-sold

q_lurker

MochaMan said:

@q_lurker @NotACPA -- Before making changes to my estate plan, I had one Quicken file with two investment accounts with the same financial services company – IRA and Regular. After updating my estate plan, I ended up with one Quicken file with three investment accounts – IRA, Regular and Trust. Apparently, you cannot just rename an existing investment account as a trust. You are required to create a new account number. The account number for the Regular account could have been changed; however, it would have required me to change all my auto pays and deposits. My financial consultant recommended that I keep my Regular account for all my auto pays and deposits and create a separate Trust account for my securities and the majority of my cash. I never realized the transfer of securities would create such a mess. If I decide to delete all the transfers, is there an easy way to do it? It appears you have to do it one transaction at a time. I don’t think I have the patience to do that.

That clarifies why you now have both the original 'regular' account and the new 'trust' account.  No problem with that setup, how you got there or where you are going.  No problem with your consultant's advice.

Deleting transfers (I don't recommend that direction):  You can generate a Banking Transaction report customized to the two INVESTING accounts and the time period of the transfer.  With that report, you can select (highlight) multiple transactions (Add Shares and Remove Shares) and then take the Edit / Delete transactions step.  Those selected transactions will be deleted in 'one' action.  

Why I don't recommend that direction:  The gist of your problem is that the Add Shares are adding to the Amount Invested total for each security.  That then throws off the ROI% calculation.  I consider that a weakness in Quicken's treatment (rather than a bug as @Tom Young suggests).  Because of that treatment, I find the Average Annual Return calculation better.  I think the difference is borne out in your pdf example.  In the big picture, the Transfers are the correct action (what you did in the real world), and there is valid performance data available.  

If you skip the transfer shares path, you are still going to have to represent your receipt of the shares in the new trust account.  (As far as I know) Any reasonable way you do that in Quicken is going to increase the associated Amount Invested.  It is not going to 'fix' the ROI calculation as I see it.  

Maybe I am missing your intent in deleting the transfers.

MochaMan

@q_lurker -- Thanks for your tip about the deletion of multiple ADD / REMOVE entries. I had tried to do it in the investment transaction report. I never considered doing it in the banking transaction report.

MochaMan

@Tom Young @Jim_Harman @q_lurker -- Here’s my rationale for purging all the ADD / REMOVE transactions…
If I had no auto pays and deposits, I would have two investment accounts – IRA and Trust. My Regular account would have been assigned a new account number and renamed Trust. As stated previously, keeping the Regular account was a workaround for handling all my auto pays and deposits.
When I look at the Security Detail View, all investment transactions for all accounts are listed. I therefore assume that all investment transactions are contained in one dataset. Using account settings, you have the flexibility to aggregate multiple accounts together. In the past, I have done that for my IRA and Regular accounts, particularly when I have the same security in both accounts.
Since the Regular and Trust accounts could have been one account, combining the Regular and Trust using selection criteria seems appropriate. The output looks like one account.
I have placed a duplicate copy of my Quicken file in a separate directory. After purging all the ADD / REMOVE transactions, it looks like all my problems are gone. Even my reinvested amounts have reappeared.
I’m going to run my regular and duplicate Quicken files in parallel for a few days. If nothing blows up, I think I will opt for the purging of all the transfer transactions. Hopefully, it will eliminate the need for me to question the validity of certain numbers and calculations.

q_lurker

Let me parse your statements if I might.
"When I look at the Security Detail View, all investment transactions for all accounts are listed. I therefore assume that all investment transactions are contained in one dataset. "
Yes, all the transactions are in one datafile (dataset), but also each transaction is associated with or tied to one specific account.   
"Using account settings, you have the flexibility to aggregate multiple accounts together."
I would phrase that as "Using customization setting for reports and views, ...", but yes, Quicken offers the ability to present information about multiple account in one presentation.
"Since the Regular and Trust accounts could have been one account, combining the Regular and Trust using selection criteria seems appropriate. "
I take it that you are not combining these "using selection criteria", you are combining them by not transferring the assets within Quicken from the regular account to the Trust account.  You removed all the Remove Shares / Add Shares transactions.  


You can certainly keep the transactions for both real world accounts -- the "regular" account (bill pays, etc.) and the 'Trust" account (investment transactions) -- in one Quicken account, though the conventional wisdom is to map Quicken one-to-one with the real world.  Downloading transactions will be the shortcoming.  Only one real world account can be mapped to (associated with) one Quicken account. 

So if you associate the Trust (real world) account to the Quicken account, you'll get all the investment transactions and holdings downloaded, but none of the bill-paying activities.  Those bill paying activities will need to be manually entered and reconciled. 

Conversely, if you associate the regular (real world) account to the Quicken combined account, you'll be able (?) to get the cash transactions downloaded, but not the investment transactions (buys, sells, dividends, etc.).  Only you can decide if either case is viable and acceptable to you. 

Something I haven't tried (a concept), you might be able to have a second Quicken account associated to the second real-world account and after downloading the transactions, then move those transactions for the second account to the 'combined' account.  After each such action (download and move) the second Quicken account would always be empty of transactions and assets.  Just a thought. 

For myself, I'd keep the two real world accounts mapped to two Quicken accounts.  I see this as a lot of trouble to try to monitor things (Amount Reinvested, et al.) that do not matter (to me).  Or a lot of trouble to avoid switching scheduled transactions from the regular account to the new real world trust account.   

"Hopefully, it will eliminate the need for me to question the validity of certain numbers and calculations."
I always encourage users to question the validity of Quicken's calculations and numbers.  To treat Quicken as a magical black box is a foolish way to manage one's financial affairs, IMO.  That questioning is what (apparently) brought you here to begin with.  Once one understands the premise of the calculations, and accepts or rejects the validity, then the user can move forward in an aware and knowledgeable fashion.  Keep questioning!