Add a “Net amount invested” column and calculate ROI using the net amount (updated title)

Quicken Community

Issue: Amount invested and ROI calculation

In portfolio view, amount invested column, Quicken only
considers the total amount invested and do not adjust for investment sold or
withdrawn.

For example, if an investment of $40000 was made to a money
market fund in Jan 2017, and withdrawals (Sold) of $10000 each was made in June
and August, Quicken considers the amount invested still as $40000. ROI is then
calculated based on $40000 which makes the result skewed.

It is suggested to add a “Net amount invested”(Total
investment minus withdrawals) column and calculate ROI based on the net amount
invested which should give a more accurate result.

q_lurker

@C K Govindan:  Your idea has been around for a long time, and has never been acted upon by the Quicken programmers - at least not directly acted upon.  I believe there are some hidden unintended consequences to approaches that decrease the amount invested (or use a net amount invested) as sales are recorded.  I further believe the programmers (again quite a long time ago) brought in the Average Annual Return presentations as a better tool than the ROI figures.

That past history does not mean the decision will not be revisited in the future.  

Jim_Harman

I agree "amount invested" by itself is not particularly useful.

However it appears that the ROI (%) columns in the Investing > Performance views handle deposits and withdrawals correctly, recognizing that they show a straight percentage return, not annualized. Thus they are useful measures for periods of 1 year or less.

Search the in-product Help for "investment performance calculations" for more info.

Rocket J Squirrel

Some of us have been asking for this for decades. Don’t forget to vote for your own idea,  @C K Govindan .

Jim_Harman

A new "Net amount invested" column might be useful, but we should be very careful about asking for changes to the definition of Amount Invested, because that number is used in other investment calculations that are working correctly. See this discussion for more details.
https://community.quicken.com/discussion/comment/20017984#Comment_20017984

Note also that "Amount invested" in the Investing > Portfolio views is NOT the same as in the Investment Transactions report. Neither is right or wrong, they are just different.

Rocket J Squirrel

All I want is for ROI and all the other quantities that start with the word "Return" in the portfolio views to be correct. Currently, they are simply wrong if a partial sale of a security has occurred. Quicken Help has admitted this shortcoming for more years than I can remember.

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.

Jim_Harman

@Rocket J Squirrel
I agree it is important for Quicken's performance calculations to be correct.

Do you have a specific example of ROI (%) XXX being calculated wrong in the current product? It appears to be working correctly for me. Remember that you have to make sure the starting date in Options > Portfolio Preferences is set correctly. I see problems if it is set to a date later than the starting date of the reporting interval, e.g. setting it to 1/1/2019 causes problems in the ROI (%) 1-year column. Leaving it at the default of Earliest available date seems to work OK. 

The only problem I have seen with ROI (%) is that as described in the discussion I linked above it appeared that there were some circumstances where the calculation did not update when it should. There are ongoing updating problems with the Price Day change data which are resolved by setting the As of date one day earlier then back to the desired date. 

Note that the Retun (%) numbers are downloaded from Quicken's quote provider and there have been instances recently where the data for individual stocks is stale by several weeks. Today's data appears to be up to date.

Rocket J Squirrel

@Jim_Harman, I would need to construct a test. I don’t think my real data has the issue right now. I rarely sell partial holdings. When I rid myself of a security, I usually sell all shares. I will do it if I get time, but my usual copious free time is suddenly being sucked up by holiday preparations. 

Jim_Harman

I looked for example at the numbers for my money market funds where the balance fluctuates quite a lot and the reported ROI (%) is close to the interest rate. Also for other securities the 1 year ROI is close to the average annual return as expected.

Here is another example, for a security I have held more than 1 year, with dividends reinvested, some small quarterly sales to cover fees, and sold about 20% of my holding in August:

Compare the Investment Performance report for the security with the date range set to Last 12 months --to-- Investing Portfolio view with Options > Portfolio Preferences set to Earliest available date

-- Average annual return in IPR matches Av Annual Return (%) 1-year in portfolio view exactly as expected

