Expectancy Really Means, Lesson 10
Building an Expectancy Spreadsheet from Scratch
Expectancy quantifies the average profit or loss per trade. A positive expectancy indicates a profitable system over a large sample size. A negative expectancy suggests a losing system. This lesson details constructing an expectancy spreadsheet.
Core Expectancy Formula
Expectancy (E) is calculated as:
E = (Probability of Win * Average Win) - (Probability of Loss * Average Loss)
This formula requires four inputs:
- Probability of Win (P_win): Total winning trades / Total trades
- Average Win (Avg_win): Sum of all winning trade profits / Total winning trades
- Probability of Loss (P_loss): Total losing trades / Total trades
- Average Loss (Avg_loss): Sum of all losing trade losses / Total losing trades
Note that P_win + P_loss = 1.
Spreadsheet Setup: Data Entry
Begin with a spreadsheet. Create columns for each trade.
| Trade # | Date | Instrument | Direction | Entry Price | Exit Price | Shares/Contracts | P&L (Gross) | Commissions | P&L (Net) | Win/Loss |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ||||||||||
| 2 | ||||||||||
| ... |
P&L (Gross): (Exit Price - Entry Price) * Shares/Contracts for long trades. (Entry Price - Exit Price) * Shares/Contracts for short trades. Commissions: Fixed cost per trade or per share/contract. Example: $0.005 per share, $2.50 per contract. P&L (Net): P&L (Gross) - Commissions. Win/Loss: A simple indicator. "Win" for P&L (Net) > 0, "Loss" for P&L (Net) < 0.
Spreadsheet Setup: Calculation Block
Create a separate section for calculations.
| Metric | Value | Formula |
|---|---|---|
| Total Trades | ||
| Total Winning Trades | ||
| Total Losing Trades | ||
| Total Net Profit/Loss | ||
| Probability of Win (P_win) | ||
| Probability of Loss (P_loss) | ||
| Sum of Winning Profits | ||
| Sum of Losing Losses | ||
| Average Win (Avg_win) | ||
| Average Loss (Avg_loss) | ||
| Expectancy (E) | ||
| Expectancy (in R-Multiples) |
Step-by-Step Calculation Example
Consider a futures trader with 10 trades on ES (E-mini S&P 500 futures). Each contract has a point value of $50. Commissions are $2.50 per side per contract ($5.00 round trip).
Trade Data:
| Trade # | Date | Instrument | Direction | Entry Price | Exit Price | Contracts | P&L (Gross) | Commissions | P&L (Net) | Win/Loss |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2023-10-26 | ES | Long | 4300.00 | 4305.00 | 2 | $500.00 | $10.00 | $490.00 | Win |
| 2 | 2023-10-26 | ES | Short | 4302.00 | 4300.50 | 1 | $75.00 | $5.00 | $70.00 | Win |
| 3 | 2023-10-27 | ES | Long | 4310.00 | 4308.00 | 3 | -$300.00 | $15.00 | -$315.00 | Loss |
| 4 | 2023-10-27 | ES | Short | 4315.00 | 4316.50 | 1 | -$75.00 | $5.00 | -$80.00 | Loss |
| 5 | 2023-10-28 | ES | Long | 4320.00 | 4323.00 | 2 | $300.00 | $10.00 | $290.00 | Win |
| 6 | 2023-10-28 | ES | Short | 4325.00 | 4324.00 | 1 | $50.00 | $5.00 | $45.00 | Win |
| 7 | 2023-10-30 | ES | Long | 4330.00 | 4328.50 | 2 | -$150.00 | $10.00 | -$160.00 | Loss |
| 8 | 2023-10-30 | ES | Short | 4332.00 | 4330.00 | 1 | $100.00 | $5.00 | $95.00 | Win |
| 9 | 2023-10-31 | ES | Long | 4335.00 | 4337.50 | 3 | $375.00 | $15.00 | $360.00 | Win |
| 10 | 2023-10-31 | ES | Short | 4338.00 | 4339.00 | 2 | -$100.00 | $10.00 | -$110.00 | Loss |
Calculations:
- Total Trades: Count of all entries in "Trade #" column = 10.
- Total Winning Trades: Count of "Win" in "Win/Loss" column = 6.
- Total Losing Trades: Count of "Loss" in "Win/Loss" column = 4.
- Total Net Profit/Loss: Sum of "P&L (Net)" column = $490 + $70 - $315 - $80 + $290 + $45 - $160 + $95 + $360 - $110 = $785.
- Probability of Win (P_win): Total Winning Trades / Total Trades = 6 / 10 = 0.60 (or 60%).
- Probability of Loss (P_loss): Total Losing Trades / Total Trades = 4 / 10 = 0.40 (or 40%).
- Sum of Winning Profits: Sum of positive "P&L (Net)" values = $490 + $70 + $290 + $45 + $95 + $360 = $1350.
- Sum of Losing Losses: Sum of absolute values of negative "P&L (Net)" values = $315 + $80 + $160 + $110 = $665.
- Average Win (Avg_win): Sum of Winning Profits / Total Winning Trades = $1350 / 6 = $225.00.
- Average Loss (Avg_loss): Sum of Losing Losses / Total Losing Trades = $665 / 4 = $166.25.
- Expectancy (E): (P_win * Avg_win) - (P_loss *
