Account level Amount Invested and ROI (%) are calculated incorrectly when a security is sold

Jim_Harman

There has been much discussion about the "Amount invested" and "ROI (%)" columns in the Investing > Portfolio views. Quicken defines ROI as Return / Amount Invested as defined below. When calculated correctly, ROI is a useful measure of short term investment performance, but when a security in an account is sold, Quicken includes the proceeds of the sale in the account's Amount invested, causing the account level ROI calculation to be wrong or at best useless.

, Quoting the in-product Help,
--------------------------------------------------------------------------

• Return represents the total return of a security: the current market value, plus the income taken out as cash, plus cash received from sales of shares, minus the amount invested.

  Reinvestments are not explicitly added to the return, because they contribute to the market value, which is already factored in.

• Amount invested is the actual dollar amount that you've invested in a security to date. Amount invested includes any expenses (such as commissions and fees) for that security. It does not include reinvested amounts, such as reinvested dividends, interest, or capital gains distributions.

  By default, Quicken calculates the amount invested over your entire account history. Quicken can also report the amount invested for a specified date range. Change the date range by changing the Portfolio's From or As of date or by using the standard Portfolio columns Amount Invested 1-/3-/ 5-Year and Amount Invested YTD.

  When you change the Portfolio date range, Quicken calculates the amount invested during that period to be the difference between the beginning amount and ending amount of the date range:

  • Beginning amount: For shares purchased before the beginning date, Quicken uses the market value on the beginning date. For shares purchased after the beginning date but before the ending date, Quicken uses the actual cost of those shares.
  • Ending amount: Quicken uses the market value on the date you specify. (Shares purchased after the ending date are not included in the amount invested.)

  When the Amount Invested changes, so do the calculations for Return and ROI (%), which are based on the amount invested.)

  Amount invested doesn't decrease when you sell shares (unless you sell all shares of a given security—then it goes to zero), whereas cost basis does. If calculations such as ROI appear lower than you would expect, it could be because the amount invested includes the cost of shares you no longer own.

--------------------------------------------------------------------------------

In simplifed terms,

Amount invested = Starting balance + amounts added 
Return = Ending balance + amounts removed - Amount invested
ROI = Return/Amount Invested

This calculation works correctly for a single security, but it appears that Quicken calculates the Amount Invested and thus the ROI wrong on the account level when there are sales.

Consider this example:
An account starts with $10,000.
On 2/1 we buy 500 shares at $10 for a total of $5,000
On 3/1 we sell 400 shares at $11 for a total of $4,400, leaving 100 shares worth $1,100.
and the price stays at $11 for the rest of the period.
The ending account balance is $9,400 in cash plus $1,100 in the stock or $10,500.

The Investing > Portfolio view looks like this


Looking at just the stock, 
the Amount invested is $5,000
the Return is $1,100 + 4,400 - 5,000 or $500
and the ROI is 500/5000 or 10% which is correct.

Looking at the account as a whole, I think the Amount invested should be $10,000 and the Return should be $10,500 - $10,000 or $500 for an ROI of 500/10000 or 5%. Internal purchases and sales should not affect the account's ROI.

But Quicken is apparently adding the $4,400 proceeds from the sale to the initial cash of $10,000 to get $14,400 as the Amount invested. This causes the account level ROI to be reported as 500/14400 or 3.47%, which is incorrect.

Jim_Harman

This appears to be the same problem as discussed here
https://community.quicken.com/discussion/7103346/roi-ytd-shows-incorrectly-for-a-single-investment-account-after-exchanging-between-mutual-funds
in 2012 and elsewhere, so it has been around for a long time...

Sherlock

My understanding is Quicken is adding $9,400 cash and the $5,000 still invested in the security to get $14,400 as the total amount invested:

Amount invested doesn't decrease when you sell shares (unless you sell all shares of a given security—then it goes to zero), whereas cost basis does. If calculations such as ROI appear lower than you would expect, it could be because the amount invested includes the cost of shares you no longer own.

Jim_Harman

Sherlock said:

My understanding is Quicken is adding $9,400 cash and the $5,000 still invested in the security to get $14,400 as the total amount invested:

