Reconciling Average Annual Return (AAR) for Portfolio of investments (not for individual Stocks)

jo18

I need some help on reconciling the calculation of the Average Annual Return (AAR) per Quicken in the Investment Performance report for PORTFOLIOs ONLY. I have no problems with reconciling the Quicken returns for individual stocks using XIRR. XIRR reconciles exactly to the returns as presented in the Investment Performance reports and in the Investing/Portfolio report for YTD, 1 yr and 3 yr AAR's. No problems. Change date in XIRR to 12/31 vs 1/1 or the previous day in range presented in Investment Performance report and everything ticks and ties.

Portfolio returns are more complicated as everyone knows. I am using the Quicken reports from the following: Reports/Reports & Graphs Center / Investment Performance and then use customize window to select specific account and time frame. The detail presented for these reports does not include 100% of the activity in the brokerage account but includes only fixed income plus fees plus distributions in the report for the account being reconciled. No dividends, no realized gains or losses, no reinvestments are included in this Quicken report. I have attempted to reconcile this report with XIRR and get close but not exact and wondering if anyone can help. Questions:

1. What detail is used for Portfolio returns calculation: Investment Performance detail or the actual detail (with adjustments as needed) in the Investment Transaction report (noting that no return is available from the Investment Transaction report; return can only be seen on Investment Performance report)?
2. If Investment Transaction report is used, which column should be used (cash with or without adjustment)
3. What adjustments if any are made to either report (Investment Performance report or Investment Transaction report) the following items:
a. Beg account balance?
b. Ending account balance?
c. Cash inflows: dividends, fixed income, realized gains (and or realized loses), proceeds from sale, new capital invested?
d. Cash outflows: Advisory fees, commissions, distributions out of account, reinvestment?
3. Has Quicken made a detail example available with all types of activity that is occuring over time so that the Quicken calculation can be reconciled?

Bottom line to me is that the AAR calculation for individual stocks is exact and the same AAR for portfolio returns is probably correct or very close (depending on perspective of the party involved – owner of account vs manager of the account) but I cannot reconcile exactly. The delta is not material but I would like to tick and tie the portfolio calculation.

Appreciate any input.

Thanks

Jim_Harman

The Investment Performance Report and the Av Annual Return columns in the Portfolio views include all the account activity that is needed to correctly compute the IRR. Reinvested distributions are not shown because they are a Return and an Investment on the same day. The effect of the distribution is included in the ending market value.

Some notes on the calculations:
-- The beginning market value is calculated based on the closing prices of the securities on the day prior to the report's start date.
-- The calculations are based on a 365 day year. You may need to adjust the dates if the date range includes one or more Feb. 29.
-- For account level calculations, make sure you pick All securities or include No Security (includes Cash) in the selected securities.
-- When account fees are paid by selling shares, you should use a Sold followed by a MiscExp. If you want the reported returns to be reduced by the amount of the fees, you should assign a security in the MiscExp transaction. 

If these factors don't explain the differences you are seeing, please post back with details and we will try to help.

jo18

Jim,

Thanks for the quick response. I still need some help. I may need to send you an example account and calculation along with my reconciliation. My comments are below [IN ALL CAPS]:

The Investment Performance Report and the Av Annual Return columns in the Portfolio views [WHY DO I NEED THE PORTFOLIO VIEW? IS IT FOR THE PORTFOLIO RETURN LOCATED AT THE BOTTOM OF THE REPORT?] But the return is included on the Investment Performance report] include all the account activity [ONLY ACCT ACTIVITY IS IN THE INVESTMENT PERFORMANCE REPORT - RIGHT?] that is needed to correctly compute the IRR. Reinvested distributions are not shown because they are a Return and an Investment on the same day [AGREE].

The effect of the distribution is included in the ending market value [DISTRIBUTIONS HAVE BEEN REMOVED SO YES I UNDERSTAND. HOWEVER IN THE ACCT BEING RECONCILED, THE ONLY INFLOWS ARE FIXED INCOME WHICH ARE NOT BEING DISTRIBUTED SO ARE INCLUDED IN THE ENDING BALANCE AND INCLUDED OVER TIME AS RECEIVED PER THE INVESTMENT PERFORMANCE REPORT. SHOULD AN ADJUSTMENT BE NECESSARY?]

