Modify Investment Performance Report So That Fees Can Optionally be Excluded.

Dan Glynhampton

Over the past 12 years or so several threads have been started in the forum about the calculations of the Investment Performance Report, for example here, here, here, here and here.

The main issue people have is that whilst the report shows the correct total return for the period selected, the inability to exclude fees from the calculations makes it difficult to see return that the investor actually received.

This idea thread is a request for Quicken to modify the Investment Performance report so that fees may be optionally excluded from the calculations.  From a user perspective the most convenient way would be to allow selection of categories in the "Customize Report" window (as is present in most other report templates).  For Example, these are the tabs from the Banking Summary report:

Whilst these are the tabs available in the Investment Performance Report:



If categories could be selected in the Investment Performance report (as they can in most other reports) it would allow the fee categories to be excluded and the report to show the actual return to the investor.

q_lurker

A good potential implementation.  By customizing on categories, some 'fees' could be excluded (mgt fees, perhaps) while other fees could be included (foreign taxes, maybe). 

To be clear (I hope), included categories would be treated as returns (expense categories) and investments (income categories).  That should be consistent with the current default.  If a category is omitted/excluded/unchecked, it would not be treated as investment or return.  Non-category transactions (Add Shares, Remove shares, Buy, Sell, RtrnCap, others?) would be included as they are now. 

Dan Glynhampton

Thanks @q_lurker, your description lines up with my thoughts. By default I would expect all categories to be selected and the report to behave exactly as it does now. Your description of how unchecking categories would work is exactly what I had in mind. 

Dan Glynhampton

In case this idea ever ends up in front of the developers I'd like to add another clarification. 

The filtering out of categories must only affect the calculation of the Average Annual Return.  So the Beginning and Ending Market Values for the period of the report must still be calculated by including all categories. For the period in between those values, the filtered out categories should not be displayed and should not be included as investments or returns in the calculation of the Average Annual Return.

markus1957

At the risk of telling the developers how to do it and in order to prevent too much mucking around in the report, it might be better to include a checkbox in the Advanced tab to do it under the hood using a hidden system category that cannot be edited.  The selection should be an option in the Tab reports also.

Consideration should be given to verifying no unintended changes in modules like Tax Planner where Investment Fees were until recently, sometimes deductible in Schedule A and may again be in the future.

q_lurker

Dan Glynhampton said:

In case this idea ever ends up in front of the developers I'd like to add another clarification. 

The filtering out of categories must only affect the calculation of the Average Annual Return.  So the Beginning and Ending Market Values for the period of the report must still be calculated by including all categories. For the period in between those values, the filtered out categories should not be displayed and should not be included as investments or returns in the calculation of the Average Annual Return.

(emphasis added above)  I am not sure how Beginning and Ending Market Values could be affected.  Aren't they shares * price for all applicable securities on the applicable dates?  There should not be any categories involved in those figures to begin with.  Am I missing something?

Another thought this brought out -- I am only referring to the REPORT version of Average Annual Return; not the values computed for portfolio views.  The portfolio views AAR values would continue to be computed as they are now with "all" categories included.  While this might lead to some confusion (why am I getting different values?), that should be explainable through Help information and would be consistent with the Report version being more flexible that the portfolio view versions.

Dan Glynhampton

q_lurker said:

I am not sure how Beginning and Ending Market Values could be affected.  Aren't they shares * price for all applicable securities on the applicable dates?  There should not be any categories involved in those figures to begin with.  Am I missing something?

I think the Values are

  (shares * price for all applicable securities) + cash in the account

I'm assuming the fees are paid from cash in the account.  If those fees are completely excluded from the report the Ending Market Value will increase as the cash balance will not be decreased by the amount of the fees.  What is needed is for the Ending Market Value to be unaffected - the value has decreased by the amount of the fees paid, but the fees should be excluded from the return calculations. 

Another thought this brought out -- I am only referring to the REPORT version of Average Annual Return; not the values computed for portfolio views.  The portfolio views AAR values would continue to be computed as they are now with "all" categories included.  While this might lead to some confusion (why am I getting different values?), that should be explainable through Help information and would be consistent with the Report version being more flexible that the portfolio view versions.

I agree, and the default setting of including all categories would give the same answer as shown in the portfolio views, so the problem should only arise if someone modifies the report to exclude categories.

