# Help Calculating P/L based on Individual Symbols With FIFO calculations

#### ONP Nino

##### New Member
Hello everyone,

Just when I thought I had it all worked out With FIFO and Averages after many days, trials and errors,
I started inputting different Symbols and it all Failed on me.

The current FIFO Module calculations works perfectly if I only use same symbol in Column "I" But not when I input Different Symbols.
I Have highlighted in Yellow the should be result, in Orange the Bad result and in Green the Good result.

How can I Calculate The P/L in Column "O" and Average-FIFO in Column "U" based only on Individual Symbol transactions?
I have attempted to find a solution with functions such as Sumproduct, Sumifs and index/match but to no avail.

I believe the Module code needs modifications to accept different symbols
Of perhaps have been writing the attempted formulas incorrectly.

All would be perfect If I could Have the FIFO P/L and FIFO Averages corrected.

Thank you for the help

Cheers
ONP

This is the Code written in the Module
VBA Code:
``````Function CostOfShares(PresentShares As Range, DescriptionRng As Range, SharesRng As Range, DebitRng As Range)

Dim T As Long, Temp As Long, Amt As Double

Temp = PresentShares.Value

For T = DescriptionRng.Rows.Count To 1 Step -1
If DescriptionRng.Cells(T, 1) = "BUY" Then          'And SharesRng.Cells(T, 1) < Temp
If Temp >= SharesRng.Cells(T, 1) Then
Amt = Amt + DebitRng.Cells(T, 1)
Temp = Temp - SharesRng.Cells(T, 1)
Else
Amt = Amt + (Temp * (DebitRng.Cells(T, 1) / SharesRng.Cells(T, 1)))
Exit For
End If
End If

Next T
CostOfShares = Amt

End Function``````

Onp MrExcel Forum Symbol FIFO Sample.xlsm
GHIJKLMNOPQRSTU
1BUY And SELL Is CASE SENSITIVE
2
3
4
5
6Doesn’t work well if Different symbols are usedFIFO
7Module FormulaAverage FIFO
8DateDescriptionSymbolSharesPriceDebitCreditBal.P/L
1212/30/2020SELLAGNC10014.0600.001,406.00200-118.002424.0012.1200
131/6/2021SELLNLY5011.0000.00550.0015050.001924.0012.8267
1510/8/2020SELLAGNC10013.1700.001,317.00150105.00-1072058.0013.7200
17
18
19
20
21
22
23GOODWorks Well if all Same SymbolFIFO
24Module FormulaAverage FIFO
25DateDescriptionSymbolSharesPriceDebitCreditBal.P/L
2912/30/2020SELLAGNC10014.0600.001,406.00200-118.002832.0014.1600
301/6/2021SELLAGNC5013.7900.00689.50150-14.502128.0014.1867
3210/8/2020SELLAGNC10013.1700.001,317.00150-99.002058.0013.7200
34
35
36
Sheet1
Cell Formulas
RangeFormula
M26:M33,M9:M16M9=IF(H9="SELL",J9*K9,0)
N9,N26N9=J9
O9:O16O9=T9+SUM(M\$9:M9)-SUM(L\$9:L9)-SUM(O\$8:O8)
T9:T16T9=CostOfShares(N9,\$H\$9:\$H9,\$J\$9:\$J9,\$L\$9:\$L9)
U26:U33,U9:U16U9=T9/N9
O26:O33O26=T26+SUM(M\$26:M26)-SUM(L\$26:L26)-SUM(O\$25:O25)
T26:T33T26=CostOfShares(N26,\$H\$26:\$H26,\$J\$26:\$J26,\$L\$26:\$L26)
Cells with Conditional Formatting
CellConditionCell FormatStop If True

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Replies
6
Views
73
Replies
15
Views
79
Replies
2
Views
893
Replies
1
Views
83
Replies
5
Views
89

1,127,005
Messages
5,622,136
Members
415,879
Latest member
drumsmasher

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back