Can you post your spreadsheet. Are you adding a new row to your spreadsheet
for the new BUY? You need to both rows to make the calculation. You should
have all purchases for the same bond on adjacent rows of the spreadsheet.
The average price should be on the last row of each bond and the calculation
should look at each row of the bond to make that calculation.

"franky" wrote:

Hi guys.

I am struggling with a problem and would appreciate any help.
Basically I want to make a system that can keep track of profit loss
on stock/bond transactions.I have made a userform where I input values
and choose whether a transaction is BUY or SELL. From here I want my
model to:

1. scan column A to see if a position in the respective paper already
exists by matching a value from the userform.

2. If a position exists a new "average price" should be calculated.
Say I already have a position in 1 bond which I purchased for 100. Now
I buy another bond at 110. The new average price should be 105 (100/2
+ 110/2) or (Average price/Total amount + new price/Total amount)

This value should then overwrite the existing "average price" value in
the same row in a column to the right.

Now if I later sell an amount say the 1 bound at 90. My total loss
will be -15 (90-105)*1 or (price - average price)*total amount. This
loss should be put into a "profit loss" column in the same row - and
pooled together with any other existing profit/loss.

Hope I have made my problem clear enough. Many thanks in advance.