This discussion shows that a clear description of what's needed is quite difficult to achieve, and that may go some way towards explaining why requests for a way to exclude fees from the Investment performance report have gone unheeded by Intuit/Quicken for at least 12 years now.  @markus1957 makes some good points in his post above about there been no unintended impacts to other areas, and his suggestion of using a hidden system category is feasible, but I don't believe it would provide as much flexibility as allowing the user to categorise fees as they choose and then decide which ones to include in the performance calculations.

Excluding the fees is a simple thing to ask for, but it's fraught with potential pitfalls. Please add any further thoughts and concerns to the thread so that if it's ever selected for implementation it has the best chance of working correctly.

q_lurker

Dan Glynhampton said:

I think the Values are

  (shares * price for all applicable securities) + cash in the account

I'm assuming the fees are paid from cash in the account.  If those fees are completely excluded from the report the Ending Market Value will increase as the cash balance will not be decreased by the amount of the fees.  ...

You are right that I was missing the cash component, but I don't see that the Ending Market Value would or should be changed by omitting the categorized entries. 

Consider as a simple example where the 'investment' grew 10% and $50 of $100 cash was spent on 'fees'.

As is:
----------------------------------Investment -------------------- Returns
BMV = [email protected] + $100 = $1,100
MiscExp $50 Fees = .........................................................$50
EMV = [email protected] + $50 = ...............................................$1,150
_________________________________________________
Totals ..................................$1,100 ...............................$1,200

An approximate AAR would be 1200 / 1100 = 9% (assuming this was over a 1 year period).  Before the fees are considered, the 'return' was 9%.  

With deducting the "Fees" transactions, the only change is dropping the line

----------------------------------Investment -------------------- Returns
BMV = [email protected] + $100 = $1,100
EMV = [email protected] + $50 = ...............................................$1,150
_________________________________________________
Totals ..................................$1,100 ...............................$1,150

An approximate AAR would be 1150 / 1100 = 4.5%.  With the fees considered, the 'return' is only 4.5%.

The Totals line should change, but the EMV line should be unaffected by the inclusion or exclusion of other lines. 

The current Quicken model is predicated that any expense paid out of the account is a return to you.  What you choose to do with that amount -- pay fees, pay taxes, but groceries, buy a boat --  is your choice, and the AAR computation uses that model.   

When I have exported the Investment Performance report to Excel and confirmed Quicken's value for AAR, the only change I make to adapt for the fees is to delete or zero out those specific line items.  No change to the EMV is necessary as I see it.  

Dan Glynhampton

@q_lurker I still have a concern about how the implementation of excluding categories from this report might result in it not working as we want it to. I apologise that I've not been able to articulate my concern clearly, I'll try again in a moment.

First though I wanted to thank you for the examples in your above post.  The way you have explained it is exactly how I want this feature to work (and I assume is what you would like to see!). If this idea ever does get selected for implementation those examples will go a long way to explaining to the developers what is needed.

Back to my concern on the filtering of categories, I'll see if I can explain it better.

If you look at your second example above in isolation (without the knowledge of what we are trying to achieve with this idea), it looks wrong.  The cash in the EMV is $50 lower than the BMV, but there's nothing in the returns column to explain where that $50 has gone. The reason is that the $50 fees are filtered out of the transactions between the BMV and EMV, but they are still included in the calculation of the EMV.

In other Quicken reports (as far as I can tell), if you exclude a category in the customisation of the report it is completely excluded from all calculations and figures in the report.  We're asking for something slightly different here, if the fees are completely excluded from the calculations that produced your second example, then the EMV cash would still be $100.

To get the desired effect the sequence of events needs to be:

• Calculate the report as it currently works (including ALL categories)
• Remove any excluded transactions between the BMV and EMV
• Recalculate the totals of Investments and Returns
• Perform the AAR calculation.

Hopefully that makes sense and you can understand my concern now; if the report is updated to simply exclude selected categories from all calculations in the report it won't work as this idea requests.

Cyclist

