Search code examples
excelvbauser-defined-functionsstockfifo

Calculate average invested price without helper columns/tables

This post is really worth a read. Another better verion of this topic is available here.

If you look at the table below, I am trying to find Avg price at every transaction without adding helper columns. The Average price is correct when the side is Buy but shows incorrect Avg price on the Sell side for which I am looking for a formula, array formula or UDF for the Avg Price column.

Date Side Qty Price Value Holding Avg Price
1-Jul Buy 225 10000 2250000 225 10000
2-Jul Buy 75 10200 765000 300 10050
3-Jul Sell -150 9950 -1492500 150 10150

The formula I have for Value is =E3*D3, for Holding is =SUM($D$3:D3) and for Avg price is =SUMPRODUCT($D$3:D3,$E$3:E3)/SUM($D$3:$D3) which I dragged downwards. Everything seems to be correct except the last value 10150. Ideally it should have 10,100 as per FIFO logic given below.

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. 22,50,000.00

2nd trade: Rs. 7,65,000.00

Total quantity = 300

Total value for first two orders : Rs. 30,15,000.00

Divide total value by total quantity:

Rs. 30,15,000.00 ÷ 300 = Rs.10,050.00 (Did it using the =sumproduct formula)

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.

Date Side Qty Price Value Holding Avg Price
1-Jul Buy 75 10000 750000 75 10000
2-Jul Buy 75 10200 765000 150 10100

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

Now to get a solution to this, additional helper columns or helper tables are needed which I am looking to eradicate and find for a formula or an array formula or an UDF to calculate the Avg Price. I request excel experts to help me with this problem.

One more example what I am trying is given below where the invested price is showing incorrect:

Date Side Qty Price Value Holding Avg Price
1-Jul Buy 5 10 50 5 10
2-Jul Sell -3 17 -51 2 -0.5
3-Jul Buy 17 3 51 19 2.63
4-Jul Sell -15 7.8 -117 4 -16.75

Edit

Done after getting a solution from @Tom Sharpe

In order to get the average price, I declared two variables avgRate and sumRate as double and modified the For Each code a bit. Please advice if there is an efficient way to do it. Appreciate if this can be converted into an UDF so that I don't have to run the code again and again. Thank you so much for the wonderful solution.

For Each bs In queue
    Debug.Print ("qty=" & bs.qty)
    Debug.Print ("rate=" & bs.rate)
    avgRate = avgRate + (bs.qty * bs.rate)
    sumRate = sumRate + bs.qty
    Debug.Print avgRate / sumRate
Next
Solution

  • OK well here is a test version of a VBA implementation.

    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.

    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

    The following goes in a normal module.

    Option Explicit


    Sub FifoTrading()
    
        ' 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
        
        For i = 2 To 5
        Debug.Print (Cells(i, 3).Value())
        Debug.Print (Cells(i, 4).Value())
        
            rate = Cells(i, 4).Value()
            qty = Cells(i, 3).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
        

        
        For Each bs In queue
            Debug.Print ("qty=" & bs.qty)
            Debug.Print ("rate=" & bs.rate)
        Next

        avRate = 0
        totQty = 0
    
        For Each bs In queue
            avRate = avRate + bs.qty * bs.rate
            totQty = totQty + bs.qty
        Next
    
        avRate = avRate / totQty
    
        Debug.Print ("average=" & avRate)
    
    
    End Sub

    For the first table, the output is

    enter image description here

    so the average rate is 10100.

    For the second table, the output is

    enter image description here

    so the average rate is 3.

    EDIT

    Here is the UDF version which is called as

    =avRate(qtyRange,rateRange)

    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