Investment Performance Report Overall Total Erroneous Calculations

Mike98102

Hi All - I'm hoping to get a better understanding of what I think has been a persistent problem over the years in the Investment Performance report. The problem that I think exists is in the Overall Total (row) Avg. Annual Return (column). The problem emerges depending on subtotals selected on the report and also when the "gear" icon is used to select specific groupings.

Here's an example:

• Report is set to the full year 2024
• Only the security type "Intl Mutual Fund" is selected in the "Customized Investment Performance" pop up (the gear icon)
• The "Subtotal by" drop down box is selected to "Security Type"

Here's the result. In this case, row 2 of the report (6.96%) looks right. However, the Overall Total of -4.85% can't be correct. It doesn't correspond to the total of all investments in the portfolio. And that Overall Total row can't possibly be the result of the "detail" rows above since the math doesn't work.

When reported to Quicken Support, I was able to get this situation escalated to the development team. They indicated to the support rep that the report is working at designed.

Does anyone have a guess as to what design would result in the figures shown on the "Overall Total" row?

Thanks in advance….

m

Jim_Harman

That does seem weird. I see the same thing subtotaling by Security Type or Investing Goal, but not for example subtotaling by Account. There is something wrong with the Overall Total calculation. The IRR at the bottom right is apparently calculated based on the Overall Total numbers, which show a loss in your example.

What I see if the default of all accounts and all securities are selected is that the Overall Total investment and Return amounts change between subtotalling by Account and anything else for the same date range. Changing the subtotal back to Account restores the original totals. A similar problem was resolved earlier this year in R57.16, but it looks like there are still issues.

Forum Moderators (users whose names begin with "Quicken") sometimes review issues discussed here and refer them to Development, but if you want Quicken to work on a problem, I recommend you contact Support via phone.  Be sure the support agent understands the problem and gives you a ticket number for future reference. Please post the ticket number here. Development only works on problems that have been referred by a Moderator or confirmed by a Support contact.

You should also use the Help > Report a Problem menu in Quicken to submit the issue online. In your submission, be as clear and thorough as possible. Reference the ticket number and provide a link to this discussion. Provide step by step instructions to reproduce the problem. They will not pay much attention to a problem unless they can replicate it in house.

You will not get a response to an electronic submission, but apparently they review and prioritize the submissions and use the data to help resolve problems they decide to work on. The more reports they receive the better.

Quicken Kristina

Hello All,

Thank you for taking the time to visit the Community to report this issue, though we apologize that you are experiencing this.

We have forwarded this issue to the proper channels to have this further investigated. If you haven't already done so, please navigate to Help > Report a problem and submit a problem report with log files attached and (if you are willing) a sanitized copy of your data file in order to contribute to the investigation.

While you will not receive a response through this submission, these reports will help our teams in further investigating the issue. The more problem reports we receive, the better.

We apologize for any inconvenience!

Thank you.  

(CTP-11590)

Jim_Harman

I experimented with the Investment Performance Report by looking at one account that has a few trades plus withdrawals, dividends received in cash, and reinvested dividends. You can see more of what is going on with your data by clicking on the Show detail button. in the report

When the report is subtotaled by Account, the IRR calculation uses the starting and ending balances and the Withdrawal transactions. Because the dividends, Buys and Sells stay in the account, they affect the ending balance but not the details. This makes sense to me.

When the report is subtotaled by Security, Security type, Investing goal, or Asset type, the calculation uses the starting and ending balances, cash dividends, Buys, and Sells, but not the Withdrawals. The overall totals and IRR with each of these subtotals are the same, but they are different than the Account subtotal numbers.

The different transactions used in the calculations explain the differences in the overall totals.

If I select an account with no deposits or withdrawals, the overall total numbers are all the same regardless of the subtotal selection.

It is not clear how the cash withdrawals should affect the overall totals when the report is not subtotaled by Account. I would expect that the IRR should be the same regardless of how the report is subtotaled.

Mike98102

Hi Jim & Quicken Kristina -

I'll report the issue via Help > Report a Problem. I spent about 1-1/2 hours on the phone escalating the ticket (ticket number 11404681), only to have development respond that it's "working as designed."

Also, Jim, I'll try to replicate the diagnosis you have about withdrawals not being included. Interesting theory; I couldn't come up with anything that might explain it so maybe you're right. I agree with you that the overall total should reflect the AAR% of the investments included in the "scope" of the report as if there were no subtotal and that subtotaling in various ways shouldn't affect the overall total AAR%.

Thanks!

m

Mike98102

Hi all

I did a little bit of analysis. I don't doubt including or excluding withdrawals may be an issue. However, I think there is a date related issue as well. See below reporting scenario and error.

Jim_Harman

Thanks for the update, @Mike98102

If you have an account like an IRA or 401(k) where you are making regular contributions or withdrawals and not doing much trading, you can select just that account, click on Show details, and change the subtotal selections to see what changes. I opened two copies of the report and sized them to fit side by side so it was easy to compare the results.

Jim_Harman

I made a very simple test file with one investing account, one security, and one withdrawal during the year. About half of the account is in cash. Here is the account transaction list.

