Calculate Avg Price, Realized gain & Unrealized gain via UDF using FIFO method

This post is in continuation of my earlier post where I have already got assistance from Tom Sharpe on how to calculate the Average Price of a stock using the FIFO method as per transaction table & UDF given below. In order to add more functionality to it, I was trying hard to calculate my profit/loss by tweaking the UDF but I was unsuccessful therefore I started a new thread for this.

Profit and Loss is divided into two parts. One is the profit/loss I made by sellling few stocks which will be referred to as Realized Gain and the second one is the Gain which is available in the stock exchange for my unsold stocks which will be called as Unrealized Gain. Both can go into negative if there is a loss instead of profit.

Calculating Unrealised Gain is fairly simple because of the solution which is already provided and the answer to it is, Remaining Qty x Avg Price. Referring the table, 150 x 10 100 = 1 515 000 (I think that is the way it should be calculated - correct me if I am wrong). But calculating Realized Gain is the challenge that I am facing. As per the table, the Realized gain works out to -7 500 which is a loss that is calculated as (Sold Price - First Price) x Sold Quantity (hope mathematics behind this logic is also correct). Plus I am facing even more difficulty when the number of transaction increases.

In short, I was looking forward for having 3 things. The Invested Avg Price (which the UDF is already giving), Unrealized profit (which can be calculated based on the UDF). Need to know how to calculate Realised Profit and if all three things can be returned using the same UDF by adding a parameter in the formula.

Here is the table

Below is the Explanation

• 1st order: Quantity = 225 | Price = Rs. 10 000.00

• 2nd order: Quantity = 75 | Price = Rs. 10 200.00

To calculate the average price, first calculate the value (Quantity x Price). Hence:

• 1st trade: Rs. 2 250 000.00

• 2nd trade: Rs. 765 000.00

• Total quantity = 300

• Total value for first two orders : Rs. 3 015 000.00

Now here is the catch. On 3-Jul, we placed a sell order 150 (out of 300) @ Price: Rs. 9 950.00

Now the FIFO (first in first out) method will be applied here. The method will check the first trade (on the buy-side). In this case, it is 225. 150 sold stocks will be deducted from 225 (first holding). The balance left of first holding which was 225 earlier will now be 225 - 150 = 75

