How do I calculate the ROI for multiple stock transactions?

[Marquis76]

Hi,


I need some help with Excel to calculate ROI for this situation:


[TABLE="width: 500"]
<tbody>[TR]
[TD]date
[/TD]
[TD]quantity
[/TD]
[TD]price bought
[/TD]
[TD]quantity
[/TD]
[TD]price sold
[/TD]
[TD]ROI
[/TD]
[/TR]
[TR]
[TD]2015-09-05
[/TD]
[TD]4,000
[/TD]
[TD] 3.16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015-11-05
[/TD]
[TD]1,500
[/TD]
[TD]3.56
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2016-07-08
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000
[/TD]
[TD]4.13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2016-11-10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000
[/TD]
[TD]4.51
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-01-05
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1,000
[/TD]
[TD]4.12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm looking to calculate the ROI of individual transactions, time sensitive. So I want the ROI of the first 2,000 stocks sold at 4.13 to be calculated using the 4,000 quantity bought at 3.16. The second tranche of 2,000 sold should also be based (deducted) from the same original 4,000 bought on 2015-09-05. However, the ROI for the tranche of 1,000 sold at 4.12 should be deducted from the second pool of purchases (1,500 on 2015-11-05) at 3.56. And so on and so forth. What formula would allow me to correctly calculate the ROI for each transaction by pegging the shares sold to the right pool of shares bought? Basically, I don't want rolling ROI (XIRR) because it will not give me the ROI per individual transactions.

Thanks a million for your help!!



