Home Quicken for Windows Investing (Windows)
Quicken Community is moving to Single Sign On! Starting 1/22/21, you'll sign in to the community with your Quicken ID. For more information: http://bit.ly/CommunitySSO

How to have correct cost basis in a corporate spinoff, e.g., Pfizer/ Upjohn/ Mylan transaction?

24

Answers

  • splashersplasher SuperUser ✭✭✭✭
    Edward Jones has not changed the Pfizer basis numbers yet either.
    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • jr7107jr7107 SuperUser ✭✭✭✭
    I managed to reconcile these numbers with Fidelity and the @Tom Young post. 
    Costbasis.com has posted information for the spinoff using closing prices on 11/17 resulting in a  0.94674/0.05326 split up of the basis.  If and when a Form 8937 becomes available they might change their calculation to conform.  Schwab has not yet posted any basis adjustments for Pfizer and Viatris.

    Quicken user since 1994.
    Quicken Forum/Community Contributor since 2005.
  • DbraultDbrault Member ✭✭
    I am trying to enter the Pfizer/Viatris stock spin off in Quicken Canadian edition release 28.15. I enter all pertinent information quicken ask..ie: stock split ratio, old stock price and new stock price. It gives me the correct amount of shares in the new company (Viatris) but the Pfizer cost basis is almost double what it used to be. I don't know what I am doing wrong.
  • splashersplasher SuperUser ✭✭✭✭
    edited November 2020
    Dbrault said:
    I am trying to enter the Pfizer/Viatris stock spin off in Quicken Canadian edition release 28.15. I enter all pertinent information quicken ask..ie: stock split ratio, old stock price and new stock price. It gives me the correct amount of shares in the new company (Viatris) but the Pfizer cost basis is almost double what it used to be. I don't know what I am doing wrong.

    What entries did you use when you did the stock spin-off?  There is a thread on this topic already, I'll flag this for a moderator to merge with it to keep it all together.
    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • DbraultDbrault Member ✭✭
    Entered 0.124079 as a ratio.
    36.04 as the old stock price
    16.34 as the new (viatris) stock price.
    Quicken removed my original pfizer shares then added the shares back using the new price with the ratio factored in. This cause my cost basis to almost double. Now, in my holding view, i am showing a return of negative 46.3% when it should show a positive return of 4.3%.
  • splashersplasher SuperUser ✭✭✭✭
    When I did the "Corporate Securities Spin-Off",  Quicken generated a RtnCapX transaction, an Added Viatris transaction for every lot of Pfizer with a basis which when totaled across all of the adds was equal to the RtnCapX transaction.
    If it did anything different, I would do a Quicken Restore and try it again.  
    This time, make a screenshot of the spin-off transaction prior to executing it so that you can show us exactly what you entered.

    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    edited November 2020
    Dbrault said:
    Entered 0.124079 as a ratio.
    36.04 as the old stock price
    16.34 as the new (viatris) stock price.
    Quicken removed my original pfizer shares then added the shares back using the new price with the ratio factored in. This cause my cost basis to almost double. Now, in my holding view, i am showing a return of negative 46.3% when it should show a positive return of 4.3%.
    The highlighted sentence suggests that the spinoff wizard did things correctly, but obviously it did not.  The numbers you entered should have resulted in approximately 95% of the old basis remaining with Pfizer and approximately 5% of the old basis assigned to Viatris.  So it sounds like something like (95% x 2) of the old basis is now showing up in the Pfizer shares and 5% of the old basis is assigned to Viatris?
    It doesn't appear that you did any thing wrong but maybe you did.  There's lots of thing you might try.  The first thing you might try is to simply delete the new entries entered by the spinoff wizard and try again.  Or, if you just used the wizard yesterday, perhaps restore a backup just before the entry, validate the file, including rebuilding investing lots, and try again. 
    Just recently Quicken changed how the spinoff wizard worked for the US subscription versions and I'm not sure if this change has affected the Canadian versions as well.  In the new approach to spinoffs Quicken makes 2 entries for each lot, one of which is a RtrnCap entry and a MiscInc entry.  Is this what you are seeing in your version?
  • splashersplasher SuperUser ✭✭✭✭
    Tom Young said:
    …Just recently Quicken changed how the spinoff wizard worked for the US subscription versions and I'm not sure if this change has affected the Canadian versions as well.  In the new approach to spinoffs Quicken makes 2 entries for each lot, one of which is a RtrnCap entry and a MiscInc entry.  Is this what you are seeing in your version?

    @Tom Young
    I believe you have the new vs old backwards.  When I did the Abbot->Abbvie split in 2011, Quicken created a RtnCapX for each lot, this time with the Pfizer->Viatris split, there was only a single RtnCapX generated for the amount of the new Viatris basis.  The original Pfizer basis/lot was not changed.

    Running US Quicken Premier 27.1.30.10 on Win10


    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    You're right. 
    Frankly the RtrnCap transactions used to cause so much problem in my file that long ago I stopped using the spinoff wizard and instead would do my own sequence of Remove and Add actions to get thing right.
    I was surprised that Quicken had gone back to the RtrnCap action to correct the error in IRR calculations for split situations.

  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    @Dbrault It sounds to me like your Canadian R28.15 is using the older methodology for Corporate spinoffs -- One remove Shares and several Add Shares.  But I think there is may be more hidden in your question.  
    Quicken removed my original pfizer shares then added the shares back using the new price with the ratio factored in. This cause my cost basis to almost double. Now, in my holding view, i am showing a return of negative 46.3% when it should show a positive return of 4.3%.
    You can edit the Add Shares for the Pfizer holding (one lit or several) and see what values it is using as the Cost Basis in those lots.  As noted, it should be about 95%
    of the original basis of the lot.  I also suggest you look at the portfolio view of the pfizer holding and see what cost basis shows for the day before and after the spinoff (adjust the As of date accordingly.)

    You have not been clear on what "return" value you are citing.  I suspect what you are seeing is that the "Amount Invested" (not the Cost Basis) has almost doubled and that the ROI based on Amount Invested is thus negatively impacted by the generated spinoff transactions.

    I prefer to look at Average Annual Return (through portfolio views or the Investment Performance report) as a measure of investment return.  That parameter seems more soundly based and less subject to some of Quicken's quirks.

    Please clarify if any of this is inaccurate.  

  • Rick8Rick8 Member ✭✭✭
    Form 8937 is attached. It is also available on the Pfizer Website under Shareholder Services - Viatris Transaction. Here is the link https://s21.q4cdn.com/317678438/files/doc_news/2020/08/Distribution-Tax-Basis-Information.pdf You can find the cost basis on Schwab Website under portfolio - positions - cost basis (in blue). Even though it says N/A, click on it and it is there. Export and delete any doubles with zero cost basis entries. (Don't know why they are there}. You can also download your Nov. Statement which also has the cost basis. It does not help with Quicken as it still calculates it wrong. Cost basis total is off by about $2.87 with each transaction off PFE (original buys) and VTRS ("added's" by quicken) for both VTRS and PFE. Used "Corporate Spin-off" with .124079 and the closing prices for PFE of 36.04 (End of 1st Day Trading) and VTRS 16.34 (End of 1st Day Trading). If anybody figures this out, please post. I have 3 accounts with PFE going back about 30 years. Thanks.
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    edited December 2020
    " It does not help with Quicken as it still calculates it wrong."
    The numbers in the Form 8937 of $35.3869 and $15.66 result in a 94.7949%/5.2051% split.
    Seems like Schwab is using something more along the lines of 94.7751%/5.2249% and deducting the fractional share cost from the oldest lot.
  • Rick8Rick8 Member ✭✭✭
    That is exactly what they are doing. Using 0.94773/0.05227.
    What prices for cost of old shares, and cost of new shares should I use?
    The wizard says Day Of Spin-off, but those results are worse!

    Stock/Date Open High Low Close
    Pfizer (PFE)
    11/16/2020 37.86 37.95 36.85 37.33 Day of Spin-off
    11/17/2020 36.49 36.50 35.82 36.04 End of 1st Day Trading

    Viatris (VTRS)
    11/16/2020 15.64 16.15 15.34 15.86 Day of Spin-off
    11/17/2020 15.39 17.93 15.30 16.34 End of 1st Day Trading


    Any other suggestions?

    Also FYI the menu dropdown for the security in security detail view last worked in version QW 28.10!
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    The numbers you enter in the spin off wizard don't have to be "real" numbers in the sense of numbers you can find on some sort of quote sheet, all they need be are numbers that give you the "correct answer" if you're trying to match Schwab.
    If you use prices of $9.551528 and $4.245633 you should get the same numbers as Schwab.
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    Tom Young said:
    The numbers you enter in the spin off wizard don't have to be "real" numbers in the sense of numbers you can find on some sort of quote sheet, all they need be are numbers that give you the "correct answer" if you're trying to match Schwab.
    If you use prices of $9.551528 and $4.245633 you should get the same numbers as Schwab.
    Let's not be too fast with that thought, @Tom Young.  Used to be the case, but with the current RtrnCapX/MiscIncX/AddShares transactions and flow of info into the Investment performance report and elsewhere, I am concerned the two values used may flow into other aspects.  Work in progress.   
  • Rick8Rick8 Member ✭✭✭
    Tom
    Thanks - they did work!
    How did you calculate these numbers.

    I am concerned with what @q_lurker says about this new way of using theRtrnCapX/MiscIncX/AddShares transactions. The last time I did this was for Carrier/Otis/Raytheon with QW 28.10 (the old way) and it worked perfectly!
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    edited December 2020
    Well you might be correct there.  Since I haven't embraced the new methodology I'm still thinking along the lines of the old Removed/Added sequence. 
    You'd think that any numbers you used that gave you the correct basis split would get that aspect of the entries correct, i.e., the RrtnCapX entries and the basis for the remaining parent shares and the new child shares would be correct,  and entering the correct end of day quotes after that would get the market values correct, but you might get some very weird entries for the MiscIncX transactions.

    ADDED: I've said it before but I think Quicken took the wrong approach here.
  • Rick8Rick8 Member ✭✭✭
    @Tom Young
    The new Viatris Shares are exact to the penny for the cost basis on each added transaction.  They agree with Schwab and my spreadsheet. The Pfizer shares have a total cost basis that is correct because of the RrtnCapX, however each original transaction cost basis is way off.  They do not agree with Schwab nor my spreadsheet! 

    I agree with @q_lurker that Quicken's new approach is all wrong.  When you sell specific shares, i.e. FIFO, those get reported on your 1099.  The old way you could import these into Turbotax and they would be accurate for year end tax planning.  With the new method, all these numbers will be wrong so importing is useless and negates using the two programs together!!!!!!!!!!
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    Rick8 said:
    @Tom Young
    The new Viatris Shares are exact to the penny for the cost basis on each added transaction.  They agree with Schwab and my spreadsheet. The Pfizer shares have a total cost basis that is correct because of the RrtnCapX, however each original transaction cost basis is way off.  They do not agree with Schwab nor my spreadsheet! 

    That is what I am seeing in my testing.  The RtrnCapX is the right total, but spread to multiple lots incorrectly.  I haven't figured out the math Quicken is doing.  A singular RtrnCapX to multiple lots is consistent with the one generated by that Corporate Spinoff -- and is wrong. 

    In most cases of return of capital distributions, the total RtrnCap would distribute to the lots in proportion to the number of shares, just as dividends are paid out per share.  In the case of Corporate Spinoffs, the allocation has to be done in proportion of the lots basis to the whole basis in that account. Effectively, each lot needs to be treated independently.   
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    In a separate version of my "live" file I used the numbers I provided you and let the spin off wizard do its thing.  The numbers - overall and at lot level detail - looked correct to me. 
    Your report that the overall total basis for Pfizer is correct while the lot detail is all wrong suggests to me that the old "RtrnCap" bug his bitten you. 
    In my live file I didn't use the Spinoff wizard for exactly that reason, having been bitten in the butt in prior years.  Instead I used one Remove and multiple Adds to get things correctly stated. I also used the average of the 17th's High/Low numbers understanding that they probably wouldn't match Schwab's numbers when they finally got around to posting them, but that they would be "close enough."
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    When I did my test I didn't bother looking at Schwab, I simply compared the numbers in the file I used to my "live" file.  The numbers were all slightly different, as you'd expect as I used a different allocation in my live file, but nothing jumped out at me as being way off.
    My test file didn't have the 12/2 dividend lot included in it.  You wouldn't think having that dividend included would change anything if you used the 11/16 date.

  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    Three Issues -- RtrnCapX, Price Chgs, Original entries

    For this investigation, I used a test file with PFE held in two accounts.  The Brokerage account had 3 lots including a pair to match the PFE example in their Form 8937.  The Brokerage 2 account only had one lot.  Entering the Corporate Spinoff action in either account generated the transactions in both accounts.  These were all done on R30.14.

    For those who don't want the gruesome details -- My conclusion -- The Corporate Spinoff action introduced in R29.9 is not fit for service at this time.

    1)  RtrnCapX -- See attached pic
     
    These are the results immediately after a Corporate Spinoff based on the PFE-VTRS action of 11/16/20.  For the action, I used the PFE data - Share ratio = 0.124079, PFE value = 35.3869, and VTST value = 15.66.  The pic shows the resulting portfolio view and Investment Performance report.  The primary item to note is that although the total cost basis for the PFE holding after the spinoff is correct (16,115.13), the values on each lot are incorrect. 

    Lot 1 shows 3,631.30 -- should be 3,791.79
    Lot 2 shows 5,631.30 -- should be 5,687.69
    Lot 3 shows 6,852.53 -- should be 6,635.64

    This appears to be an issue with the RtrnCapX itself.  Entering the RtrnCapX transaction directly yields the same values.  As I noted previously, the behavior of the RtrnCapX transactions as being used a corporate spinoff action should be different than one used in a more common dividend-like distribution -- proportional to basis of the lot rather than shares in the lot.

    The cost basis as developed for the new Viatris holding appears is correct per my independent calculations.

    For my second and third concerns, I refer to the attached 4-page pdf file -- four variations of the Investment performance report..

    2)  Price changes.  See pages 1 and 2 of the pdf.  The form 8937 data presented the fair market value of Viatris as $15.66 for purposes of the allocation of basis.  Yet, Quicken's downloaded data for VTRS on 11/16/ is going to plug in $15.855.  Page 1 shows the IPR immediately after the spinoff with the 11/16 price still set at 15.66 per the spinoff action.  Note that the numbers balance (within a penny).  In the Brokerage account $2,331. 68 was "Returned" from the PFE holding; $2,334.67 was "invested" in the new VTRS holding.  The Brokerage 2 account similarly balances at 38.85 vs 38.86.

    Page 2 shows the effect of applying the ticker to the VTRS holding and updating the prices.  For 11/16, the price went from 15.66 to 15.855.  That changes the "investment" value of the VTRS holding.  The new total in Brokerage goes to 2,360.71 and to 39.34 in the Brokerage 2 account.  Both those are now out-of-balance with the dollar amount that was returned from the PFE holdings.  

    There are a variety of ways to calculate the fair market of the two securities and it will often be the case that the value used for the spinoff allocation is different than the closing value for that date.

    3)  Data entry:  In past Quicken versions, only the relative proportion of the two fair market values was relevant.  Mathematically, that is true.  In this current implementation of Corporate Spinoff within Quicken, that is no longer true.  

    To test this, I chose to double the two fair market entries.  Doing so skewed the results as shown on pages 3 and 4 of the pdf.  Page 3 is comparable to Page 1 and mathematically should yield the same results.  Page 4 changing to 'correct' closing values (to 15.855 from 31.32) creates even more mind-boggling values.

    My conclusion -- The Corporate Spinoff action introduced in R29.9 is not fit for service at this time.

    .    
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    Focusing only on the accounting, I've taken @q_lurker 's first example on Page 1 of his PDF - two lots of 500 shares and one lot of 200 shares- and put the results - "Correct", "Quicken", and "Difference" - into spreadsheet form.  It might make it more clear what the underlying "accounting" problem is.  That issue aside, I simply don't understand the developers' convoluted approach to what seems to be a pretty simple situation.


  • Rick8Rick8 Member ✭✭✭
    @Tom Young
    What I do not understand is that the Quicken "old method" worked fine and was accurate.  What is it that they are trying to accomplish?  I really get annoyed at developers that always have to justify their existence by changing what works!

  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    @Rick8 The older method had some issues with the Average Annual Return calculation, but I think those could have been resolved with a less dramatic change. 
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    @Tom Young. Thanks for figuring out Quicken’s math — the 884.87 distributed per share. 
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    q_lurker said:
    @Tom Young. Thanks for figuring out Quicken’s math — the 884.87 distributed per share. 
    Well you'd clearly figured that out for yourself.  I simply put that in a more "visual" format.
  • q_lurkerq_lurker SuperUser ✭✭✭✭✭
    Rick8 said:
    Tom
    Thanks - they did work!
    How did you calculate these numbers.

    I am concerned with what @q_lurker says about this new way of using theRtrnCapX/MiscIncX/AddShares transactions. The last time I did this was for Carrier/Otis/Raytheon with QW 28.10 (the old way) and it worked perfectly!
    @Rick8 I think my later post did 'prove' (or at least suggest) that you need to use realistic numbers.  

    The basic parameters are share ratio (SR), 'old' price (FMVold), 'new' price (FMVnew), allocation percent to old and new (PCold and PCnew), but there are mathematical ties among those parameters, like PCold + PCnew = 100%).  The SR is effectively a given. 

    If you know the PCold (and thus also PCnew) with a given share ratio, it becomes a matter of choice.  Choose a FMVold or FMVnew and calculate the other one. 
     
    FMVold = (FMVnew x SR x PCold) / PCnew
    OR
    FMVnew = (PCnew x FMVold) / (PCold x SR)

    So you could use the FMVnew as 15.66 as in the Form 8937, or 15.855 as Quicken shows for the close, and Schwab's percents to compute a usable FMVold.

    Or you could use the FMVold as 35.3869 or some other value to get a FMVnew value to use.  

    But beyond that aspect, I am trying to figure out a 'viable' path to recommend for those dealing with multiple lots, because of my current distrust in the current spinoff process.  Hope to have more to offer in that direction soon.  

  • splashersplasher SuperUser ✭✭✭✭
    Maybe it has been written somewhere above, but I understand the basis of everything after the split should be equal to Pfizer's basis before the split.
    But, the cash you get in lieu of the partial Viatris share, is it part of the basis of Viatris?
    Without the "in lieu cash", Edward Jones shows a combined basis that is $8.79 lower than the original Pfizer basis, but if you add in the $10.56 cash, my overall basis is higher, so theoretically, I got a reduction of unrealized gains of $1.77.
    I am so glad I have only owned Pfizer stock since 2013, getting to tweak 28 lots was a real snore.
    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

Sign In or Register to comment.