Here are two copies of the IPR, one subtotaled by account and one by security. When subtotaled by account, or Don't subtotal is selected, the report looks correct. If I subtotal by Security or the other investing measures, the Overall Totals line is wrong.

I would expect the overall totals to be the same regardless of how the report is subtotaled, and when subtotaling by Security, the Investments and Returns totals should include the cash and the withdrawal.

It looks like when subtotaled by Security, the cash in the account is included in the totals but not in the details above, and the return from the withdrawal is ignored. I think when the report is subtotaled by Security, there should be a section for "No security (includes cash)" that includes the cash balance and the withdrawal. The cash should also be included in other subtotals. The overall IRR should be the same as when subtotaled by Account regardless of which investing grouping is selected for subtotaling.

I have submitted this issue via Report a Problem.

Mike98102

Hi Jim - Very good analysis, and thank you for submitting the defect as well.

After a bit more analysis, I've rewritten the scenario above to reflect the "hidden" effects of unrelated cash and cash transactions more precisely. It's posted here below. I'm submitting this along with a few more examples of this core issue to Quicken.

I really think there are 2 related problems for this report when subtotaling or customizing by Security Type, Security, etc: 1)cash or no type asset beginning or ending market value and/or transactions are being included in calculations but not shown in the details, which is confusing and 2)there is inconsistent treatment between what is included in beginning vs. ending values, which leads to the incorrect calculations for AAR% regardless of whether one takes the view that unrelated cash transactions and market value should or shouldn't be included in the report.

drinian

I am seeing a similar issue. I did the investment performance report for all my bond investments for the period 2019 to the present. I then exported the report to an Excel spreadsheet. In that spreadsheet the sum of both the "Investments" and "Returns" column were different than that reported by Quicken in the row marked "Overall Total". In the Excel version I showed a profit for the Overall Total where Returns were greater than Investments, while the Quicken version showed just the opposite, a loss for that period. Something seems amiss in the Quicken report.

Quicken Kristina

Hello @drinian,

Thank you for taking the time to report this issue to the Community, although we apologize for any frustration or inconvenience experienced.

This issue is a bug that has been reported to our Development and Product teams for further investigation and resolution. Though we do not currently have an ETA, once a solution is created it will be made available as part of a future release.

Thank you!

(CTP-11590)

CaveCreek

I have noticed that if I uncheck "No Securities" under "Customize Investment Performance/Securities" I get consistent "Overall Total" regardless of the "Subtotal by" selection. By consistent, I mean the same result without saying whether the result is correct or not. I've been using this as a work around as I mostly use the performance reports as a relative trend and comparison indication. I certainly appreciate this thread and the research all have done on this confusing issue.

Jim_Harman

Good observation @CaveCreek

I tried that with my test file and saw the same result. It points to my conclusion about the cause of the problem: The report is handling cash in the account differently depending on how it is subtotaled.

In my super-simple test file, which is about half cash with a withdrawal in January and the rest in an S&P 500 index fund, if No Security (includes cash) is checked, the full-year overall IRR is 13.33% when subtotaled by Account (this is correct) and 2.29% when subtotaled by security or the other investing measures. If I un-check No Security, the IRR is 27.94% regardless of how the report is subtotaled.

Un-checking No Security forces the report to always ignore the cash in the account and just include the index fund's return in its calculations. 27.94% is Quicken's calculation of the fund's return for the year.

drinian

Thanks @CaveCreek! I used your suggestion to uncheck "No Securities" and now the spreadsheet totals match the totals on the Quicken report. As @Jim_Harman suggests, apparently including cash in the account throws off the calculation of the CAGR for the position.

Jim_Harman

@drinian note that unchecking "No Securities" causes the report to ignore the cash in the account unless it is subtotaled by Account. If you only want to see the performance of your securities, that may be what you want, but if there is any significant cash in the account the overall performance will be wrong.

q_lurker

@drinian You made a CAGR (compound annual growth rate) reference. The investment performance report makes an IRR (internal rate of return) calculation which Quicken calls Average Annual Return. The two are similar, but not the same.

Ref: https://www.investopedia.com/ask/answers/070914/what-are-main-differences-between-compound-annual-growth-rate-cagr-and-internal-rate-return-irr.asp#:~:text=The%20compound%20annual%20growth%20rate,value%2C%20and%20the%20time%20period.

CaveCreek

@Jim_Harman — correct, cash is the issue. I'm guessing that unless cash is associated with a "sweep" security, for example: Fidelity FDRXX Fidelity Cash Reserves or some money market account, the overall account return will be off if cash is included. I long ago, incorrectly, stopped trying to account for cash returns but I'm going to start again by tracking the cash sweep securities. When cash was returning next to nothing it didn't make much difference to me but now they are getting some returns. I appreciate your deep dive on this issue which I've ignored for a while.

Jim_Harman

I'm guessing that unless cash is associated with a "sweep" security, for example: Fidelity FDRXX Fidelity Cash Reserves or some money market account, the overall account return will be off if cash is included

That's right, my experiments indicate that if the cash is held in a sweep security and the sweep fund is bought and sold on the same days as any other cash transactions, the overall return is correct and the same regardless of how the report is subtotaled. If the account has a Cash balance, the cash is not counted in the overall return unless the report is set to subtotal by Account or Don't subtotal.