Amount invested doesn't decrease when you sell shares (unless you sell all shares of a given security—then it goes to zero), whereas cost basis does. If calculations such as ROI appear lower than you would expect, it could be because the amount invested includes the cost of shares you no longer own.

Whatever calculation Quicken is performing, it still seems wrong and inconsistent with the definitions in the Help at the account level:
The starting balance is $10,000, nothing is added to or removed from the account, and the current market value is $10,500. 
Amount Invested = 10,000
Return = 10500-10000 = 500
ROI = Return/Amount Invested = 500/10000 = .05 = 5% not 3.47%

Sherlock

Jim_Harman said:

Sherlock said:

My understanding is Quicken is adding $9,400 cash and the $5,000 still invested in the security to get $14,400 as the total amount invested:

Amount invested doesn't decrease when you sell shares (unless you sell all shares of a given security—then it goes to zero), whereas cost basis does. If calculations such as ROI appear lower than you would expect, it could be because the amount invested includes the cost of shares you no longer own.

Whatever calculation Quicken is performing, it still seems wrong and inconsistent with the definitions in the Help at the account level:
The starting balance is $10,000, nothing is added to or removed from the account, and the current market value is $10,500. 
Amount Invested = 10,000
Return = 10500-10000 = 500
ROI = Return/Amount Invested = 500/10000 = .05 = 5% not 3.47%

We may not agree with Quicken's definition of amount invested but Quicken is being consistent with their definition:

Amount Invested = 9400 + 5000 = 14400
Return = 10500 + 4400 - 14400 = 500
ROI = 500 / 14400 = 3.47%

Suppose there was no cash in the brokerage account:

Amount Invested = -600 + 5000 = 4400
Return = 500 + 4400 - 4400 = 500
ROI = 500 / 4400 = 11.36%


And what if there was no cash and we didn't sell any shares:

Amount Invested = -5000 + 5000 = 0
Return = 500
ROI = ???

q_lurker

Jim_Harman said:

This appears to be the same problem as discussed here
https://community.quicken.com/discussion/7103346/roi-ytd-shows-incorrectly-for-a-single-investment-account-after-exchanging-between-mutual-funds
in 2012 and elsewhere, so it has been around for a long time...

@Jim_Harman:  I obviously spent some time on that other discussion (time I don't remember from almost 8 years ago).  I am not sure my conclusion there supports your point here, though I did not today read and re-process all of that other discussion.  What I did take away from my quick review was that Quicken appeared at that time to calculate at the account level based on totaling values (Return$ and Amt Invested$) for the securities.  That seems to be the point Sherlock is making.  That prior discussion also centered on ROI YTD rather than the longer term ROI; that may not be significant.

That SUM(securities) direction seems to be born out by adding one more transaction to your list -- on 4/1/19, sell the remaining 100 shares of Stock Fund (I chose to sell those 100 shares for 11/share).  Now your account is back to all cash.  The portfolio view should show Amount Invested = $10,500 = total value of cash), and Return$ = 0, leading to no value for ROI.  The return of $500 you got in the interim from the Stock Fund investment is moot.  It doesn't count as return for the account if there are no shares in the account.  

As I said 8 years ago -- quirky -- which is largely why I still don't give ROI much value as a tool.  Noting my other comment in that prior discussion: ROI is less valuable as more trading takes place.  ROI is probably OK in a buy-and-hold situation, but not valuable to me beyond that.

Jim_Harman

@q_lurker in your example where the stock fund is bought and then sold at a profit, I think the account level ROI should be reported as 5%.

The Amount Invested at the account level should not be the sum of the Amounts Invested for the securities in the account, it should be the amount put into the account, whether by Deposit, Xin, Add, etc.

In your example the account level Amount Invested would be $10,000 and should not be affected by trading inside the account.

The account level Return in your example should be the account's ending balance of $10,500 + amounts taken out of the account (0) - Amount Invested (10,000) or $500

The ROI should be 500/10000 or 5%.

This is consistent with the way reinvested distributions are handled when computing Amount Invested and Return for securities, and is also consistent with the account level calculations in the Investment Performance report.

Calculating the account level Amount Invested in this way would make the account level ROI a useful metric, which is not currently the case if there is any trading in the account.