Some notes on the calculations:
-- The beginning market value is calculated based on the closing prices of the securities on the day prior to the report's start date. [ARE ANY ADJUSTMENTS NEEDED FOR ENDING BALANCE - E.G. INFLOWS OR OUTFLOWS LISTED OVER TIME IN INVESTMENT PERFORMANCE REPORT]
-- The calculations are based on a 365 day year. You may need to adjust the dates if the date range includes one or more Feb. 29. [GOT IT]
-- For account level calculations, make sure you pick All securities or include No Security (includes Cash) in the selected securities. [GOOD POINT. I'VE BEEN INCLUDING CASH IN MY RECON.]
-- When account fees are paid by selling shares, you should use a Sold followed by a MiscExp. If you want the reported returns to be reduced by the amount of the fees, you should assign a security in the MiscExp transaction [HOW AND WHERE DO I DO THIS???].

THANKS AGAIN
JERE

If these factors don't explain the differences you are seeing, please post back with details and we will try to help.

Jim_Harman

jo18 said:

Jim,

[WHY DO I NEED THE PORTFOLIO VIEW? IS IT FOR THE PORTFOLIO RETURN LOCATED AT THE BOTTOM OF THE REPORT?]
The portfolio view has a quick snapshot of the Av. Annual Return (IRR) for set time periods and other measures. If all you care about is the IRR, the Investment Performance Report (IPR) is more flexible.
  
 [ONLY ACCT ACTIVITY IS IN THE INVESTMENT PERFORMANCE REPORT - RIGHT?]
The activity in the IPR includes whatever accounts and securities you have selected. 

[DISTRIBUTIONS HAVE BEEN REMOVED SO YES I UNDERSTAND. HOWEVER IN THE ACCT BEING RECONCILED, THE ONLY INFLOWS ARE FIXED INCOME WHICH ARE NOT BEING DISTRIBUTED SO ARE INCLUDED IN THE ENDING BALANCE AND INCLUDED OVER TIME AS RECEIVED PER THE INVESTMENT PERFORMANCE REPORT. SHOULD AN ADJUSTMENT BE NECESSARY?]
No adjustment should be necessary if you have included the desired account(s) and securities in the report. Think of it as drawing a circle around your selections. The inflows and outflows should be just what crosses that boundary.

[ARE ANY ADJUSTMENTS NEEDED FOR ENDING BALANCE - E.G. INFLOWS OR OUTFLOWS LISTED OVER TIME IN INVESTMENT PERFORMANCE REPORT]
I have not tested all the possible transactions but I don't know of any needed adjustments.

If you want the reported returns to be reduced by the amount of the fees, you should assign a security in the MiscExp transaction [HOW AND WHERE DO I DO THIS???].
Pick the appropriate security next to Security Name

If you do not select a security, the fee will be treated as a Return in the report, just like a withdrawal.

q_lurker

@jo18
I'm going to stick my $0.02 in though I think you are getting great answers from Jim.  (In part, I am trying out this explanation to see if it resonates with anyone.)

The Average Annual Return computation in Quicken calculates using a universe of investments over a defined period of time.  That universe may be one security, several securities, some security type(s), one or more accounts, up to the entire investment collection in the database.  For the portfolio view presentations, the universe (applicable securities and accounts) and time periods are defined.  For the Investment Performance Report, the user has complete control on defining the universe (selecting securities, security types, and accounts) and defining the time period.  As Jim noted, that report offers much greater flexibility.  The report also can provide greater detail on the ins and outs, such that matching up to the Excel XIRR calculation is possible. 

Once that universe is defined, the calculation centers on what goes into that universe (investments) and what comes out (returns).  What takes place within that universe is immaterial to the average annual return value.  The transactions within that universe will affect the final size of the universe (Ending Market Value) which is where their impact on the calculation is felt.    

So if you define the universe as a single account including all securities (and the cash), a stock dividend that becomes cash into the same account is just noise internal to that universe.  If that stock dividend transfers to your checking account (outside the universe), it is a return.  

If the universe is a single stock, the cash dividend is a return going outside the universe on the one stock to a separate "cash universe".  It is a return.  But the reinvested dividend stays within that single stock universe affecting the final size; not an intermediate return.  A cash dividend with an immediate buy of shares (the same effect as a reinvested dividend), will be reported as a return and an investment.  As long as those occur on the same date, there is no effect on the calculation.  A lag between those to dates will have a minor effect. 

@Jim_Harman wrote:  "-- The calculations are based on a 365 day year. You may need to adjust the dates if the date range includes one or more Feb. 29."

My take on that statement (and Jim will correct me if I am wrong) is that the "Annual" part of the calculation is a 365 day year; it is actually an "Average 365-day Return" being calculated.  So if the 'year' includes a Feb. 29 date (366 days) there may be a minor differential.  That is not something I had thought about before.

@jo18
The use of the "Amount Invested" column in an Investment Transactions Report appears to be inconsistent with other uses of Amount Invested and is not relevant to the Average Annual Return calculation except to the extent it might correspond to something coming into or out of the applicable universe of that calculation.  

My experience is the Quicken's calculations and Excel's XIRR calculation are highly equivalent.  I have seen differences of 0.02% or so,but no significant differences whether on a security level or an account level.  What differences are you seeing at an account level?

jo18

q_

I agree with your interpretation. When I export the Investment Performance report for two different brokerage accounts into excel and use only the beg and end balances in my analysis, the calculated xirr is 82 basis points and 84 basis points lower than the Quicken AAR % for the two accounts. If I add back the advisor fees to the ending balance, the calculated xirr is only 3 and 4 basis points lower respectively for the two accounts. One of the accounts did have a transfer of assets to a third account so I treated that distribution as a return.

The key in this example seems to be the handling of the advisor fees. Have you run across this before?

I am still concerned about how Quicken actually makes the calculation.

Thanks,

@jo18

Jim_Harman

The topic of advisor fees was discussed extensively here
https://community.quicken.com/discussion/7850158/modify-investment-performance-report-so-that-fees-can-optionally-be-excluded

For information on Quicken's performance calculations, you can search the in-product Help for "performance calculations"

The info there is confusing in places but mostly accurate.

jo18

Very helpful. I have reconciled exactly the Quicken AAR % for all accounts using xirr. Thanks to all.
jo