Return calculation by Lot, not just Position
A while ago I added the "Return" column to the main view I use in the Portfolio summary grid in order to better compare the performance of investments paying dividends to those that do not. I was confused when I saw that some positions that have never paid dividends showed differing amounts for "Return" when compared to "Gain". When I hovered over the title for "Return" the pop-up definition included income from sales as well as from dividends, which also seemed strange. Later I happened to notice that when I expand the grid to show the individual lots within a position the "Return" value is only displayed for the position as a whole and not for each lot. Finally, I noticed that a recent investment showed a positive "Gain" but a negative "Return". In the Security Detail View it showed that I had previously held that stock for a short time about five years ago, and I realized that the return was calculating "Return" including previously closed lots. Although this may be desirable in situations such as a long-term position with dividend reinvestments, it strikes me as quite misleading, if not wrong for most situations.
So my request is that Q consider recording dividends (along with return-of-capital, etc.) on a lot-by-lot instead of at the position level and then then simply using the same calculations, except with the lot identifier included. This would be rolled up to the position level, of course, just as "Gain" is handled.
Depending on the structure of your internal database, I assume that this would probably involve a conversion process. I realize, too, that since dividends come in as a lump sum for the whole position, the total would have to be prorated to all the underlying open lots. Any odd cent or two cent remainder could be added to the last lot, since it would be quite immaterial in the long run. Note that the dividends should only be applied to lots that were open before the ex-div date and are not closed before that date. (I had one case where a dividend arrived for a lot I had sold after the ex-div date.) Note that Q already keeps the ex-div date, so it is available.
There may be a problem for converting the database to do this with historical ex-div dates. I have not thought this part through completely, but it seems the conversion would only be needed for open lots, since there are not displays that include closed lots. Off hand I think that should not be a problem.
What do you think?
Comments
-
BTW—
When I first noticed the formula for the "Return" column in the Portfolio summary grid included adding in sales amounts in addition to the different forms of cash income I thought it was strange.
Shortly after I posted this, however, it dawned on me that for an open lot that is necessary if there have been any partial sales of the original number of shares. (For a closed position, the current market value in the formula drops to zero so the return is left as the sales amount minus the cost.)
0 -
One more thing.
Is there any reason this should not be considered a "bug", instead of just a request for a change?
In trying to use the "Gain" and "Return" columns to better compare positions returning a dividend (or a return-of-capital, etc.) to those without, the "Gain" column of the Portfolio Summary grid includes only open lots, while the "Return" column has all of the history for that holding. That seems quite inconsistent, if not actually wrong.
0 -
There are a lot of similarly named columns in the portfolio views so you must be specific with questions. The Return (%) XXX columns are not based on your transaction history, they are downloaded with the quote data.
When you say "Portfolio summary grid" are your referring to the page titled Account Overview that is displayed when you click on Holdings when viewing an account, or something else?
QWin Premier subscription0 -
The 'grid' I am talking about is the menu item, Investing/Portfolio. It has a number of different views that can be used to show your holdings, etc.
There is a column that is named, simply, "Return" that has the definition, "Market value plus cash income plus sale income minus dollars invested (since you invested)." That last part is not clear—since I start my portfolio or since I first purchase this lot.
0 -
The "Return" column:
- generally (default) computes from the beginning date of your Quicken file. If you have your view grouped by account that can mean from when you first owned that security in each specific account.
- is subject to the setting under Options / Portfolio Preferences for Show Return Calculations from: ___ where you can set a different starting date.
- is fundamentally used in conjunction with an "Amount Invested" column which is also subject to those same starting date considerations.
Combined that Return / Amount Invested yields a return on investment (ROI) percentage. My spin on those three in that combination becomes that Quicken is addressing the market timer - Did they successfully buy low and sell high as they bought and sold a security across their investing period.
The values presented for both Return and Amount Invested do not meet everyone's expectations, but are (seem to be) the intended programming, thus not a bug.
I believe the Income column may be more in line with your desires because gain/loss from sales is not included in the value — but then neither is unrealized gain/loss. Still that is not determined at a by-lot level.
I believe considering the ex-div date issue prevents the assigning dividends to lots . These return and income values are (I believe) computed on the fly, not computed one time and recalled from the database. Historical ex-div dates are not maintained. Indeed, the ex-div date is downloaded via the quotes supplier when available, but is not used anywhere else in the program to my knowledge. It like many of its similarly downloaded data (P/E, Market Cap, etc.) are only provided as a single simple reference — sometimes printed or displayed but generally ignored.
0 -
Q-lurker has several things in his/her comments.
First, I never noticed the option to include closed lots in the display. When I checked that option, I found the added information both interesting and confusing. For example, on a closed position the shares and market value columns show zero, as expected, but at the lot level the date column (under the Name) shows the sale date while the price and shares columns show the sale price and quantity instead of the current values usually there. (It appears that partial sale of a lot shows up as a separate lot that is closed.) For an open position that includes closed lots the rows are shaded in grey—presumably because some of the vallues, such as shares, cost, and gain, do not roll up into the the totals for the position as a whole It gets confusing, however, when a closed position shows values for the Amount Invested, Income, and Return but zeros for the cost and gain while the lots within the position show the opposite—the lots do not add up to the position totals. It looks like they did not want the values for the closed positions to be included in the current totals for the portfolio at the bottom of the display, so they display zeroes for the position but then display the detail for the lots in grey. But that leaves us with numbers that are meaningless at both levels. It does not even provide the sales amount for closed positions. In other words, this display seems to be intended for analyzing current holdings and, frankly, it is useless for those times when I may want to look back to see how I did on past holdings (the report, Investment Performance, is ideal for that--either for a specific holding or all the history). For day-to-day monitoring I use this display with the options to show closed lots turned off because it adds too much confusing old history (my current portfolio accounts go back almost 20 years with even older history in long closed accounts).
As it is now, if there is a prior closed position for the same security, the Return will be misleading because the Amount Invested and the Return values include old, closed lots while the Market Value, Cost, and Gain values do not—the data from the closed lots is needed to prove the values, but that means a ton of old irrelevant data is added, making the display unusable. So if I turn off the option to include closed lots, I don't see why including some of that data should not be considered a bug.
As for the thought that the totals in the display are probably calculated 'on the fly', I am not so sure. When launch the program it does take a minute or two before it displays my 'Home' screen, which has the Net Worth display starting from 1990. But then when I click on Investing, the display comes up instantly. If the investment data is summarized while the program is first reading through the data files and is not kept in the database, then it must be in a temp file somewhere.
Next, there is the question of the ex-div date. I agree that a compromise would be needed if a conversion program is required to store the dividends by lot, but it would not be that serious. I imagine dividends are received in brokerage accounts within 30 days of the ex-div date—40 days at most. So dividends could be prorated to lots that were open at least 30 days before receipt and not closed 30 days before receipt. For closed positions, any inaccuracy would never be noted; even for open lots, some initial inaccuracies in distributing the dividends to the lots would be insignificant if the Return value can be rolled up from the lots to the position without having to included amounts from long closed positions. Although this calculation could be used on a continuing basis if the portfolio totals are, in fact, done 'on the fly', it would eliminate any inaccuracies if a one-time conversion were done to store the historical dividends by lot and then new dividends were added as they come in. There must be some place in the database where some data is stored for each lot—purchase date, shares bought, price, commission, purchase amount, at a minimum—so one more data item could be added there.
0 -
Can I suggest taking a step back. Your interest is "to better compare the performance of investments paying dividends to those that do not. "
For two reasons, I can see relying solely on portfolio views to be limited in helping with such a comparison. The first is timing. I would think you would want to know how the two sets of securities compared over a specific time frame - YTD, last 12 months, 5-years, etc. While this can be done with portfolio views (as noted in prior comments), I find such specifications awkward and not very transparent.
My second cause for hesitation relates to available data columns. In particular, any such comparison needs to be normalized in some fashion - some sort of 'return' as a percent of 'something else'. That something else could be cost basis, value at the beginning time, current market value (or ending time market value) or maybe something else. None of the current available columns are well tuned for such a comparison. I am not sure your proposal for return on a lot level overcomes those shortcomings.
Where would I look / what would I do?
Question 1: Are you trying to consider how the two types performed in your portfolios, or more generically independent of your trading history?
Question 2: Do you want to compare individual stocks such as Apple paying a dividend versus Amazon not paying a dividend? Or do you want to compare all (or a group) of your dividend payers against a group of non-dividend payers? If that later is your direction, I would first consider setting up Investment Goals for securities accordingly, such that some securities would have one goal, others the second goal. You might even have a third goal for interest bearing securities (bonds, preferred stocks, and bond funds). Both portfolio views and most investment reports can then be grouped by Investment Goal.
Generically: I'd suggest in Quicken looking at portfolio views, Return% for YTD, 1-, 3-, and/or 5-Yr columns. These are downloaded values independent of your transactions. Unfortunately, I am currently not getting the 3- and 5-Yr values downloaded for stocks, only for mutual funds.
Based on your portfolio: In the portfolio views, I'd suggest the Average Annual Return columns (YTD, 1-, 3-, and 5-yr available). Be clear that these are annualized values. A 10% average annual return for 5 years compounds to total 5 year return of 61%. Similarly, a 10% real return for 3-months (well less than a 1 year) annualizes to 46% annual value which can be then seem very misleading.
Neither if those approaches groups your dividend and non-dividend securities. It can't be done on a generic view. For your portfolio, you can use the Investment Performance Report.
Below are two screen snips for selected securities along the lines I am suggesting. The first is a portfolio view. I added ROI% and Income% in case those need to be discussed in some fashion.
The second snip is an Investment Performance Report as compact as possible for the same set of securities for a 5-year period. (Private Inv and return data erased.)
Thus you can see for example that as balanced in this part of my portfolio, the 5 year AAR 40.16% for Apple and 28.74% for MSFT become a grouped average of 33.41%.
Rather than trying to manipulate per lot data, these would be the things I would look at comparing div and non-div securities.
0 -
I have a few exceptions to what q_lurker noted, and will try to follow his/her sequence.
As to my interest in this, it is simple. Each market day I use the "Show" option set to "Value" for the display, and use the Update button to get the closing prices for the day. I use this presentation option (with some column changes from whatever the original had) to keep an eye on the positions in my portfolio, and occasionally to look at the lots within a position. (I also keep a spread-sheet of my portfolio for other personal kinds of tracking and analysis.) But it is basically for monitoring my current portfolio positions to watch for losses and under performers. But this is harder when looking at a stock that is paying a 5% dividend (based on original cost) but shows a Gain % that is not impressive. As I said previously, it appears the whole portfolio display is primarily oriented toward the current portfolio—for example when the option for closed lots is activated, most of the totals are kept at the current values, leading, strangely, to the omission of the amounts for those items from the history of the closed lots.
Next are reasons why portfolio views are not ideal for comparisons of stocks with and without dividends. First, I agree that some analyses, such as the examples, are simpler with Reports, where limiting date ranges and selecting stocks is easier. But I disagree that a time-period or date range is needed—I am looking for the totals for each lot and position, for the time period of each. Second, Return, just like Gain, is not always a percentage, but can also be considered as a monetary value, so a denominator is not always needed. In fact, it makes sense to display both the dollar amount and the percentage of each measure for analysis—in fact, both $ and % are already available for both Gain and Return.
The problem is that the Return (and, thus, Income) is calculated for all time, including closed positions from years ago, so that the value (and the "ROI(%)") can be quite misleading. In my case, a while back I bought a stock in a new position, then noticed a while later that although the Gain was positive, the Return showed a negative value because I had forgotten that five years ago I had held it for a short time but sold it to 'harvest' a loss. I suspect this was done because of the problems with Dividends (which are included in Income) received after the lot is sold or when a new lot is bought after ex-div but before the receipt date. In my previous discussions I proposed a formula that should be within a cent or two to take care of those situations. As it is now, verifying the calculations is almost impossible with the current method of display, and using the option to include closed lots just makes a completely confusing display. In addition, if I uncheck the option to include closed lots, why are they still included, but only for Dividends and Income? Is that not a bug?
I do see where including information from closed lots can be useful, perhaps even necessary, in some cases. For example, when a portion of a lot is sold, a closed lot is created for the sale, and for evaluating the position you would probably want to have the amount of the partial sale included in the Return, but for an old, long-held position with several sales over time, it may not be as useful. The complete solution would be calculating the Dividends at the level of each lot and then adding a new option to include closed lots for just the open positions.
0 -
With all due respect, I am trying to understand your case for wanting the Return values by lot rather than solely the total Return for the entire position. I am not getting it. I don't see where it helps your objective to compare various stocks. Rather than get into any further conceptual discussions and disagreements, I think it better for me to let things stand as they have been expressed. Good Investing.
0 -
Q_lurker: let me help you understand my argument. You seem to be thinking about comparing different stocks or groups of them, but in order to compare investments, you first have to be able to evaluate each of them individually. To show how the current display is wrong I put together this display of a simple case at a point in time shortly after the position was opened and before any dividends were received.
At this point in time, without the closed lot in the display is completely confusing and incomplete. The Market Value – Cost = Gain is complete information, but then you notice that the Gain is positive while the Return is negative and the Amount Invested is not the same as the Cost. Displaying the lots within the position (just one in this case) does not add any more information—there is no way to figure out what is going on because the information is not complete. (At that time I did not include Income in the display, either.)
Then I went to the security detail report and found there was an old forgotten lot that was sold at a loss (during the height of the pandemic) that was being included. By activating the option to display closed lots you can see the loss, so you can add the Income column to reconcile the Gain with the Return amount. But you still cannot reconcile everything because the sale amount is not available for the closed lots to show how the loss was calculated; similarly, you have no idea where the Amount Invested came from because the purchase cost for the closed lots is not displayed. Note that the Income in this example is from dividends from the closed lot, so trying to take that into account later when looking at the Return would mean adjusting for subsequent dividends, but you have to go to the security details to find that out.
The point is, then, that the current logic makes it hard (if not impossible in messy cases) to properly evaluate a stock with dividends and previously closed lots. If you cannot properly evaluate a stock, then you cannot compare it to others or to your goals and norms. Second, it seems wrong that portions of the data for closed lots is included even when the option is set to omit closed lots.
It is obvious that this was done because dividends are received for the whole position and not lot-by-lot, and since dividends go into Income, which goes into Return, those were all left blank at the lot level as well. However, for on-going posting of dividends a pro-rata distribution would only require adjusting one lot for possible rounding cents (even auditors allow for that kind of rounding) so long as the ex-div date is taken into account. Even if a one-time conversion of the database is needed, the logic I previously proposed would probably be 99% accurate with the exception being a case where there a lot that should be covered is beyond the 30-day assumption. If this were done, then all the columns that are currently left blank for closed lots could be populated, since the graying of those lines implies they are not included in the portfolio totals at the bottom.
I hope this helps you understand.
0 -
Largely summarizing previous comments, there are a couple of ways you could make this analysis more useful. One would be to click on Options at the top right of the Portfolio view and set the starting date for the analysis. This defaults to Earliest to date, which will include all your closed lots. You might pick a year ago or YTD depending on the period you want to analyze. That would eliminate the long-ago closed lot.
Or you could use the Investment Performance Report, which does an annualized IRR calculation and again allows you to set a starting date. This is most useful for periods of one year or more.
Or if you want to compare the performance of publicly traded securities for time periods ending today without regard to when you bought or sold them, you could use the Return (%) XXX columns in the Portfolio views. These use data downloaded from Quicken's quote provider, and I think assume that any dividends or other distributions were reinvested.
QWin Premier subscription0 -
You seem to be looking at it the same way as q_lurker—evaluating over a specified period of time and comparing the performance to another stock, class, or index. I agree there are other displays that are better for analyzing YTD, 5-year, etc. performance or for comparing groups of stocks.
But I use this display each day when I update the quotes with the closing prices. I monitor my holdings to see whether any are becoming a problem, to spot any laggards, to see whether any do not meet my standards, and so forth. The time period is from the date each position was opened or each lot was purchased. Again, if you cannot properly evaluate a position, you cannot compare it to another or to your own expectations. For any stock, but especially those with dividends, this can be difficult if information if misleading data is displayed.
It would take checking each holding one by one using several steps and reports to get the accurate totals. But why, when there is a simple way to make the display much better?
0 -
I would think that monitoring your holdings would consider the performance all of the lots of each security that you currently hold or might buy over some time period. I don't think it is "fair" to use different time periods for each security or each lot, except for the purposes of capital gains taxes.
But you have certainly explained the idea clearly. We will see if others think this would be an important improvement.
QWin Premier subscription0