q_lurker

For the record -- I am not particularly advocating for Quicken's approach.  I am suggesting it is a purposeful approach they consciously chose quite a long time ago.  So likewise, I am not trying to be argumentative or contrarian.  I am more trying to see things as they are and see if there are 'other' issues.

I'll start with what you started with - Quicken's in-program help.  
"Amount invested is the actual dollar amount that you've invested in a security to date."
Amount Invested is treated as a Security value - calculated only at a security level.  That seems to create the baseline for an additive approach. 

Your perspective (and mine, usually) for portfolio views is grouped by accounts.  I have put forth Quicken take to securities in the account - portfolio view, adds up their  respective Amount Invested and Return amounts, and thereby computes an account level ROI.  

You are advocating for a completely different summation across the account to get different AI and Return values and thus a different account level ROI.  What if:

• the view is limited in scope based on securities -- meaning not all the securities in the account are included in that portfolio view?
• the view is not grouped by account, but rather by Security Type, investing goal, or some other selection?

In the current scheme, consistency is maintained.  The program adds up the presented set of securities AI and Return and presents those on Group-by line, and then also at the bottom as the 'specified' portfolio values.  Very much what you see is what you get.  Very spreadsheet like.

How about rather than changing the existing stuff, a new tab / window / viewpoint gets created specific to account performance.  Might include account level Amount Invested, Return, and ROI as you are promoting, along with 1-, 3-, and 5-yr AAR, maybe a table of account balances vs time.  maybe a Growth of 10,000 pic.  I am really thinking of a new "Performance" button next to the "Holdings" button.  

Jim_Harman

Thanks for your comments, @q_lurker

I agree that groupings other than complete accounts should also present useful ROI data, but I'm not sure that totaling the Amounts invested and Returns for each security is the right way to do it.

More to come ...

melissar1chards

I don't understand why this problem is never fixed, where the Amount Invested is not updated properly when shares are sold, and thus the ROI is not correct.

Is there a proper channel to report this bug? Seems posts on this subject are not monitored by the product team as this has been a complaint for years.

"Amount invested doesn't decrease when you sell shares (unless you sell all shares of a given security—then it goes to zero), whereas cost basis does. If calculations such as ROI appear lower than you would expect, it could be because the amount invested includes the cost of shares you no longer own."

Rocket J Squirrel

melissar1chards said:

Is there a proper channel to report this bug?

No.

Seems posts on this subject are not monitored by the product team as this has been a complaint for years.

I would say "decades". The Quicken folks decided long ago to document this bug rather than fix it.

Rocket J Squirrel

There was a good idea posted here a while back, which was to allow Quicken to retain the bug in the calculation of Amount Invested, but add a new quantity called Net Amount Invested, which properly would decrease when shares are sold. Then more accurate Return data could be calculated from that while allowing whoever likes Amount Invested as is could still have it.

https://community.quicken.com/discussion/7667979/add-a-net-amount-invested-column-and-calculate-roi-using-the-net-amount-updated-title

Mike48

Not sure if @Jim_Harman is still monitoring this bug or not. Is there a way to do this in a custom report? Since we know it is wrong on the portfolio page I'm trying to figure out a backdoor way to doing this. If I could just get Quicken to generate a report that gives the sum of all $ added/transfer/contributed to an account and the current account balance then at least I can calculate the returns on my own.

Jim_Harman

I'm still here.

If I understand your question, I think your best bet would be to use the Investment Performance report.

Customize to select the account or combination of accounts and time period you are interested in.

The Investments column will have the opening balance and everything that has been added to the account. The Returns column will have everything that has been removed from the account and the closing balance. 

The percentage at the bottom right, which you may or may not be interested in, is the annualized rate of return (IRR).

[Edit to add] This is different from the non-annualized ROI (%) which is a straight percentage.

You can export the report to Excel and manipulate the data if you want.

Perhaps one way Quicken could resolve this issue would be to use the same numbers as in the Investment Performance report but add a column to the report that shows the non-annualized percentage gain or loss. They might want to give this column a name other than ROI (%) to avoid confusion with the column in the Investing > Performance views that is the topic of discussion here.