An error in the formula of MTM of the Pair trading in Excel file

General
1

Course Name: Statistical Arbitrage Trading, Section No: 3, Unit No: 5, Unit type: WaterMarkVideo

There is an error in the formula of MTM of the Pair trading in Excel file. Let’s use MTM of row 15 as an example. It should be = =IF(M14=“Buy”, (K14-D15)$C$9+(C15-J14)$C$8, IF(M14=“Sell”, (K14-C15)$C$8+(D15-J14)$C$9, “”))
Can you please confirm?

Thanks

2

Hi Winnie,

Thank you for pointing this out and for sharing the corrected formula. You’re right, the MTM formula in the Excel sheet is conceptually incorrect, and your observation is valid.

What was wrong

In the original file, the order sizes were applied to the wrong legs while calculating MTM:

  • The Aluminium price change was multiplied by the Lead order size
  • The Lead price change was multiplied by the Aluminium order size

Each leg’s P&L should always be multiplied by its own order size.

Why this wasn’t noticed earlier

In the provided Excel template, both Aluminium and Lead order sizes are set to the same value (5000). Because the quantities were identical, swapping them did not change the numerical MTM values, which is why the issue went unnoticed.

What has been corrected

Your suggested formula correctly:

  • Applies Aluminium P&L × Aluminium order size
  • Applies Lead P&L × Lead order size

This makes the MTM calculation logically correct and robust, especially when the two order sizes differ, which is the more realistic case.

We’ll update the Excel file in the course material to reflect this correction shortly. Meanwhile, please continue using the corrected formula you’ve suggested. Thanks again for catching this and helping us improve the learning material.

3

Hi Arundhati,

Thank you for the confirmation.

Warm regards,
Winnie Sun

4

Hey Winnie

We have updated the changes suggested by you for both our course video and the excel file. Thanks once again for pointing it out.

Regards

1 Like