Investment Performance Report errs by not using the cost basis

WindowFinance

When you "Add" shares in an investment account and you input a cost basis for such "Add" transaction, that cost basis is not used as the "Investments" value in the Investment Performance Report. Instead, the Report uses the market value of the position on the date of the "Add" transaction as the value to be added in the "Investments" column of the Report.

I think this is erroneous and a bug. For example, due to some share transfer, I added 10 shares of A to the account on 2/1/2020. Those shares were acquired on 1/1/2020 at $10 a share. So the cost basis should be $100 for the position. On 2/1, A price is higher, and the value of the 10 shares is $500. In the Investment Performance Report, currently, the "Add" transaction would show $500 in the Investments column, the market value on the "Add" date, instead of the correct original investment (cost basis) of $100. As a consequence, my return is lower.

I would like the performance report to show my performance compared to the cost basis, not the date I transfer the shares into the account, which should have no bearing on my performance. How can I do this? Thanks.

Jim_Harman

This is not a bug IMO.

When presenting performance subtotaled by account, the Investing Performance Report correctly computes performance based on the market value of any securities added to the account on the date they were added. It is answering the question, "How did my account perform over the analysis period?"

If on the other hand you want to know "How did my holdings of Security A perform over the period?" you should subtotal the report by security and include all the accounts where you held the security.

In your example if you want to know how Security A performed, you should subtotal the report by security and include both the source and destination accounts. The Returns and Investments for the transfer showing the market value on the transfer date will cancel each other out and the report will show the overall performance of the security. 

WindowFinance

Thanks. That makes sense. But that doesn't help my situation: I acquire Security A not as a result of a purchase of A but a stock exchange of another security X for A. For example, I purchased one share of X for $10 a share. Then in a corporate action, I receive 2 shares of A for every X. I enter a "Remove" transaction to get rid of X, and an "Add" transaction for 2 shares of A. The cost basis of the 2 shares of A is the $10 spent purchasing the X.

The Returns and Investments for the transfer showing the market value on the transfer date do not cancel each other because on that date the market value of 1 share of X is not necessarily the same as the market value of 2 shares of A. Generally, I receive a premium for the exchange, meaning the value of 2 shares of A exceeds the value of the 1 share of X. That's why I need to use the cost basis to measure the performance of A.

Any other method? I've tried the Investment Transactions Report which produces the correct dollar amount of the gain in the "Cash+Invest" column. Unfortunately, it does not give me the Avg. Annual Return percentage that I am looking for. Thanks.

Jim_Harman

If you enter a Corporate Action transaction in Quicken the Removes and Adds should cancel each other out and have no impact on the account's performance. The cost basis should be unchanged. 

WindowFinance

I enter a corporate security spin-off by recording a Remove of the old shares and an Add of the new shares using the cost basis of the old shares. The cost basis is in fact unchanged. This way of the recording the transaction, as I explained above, creates a problem with the Investment Performance Report. Is there a better way to record this corporate spin-off transaction?

Jim_Harman

I still do not understand what problem you are having with the Investment Performance Report. Is this a small difference, perhaps between the conversion price of the new security and the closing price on the conversion date, or something bigger?

Have you tried clicking on Enter Transactions in the account where the security is held and using Quicken's Corporate Securities Spin-off macro transaction?

That should create the correct Removed and Added transactions. Sometimes there are complications, for example when you receive cash in lieu of a partial share.

If you post the names of the securities involved, someone hare can probably provide more specific instructions. 

Jim_Harman

Note: with a spin-off like you have described or a mutual fund share class conversion, if the securities involved are all in one account, make sure you include both the old and new securities in the report customization.

Note 2: I assume you are using the current version of QWin Subscription, and that this is not a mutual fund where you have selected "Use average cost". Earlier versions had problems with these conversions and share class conversions with average cost selected still do not work correctly.

WindowFinance

Thank you very much for your patience and help on this. Below are more details of the exchange. I use fake numbers for ease of reading.

• 3/1/2019: I bought 1 share of LLY at $10 a share. My cost basis for this purchase is therefore $10.

• 4/1/2019: I tendered the 1 share of LLY to exchange for 5 shares of ELAN. On that day, LLY was $15 a share and ELAN was $6 a share.

• 5/1/2019: I sold the 5 ELAN shares at $8 a share.

The Investment Performance Report including both securities shows something like this:

LLY
Date Action Description Investments Returns Gain
3/1/2019 Bought 1 share of LLY 10
4/1/2019 Removed 1 share of LLY 15 $5


ELAN
Date Action Description Investments Returns Gain
4/1/2019 Added 5 shares of ELAN 30 ($10 cost basis)
5/1/2019 Sold 5 shares of ELAN 40 $10

According to the Investment Report, my total gain from the investment involving LLY and ELAN is $15 (5+10). In fact, the gain should be $30 (40-10).

The Investment Report overlooks the fact the LLY company rewards me for the exchange by giving me more in ELAN shares than the LLY shares I give them. On the day of the spin-off (the exchange day), I gave them only $15 worth of LLY shares, but they gave me $30 worth of ELAN shares. This is very typical of an exchange offer in a corporate spin off. This example is based on an actual spin-off. I use round numbers here for illustrations. The actual spin-off is described here: https://investor.lilly.com/news-releases/news-release-details/lilly-announces-final-results-elanco-exchange-offer.

