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
| 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 | 10100 |
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.
| Date | Side | Qty | Price | Value | Holding | Avg Price |
|---|---|---|---|---|---|---|
| 1-Jul | Buy | 75 | 10000 | 750000 | 75 | 10000 |
| 2-Jul | Buy | 75 | 10200 | 765000 | 150 | 10100 |
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
on error,if bs.qty<=0but then all cells results in error. – Wristwatch