-- ROI (%) 1-year is approximately the same as the Av Annual Return and exactly equals (Total Returns) / (Total Investments) at the bottom of the IPR, also as expected.

I have not tested this for Return of Capital, splits, acquisitions, Add, Remove, etc. but at least in this instance the numbers appear to be correct.

Rocket J Squirrel

Sorry, my lone remaining brain cell finds your example too complex. What I had in mind was a simple example. So I made one.

I created a new file with one brokerage account, initial balance $100,000 as of 12/31/18.

I bought 1000 shares of RocketStock (ticker RJS) at $100/share on 1/1/19.

I sold 500 of those shares at $200/share on 6/1/19.

The investing register is correct, but I am seeing some strange results in the portfolio view. I'm a little pressed for time today; can you explain this?

Jim_Harman

[clarified and corrected ROI calculation]

I assume you did not create any extra entries in your price history.

On 1/1 you bought 1000 shares at 100 for a total of $100,000

On 6/1 you sold 500 shares at 200 for a total of $100,000 

The amount you put in (on the Portfolio page this is called Amount invested) is $100,000.

The share price stayed constant at 200 for the rest of the year so at as of 12/10 your remaining 500 shares of stock is worth $100,000 and you have received $100,000 in cash for a total of $200,000. Your Return as Quicken defines it is $200,000 - $100,000 or $100,000

ROI (%) is (200,000 - 100,000)/100,000 or 100% as reported. The Av. Annual Return will be a little higher because it is annualized and the year is not over yet.

If you had sold the shares at 100, and the ending price was still 100, your ROI should be 0%

Rocket J Squirrel

But look at the portfolio view line for the account. With only 1 security, it should be identical to the line for that security (except for market value). Amount invested is double what it should be, throwing off ROI. Cost basis is more dollars than were ever in the account, throwing off gain/loss (%).

[Edited to clarify gain/loss % is thrown off. q_lurker]

Jim_Harman

You are right, on the account level the Amount Invested appears to be wrong, it should be $100,000 not $200,000, and this makes the ROI incorrect.

[Discussion of account level ROI continued here
https://community.quicken.com/discussion/7864889/account-level-amount-invested-and-roi-are-calculated-incorrectly-when-a-security-is-sold/p1?new=1
--JH]

However I think the cost basis is correct. It includes the realized gain of $50,000 on the shares that were sold on 6/1.  

Rocket J Squirrel

Jim_Harman said:

However I think the cost basis is correct. It includes the realized gain of $50,000 on the shares that were sold on 6/1.  

Realized gain is never included in cost basis. Realized gain equals sale proceeds minus cost basis.

q_lurker

@Rocket J Squirrel  The cost basis and thus gain/loss % is actually right at the account level as far as this example goes.  The cost basis of the shares still in the account is $50K.  The cost basis of the $100K cash is $100K for a total of $150K.  It is not material that there was never that much cash in the account.  Just prior to the sale, the value was of the shares was $200K with $100K of Cost Basis and $100K in unrealized gains.  After the sale, those became $150K cost basis and $50K unrealized gains.  The $50K of prior unrealized gains were realized and turned into cost basis for the new 'investment in cash.  

Is that gain/loss % meaningful?  A wholly different question.

This entire concept of ROI as implemented is not what people think it is.  For the programmers, they are saying the investor over time dropped $X into the pot of RocketStock.  Over all that time through multiple buys and sells, the investor got back some realized gains and losses, some dividends, any other 'return' and now has value of $Y.  The programmers defined ROI considers that entire history -- the total amount the investor has put into acquiring shares of that company -- aka Amount Invested.    

To many common users, they see a line for x shares and they expect the ROI to be applicable to those currently held shares; not for all the ins-and-outs of prior investments in that company.  Their desired base is closer to Cost Basis EXCEPT if shares were acquired by reinvested dividends -- those acquisitions don't count.

In your example for the one security, Quicken programmers are computing as Jim explained.  $100K went in.  $100K came out and it is worth $100K.  That is a 100% ROI return.  

