Incorrect avg annual return % in Performance reports

Rick  B
Rick B Quicken Canada Subscription Member ✭✭
edited October 2023 in Reports (Windows)

When using investment performance reports , I get correct return % when using account or time slices . But incorrect % when using stock or allocation slices .

I understand there will be some difference using stock based reports as they do not include cash which has not return . In that case the % return should be slightly higher in those reports. But it shows lower.

Taking the data to excel it validates my assumption . Numbers are spot on for subtotals but the totals are not . In excel it shows a slightly higher total as should be expected .

In this example portfolio view and Account Performance report return matches at 15.61%

When slicing by Security type it shows 13.44%

Bringing that data to excel , the subtotals match but the total shows 16.0% That seems reasonable given the non performing cash component is removed in the security type slice .

Any help would be greatly appreciated

Answers

  • Jim_Harman
    Jim_Harman Quicken Windows Subscription SuperUser ✭✭✭✭✭
    edited August 2023

    I hate to be nit-picky, but please don't call anything "Return %" unless you are referring to a column in the Portfolio views that has exactly that title. You are referring to Avg. Annual Return or IRR in the Investing Performance Report, right? The Return (%) columns in the Portfolio views are based on downloaded data, not computed based on your holdings and transactions.

    Let's make sure we are looking at the same thing.

    In the Investment Performance Report (IPR), I click on the gear and select my main investing account then change the Subtotal by options for Don't subtotal, Account, Security, Security Type, etc.

    When I hover over the dot at the end of the Total IRR line, the total IRR is exactly the same for all selections.

    If I select all accounts, the Total IRR is 10.79% for Don't subtotal and Account, but 10.87% for the other options.

    Does this match what you are seeing? Of course your numbers will be different from mine, but we are looking for changes in the Total IRR when you change the subtotaling options.

    I have not studied this discrepancy, but my next step would be to look at different accounts to see if some are the same for all subtotaling options and some not.

    QWin Premier subscription
  • Rick  B
    Rick B Quicken Canada Subscription Member ✭✭

    Thanks Jim

    Yes your summary is correct , the totals will be correct i.e. 10.79% for Don't subtotal , And Account as well as time slices . And as per your example 10.87% for all other options (All other options are securities views ) .

    As per your suggestion I tried using individual accounts the same issue occurs . By doing this it illustrates the problem more clearly.

    In this example Don't subtotal shows 9.42%. With subtotal by security type it shows 9.68% in the detail view (stock is the only security type) As initially discussed the difference can be attributed to the cash component which is not shown. Hovering over the total line on the graph shows 6.20%

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    So to be clear, you are looking at the Total IRR graphic and noticing how that the value changes as various "Subtotal by" selections are made. The component accounts and securities are NOT being changed, only the subtotal selection. Since the components are the same, you (and I) would expect the Total IRR depiction to be constant. FWIW, I rarely (never) look at the graphical representation but I have always considered it odd and unfortunate that the table never showed a Total when a subtotal by selection was made.

    I think you are onto something with respect to the Cash consideration, but there may be more to the underlying issue than that.

    Of all the accounts I monitor, I only keep cash represented in Quicken in one account. All other accounts, I move the cash in and out of a MM fund. Looking only at the difference between Don't Subtotal and Subtotal By Security Type, all those other accounts maintain the same Total IRR value; no difference based on subtotal change. Only the one account with a cash balance shows a change in the Total IRR graph line as the subtotal changes from Don't to by Security Type. That is,:

    • Subtotal by = Don't —> Total IRR = 11.97%
    • Subtotal by = Security Type —> Total IRR = 11.01%
    • Subtotal by = Security —> Total IRR = 11.01%

    Noting that when you choose to subtotal by Security or Security Type, the program omits the 'cash' assets since they are a "No-Type", "No Security" asset, I think I have determined that nevertheless for the Total IRR calculation, those cash assets are still included. I'll try to document this more clearly in a compact test situation. More to come (I hope).

    (Not germane to the issue, but I am choosing the Date Range as Yearly, Current year rather than your YTD selection. I don't have any future dated transactions in this account or any other investment account.)

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    Test case: This is my test case transaction list

    Began the year with three assets - Cash, AAPL, and VFINX - all at a $10,000 valuation. AAPL paid out 3 dividends totaling $55. VFINX paid out one dividend of $39. As of 8/31/23, valuations were

    • Cash = $10,093.91
    • AAPL = $14,508.20
    • VFINX = $11,789.82
    • TOTAL = $36,391.93

    I look at the Investment Performance Report (IPR) for the Current year, 2023, so those same valuations are applied to 12/31/23. For this account, all securities, all types, no subtotals, the IPR shows an Average Annual Return (also known as Internal Rate of Return or IRR) of 21.31%.

    If Subtotal by Security or Security Type is selected, the Total IRR line in the graph shows a value of 21.67% (see below). Those presentations do NOT include cash as (apparently) cash is not considered to be a security or have a security type. Real world cases as presented previously in this discussion can produce a subtotaled IRR above or below the non-subtotaled rate. What is the explanation for the the 21.67% value? Is it a correct value?

    To research this, I customized the IPR, specifically the included/excluded securities and security types. If I simply exclude the security = No Security and no subtotals, I get the version below which shows a 32.06% IRR. Note the beginning market value of $20,000 consistent with the initial cash value of $10,000 being omitted.

    If instead I simply exclude the security type = No Type and still no subtotals, I get this version which shows the 21.67% IRR.

    But also note in this variation, the Beginning market value (and ending) does include the $10,000 cash. This is NOT correct and is the source of the incorrect IRR when subtotal by security or by security types are applied.

    As a further test, I customized the report to only use Security = No Security, and Security Type = No Type. I get the following which appears to me to be the correct tabulation for the cash. Thus I see no reason for the decision to exclude Cash when the report is subtotaled.

    Questions, comments, and cross-checking from the community would be appreciated.

  • Rick  B
    Rick B Quicken Canada Subscription Member ✭✭

    Digging a little deeper it appears the cause of the problem is the security named No security . ( This includes cash )If it is selected all of the subtotal views using security or security types will be incorrect for total IRR. If it is deselected the reports will be correct . The different reports will not be comparable because of the cash impact on total IRR . There seems to be a problem in the way Quicken calculates the total IRR if no security is selected .

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    @Rick B

    Your graphic implied the "green" results were the same. For my trial case, the table (with values) would look like this

    TOTAL IRR per GRAPH

    • SUBTOTAL BY …. INCLUDE ALL … EXCLUDE "No Security"
    • None ……………………….. 21.31% ……………….. 32.06%
    • Account ……………….. 21.31% ……………….. 32.06%
    • Time (MQY) ……….. 21.31% ……………….. 32.06%
    • Security ……………….. 21.67% ……….……….. 32.06%
    • Security Type …… 21.67% ……………….. 32.06%
    • Investing Goal … 21.67% ………….…….. 32.06%
    • Asset Class …….…. 21.67% ……….……….. 32.06%
    • Option Group ….. 21.67% ………….…….. 32.06%

    The 21.31% is correct for the whole account including the cash which is the desire. That is, the base specification is to include all securities, including No Security and all Types. The 21.67% is coincidentally close, but incorrect. The degree of closeness is a function of the cash component in comparison to other components of the whole. The 32.06 is correct only if the cash component is neglected, but that is not the desire here.

    Thus I would not rate the Exclude "No Security" procedure as 'correct', acceptable, or 'green' even though it gave the same answers for all subtotal selections. Perhaps that is what you mean by:

    The different reports will not be comparable because of the cash impact on total IRR .

    Beyond that …

    There seems to be a problem in the way Quicken calculates the total IRR if no security is selected.

    I see the problem as how Quicken treats the cash component of the holdings when the cash does not neatly fall into one of the subtotal groupings., IMO, if there is cash — No security (includes cash) — selected, the cash should be treated as a follows:

    • SUBTOTAL BY ………. INCLUDE As
    • None ……………………….. No change necessary, cash is included
    • Account ……………….. No change necessary, cash is included
    • Time (MQY) ……….. No change necessary, cash is included
    • Security ……………….. No Security
    • Security Type …… No Type (The report already presents a "No Type" section but does not include the cash values)
    • Investing Goal … No Goal (The report already presents a "No Goal" section but does not include the cash values)
    • Asset Class ………. No Asset Class (The report already presents a "No Asset Class" section but does not include the cash values)
    • Option Grp ……….. No Option Group (The report already presents a "No Option Group" section but does not include the cash values)
    • From that expanded inclusiveness, the TOTAL IRR value would be computed and should be consistent.

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭
  • Rick  B
    Rick B Quicken Canada Subscription Member ✭✭

    Thanks very much for your help on this . Hope Quicken is able to address this issue .

  • Quicken Kristina
    Quicken Kristina Quicken Windows Subscription Moderator mod

    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. In the meantime, if you haven't done so already, we request that you 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.    

    Quicken Kristina

    Make sure to sign up for the email digest to see a round up of your top posts.

This discussion has been closed.