Calculate Realized and Unrealized Profit/Loss

[Lammy]

Hi everyone,

Thanks for taking time out of your day to help.

I am stuck at this task where I need to calculate the Unrealized Profit/Loss (also called Mark-To-Market) and Realized Profit/Loss.

Consider the following example showing the PnL over time (A) of an hypothetical stock XYZ with the number of shares (Column E) and Price (Column B).

I highlighted the columns green that I want to achieve in my calculation. You should see that the "Net Position" can also be negative (i.e. "short selling"), hence if the price drops, the PnL increases.
I buy 5 shares on 02.01.2022 and sell 10 shares on the 04.01.2022. This means I have a realized loss of 15 € and at the same time a short position of 5 shares. I hope the calculation makes sense
Unit Cost referes to the price of the share when buying/selling.

I hope someone can guide me in the right direction.

Best Regards,

Björn

 

[Lammy]

Apologies,

There was a mistake in the screenshot above. The expected ouput (column L/M) is cumulative. Hence the value in K6 should also be in K7/K8. This also affects the total PnL (column L)