It just so happens that also applies to the current holding.  You put $50K into those 500 shares.  You have gotten no cash from those shares.  They are now worth $100K.  That would be 100% ROI for the alternate calculation.  But that result is more coincidental from the simplicity of the example.

I believe the current OP and many before him have sought that alternate calculation.   

Jim_Harman

When you sell a security that has appreciated, isn't the realized gain included in the cost basis of the proceeds that are added to the cash in the account?.

In your example, after buying 1000 shares RJS at 100 you had $100,000 of cost basis in RJS and no cash. 

After selling 500 shares of the RJS at 200, the account has $50,000 of cost basis in RJS and $100,000 in cash.

Your total cost basis in the account is $150,000 as reported, right?

Rocket J Squirrel

You know, I never noticed that Cost Basis in the Portfolio View included the cash in the account.

Knowing that, I can't argue that Cost Basis and Gain/Loss are incorrect; neither is affected by Amount Invested.

There is no explanation for the $200,000 Amount Invested in the account line in the view. That can't be anything other than a bug. I'm sure you can reproduce it with my simple example.

But I agree that my example's numbers are too simple. I know I have seen bad values for various Returns in the past. I will try to come up with a better example.

Jim_Harman

Yes I duplicated your example and saw the same result. My observations on ROI have always been on single securities but I agree there is something wrong on the account level.

At least the IRR calculations for the Av annual return are correct on both the portfolio page and and the investment performance report.

q_lurker

I think the 'better example' is to add one more transaction to the list.  Sometime after the sale, buy 500 shares at $200/share.  You've then got 500 shares you bought for $100/share and 500 shares you bought for $200/share.  But the Quicken ROI is going to be based on an Amount Invested of $200K. 

The Quicken ROI is going to be (Value + Return) / (Amount Invested) = (200 + 100) / 200 = 1.5 >> 50% ROI

The alternate current holdings ROI is (Value + Return) / Amount Invested) = (200 + 0) / 150 = 1.33 >> 33% ROI

The Quicken number is saying over time you've put $200K into RocketStock.  (You made two $100K purchases.)  From that, you got out $100K Cash and now have $200K of stock.  Your investment strategy with RocketStock has produced $200K on $200K of investments.  You've gotten a 50% return on your investment.

