Can anyone share with me a Spreadsheet for 1099B reported transactions for EMC/DELL merger?

[jellis002]

Received a 1099B reporting two transactions resulting from the EMC/DELL merger.]

1) 9/7./16...200.00 shares of EMC... Proceeds $2405.00."various" date acquired...no cost basis

2) 9/20//16....0.146 shares  of DVMT sold...proceeds 7.07....N/A date acquired...no cost basis

I understand that to derive the values needed for a Form 8949 in a tax return it is very complicated and requires a spreadsheet to do it.  Does anyone have such a spreadsheet that they will share with me??

EDIT/CORRECTION:

I am attaching a spreadsheet in which I have tried to work out the total cost basis and total proceeds of the EMC/DELL merger.  Please tell me if you think it is correct or where I went wrong.

[TomYoung]

The derived basis to use depends heavily on the ACTUAL basis of the EMC shares tendered.  Do you know your actual basis?

[jellis002]

I only had one lot of 100 EMC shares (long term) at time of merger, whose basis was $1,104.95 or $11.0495 per share.   Can you advise me what basis to use for (1) the 86.77% of shares in the Section 351 exchange, (2)  the 13.23% of shares in the redemption, and (3) the partial DVMT share sold later on 9/20/16?   I think I have already figured out the "proceeds" for these 3 cases per Form 8937 as (1) $2,206.56 LT, (2) $41.33 LT, and (3) $7.07 ST.  Do those sound correct??
If you can give me cost basis for each of these 3 cases I can subtract them from each of the "proceeds" and then generate the Form 8949 for the 1099B.

[jellis002]

Correction to "proceeds": (1) $2,206.56 LT, (2) 388.00 LT, (3) $7.07 ST

[TomYoung]

Did you read https://ttlc.intuit.com/questions/3405510 ?

Your proceeds for the Sec 351 calc is 100 x $20.17 (cash) + 100 x .11146 x $47.20 (stock) for total proceeds of $2,543.09.  You subtract basis of $1,104.95 x .8677 = $958.77 for a "real" gain of $1,584.33.  Since that amount is less that the cash received that's the gain you report.  Most brokers seem to report "proceeds" for these transactions as only the cash received or $2,017.00 in this case so your DERIVED basis is $432.67 to come up with the $1,584.33 gain.

Your proceeds for the Redemption calc is $3.88 per share of $388.00.  You subtract from that  $1,104.95 x .1323 (remaining basis) for a gain of $241.82.

You can combine these and report proceeds of $2,405.00 and basis of $578.86.

Your basis in the 11.146 shares received is $958.765 - $2,017.00 + $1,584.33 = $526.09 so you partial share has a basis of $6.89

Tom Young

[jellis002]

Thanks Tom, but in the 351 proceeds calc you used $24.05 as the value of each .11146 share of DVMT stock received. whereas the Form 8937 Line 16 says the value is $5.26.  There was also a math error in your calc on the proceeds for Sec 351.  Therefore, I think the following corrections are required:  
       100 x $20.17+100 x .11146 x $5.66 = $2,075.63.... not $2,543.09 as you listed.
This correction ripples through your other calcs as follows for new numbers:
   1) Real gain= $2,075.63 - $1,104.95 x .8677 = $1.116.86...not the $1,584.33 you listed.
   2) Redemption Gain =  $388.00 - $1,104.95 x .1323 = $241.82..as you also listed.
   3  11.146 DVMT stock basis = $958.765 - $2,017.00 + $1,116.86 = $58.628...not the $526.09 you listed.
   4) Therefore, Partial share DVMT stock basis = 0.146 X $58.628 / 11.146 = $0.77....not the $6.89 you listed.
Do you concur with these corrections?  If so I am ready to finish my tax return.  If not, please tell me where I went wrong.
Thanks so much for your help on this, Tom.  I hope it will also help others who have to report EMC/DELL merger on their taxes.  BTW i corrected my spreadsheet to use your calculation formulas so if anyone wants it  I can provide it to them to use on their own case.
John Ellis

[TomYoung]

@jellis00

I'm away from my computer but you are correct. Obviously I picked up the wrong number - total cash received - and typed that in.  But the proceeds number is CORRECT.  The $5.26 is per FRACTION of a share so proceeds = 100 x $20.17 + 100 x $5.26 = $2,543.