How can I generate a performance report that captures the premium the LLY company gave me for tendering my LLY shares in this exchange offer?
Note that the spin-off is tax free. I am not taxed unless and until I sell the ELAN shares to realize the $30 gain. The 1099 I received correctly reported this $30 gain.

Thanks.

q_lurker

Did you exchange all or just some of your LLY shares for ELAN shares?  Question applies with respect to all accounts.  

If all, I would use the Corporate Acquisition transaction with Elan acquiring LLY at the 4.5121 ratio.  Quicken will then create a Remove Shares transactions for all shares of LLY (in all accounts) and a set of Add Shares for the ELAN holding.  (If a fractional share of ELAN developed, you would sell that fractional share for cash-in-lieu received).  The current Quicken will generate those transactions such that the Remove value and Add Value in the Investment performance report counteract each other (both reflecting current market value).

If you generated the Remove Shares yourself,you likely need to edit the transaction.  Do that in the transaction list itself (not through an Edit Transaction button), and change the value shown under the transaction date to the market value applicable at that time.  That should (I believe) clear up the discrepancy.  

WindowFinance

Unfortunately, I only tender some of my LLY shares. So, I think I have to enter the Remove and Add transactions myself instead of using Quicken's corporate action features.

The "Remove" entry, either via Edit or in the transaction list, only allows me to modify the number of LLY shares I am removing. Quicken automatically uses the cost basis as the value being "removed." How do I change that?

Thanks.

q_lurker

a) Let's be clear we are talking about the Investment Performance Report that has column headers of Date, Account, Action, Description, Investments, Returns, and Average Annual Return.  No 'Gain' column.

b) My apologies for the mis-direction in my last post.  They have at some time removed the ability to edit the 'Market Value" associated with removed shares.  It was in QW2017; not in QW-Subscription R26.23.  (sigh)

c) (Note to @Jim_Harman -- Damn, this is harder than it should be) --- What I see you need to do to get things right is

1. Delete the existing Remove shares and Add Shares for this transaction.
2. Decide on your "transaction date".  Appears to me to be 3/11/19, but you may be using some other date like your 4/1/19 date.  Delete any price you have on that date for both LLY and ELAN.
3. Use a Corporate Acquisition entry as shown below.  I've chosen the $30.70 ELAN value based on the closing for 3/11/19.  Another date, you might want another value.
4. That should generate one Remove Shares and possibly one or more Add Shares transactions, depending on the number of lots of LLY you held at the time.  Edit each of those transactions.  For the Remove Shares, reduce the number of shares to the number you actually tendered.  Edits to the Add Shares would need to be similar (change the number of shares accepting the change to Total Value), but if there are several lots you may edit each Add differently and you might delete some Add Shares transactions completely.
5. In my trial run through this, I started with 2 LLY shares, did the Corporate Acquition turning the 2 into 9.0242 ELAN shares, then edited to alter the Remove to 1 share and the Add to 4.5121 shares.  I did not try a multi-lot holding. 
6. Upon completion, your Investment Performance Report (for those two securities) should look something like this where the Return Value (removal of LLY shares) matches the Investment value (Adds of ELAN shares).
7. Caution:  if you already have prices for ELAN or LLY in your records for that date, those prices will be used rather than the price you use in the Corp Acq transaction.  If those prices are inconsistent with the share ratio, the IPR value will not balance each other.  Thus step 2 is critical to getting the proper results.  

Hope this helps.  (and I I noted, it shouldn't be this hard.  grrr.)
  

WindowFinance

Thanks @q_lurker for the very detail and easy to understand work-around. Thank you for doing a simulation in your own Quicken. I assume you ran the Investment Performance Report before you edited the Remove/Add transactions to reduce the number of shares tendered by half. If you had run the report after, it should have shown the value of the exchange to be $138.52, right?

I think this work-around will work and I may try that next time. For this particular exchange, however, because I have various lots in different accounts, I'll just leave them alone.

Thanks @Jim_Harman and @q_lurker a lot for your phenomenal help. I hope the Quicken developers will read this and make entering a spin-off transaction easier and make it work better with the Investment Performance Report.

Thanks.

q_lurker

WindowFinance said:

Thanks @q_lurker for the very detail and easy to understand work-around. Thank you for doing a simulation in your own Quicken. I assume you ran the Investment Performance Report before you edited the Remove/Add transactions to reduce the number of shares tendered by half. If you had run the report after, it should have shown the value of the exchange to be $138.52, right?

...

Thanks.

Actually, not right.  Turns out I left out a step 4a, that I had not realized was necessary.  

After step 2, the prices for ELAN and LLY are blank for the selected transaction date.  Step 3 inserts prices for those securities for those dates.  $30.70 for ELAN because you provided that price.  $138.52 for LLY because that is 30.70 * 4.5121.  That is all fine to the extent the numbers work out..  

Very oddly (and improperly),after editing the Remove Shares and Add Shares transactions for the number of shares, the prices for those securities changed.  They doubled because I halved the shares involved.  So a Step 4a is needed.

After step 3 before step 4, take note of the prices for the two securities on the transaction date. 

4a)  Edit the prices for the two securities to match the prices that were in place after step 3. 

Further note to that set of circumstances -- Quicken is using the price history record to determine the values for the Investment Performance Report.  Further, it is inserting values in the price history record that may not be applicable.    If you come in later and alter the prices on that date, it seems the IPR values will also change. That too seems incorrect to me.