[TABLE="class: yklcuq-19 cUmOil"]
<thead>[TR="class: yklcuq-20 dqbluc"]
[TH="class: yklcuq-25 jpUfLs"][/TH]
[TH="class: yklcuq-25 jpUfLs"][/TH]
[TH="class: yklcuq-25 jpUfLs"][/TH]
[TH="class: yklcuq-25 jpUfLs"][/TH]
[/TR]
</thead><tbody>[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
[TR="class: yklcuq-20 dqbluc"]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[TD="class: yklcuq-21 guVwEK"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Fergus]

Hello

You need to set up your data in a way that you described. Split the first purchase into two lots of 2000 - show each on different rows. Have the sale date and value on the same row using the FIFO method you described. With a 1 to 1 relationship calculating the ROI for each line will be easy.

 

[Marquis76]

Hello

You need to set up your data in a way that you described. Split the first purchase into two lots of 2000 - show each on different rows. Have the sale date and value on the same row using the FIFO method you described. With a 1 to 1 relationship calculating the ROI for each line will be easy.

Thanks Andrew. Well this is the issue. I was looking for a way to automate these operations so that I wouldn't have to manually attribute and match individual transactions to each other. There has to be a way in Excel to subtract until the cell reaches zero, then move on to next cell, and calculate ROI automatically by accurately matching the tranche sold to the right pool of shares purchased.

Any possibility?

Thanks again.

 

[Andrew Fergus]

This can be done but the data still needs to be set up in a way that has a 1:1 relationship given there is no native excel function. I can't get my html displayer to work so bear with me. The columns to the right of the words "Sales->" allocate the sale quantities by date to each of the purchases. In my opinion it is much easier to split the purchases but this seems to work.

I got it to work by setting up some additional columns like this:

[TABLE="width: 937"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Buy Qty[/TD]
[TD]Buy Price[/TD]
[TD]Sell Qty[/TD]
[TD]Sell Price[/TD]
[TD]Proceeds[/TD]
[TD]Cost[/TD]
[TD]Return[/TD]
[TD]ROI[/TD]
[TD]Sales ->[/TD]
[TD]8-Jul-16[/TD]
[TD]10-Nov-16[/TD]
[TD]5-Jan-17[/TD]
[TD]n/a[/TD]
[/TR]
[TR]
[TD]5-Sep-15[/TD]
[TD]3,500[/TD]
[TD]$3.16[/TD]
[TD][/TD]
[TD][/TD]
[TD]$0[/TD]
[TD][/TD]
[TD]$0[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]1,500[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5-Nov-15[/TD]
[TD]1,500[/TD]
[TD]$3.56[/TD]
[TD][/TD]
[TD][/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]1,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8-Jul-16[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]$4.13[/TD]
[TD]$8,260[/TD]
[TD]$6,320[/TD]
[TD]$1,940[/TD]
[TD]31%[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-Nov-16[/TD]
[TD][/TD]
[TD][/TD]
[TD]2,000[/TD]
[TD]$4.51[/TD]
[TD]$9,020[/TD]
[TD]$6,520[/TD]
[TD]$2,500[/TD]
[TD]38%[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5-Jan-17[/TD]
[TD][/TD]
[TD][/TD]
[TD]1,000[/TD]
[TD]$4.12[/TD]
[TD]$4,120[/TD]
[TD]$3,560[/TD]
[TD]$560[/TD]
[TD]16%[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in Cell F2 (Proceeds): =D2*E2 (copy down)

Formula in Cell G3(Cost): =IFERROR(SUMPRODUCT(OFFSET($J$1,1,MATCH(A3,$K$1:$N$1,0),COUNT(A$2:A3),1),C$2:C3),0) (copy down)
Notice the formula above only looks at K1:N1 - if you copy the dates across to more columns beyond N (see below) then N needs to change in this formula.

Formula in Cell H2 (Return): =F3-G3 (copy down)

Formula in Cell I3 (ROI): =IFERROR(H3/G3,"") (copy down)

Column J is blank except for the heading

Array formula in cell K1: =MIN(IF(D:D>0,A:A)) {use ctrl + shift + enter to activate array formula}

Array formula in cell L1: <count($d:$d),min(if($d:$d>=IF(COUNT($K1:K1)<count($d:$d),min(if($d:$d>0,IF($A:$A>K1,$A:$A))),"n/a") (copy across as far as is needed - I copied to column N, use ctrl+shift+enter, if you copy over more columns then the formula in G3 (and copied across) needs to change

Formula in cell K2: =IF(ISNUMBER(K$1),MIN($B2-SUM($J2:J2),INDEX($D:$D,MATCH(K$1,$A:$A,0))),"") (copy across to column N)

Formula in cell K3: =IF(ISNUMBER(K$1),MIN($B3-SUM($J3:J3),INDEX($D:$D,MATCH(K$1,$A:$A,0))-SUM(K$2:K2)),"") (copy down as far as your data and across to column N)

P.S. I forgot to mention this assumes the first 5 columns are in A to E, the heading is in row 1 and the first data row is row 2.</count($d:$d),min(if($d:$d></count($d:$d),min(if($d:$d>

 

[Marquis76]

Thanks Andrew! I'll let you know how it goes.

 

[Marquis76]

HI Andrew,

A million thanks for your help again. I tried it but L1 doesn't seem to work. I get an error Err:508. Any idea what is going on?

Thanks!

 

[Andrew Fergus]

Hello

It appears something was corrupted in my reply. I have added spaces to the following formulas to ensure they are not corrupted in this forum.

Array formula in L1 should be (copy across):
=IF(COUNT($K1:K1) < COUNT($D:$D), MIN(IF($D:$D > 0 ,IF($A:$A >K1, $A:$A))), "n/a")

Make sure you use Ctrl + Shift + Enter.

Formula in K2 should be: (copy across)
<count($d:$d),min(if($d:$d>=IF(ISNUMBER(K$1), MIN($B2 - SUM($J2:J2), INDEX($D:$D, MATCH(K$1, $A:$A, 0))),"")

Formula in K3 (copy down and across):
=IF(ISNUMBER(K$1), MIN($B3 - SUM($J3:J3), INDEX($D:$D, MATCH(K$1, $A:$A, 0)) - SUM(K$2:K2)), "")

Andrew</count($d:$d),min(if($d:$d>