Stock split failed to double number of shares QW18 Premier

D Bunker
D Bunker Member ✭✭
edited October 2018 in Investing (Windows)
Entering old Maytag / Whirlpool Corp transactions from 1985 to acquisition by Whirlpool in 2006 (purchases and div reinvestment). Maytag underwent 2 splits (2:1), dated 01/17/1986 &12/16/1987. Entered the stock split, but the share numbers prior to the splits remained unchanged. I am entering the old data as Whirlpool, to avoid the name change later if that makes any difference.

Comments

  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    Mechanically that's simply how Quicken works with splits: the shares get multiplied appropriately and new prices downloaded post split reflect the split, but prior period numbers - shares and price - are not adjusted.
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    Interesting, however, when I exported the data report to an Excel spread sheet, the pre-split numbers are unadjusted and the total is wrong. You are right that the account shares total WITHIN Quicken is correct.
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    I'm not sure exactly what you're exporting to Excel; what report are you using and what totals are you looking at?
    But yes, you're exporting a fruit salad of pre-split and post-split information with some reports and while all dollar numbers - buys and sells, proceeds and costs - are correct share numbers might not be.
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    Thanks again, Tom. I used Report, Investment Transactions, Customize, Earliest to date, which I then exported to Excel Workbook. In Excel, I applied the stock splits. I wanted the cost basis of each lot to share with the new broker. Is there a better way?
    Next issue is perhaps more difficult - the merger (Maytag, Whirlpool) with cash to boot and sale of fractional shares of the new stock. Can you walk me through that? Exchange ratio 0.1193 and $10.50 cash to boot per original share. The cash to boot apparently reduces the cost basis. I ran the numbers through the costbasis.com website (tools, cash to boot) so I think I know what the result should be. Any help would be greatly appreciated.
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited May 2018
    If you have all the transactions properly created in Quicken through a specific date, Quicken will show you the proper cost basis as of that date, either as a total for the holding, on a lot basis, or on a per share basis.  There should not be any need to export to Excel to get the data to pass on to your broker.  (Exceptions may apply.)

    As for the Cash-to-boot acquisition, I typically recommend a sequence of 
    Sell Shares (for the correct price),
    Buy Shares,
    Remove Shares, and then 
    Add Shares.  

    When that is complete, then sell any remaining fractional shares for cash-in-lieu received.

    See https://getsatisfaction.com/quickencommunity/topics/how-do-i-record-the-analog-devices-inc-cash-and-... for an example with further explanations.  

    I have found costbasis.com to be an excellent tool.  
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    Thanks again, Tom. I used Report, Investment Transactions, Customize, Earliest to date, which I then exported to Excel Workbook. In Excel, I applied the stock splits. I wanted the cost basis of each lot to share with the new broker. Is there a better way?
    Next issue is perhaps more difficult - the merger (Maytag, Whirlpool) with cash to boot and sale of fractional shares of the new stock. Can you walk me through that? Exchange ratio 0.1193 and $10.50 cash to boot per original share. The cash to boot apparently reduces the cost basis. I ran the numbers through the costbasis.com website (tools, cash to boot) so I think I know what the result should be. Any help would be greatly appreciated.

    The transactions report won't really "present to you" the various lots and their bases, though with enough work and an understanding of what method was used for pricing any sales you could derive that information.
    Go to Investing > Portfolio View and customize on of the views to only the Account and Securities you want to focus on, click on "Expand All" to get each lot listed out and then click on File > Print Portfolio and select click on "Export" and select "tab-deliminated."  That will get you a file you can import directly into Excel using the import prompts.
    Assuming the Maytag/Whirlpool was a classic "stock plus cash" acquisition requiring a lot-by-lot calculation of gain or loss, a limitation of gain to the lesser of gain calculated on the "proceeds" of cash+FMV of stock or cash, not allowing loss to be recognized for tax purposes and with basis and holding period carried over to the new stock, then the first thing to do is to make all your calculations outside of Quicken to determine the gain (if any) to be recognized and what the new basis of each lot is. 

    I've typically done a "Remove" of all the old stock, followed by an "Add" of the new stock at a DERIVED basis that gets the gain (if any) reported correctly.  (You might need two Adds if both ST and LT gain are involved.  That gets your cash correct and your reported gain correct.  That's then followed by a bunch of Adds to get the new stock lots correctly stated as to holding period and to cost.  Then sale the fractional share at the actual gain or loss.
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    Dear Tom & Q,
    That sounds like a lot of work. How about using a stock split as the exchange ratio and adjusting the cost basis somehow to reflect the cash to boot, change the name and sell the fractional shares?
    I ask now because I will be entering AT&T and ALL the Baby Bells next. I cannot imagine entering, removing, etc. Please tell me there is a better way.
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    First, let me correct a typo and make an addition to the first sentence of my "process": I remove the old stock and then add the OLD stock back with the derived basis. Then sell the old stock for the cash to get the gain and cash correctly stated.



    If the process is onerous because of lots of divided reinvestments then probably compressing all the dividends for a year into one "lot" wouldn't materially affect the final answer and would reduce the effort somewhat.



    And, is this effort even necessary? If the stock was held in a taxable account then the correct calculations should have been made over a decade ago. If they weren't then it's too late to change things and you might as well simply carry forward whatever basis was implied in the new stock based on the actual tax reporting for the sale. If the stock was held in a tax deferred account then you generally don't even have to worry about individual stck's basis.
  • D Bunker
    D Bunker Member ✭✭
    edited October 2018
    That old information was lost! I have only the transfer agent's information to work with concerning shares in DRIP. The goal is still a reasonable cost basis of current holdings.
    A test run using a stock split as the exchange ratio, adjusting the cost basis to reflect the cash to boot (using Return of Capital), changing the name and selling the fractional shares appears to work miracles. Your thoughts?
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    That old information was lost! I have only the transfer agent's information to work with concerning shares in DRIP. The goal is still a reasonable cost basis of current holdings.
    A test run using a stock split as the exchange ratio, adjusting the cost basis to reflect the cash to boot (using Return of Capital), changing the name and selling the fractional shares appears to work miracles. Your thoughts?

    I can't see how that possibly work.  A "stock split", (followed, I guess, by a removal of the old stock?), would allocate the basis between the old and new stock, and that's not the starting point of the exercise.  (If you could tell Quicken to allocate ALL of the basis to the new stock that might get you to the correct starting point, but I don't think you can do that.)
    But it's not the case that you adjust the basis for each lot of "old" stock by simply subtracting the cash.  The correct formula is
    new basis = old basis  - cash + gain
    so unless every lot was a loss lot you'd end up misstating the basis for all of the gain lots.
  • Rocket J Squirrel
    Rocket J Squirrel SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    That old information was lost! I have only the transfer agent's information to work with concerning shares in DRIP. The goal is still a reasonable cost basis of current holdings.
    A test run using a stock split as the exchange ratio, adjusting the cost basis to reflect the cash to boot (using Return of Capital), changing the name and selling the fractional shares appears to work miracles. Your thoughts?

    Excuse my ignorance, but what does the word "boot" mean in this context?

    Quicken user since version 2 for DOS, now using QWin Biz & Personal Subscription (US) on Win10 Pro.

  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    That old information was lost! I have only the transfer agent's information to work with concerning shares in DRIP. The goal is still a reasonable cost basis of current holdings.
    A test run using a stock split as the exchange ratio, adjusting the cost basis to reflect the cash to boot (using Return of Capital), changing the name and selling the fractional shares appears to work miracles. Your thoughts?

    Generally mergers between companies can involve all stock, all cash, or some stock and some case.  The latter is typically called a stock merger "with cash to boot."  More broadly, "boot" can refer to most anything in addition to company stock.
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited May 2018
    AT&T breakup!  I would not dream of following or re-creating that in Quicken at this stage after the fact.  See: https://www.theverge.com/2016/10/24/13389592/att-time-warner-merger-breakup-bell-system-chart for all the players that could be involved.  What a mess.  

    Or this article:  https://bimajority.org/~wollman/t.html (I am not vouching for its accuracy).

    There is a site out there somewhere that does present some of the associated details, though I am not turning it up at this point.  

    costbasis.com is a potential resource in that regard that I have found to be reliable.  

    So I would use 'outside' (of Quicken) resources to get to whatever quality estimate you could of the basis for the current holding, and then enter into Quicken that current summary values probably through Add Shares transactions.
  • Rocket J Squirrel
    Rocket J Squirrel SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    That old information was lost! I have only the transfer agent's information to work with concerning shares in DRIP. The goal is still a reasonable cost basis of current holdings.
    A test run using a stock split as the exchange ratio, adjusting the cost basis to reflect the cash to boot (using Return of Capital), changing the name and selling the fractional shares appears to work miracles. Your thoughts?

    Thanks. So is "cash to boot" pretty much the same as "cash in lieu of fractional shares"?

    It reminds me of the old saying "Cats are cute, and fun, to boot." which changes meaning if you omit the second comma.

    Quicken user since version 2 for DOS, now using QWin Biz & Personal Subscription (US) on Win10 Pro.

  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    Let me suggest this. How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    If not, I will "collapse" several years of dividend reinvestments to a single lot. There are 21 years & 2 splits.
    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? Or import from Excel where it is easy (for me) to merger years?
    I see that Quicken is a great program to follow the market value of a portfolio, sell lots, etc. but not so convenient for complex mergers.
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    Let me suggest this. How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    If not, I will "collapse" several years of dividend reinvestments to a single lot. There are 21 years & 2 splits.
    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? Or import from Excel where it is easy (for me) to merger years?
    I see that Quicken is a great program to follow the market value of a portfolio, sell lots, etc. but not so convenient for complex mergers.

    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? 
    Sure.  Make a backup.  Run your trial.  If you don't like the results, restore the backup.  
    Or import from Excel where it is easy (for me) to merge years? 
    Not so easy.  There are external programs that will convert Excel data to a QIF file that can be imported, but those are less effective (IMO) for investment accounts than for cash accounts.  
    How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    You can certainly do whatever you like to get from wherever you are starting to wherever you are now.  I guess my question is: What exactly are you trying to identify?  

    In that second link I provided, the write started with 100 AT&T in 1970 and ended with nine securities plus cash some 44 years later.  But that seemed to assume none of the various securities were disposed of along the way nor additions, like reinvested dividends.  If NyNex or PacTel were sold off soon after the 1984 breakup, that would change some part of the picture, but i am not sure which part.  

    OTOH, if you are trying to create in Quicken that you started with 100 shares of AT&T in 1970 with some basis and today you have 72.345 shares in 27 lots (due to DRP participation) with some other basis ... yes, you can fake in a stock split and fake in a  RtrnCap but I am not sure what that accomplishes.  I would be more inclined to keep it simpler with Remove Shares (of the old) and Add Shares (with the new, in however many lots you want).  But somehow, you have to know the basis for those Added shares - for either the Shares Added transaction or the RtrnCap transaction you suggest.  By any measure, the basis is not as simple as original basis less "Cash to boot - Gain".  (Indeed, I am not clear what you mean by that "Cash to boot - Gain" phrase, anyway.)
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    D Bunker said:

    Let me suggest this. How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    If not, I will "collapse" several years of dividend reinvestments to a single lot. There are 21 years & 2 splits.
    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? Or import from Excel where it is easy (for me) to merger years?
    I see that Quicken is a great program to follow the market value of a portfolio, sell lots, etc. but not so convenient for complex mergers.

    Goal #1: Establish cost basis for Whirlpool Corp from incomplete records from 1985 forward thru 2 splits and multiple dividend reinvestments, merger with Maytag with Cash to Boot in 2006 and ongoing DRP. Account now with broker w/o a cost basis. Tools available: Quicken, Excel, internet.
    Goal #2: Establish cost basis for Baby Bells with equally sketchy records for AT&T from 1978 forward.
    Comment: As per Tom Young, the new basis = old basis - cash + gain.
    Restated, New CB = Old CB - (Cash to Boot - Gain). Having verified that for all lots,  there was no loss and that the Gain was always less than the Cash to Boot, I believe that it is truly that simple in this case.
    Gain defined as: Mrk value new shares + Cash to Boot - original cost basis.
    Having run the numbers thru costbasis.com, I know the cost basis for Whirlpool shares post merger. I was trying to preserve the lots, arrive at the correct cost basis in Quicken and not remove and re-enter all those lots. Naive or optimistic, perhaps?
    I will backup the account, run the trial and see if the final result (Whirlpool CB) matches the answer from costbasis.com.
  • Tom Young
    Tom Young SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    Let me suggest this. How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    If not, I will "collapse" several years of dividend reinvestments to a single lot. There are 21 years & 2 splits.
    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? Or import from Excel where it is easy (for me) to merger years?
    I see that Quicken is a great program to follow the market value of a portfolio, sell lots, etc. but not so convenient for complex mergers.

    @ D Bunker

    "Comment: As per Tom Young, the new basis =
    old basis - cash + gain"

    As I had only responded to your Maytag/Whirlpool deal and had assumed it was a "classic" stock + boot transaction needing some specialized accounting for tax purposes, my response pertained to that deal only

    I know that that "old" AT&T and all the Baby Bells went through an incredible number of splits, spinoffs, splitoffs and mergers but (luckily) I never owned any of the stock so I'm unfamiliar with the accounting needed for all that.  Each deal, even deals that on their surface are exactly the same, has its own logic for gain/loss recognition and determination of basis.
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    Back to the drawing board. The new share numbers are all correct, of course Quicken used the exchange ratio correctly to calculate the number of new Whirlpool shares. However, I checked one purchase made in 1988, after the splits, and the cost basis was wrong. Is it now easier to adjust each lot to the correct basis for each lot given that the number of shares is entered with the correct date? I think that this may be a little easier than removing all shares and entering the new ones. However, the jury is still out on that at this time. . . .
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018
    D Bunker said:

    Let me suggest this. How about using a stock split as the exchange ratio and adjusting the cost basis with a Return of Capital equal to (Cash to Boot - Gain), change the name and sell the fractional shares? In the aggregate, this is appears to work, I will have to look at individual lots.
    If not, I will "collapse" several years of dividend reinvestments to a single lot. There are 21 years & 2 splits.
    Is there an easy way to run a trial and return to a previous time point if the trial "fails"? Or import from Excel where it is easy (for me) to merger years?
    I see that Quicken is a great program to follow the market value of a portfolio, sell lots, etc. but not so convenient for complex mergers.

    I agree 100% and do not want to put words in your mouth. I think that calculation is correct in the Maytag / Whirlpool situation. I humbly recognize your expertise and thank you and Q for your kind efforts.
    Given the size of the AT&T cost basis task, I am looking for the easiest way forward and was experimenting with Quicken and Maytag / Whirlpool. I helped a family member with Quicken 2004 and bought Q18 hoping to simplify the task. Alas, it will not be simple.
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited May 2018
    Goal #1: Establish cost basis for Whirlpool Corp from incomplete records from 1985 forward thru 2 splits and multiple dividend reinvestments, merger with Maytag with Cash to Boot in 2006 and ongoing DRP. Account now with broker w/o a cost basis. Tools available: Quicken, Excel, internet.
    Whirlpool bought Maytag closing the deal 3/31/2006.  The Maytag shareholder received for each share of Maytag, 0.1193 shares of Whirlpool (WHR) and $10.50 in cash.  The next trading day (4/3/06) WHR opened at 92.60, went as high as 93.45 and as low as 91.65, before closing at 92.28.

    For these types of deals, it is necessary (when being rigorous) to determine the fair market value that the Maytag owner received.  Whirlpool likely put out a form 8937 at the time with their opinion, but ultimately, it is the shareholder's responsibility.  Some might choose the opening value or the closing value or some average or weighted average value.  For going forward here, I am going to use the average of the opening and closing values or $92.44 as the value of one share of Whirlpool at the time.  So the receipt of 0.1193 shares @ 92.44 ($11.028) plus the cash ($10.50) meant each share of Maytag was traded for $21.528 in other value (Whirlpool shares and cash).

    Step 2 is determining how much, if any, of the $10.50 should be (have been) treated as capital gains.  There are three possibilities - All the 10.50 is capital gains, none of the 10.50 is capital gains, and in-between those two.  The demarcation for those three cases is based on your basis in the Maytag shares compared to the two values of $11.028 and $21.528




    1. Case 1: 
      Your shares were bought cheaply; Basis < Value of Shares Received =
      11.028 per share originally held.  




      Your cap gain is limited
      by the amount of cash you received.  








      Sell all shares of this
      lot for (basis + cash received) adding cash to your account.  Buy proper number of new
      shares for lesser amount leaving cash received in the account.  Your total basis in new
      shares for this lot will equal your prior basis for this lot.
        The entire cash received is capital gains.




      Remove shares just
      bought, Add same number of shares for same cost but with original lot date as
      acquisition date.






    2. Case 2: 
      Your shares were 'expensive'; Basis > Total Value Received =
      21.528 per share originally held.  








      You have no current cap
      gains liability with respect to this lot.  











      Sell all shares of this
      lot at basis value adding cash to account.  Buy proper number of new
      shares for lesser amount leaving cash received in the account as the cash you
      received (purchase price is old basis less cash received).  Your total basis for
      this lot will be less than your prior basis for this lot; you have no cap
      gains now, but will have greater cap gains in the future.
        





      Remove shares just
      bought, Add same number of shares for same cost but with original lot date as
      acquisition date.










    3. Case In-between: 
      Your shares were bought in-between those two price points.








      Your have a current cap
      gain liability, but it is less than cash received.  






      Sell all shares of this
      lot for value received ($21.528/share) adding cash to your account.







      Buy proper number of new
      shares for lesser amount leaving cash received in the account.  




      Your total basis for
      this lot will be less than your prior basis for this lot. 
      Only part of the cash received is capital
      gains.



      Remove shares just
      bought, Add same number of shares for same cost but with original lot date as
      acquisition date.

    So as Tom qualified himself, it is not so simple as suggested.  Depending on the price you paid for the Maytag shares, that cash received might or might not have adjusted into the cost basis for the Whirlpool shares.  I've tried to capture that logic in the following snip from a spreadsheet showing and example for a lot from each category.

    image
    That is specific to this merger with my assumption on the value to the WHR shares received (92.44).

    Rigorously, you would treat each lot independently, though from a practical sense, you could probably group like sets of lots.  

    Still being rigorous, one would then sell the fractional share (0.335 in my data example) for cash-in-lieu received.  Since you likely don't have that Cash-in-lieu amount, you could estimate it off the $92-range trading at the time.  That sale would further adjust the basis going forward from there.  

    Now to be somewhat fair, note the case 2 citation.  In that case, your Maytag shares were expensive, the cash received was NOT subject to capital gains at the time, and the basis for the Whirlpool shares was adjusted downward by that cash-to-boot received.  I would suggest that if you did not report (all or some of) the cash as capital gains at the time, the IRS might look kindly on you adjusting the basis downward now.  (Note I am not a tax adviser and have NO credentials to cause anyone to listen to what I say or suggest.)

    So that process would be applied to all shares owned as of 3/31/06.  Later splits and reinvestments would follow along accordingly.  

    As to whether you have to proceed through this merger maintaining individual lots, I'll choose to be quiet - noting my lack of credentials.     

    Enough for this aspect.  That is how one would proceed rigorously.  Proceed as you deem appropriate. 
  • D Bunker
    D Bunker Member ✭✭
    edited May 2018

    Concerning the sale of fractional shares, the only guidance
    I see from the IRS is in Pub 550, page 22. My reading of their exercise is 1
    share generates 0.05 shares (10/200) as a dividend which is sold. Try as I
    might, I cannot duplicate their result. If I use FIFO, I sell 0.05 shares for
    $10 with cost basis of $5, so a gain of $5. If I designate the new lot, I sell
    0.05 shares for $10 with cost basis of $10, so a gain of $0. If I cost average,
    I sell 0.05 shares for $10 at cost basis of $5.24 (0.05 x 110/1.05), and
    declare a capital gain of $4.76 (curiously backwards from the IRS result).

    If FIFO is the default (and averaging reserved for mutual
    funds), what are they saying here?
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited May 2018
    D Bunker said:

    Concerning the sale of fractional shares, the only guidance
    I see from the IRS is in Pub 550, page 22. My reading of their exercise is 1
    share generates 0.05 shares (10/200) as a dividend which is sold. Try as I
    might, I cannot duplicate their result. If I use FIFO, I sell 0.05 shares for
    $10 with cost basis of $5, so a gain of $5. If I designate the new lot, I sell
    0.05 shares for $10 with cost basis of $10, so a gain of $0. If I cost average,
    I sell 0.05 shares for $10 at cost basis of $5.24 (0.05 x 110/1.05), and
    declare a capital gain of $4.76 (curiously backwards from the IRS result).

    If FIFO is the default (and averaging reserved for mutual
    funds), what are they saying here?
    I'll try to rephrase the IRS example:
    You started with 1 shares, basis = $100
    Company declared a stock dividend of 5% meaning your 1 share became 1.05 shares still with the $100 total basis
    The fractional 0.05 share gets sold rather than distributed to you at the $200/share rate netting you $10.
    Your basis for that 0.05 share that was sold is 0.05 / 1.05 * $100 = $4.76
    Your capital gain is then $10 - $4.76 = $5.24

    In Quicken, you would have
    1) the original buy of 1 share @ $100 / share = $100
    2) A stock split -- new shares = 1.05; old shares = 1
    3) A Sell Shares -- 0.05 shares for $200/share = $10

    Though I have not run that through, I am confident it comes out right.

    With respect to multiple lots (not applicable in the example), I have never seen any guidance on where the fractional shares should be or need to be taken from.  I have always assumed it to be "investor's choice".  So even though you might have 10 lots that might contribute to the total net fractional share to be 'sold', you can choose which lot to actually use for the sale (though Quicken would default to the FIFO if you did not specify).
This discussion has been closed.