The alternate approach says you have put in $150K (50K for 500 shares bought initially and still held plus 100K for another 500 shares bought as a second lot.)  That holding is now worth $200K.  Those shares have not returned any cash.  (The shares previously bought that produced a gain don't matter in this current holding view.  That is ancient history.)  The $150K in has produced value of $200K, a 33% alternate ROI. 

Which is right?  They both are.  They are saying different things. 

(Caveat:  I am still plugging away with QW2017, but I think subscription is going to produce the same results.) 

Rocket J Squirrel

Jim_Harman said:

When you sell a security that has appreciated, isn't the realized gain included in the cost basis of the proceeds that are added to the cash in the account?.

No. The sale proceeds are simply added to the account’s cash balance.

Basis is a tax fiction. It is subtracted from proceeds to calculate realized gain, which is taxable income.

q_lurker

Rocket J Squirrel said:

Jim_Harman said:

When you sell a security that has appreciated, isn't the realized gain included in the cost basis of the proceeds that are added to the cash in the account?.

No. The sale proceeds are simply added to the account’s cash balance.

Basis is a tax fiction. It is subtracted from proceeds to calculate realized gain, which is taxable income.

Alternate answer:  Yes, the sale proceeds become a new cash holding with an associated cost basis.  The process of adding the proceeds to the account's cash balance also adds to the total cost basis of all holdings in the account.  Does cash have a cost basis?  In Quicken, it does.  

Basis is and is not "tax fiction".  It is because it is used to distinguish the profit on a wise investment from the investment itself.  Realized gain is not always taxable income.  It is not "tax fiction" because it applies or is validly used in both taxable accounts and non-taxable (tax deferred) accounts.  

Rocket J Squirrel

I refer to basis as a "fiction" because the government defines what it includes.

If I inherit an asset, its basis magically changes to the fair market value on date of death. (This is usually called a "step up", but if one is unlucky enough to inherit during a recession or depression, it could be a step down.)

If I replace a broken window on my house, it does not increase basis. If I replace the roof on my house, it does increase basis.

Basis only applies when taxes are involved. The computation certainly exists in non-taxable accounts, but it applies to nothing.

Quicken Anja

Hello All,

This idea seems to have fallen stagnant and due to the age of the request and lack of user votes/comments, will be archived within the next 7 business days.

If you would like to see this idea kept alive and considered for possible future implementation in Quicken, be sure to add your vote and a comment explaining how this idea would be beneficial for you.

More information, including steps to vote and how to submit your own ideas for future product features/improvements, is also available here.

Thank you,

Quicken Community Support Team

q_lurker

@Quicken Anja -- I don't think the idea is stagnant.  Rather it has been discussed to death, and never gotten anywhere.  Users want something different with respect to ROI than they are currently getting.  Quicken programmer's have offered nothing.  This pattern is years old.  

IMO, if Quicken is not inclined to alter, update, or supplement the current situation, they should at least offer an updated clearer explanation of what is being provided.  Perhaps this is along the lines that I suggested above.  Perhaps there is some other explanation for why the current numbers are not wrong as so many claim.  Perhaps it is clarifying the limited use of the current calculation. 

Please give the users something.   

Jim_Harman

I strongly agree with @q_lurker that this idea is still very active. 

There is an active discussion that touches on the confusing, not-very-useful, not-what-users-expect-it-to-be definition of Amount Invested and the seemingly incorrect calculations based on it.
https://community.quicken.com/discussion/7889970/investment-discrepancy-edited

Vince_L

Quicken Anja - I think Quicken needs to offer some type of alternative here! We need an accurate way of looking at YTD portfolio growth. Today, the account % ROI calculation (not individually securities) is completely/blatantly wrong! I have entered my entire portfolio into Quicken, Vanguard and 3 online portfolio aggregators (yes, your competition!), and the result is that all agree on the portfolio YTD ROI (%) down to a hundredth of a percent, however Quicken is way off!

BBG

Investments tab shows income for an investment position from the first ever acquisition.  When one has 30+ securities, some repetitively bought and sold over time and some decadal holdings, what I want to see is the income and ROI on the current holding. In a simple case I buy 100 and sell the 100 after a year.  A year later I buy 100 .  The income and ROI displays will include income from both holding periods while the capital gain only is the current holdings, eg I can see the gain/loss of the current holding but cannot separate the income from the first and second holding periods without changing the date range. What I need might not suit everyone but if a holding goes to zero there should be an option that should trigger a 'restart' of all related P/L/ROI computations for new positions in that security not just the capital gain/loss.

q_lurker

See if you agree that your expressed idea is consistent with this one
https://community.quicken.com/discussion/comment/20047374#Comment_20047374
I think it is with you adding the Income column to one that also needs to be adjusted accordingly.

If you agree, I'll suggest a moderator move/merge this idea into that one.

Quicken did attempt to correct the amount Invested and ROI process for completely sold positions (your specific scenario) but I have never been convinced they got that right in all cases.  Thus I have not been able to point out specifically the current faults.

BBG

My 'problem' is indeed related to your own 'new idea'.  Simply I do not want anything from disjointed positions in TICKER, eg buy 100sh sell 100sh in year 2000; then buy 500sh in year 2005 and sell those 500sh in year 2010; then buy 200sh in year 2021 being the current position.  I only want the Gain/Loss, ROI and income from the current 200sh holding not any aggregate or combination of all holdings current and previous.  As was noted in your post there are multiple ways of looking at ROI for TICKER and there should be a switch so we see what we need and want.

Quicken Anja

Hello @BBG,

Thank you for reaching out to the Community with your request.

Your idea has been merged into this already active Idea thread regarding the same request.

Thank you!

Pawlu

Any update on this? It is now 2023 and yet I still cannot get accurate ROI's on my investments that I have sold and re-invested. Totally ridiculous that there is no solution to this, users have been hammering about it for over 5 years now. We simply want to easily see our ROI so that it matches our Brokerage Accounts.. come on...................