Home Quicken for Windows Product Ideas - Quicken for Windows Investments (Windows)

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

UnknownUnknown Member
edited December 2019 in Investments (Windows)

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.

3
3 votes

No Status · Last Updated

Comments

  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    edited May 1
    @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_HarmanJim_Harman SuperUser ✭✭✭✭✭
    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.
    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    edited December 2019
    Some of us have been asking for this for decades. Don’t forget to vote for your own idea,  @C K Govindan .
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    edited December 2019
    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.
    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    edited December 2019
    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.



    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    @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.


    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    edited December 2019
    @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. 
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    edited December 2019
    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.
    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    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?



    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    edited December 2019
    [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%

    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    edited December 2019
    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]
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    edited December 2019
    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.  
    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    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.
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    @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_HarmanJim_Harman SuperUser ✭✭✭✭✭
    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?
    -- Jim QWin Premier subscription
  • Rocket J SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    edited December 2019
    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.
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • Jim_HarmanJim_Harman SuperUser ✭✭✭✭✭
    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.
    -- Jim QWin Premier subscription
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    edited May 1
    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 SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    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.
    Quicken user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    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 SquirrelRocket J Squirrel SuperUser ✭✭✭✭✭
    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 user since version 2 for DOS, now using QWin Premier Subscription on Win10 Pro.
Sign In or Register to comment.