I’m trying to create an excel sheet for backtesting trading ideas and I’m having trouble figuring out the PnL formulas.
I’m primarily using Bitmex so not sure if formulas are the same across other leveraged trading platforms.
I basically want to have a simple sheet where I can start with a certain amount then go long or short (1x) on each historical price that my chosen indicator signals. Then compound that amount over years to get a simple idea of how a strategy might work if strictly adhered to.
(I’m not including fees for the sake of simplicity at this stage.)
I’ve tried to reverse engineer the “ministry of margin trading” PnL calculator but can’t figure out a few key formulas.
Can someone please tell me what formula I need to add to an excel cell to automatically calculate the profit or loss from a Long or short in Bitmex?
If I’m just buying & selling this is easy: =Exit price - Entry price
But with Bitmex PnL calculators the results seem to be quite complicated.
I’m guessing it needs to be = ( (Exit Price - Entry Price) / Something? ) X Leverage
I’d also be grateful if someone can point me towards an existing (unlocked) excel sheet online that I could download for backtesting.
Also very keen to figure out how to code backtests in Trading View, but this is also taking a bit of work. Any tips or links here also greatly appreciated.
Thanks!