I was directed to this thread because I was after the same thing.  Agree with Dan that excluding the category would provide an incorrect return, as the cash component of EV is calculated.  How's this for a potential solution.  There could be a check-box in Categories entitled "Exclude from Annualized Return in Investment Reports".  Furthermore, there could be an option (the advanced tab would be a great place for this) entitled "Exclude Categories marked 'Exclude from Annualized Return in Investment Reports'" for each Investment Report that includes annualized return (this way, reports could still be run the way they are now if this box is unchecked - don't know about you, but I'd like to see them both ways).  As Dan mentioned above, the actual programming logic would have to keep two accumulators - one with all transactions to calculate the EV, and one that excludes the categories tagged to calculate the annualized return.  It would help if the transactions which are being excluded from the return calculation were somehow identified visually in the report (perhaps with an asterisk next to the amount).  I believe this would fit the bill.  Comments?

q_lurker

@Dan Glynhampton wrote: 

• If you look at your second example above in isolation (without the knowledge of what we are trying to achieve with this idea), it looks wrong.  The cash in the EMV is $50 lower than the BMV, but there's nothing in the returns column to explain where that $50 has gone. The reason is that the $50 fees are filtered out of the transactions between the BMV and EMV, but they are still included in the calculation of the EMV.

I disagree that is "looks wrong"; it is what I expect of the calculation.  There are a multitude of reasons why the cash in the account might be different but not itemized in the report -- shares bought and sold, dividends and interest received and what we are after - fees paid that are not 'return' on the investment.  The report is not a cash flow report.  The should not expect it to be.  

• We're asking for something slightly different here, if the fees are completely excluded from the calculations that produced your second example, then the EMV cash would still be $100.

I am not asking that fees be completely excluded as you described.  BMV and EMV are what they are - whole and complete calculations for the real-world representation managed within Quicken.  I am simply asking that for transactions associated with specific categories not be included in the "Investment" or "Return" columns as currently presented in the report, and thus also not included in the ensuing Average Annual Return calculation.  

I appreciate the perspective you are presenting, but it seems to me you are overcomplicating the process.  Perhaps in my naivete, I am looking at this report as a stand-alone issue, unencumbered by however other reports might be processed and presented.  