After FIFO, the table gets converted like this after deducting the sell quantity. See the first Qty is changed from 225 to 75 because 150 stocks were sold and hence the Average Price is 10100 (which I am able to get it from the UDF below.

In case the sell quantity was more than 225, then it would have moved to the next trade to deduct the remaining quantity

Thanks to Tom Sharpe for this UDF which is called as =avgRate(qtyRange,rateRange)

The program uses a class BuySell so you need to create a class module, rename it to BuySell and include the lines

Public rate As Double
Public qty As Double

Here is the UDF

Function avgRate(qtyRange As Range, rateRange As Range)


    ' Create the queue

    Dim queue As Object
    Set queue = CreateObject("System.Collections.Queue") 'Create the Queue

    ' Declare some variables
    Dim bs As Object
    Dim qty As Double
    Dim rate As Double
    Dim qtySold As Double
    Dim qtyBought As Double
    Dim qtyRemaining As Double
    Dim rateBought As Double
    Dim i As Long
    Dim sumRate As Double, totQty As Double

    For i = 1 To qtyRange.Cells().Count

        qty = qtyRange.Cells(i).Value()
        rate = rateRange.Cells(i).Value()

        If qty > 0 Then

            'Buy
            Set bs = New BuySell

            bs.rate = rate
            bs.qty = qty

            queue.Enqueue bs

        Else

            'Sell
            qtyRemaining = -qty

            'Work through the 'buy' transactions in the queue starting at the oldest.

            While qtyRemaining > 0

                If qtyRemaining < queue.peek().qty Then

                'More than enough stocks in this 'buy' to cover the sale so just work out what's left

                    queue.peek().qty = queue.peek().qty - qtyRemaining
                    qtyRemaining = 0

                ElseIf qtyRemaining = queue.peek().qty Then

                'Exactly enough stocks in this 'buy' to cover the sale so remove from queue

                    Set bs = queue.dequeue()
                    qtyRemaining = 0

                Else

                'Not enough stocks in this 'buy' to cover the sale so remove from queue and reduce amount of sale remaining

                    Set bs = queue.dequeue()
                    qtyRemaining = qtyRemaining - bs.qty

                End If
            Wend
        End If
    Next i

    'Calculate average rate over remaining stocks

    sumRate = 0
    totQty = 0

    For Each bs In queue
        sumRate = sumRate + bs.qty * bs.rate
        totQty = totQty + bs.qty
    Next

    avgRate = sumRate / totQty

End Function

Algorithm:

If 'buy' transaction, just add to the queue.

If 'sell' transaction (negative quantity)

  Repeat 

    Take as much as possible from earliest transaction

    If more is required, look at next transaction

  until sell amount reduced to zero.

EDIT: Adding image of a larger sample that I tried with the provided solution

Need to get the gain (or loss) per sell transaction by using the existing code to remove earliest bought stocks from the queue, but add additional lines to work out:

gain = sale price * sale quantity - ∑ buy price * buy quantity

where the summation is over the different 'buy' transactions that satisfy the sale quantity, in chronological order.

I have now added the additional calculations suggested by OP and added some basic error handling (e.g. that the user doesn't try to sell more stocks than are available, making the queue become empty).

The UDF only accepts single-column arguments either as ranges or arrays.

UDF

Need a BuySell class as before:

Public rate As Double
Public qty As Double

---

Option Explicit

Function avgRate(qtyRange As Variant, rateRange As Variant, Optional calcNumber As Integer = 1)
 
    ' Create the queue
    
    Dim queue As Object
    Set queue = CreateObject("System.Collections.Queue")
    
    ' Declare some variables
    
    Dim bs As Object
    Dim qty As Double
    Dim rate As Double
    Dim qtySold As Double
    Dim qtyBought As Double
    Dim qtyRemaining As Double
    Dim rateBought As Double
    Dim i As Long
    Dim sumRate As Double, totalQty As Double
    Dim avRate As Double
    Dim saleValue As Double
    Dim purchaseValue As Double
    Dim gainForThisSale As Double
    Dim totalGain As Double
    Dim totalCost As Double
    Dim totalProfit As Double
    Dim overallCost As Double
    Dim tempQty() As Variant, workQty() As Variant, tempRate() As Variant, workRate() As Variant
    Dim nRows As Long
    Dim argType As Integer
    
    
    
    'Copy from range or array - assuming single column or single element in both cases.
    

    If TypeOf qtyRange Is Range Then
        If IsArray(qtyRange) Then
        ' column range
            argType = 1
        Else
        ' Single element range
            argType = 2
        End If
    Else
        If UBound(qtyRange, 1) > 1 Then
        ' Column array
            argType = 3
        Else
        ' Single element array
            argType = 4
        End If
    End If
    
    Debug.Print ("Argtype=" & argType)
        
     Select Case argType
        Case 1
            tempQty = qtyRange.Value
            tempRate = rateRange.Value
        Case 2
            nRows = 1
            ReDim workQty(1 To nRows)
            ReDim workRate(1 To nRows)
            workQty(1) = qtyRange.Value
            workRate(1) = rateRange.Value
        Case 3
             tempQty = qtyRange
             tempRate = rateRange
        Case 4
            nRows = 1
            ReDim workQty(1 To nRows)
            ReDim workRate(1 To nRows)
            workQty(1) = qtyRange(1)
            workRate(1) = rateRange(1)
    End Select
        
    If argType = 1 Or argType = 3 Then
            nRows = UBound(tempQty, 1)
    
            ReDim workQty(1 To nRows)
            ReDim workRate(1 To nRows)
            For i = 1 To nRows
               workQty(i) = tempQty(i, 1)
               workRate(i) = tempRate(i, 1)
            Next i
    End If
            

      ' Loop over rows
    
    totalProfit = 0
    overallCost = 0
    
    For i = 1 To nRows
   
        qty = workQty(i)
                
        ' Do nothing if qty is zero
        
        If qty = 0 Then GoTo Continue:
        
        rate = workRate(i)
        
        overallCost = overallCost + rate * qty
        
        If qty > 0 Then
        
            'Buy
            
            Set bs = New BuySell
            
            bs.rate = rate
            bs.qty = qty
            
            queue.Enqueue bs
        
            
        Else
        
            'Sell
        
            qtyRemaining = -qty
            
            'Code for realized Gain
            
            purchaseValue = 0
            saleValue = rate * qtyRemaining
            
            totalProfit = totalProfit + saleValue
            
            'Work through the 'buy' transactions in the queue starting at the oldest.
            
            While qtyRemaining > 0
            
                If queue.Count = 0 Then
                    avgRate = CVErr(xlErrNum)
                    Exit Function
                End If
            
                If qtyRemaining < queue.peek().qty Then
                
                'More than enough stocks in this 'buy' to cover the sale so just work out what's left
                
                    queue.peek().qty = queue.peek().qty - qtyRemaining
                    
                    'Code for realized gain
                
                    purchaseValue = purchaseValue + qtyRemaining * queue.peek().rate

                    
                    qtyRemaining = 0
                    
                    
                ElseIf qtyRemaining = queue.peek().qty Then
                
                'Exactly enough stocks in this 'buy' to cover the sale so remove from queue
                
                    Set bs = queue.dequeue()
                    qtyRemaining = 0
                    
                    'Code for realized gain
                
                    purchaseValue = purchaseValue + bs.qty * bs.rate

                    
                Else
                
                'Not enough stocks in this 'buy' to cover the sale so remove from queue and reduce amount of sale remaining
                
                    Set bs = queue.dequeue()
                    qtyRemaining = qtyRemaining - bs.qty
                    
                    'Code for realized gain
                
                    purchaseValue = purchaseValue + bs.qty * bs.rate
           
                    
                End If
                
            Wend
            
            'Code for realized gain
            
            gainForThisSale = saleValue - purchaseValue

            
            totalGain = totalGain + gainForThisSale
            
        End If
        
Continue:
        
    Next i
    
    'Calculate average rate
    
    If queue.Count = 0 Then
    
        avRate = 0
        
    Else

        totalCost = 0
        totalQty = 0
        
        For Each bs In queue
            totalCost = totalCost + bs.qty * bs.rate
            totalQty = totalQty + bs.qty
        Next
        
        avRate = totalCost / totalQty
        
    End If
    

    
    Select Case calcNumber
        Case 1
        'Average rate
            avgRate = avRate
        Case 2
        'Realized gain
            avgRate = totalGain
        Case 3
        'Invested
            avgRate = totalCost
        Case 4
        'Bal qty
            avgRate = totalQty
        Case 5
        'Net worth (total quantity times most recent rate)
            avgRate = totalQty * rate
        Case 6
        'Total profit (total sale amounts)
            avgRate = totalProfit
        Case 7
        'Unrealized gain
            avgRate = totalProfit - totalGain
        Case 8
        'Overall cost
            avgRate = overallCost
        Case Else
            avgRate = CVErr(xlErrNum)
    End Select
    
     
End Function

---

I have added a new version which tests for the first argument being an array or a range (and assumes the second argument is of the same type). OP has asked me to check for the case where it is a single element array or single-cell range as well. The main point of allowing arrays etc. is that you can have a function call like:

=avgRate(FILTER($C2:$C10,C2:C10=10),FILTER($A2:$A10,C2:C10=10),8)

or

=avgrate($C$2,$A$2,8)

to select (in this case) just the first row. This makes the UDF more versatile in situations where you may have stocks from more than one company and want to filter on the company.

Update

Just for interest, it would be possible with the new Lambda functions to implement a queue by formula, at the expense of allocating one row per individual share e.g.

=AVERAGE(REDUCE("",SEQUENCE(COUNT(B:B),1,2),
LAMBDA(q,i,IF(INDEX(A:A,i)="Buy",VSTACK(q,SEQUENCE(INDEX(B:B,i),1,INDEX(C:C,i),0)),
IF(INDEX(B:B,i)=COUNT(q),"",VSTACK("",DROP(q,INDEX(B:B,i)+1)))))))

applied to this test data:

Plz see this answer for Google Sheets version.