The formula
P&L ($) (fill down)ExcelGoogle Sheets
=(C2-B2)*D2*IF(A2="Short",-1,1)In G2 → $640.00
P&L % (fill down)ExcelGoogle Sheets
=(C2/B2-1)*IF(A2="Short",-1,1)In H2 → 10%
Return on margin (ROE) (fill down)ExcelGoogle Sheets
=H2*E2In I2 → 50%
✓ Tested. All 6 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Side | Entry | Exit | Qty | Leverage | ||||
| 2 | Long | 3200 | 3520 | 2 | 5 | $640.00 | 10% | 50% | |
| 3 | Short | 3200 | 3520 | 2 | 5 | -$640.00 | -10% | -50% |
How it works
- (C2-B2)*D2
- Price change × quantity.
- IF(A2="Short",-1,1)
- Shorts profit when the price falls.
- H2*E2
- With leverage, the % return on your margin is the price % × leverage (before fees and funding).
Common errors and fixes
Short trades show the wrong sign?
Side text doesn't match exactly ("short " with a space). Use TRIM(A2).
ROE doesn't match the exchange?
Exchanges include fees, funding and may use mark price.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.