Perhaps backing up a level is also necessary so that we understand each other and the Quicken programmers understand us.  I tend view this report first and foremost in an singular state (Don't subtotal).  I will frequently customize for time period or account(s) to be included and possibly on other considerations (security, securities, or security type(s)), but I am typically after the singular Average Annual Return value.  At times, I will choose to subtotal; on security, account, or security type, possibly on a time increment - quarter, month, year.  I make this point because subtotaling can impact the presentation of information for this report - particularly subtotal by security.  So everyone needs to be aware of the differences.

I see the customization selections (accounts, securities, security types, and times) defining a universe for that customized report.  Starting with the singular Don't-subtotal version, at the beginning of that universe (beginning date), the program determines a Beginning Market Value (BMV) -- the summed value of all securities and cash in that universe.  The source of any of that value is immaterial.  Similarly for the end times, an Ending Market Value (EMV) is determined as the summed value of all securities and cash in that universe.  Again, it does not matter how or when that value accumulated.  Those are both 'simple' values to determine for that defined universe.

The next step for the program is to review each and every transaction in that universe through that time period with the question:  Did that transaction Add to or Subtract from the universe?  Yes, if something from outside came in or something within was sent out.  Otherwise, no.  The Nos get skipped over.  The Yesses get tabulated into the list in the Investments (added from outside) or Returns (sent to the outside) columns.  From that data, compute the ARR. 

What happens for subtotals?  A bunch of smaller, more tightly determined universes (galaxies, solar systems?), but within each such sub-universe, the exact same process -- BMV, EMV, Yes/No for each transaction, compute AAR for that sub-universe.  

But beware!  The yes/no answer likely changes.  Something that was No (nothing in or out) for the big universe may become Yes (something in or out) for the smaller universe.  Further, something Yes for the big universe likely only applies to one of the smaller sub-universes.  Let's try some examples. 

Start with the big universe -- all (investment) account, all securities, 1/1/year to 12/31/year.  While all sorts of transactions take place, let's try three.  
BMV = $10,000; EMV = $12,000 (I contend it is not important what makes up those two values or how those assets got into that universe.  Those quantities are what they are.)  At some point, your paycheck contributed to a the retirement account in that universe - Yes that was $500 from outside the universe added to the universe.  Some other time, you pulled out some money from your spouse's birthday gift from a different account.  Yes, $1500 left the universe for a better place.  And finally there was that one account where the manager charged you a $200 management fee.  As far as Quicken is now programmed, that money too has left the universe of this report. 

All those other transactions - Dividends, Reinvestments, Buy this security, Sell that security, Hold as cash for a while - that is all value (money) that did not cross the border of the universe of this report.  They are not relevant to this calculation.

The report takes the form of
----------------------------------Investment -------------------- Returns
BMV .................................. $10,000
Retirement Acct Contrib ......... $500
Acct 2 Withdrawal ........................................................$1,500
Acct 3 Mgt Fee ............................................................... $200
EMV ........................................................................... $12,000
_________________________________________________
Totals ..................................$10,500 ............................$13,700
AAR = (whatever it is, about 30.5% = 13,700/10,500)

Whether the BMV was all cash and during the period you put into bonds or all in GE stock and the EMV was the part cash and part securities -- none of that matters.  This tabulation is not cash flow. 

If you choose to eliminate the Mgt Fee expense from the calculation, the AAR is reduced since the Total Return is reduced by $200.  AAR becomes about 28.6% = 13,500/10,500.  You do not adjust the EMV.  You might note that the $200 Mgt Fee on the 10,5000 of "Investment Total" matches the drop in my approximate AAR, 1.9% (basic math there).   

If you then choose to subtotal by security, the picture changes but the principle of the report is the same.  BMV of that security, maybe Div transactions for that security that became cash (now out of that security's universe), maybe bought (investment in) or sold (return from) some of that security, EMV -- all lead to AAR for that security.

The basic process I am advocating is what I do in modifying the AAR calculation within Excel.  I basically export the Investment Performance Report, omit the "fees" or other transactions I don't want treated as investments or returns, leave the BMV and EMV alone, and I let Excel calculate a new AAR.  I am satisfied with the resulting calculation. 

Jim_Harman

I agree 100% with @q_lurker's explanation. 

An additional thought is that giving users the option to include them or not serves a valuable purpose.  

Suppose I have two accounts, one with an advisor who charges a 1% annual fee at the end of the year and one with the same investments that has no advisor fee.

The way the Investing Performance Report works today, because the fee leaves the account it is treated as part of the returns and the two accounts will show the same return. The report is showing the return of the underlying investments.

But the no-fee account has a 1% higher ending balance and thus the return to me is higher. To compare the performance of the two accounts, I should exclude the fee from the analysis of both accounts. To earn his keep, the advisor had better choose investments that return more than his fee.

Dan Glynhampton

@q_lurker Thanks again for your input to this discussion. I think we are 100% in agreement about what we want to happen with the report, but we seem to have become sidetracked into a debate about a clarification I made to my suggestion of how it might be implemented.

Rather than worrying about the implementation, I'll just restate that I started this idea thread to request that:

• The Investment Performance Report be modified to optionally allow the exclusion of fees from the AAR calculation.

I'm not optimistic that it will ever be implemented, but if it is there's a wealth of debate  in this thread which will hopefully guide the developers.

q_lurker

@Dan Glynhampton -- Thanks for the reply.  Seeing that this discussion has manged to gather more votes along the way and that clarity has been added to viable procedures for implementation, I am more optimistic that it may come about.  

Jim_Harman

Investigating this further, it appears that this capability is already (somewhat) available in Quicken. If you record the management fees as MiscExp's and leave the security name blank. the fee is treated as a withdrawal as discussed above.

However, if you assign a security name in the MiscExp transaction and run the report on just that security, the fee appears in the Investment column of the Investment Performance Report, thus reducing the IRR of that security. 

At the account level, the fee is now excluded from the report, resulting in the reduced overall performance for the account.

markus1957

Jim_Harman said:

Investigating this further, it appears that this capability is already (somewhat) available in Quicken. If you record the management fees as MiscExp's and leave the security name blank. the fee is treated as a withdrawal as discussed above.

However, if you assign a security name in the MiscExp transaction and run the report on just that security, the fee appears in the Investment column of the Investment Performance Report, thus reducing the IRR of that security. 

At the account level, the fee is now excluded from the report, resulting in the reduced overall performance for the account.

Very Interesting-  For years I have been using an "Investment Fees" security to create a short-term loss that accounts for the fee. That requires customizing the CapGains Report and Tax Planner to get those right.

I'm going to try your solution by applying the fee to my "Fees" security to see if I can accomplish the same thing without the extra work.

Adding- It Works!!!!!!!!!!!!!!!!!!!!!!!!!!
No extra steps other than creating a "zInvestment Fees" security with zero shares and a price of zero. The "z" makes it easy to find in the pull-down when making the change to the downloaded transaction.  I had also created an "Inv Mgmt Fee" Investment Type to allow easier report customization but I'm not sure I would need to do that now.

Adding further- This thread status should be marked Already Offered with thanks to  @Jim_Harman

Dan Glynhampton

Jim_Harman said:

However, if you assign a security name in the MiscExp transaction and run the report on just that security, the fee appears in the Investment column of the Investment Performance Report, thus reducing the IRR of that security. 

That’s a really useful workaround Jim, thanks for highlighting it. 

I still hope that the report can be modified as suggested above though, as I’d like to be able to save customised versions of the report with and without fees included so that I can compare them easily. 

q_lurker

Dan Glynhampton said:

That’s a really useful workaround Jim, thanks for highlighting it. 

I still hope that the report can be modified as suggested above though, as I’d like to be able to save customised versions of the report with and without fees included so that I can compare them easily. 

Ditto.  (Why can't I "Like" a post in an Idea discussion?)

Jim_Harman

Perhaps we could assign the fees to a security that has a very small holding in the account then control how the fees are handled by including or excluding the security in the report (I have not tried this)

markus1957

Misc. Expenses assigned to a manually created dummy security with zero shares will not show up in a standard performance report so no need to exclude it.

Dan Glynhampton

Jim_Harman said:

Perhaps we could assign the fees to a security that has a very small holding in the account then control how the fees are handled by including or excluding the security in the report (I have not tried this)

That's a good idea Jim, but unfortunately it doesn't work as I hoped it would.  If you exclude the dummy security from the report (it doesn't matter what the holding of the dummy security is, you can leave it at zero), then the fees reappear in the report but in the Investments column, not the Returns column. That gives the opposite effect to the one we want.

I'm still trying to get my head around why it works that way...

Dan Glynhampton

markus1957 said:

Misc. Expenses assigned to a manually created dummy security with zero shares will not show up in a standard performance report so no need to exclude it.

The reason for excluding it is to try and force the Misc expenses back into the performance report so that there's a simple way to compare returns with and without the fees included.  Unfortunately it doesn't work as I explained in the post above, the misc expenses are treated as investments if you exclude the security.

q_lurker

@Dan Glynhampton
@Jim_Harman

Dan wrote: "That's a good idea Jim, but unfortunately it doesn't work as I hoped it would.  If you exclude the dummy security from the report (it doesn't matter what the holding of the dummy security is, you can leave it at zero), then the fees reappear in the report but in the Investments column, not the Returns column. That gives the opposite effect to the one we want."

I am seeing it Dan's way with QW2017, though I would say it overcompensates rather than being the opposite effect.  Looking at an example:

The account transactions:

Note that the first Fees (3/31) is not associated with a security, the second (6/30) is.

With all securities included in the Report I get:

Both transactions appear as 'Returns' thus inflate the resulting performance value.

If I select the security list to keep all but the "Fees (as a Security)" security, then I get:

The second Fees MiscExp has moved from the Returns column to the Investments column.  What I am looking for is that transaction not showing at all.  Being removed from the Returns column is great; it should give the desired result = a lower IRR value.  But by adding that transactions now as an investment, that side of the equation is now overstated and the IRR is below what I expect.  

Please tell me I am doing something wrong, or that QW2019 is behaving differently.  

(My apologies for not following up on this earlier.)

markus1957

In QW2019, a misc. expenses transaction assigned to a dummy security of zero shares held and selected to display in the report, does not appear in the Investment Performance report details list. Effectively causing the fee to be reflected as a reduction in market value and (correctly I believe) lowering the return by the amount of the fee.

When the dummy security is de-selected from the report, the fee transaction appears in the Investments column, effectively doubling the fee's impact to investment return.

So there appears to be no easy way to select/de-select the dummy security in the Investment Performance report to show IRR including/excluding the fee.